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.
5 1499
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.
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
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
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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',...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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
-- -------------- -------------- ...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
|
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...
|
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...
| |