CODEWITHSUNDEEP
mysqlquery-optimization
Vince Carter
Vince Carter

Reputation: 946

Mysql optimize date between query

I need to optimize a MySQL query. I have a table 'betting' with less than 100.000 records. I need to display all records for the current month. 

SELECT betting.id 
FROM betting, members 
WHERE members.status = 1 
and members.id = betting.user 
and betting.date between '2016-09-01' and '2016-09-30' 
and betting.status = 1 
and betting.type = 1;

When i try the explain command:

SIMPLE  betting range   user,date,type,type_2   type_2  7   NULL    4966    Using where
SIMPLE  members eq_ref  PRIMARY PRIMARY 4   betting.user    1   Using where

Is there any chance to reduce the database searches ? The problem is that the page which displays this results is called too often and that causes a lot of CPU costs. Unfortunately i cannot cache the results, because they need to be live. Any ideas ?

Upvotes: 0

Views: 748

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Here is your query written in a format that is easier (at least for me) to understand:

SELECT b.id 
FROM betting b join
     members m
     ON m.id = b.user 
WHERE m.status = 1  
      b.date between '2016-09-01' and '2016-09-30' and
      b.status = 1 and
      b.type = 1;

I think the best indexing strategy is: betting(status, type, date, user) and members(id, status).

Upvotes: 1

Related Questions