Mysql optimize date between query

Question

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 ?

You can use cache dependency with mysql. It means that you will keep your results in cache until the table changes. — Offir
– Offir, Commented Sep 19, 2016 at 12:32

Gordon Linoff · Accepted Answer · 2016-09-19 12:08:33Z

1

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).

answered Sep 19, 2016 at 12:08

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vince Carter Over a year ago

I agree with you about the readability, but this query does not help me. Is still needs to search in around 5000 rows. I am not sure if my query can be optimized more ...

Gordon Linoff Over a year ago

@VinceCarter . . . Did you add the indexes?

Vince Carter Over a year ago

yes, now the situation hasn't changed a lot: 1 SIMPLE b range user,date,type,type_2 type_2 5 NULL 4759 Using index condition 1 SIMPLE m eq_ref PRIMARY,id PRIMARY 4 b.user 1 Using where

Willem Renzema Over a year ago

@VinceCarter Does the number of rows matter if it is fast? What is the execution time of your query now? What was the execution time of your original query, before the indexes? Make sure to run each at least twice, and discard the first result of each, else the buffer cache will distort the results.

Collectives™ on Stack Overflow

Mysql optimize date between query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related