473,323 Members | 1,574 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Help with simplifying this query

HaLo2FrEeEk
404 256MB
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
5 1499
dgreenhouse
250 Expert 100+
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
404 256MB
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
250 Expert 100+
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
404 256MB
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
250 Expert 100+
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

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

Similar topics

9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
4
by: sah | last post by:
I need some help with the following query: DECLARE @SRV VARCHAR(20), @date smalldatetime SET @SRV = (select @@servername) SET @date = '20040901' select Srv_Name = @SRV, DB_Name = 'DB_NAME',...
7
by: Dave Hopper | last post by:
Hi I posted a question recently regarding problems I am having getting a value from a list box to use in a query. I got a lot of help, for which I thank you and it's nearly working! But I need...
5
by: Peter | last post by:
I have a query that returns: Turner Completion Turner Interim 1 Kemp Completion Steel Completion Steel Interim 1 Steel Completion Steel Interim 1 Webb Completion
5
by: Bob Weisenburger | last post by:
I have a table of "memos". each record in that table has a primary key "memo ID" that is autonumber. I have another table that is "memo receipts" with each record having a "receipt id" field that...
10
by: L. R. Du Broff | last post by:
I own a small business. Need to track a few hundred pieces of rental equipment that can be in any of a few dozen locations. I'm an old-time C language programmer (UNIX environment). If the only...
0
by: Rational Repairs | last post by:
I have a query situation I can't, for the life of me, figure out. Any help would be greatly appretiated. I have a table of customers, and a seperate table of orders. An order consists of (among...
23
by: mlcampeau | last post by:
Hey guys, I have been mulling over this problem for a few days and have yet to come up with a query that will give me the expected results. I am working on a database that stores employee...
3
by: pbd22 | last post by:
Hi. I need some help with structuring my query strings. I have a form with a search bar and some links. Each link is a search type (such as "community"). The HREF for the link's anchor looks...
6
by: Daveo | last post by:
Hi there, I have a query (say it's called "Query1") that contains data in the following structure: id reference scoretype score -- -------------- -------------- ...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.