0

I have a table with around 50 million of rows, and I need to do queries often. I realize that when I determine what is the minimum ID in where clause, the query is very faster.

For example:

select * from huge_table 
 where date <= '2016-03-01' 
 and date >= '2016-03-01 23:59:59';

However, if I know beforehand the lowest corresponding id, and use it as a where clause, thus here:

select * from huge_table 
 where id >= 
 (select id from huge_table where date >= '2016-03-01' limit 1) 
 and date <= '2016-03-01' 
 and date >= '2016-03-01 23:59:59';

the query is very faster.

Is it a known tuning approach? Has a name?

update:

There are indexes on id and date columns.

Here the EXPLAIN: First query (without ID)

id   select_type table type     possible_keys            key        key_len ref   rows        Extra
'2', 'DERIVED',  't',  'ALL',   'PRIMARY,username,date', NULL,      NULL,   NULL, '21535250', 'Using where'

Second query (using ID):

id   select_type table type     possible_keys            key        key_len ref   rows        Extra
'2', 'DERIVED',  't',  'range', 'PRIMARY,username,date', 'PRIMARY', '8',    NULL, '5635840',  'Using where'
deldev
  • 1,296
  • 18
  • 27

0 Answers0