CODEWITHSUNDEEP
mysqlsearch
Ahmed Al-haddad
Ahmed Al-haddad

Reputation: 833

How to decrease the searching time in MySQL DB when searching for a range of dates?

I performed a search on MySQL on the following table that has following columns and indexes:

columns

indexes

The query was the following, please notice the number of days as 6: 

SELECT * FROM data WHERE TEMP<"3000" AND TEMP>"2600" AND Date_Time >"2016-05-05 %" and Date_Time <"2016-05-11 %";

This query returns the results in 0.59s

If I change the number of days to more than that like in this query then I will get the result in a lot more time 2.15s

SELECT * FROM data WHERE TEMP<"3000" AND TEMP>"2600" AND Date_Time >"2016-05-04 %" and Date_Time <"2016-05-11 %";

How do I fix this issue? I find it absurd that only another day affects the speed that much! I am aiming to search in a wider range of time, so I somewhat need to know the reason behind this.

explain statements

Upvotes: 3

Views: 1344

Answers (1)

fathineos
fathineos

Reputation: 36

Fact 1: MySQL cannot use more than 1 index in your case
As explained in this blog post https://www.percona.com/blog/2009/09/12/3-ways-mysql-uses-indexes/

if you have index on (A,B) This index can be used to lookup rows for WHERE clauses like A=5 ; A BETWEEN 5 AND 10 ; A=5 AND B BETWEEN 5 AND 10 it however will NOT be able to help lookup rows for B BETWEEN 5 AND 10 predicate because it is not index prefix.

So, in your case that you have the columns TEMP and Date_Time and you search by range, the range index will be used for either TEMP or Date_Time and the second part of the query will be executed using where. This is structural limitation of BTREE indexes.

Fact 2: The index is not covering your query, which means query cannot be executed using only data from the index without reading the row itself

A full table scan is performed using reads from the index to look up data rows > in index order. Uses index does not appear in the Extra column. http://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_range

So what happens in your first query:
1. MySQL will lookup for Date_Time in Date_Time index and choose the index_rows that fit 'Date_Time >"2016-05-05 %" and Date_Time <"2016-05-11 %"'
2. In the index the TEMP info is not available, so MySQL needs to collect all the index_rows from the data table.
3. MySQL will evaluate the condition 'TEMP<"3000" AND TEMP>"2600"' on each one of these rows.

What happens in the second query?
MySQL decides not to use any of the available indexes (possible_keys) and performs a full table scan to return the values. This means that each row of the table will be scanned to check if matches the conditions TEMP<"3000" AND TEMP>"2600" AND Date_Time >"2016-05-04 %" and Date_Time <"2016-05-11 %". This probably happens because there is no value with prefix "2016-05-11" or "2016-05-04".

Upvotes: 1

Related Questions