Timeline for InnoDB SELECT from disk slow for large table but MyISAM is fast
Current License: CC BY-SA 3.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Feb 10, 2017 at 0:09 | comment | added | Rick James | It is hard to optimize time ranges involving two columns. | |
| Feb 10, 2017 at 0:09 | comment | added | Rick James |
That implies that To is better at finding the rows than From. But, look at the other occurrences of this query -- will they work as well, or does From sometimes work better? It sounds like there are 123 rows with ServiceTo >= '2016-01-01' and 254-123 before that cutoff. Meanwhile, none match ServiceFrom <= '2017-01-01'.
|
|
| Feb 9, 2017 at 23:05 | comment | added | TheCommunistCow | @Rick James I'll add the new handler results to the original post. | |
| Feb 9, 2017 at 23:05 | comment | added | TheCommunistCow | Thanks @RolandoMySQLDBA, that helped a good amount, the query went from 2-2.5 seconds to around 1 second, which is definitely better but still worse than the performance I got from MyISAM or SQL Server. I'll mark this as the accepted answer if no one has any other insights by tomorrow. | |
| Feb 9, 2017 at 22:51 | comment | added | Rick James | But according to the Handler numbers, only 254 rows (presumably in the index) were touched to get the 123 rows of output. This says that the original index order was at least pretty good. @TheCommunistCow - please run the Handler trick with Rolando's rearranged index. | |
| Feb 9, 2017 at 17:16 | history | edited | RolandoMySQLDBA | CC BY-SA 3.0 |
added 28 characters in body
|
| Feb 9, 2017 at 17:10 | history | answered | RolandoMySQLDBA | CC BY-SA 3.0 |