Help with simplifying this query

[HaLo2FrEeEk]

I'm tracking challenges for a video game. There are 4 daily challenges and 1 weekly challenge. They update at 3am PST. I have a cron job that runs at 5 after to make sure the new information is in the API before I make the request, and I store the challenges in a table in the database, with an id. If the challenge is already present in the table, I don't insert it again, I just get the existing row's id. I have another table that contains the timestamp for when the challenge became active, theexpiration timestamp, and 4 columns for the 4 id's (and the same for weekly except one column for the id). Since challenges are reused, I'd like to be able to see what other days a given challenge was used for, currently I'm using this query, and I'm not happy with it:

SELECT date FROM `current_daily`
WHERE daily1 = #
OR daily2 = #
OR daily3 = #
OR daily4 = #
ORDER BY date ASC

Where # is the id of a certain challenge. Weekly challenges are never reused, so I ignore them. Anyway, this works, but it's just so clunky. I was reading about FULLTEXT searches and was wondering if I could do something similar with this. I know FULLTEXT can't be used on integer columns, but is there anything equivalent?

Much obliged.

[dgreenhouse]

I don't see where that can be any easier since you're testing different columns.

If you had one column that takes on the values that daily1 through daily4 do,
then you could use the select statement's "IN" clause.

i.e.

SELECT date FROM current_daily
WHERE daily IN (#1,#2,#3,#4)
ORDER BY date ASC

You could reconstruct the query,
but my thought is that it would end up being more
complicated than what you're already doing.

[HaLo2FrEeEk]

Actually, I didn't know that IN() could be used like this, but it turns out that:

SELECT date FROM current_daily WHERE # IN(daily1, daily2, daily3, daily4) ORDER BY date ASC

That works great. The # is the same, so for example I might be checking for all dates where id 10 was used, so I'd do this:

SELECT date FROM current_daily WHERE 10 IN(daily1, daily2, daily3, daily4) ORDER BY date ASC

[dgreenhouse]

I'm glad that worked... I didn't think of using it that way either, but of course, that's the benefit of relational algebra.

I'd be curious which method returns faster results.

Try using explain with both queries to see what differences the engine notes.

i.e.

EXPLAIN SELECT date FROM current_daily WHERE 10 IN(daily1, daily2, daily3, daily4) ORDER BY date ASC

versus...

EXPLAIN SELECT date FROM `current_daily`
WHERE daily1 = 10
OR daily2 = 10
OR daily3 = 10
OR daily4 = 10
ORDER BY date ASC

[HaLo2FrEeEk]

The queries take exactly the same amount of time, since all the IN() is doing is grouping the id = # conditions together. The explain results were identical.

[dgreenhouse]

That's kind-of what I expected...

The database engine is pretty smart about optimizing queries, and as you've noted, the queries are for the
most part functionally identical.