By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,066 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,066 IT Pros & Developers. It's quick & easy.

Help with simplifying this query

HaLo2FrEeEk
100+
P: 404
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT date FROM `current_daily`
  2. WHERE daily1 = #
  3. OR daily2 = #
  4. OR daily3 = #
  5. OR daily4 = #
  6. 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.
Mar 30 '11 #1
Share this Question
Share on Google+
5 Replies


dgreenhouse
Expert 100+
P: 250
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.
Apr 6 '11 #2

HaLo2FrEeEk
100+
P: 404
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
Apr 6 '11 #3

dgreenhouse
Expert 100+
P: 250
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
Apr 6 '11 #4

HaLo2FrEeEk
100+
P: 404
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.
Apr 6 '11 #5

dgreenhouse
Expert 100+
P: 250
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.
Apr 13 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.