Need help speeding up a query (FROM_UNIXTIME)

user_13113539 0
Viewed 88

I have a query which requires the usage of FROM_UNIXTIME and it takes at least 0.2-0.5 seconds and I need to use the same query with a different where statement at least 10 times on the page.

Here is the query

FROM `Users`
WHERE `Referrer` = '220048'
AND FROM_UNIXTIME(JoinDate, '%Y-%m-%d') = CURDATE()
AND `IP` NOT IN('IP', 'IP', 'IP')
AND `RegisterIP` NOT IN('IP', 'IP', 'IP')
AND `IP` != ''
AND `RegisterIP` != ''

How would I change the FROM_UNIXTIME to do the exact same thing but faster (Database contains around 900K rows)

Thanks in advance!

database-performance datetime mysql
3 Answers

In general, you can ease the task if you follow simple rules for the WHERE clause:

  • Do not place the function call in the left part of the comparison. so change it to this: JoinDate = UNIX_TIMESTAMP(CURDATE())
  • Put your parts, joined by "AND" in order which is used in a compound index. Just for example: if you have a KEY(JoinDate, Referrer), then start your "where" clause from the same order of fields: JoinDate = ... AND Referrer = ... The rest of the field "where" list can vary, but the begining is very important.

In this case, it is very likely that the MySQL engine will choose the effective execution plan, utilizing the index.

user_2728850

Try:

  1. index on JoinDate
  2. use something like JoinDate = TO_UNIXTIME(CURDATE())

Theory being that the optimzer only has to do the conversion once and can use the index.

user_43720630 
WHERE `Referrer` = '220048'
AND FROM_UNIXTIME(JoinDate, '%Y-%m-%d') = CURDATE()
AND `IP` NOT IN('IP', 'IP', 'IP')
AND `RegisterIP` NOT IN('IP', 'IP', 'IP')
AND `IP` != ''
AND `RegisterIP` != ''

WHERE `Referrer` = '220048'
  AND JoinDate = UNIX_TIMESTAMP(CURDATE())  -- assuming `JoinDate` is a DATE
  AND `IP` NOT IN('IP', 'IP', 'IP', '')
  AND `RegisterIP` NOT IN('IP', 'IP', 'IP', '')

and have

INDEX(Referrer, JoinDate)
user_17668310
Related