3 ms·

While the write up was good, the author makes assumptions about what's happening seemingly without checking. He didn't post the actual query or the original an

by kogir 17y ago

While the write up was good, the author makes assumptions about what's happening seemingly without checking. He didn't post the actual query or the original and final query plans. Partitioning is a rather complex solution to his problem. See the limitations at http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitati... I can't know for sure since there's not enough information there, but I'd suspect a simple index would have done the trick. CREATE INDEX my_index ON Location (device_id, date_added desc) SELECT * FROM Locations USE INDEX (my_index) WHERE device_id = ... ORDER BY date_added DESC LIMIT 6 SELECT * FROM Locations USE INDEX (my_index) WHERE device_id = ... AND date_added BETWEEN ... AND ... ORDER BY date_added DESC I'm assuming that he had the index and it just wasn't getting used, so the index hints are there in case the query planner was somehow missing them. That said, table partitioning is an awesome feature. I've seen it be most useful when you spread the partitions over tiered storage (latest on SSD, archive on spindles), or when you want to drop a whole range of data quickly on a regular basis (like last month's logs). * Edit: Missed something: You may need to specify more hints than I originally thought if you want to force index usage for the order by clause as well. See http://dev.mysql.com/doc/refman/5.1/en/index-hints.html http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

lsb 17y ago
That's entirely right; to explain a bit more, the reason that index is good is because it's a covering index. You have all the data you need to pull from the table in the index (which you've found via binary search through your B+tree), so you don't need to run out again to the table, and that saves an extra disk seek. To make a covering index takes longer, so insertion speed drops, but that shouldn't be the hotspot.
- kogir 17y ago
  Actually, since he was effectively doing SELECT * ... it still has to go pull the rows from the table after seeking the index. This likely isn't a huge problem though. To prevent going back to the table in this case, you'd either need the index I mentioned to be the primary key, or a construct like INCLUDE (which mysql appears to lack). See http://msdn.microsoft.com/en-us/library/ms188783.aspx http://msdn.microsoft.com/en-us/library/ms188783.aspx

by kogir 17y ago

lsb 17y ago

That's entirely right; to explain a bit more, the reason that index is good is because it's a covering index. You have all the data you need to pull from the table in the index (which you've found via binary search through your B+tree), so you don't need to run out again to the table, and that saves an extra disk seek. To make a covering index takes longer, so insertion speed drops, but that shouldn't be the hotspot.

kogir 17y ago
Actually, since he was effectively doing SELECT * ... it still has to go pull the rows from the table after seeking the index. This likely isn't a huge problem though. To prevent going back to the table in this case, you'd either need the index I mentioned to be the primary key, or a construct like INCLUDE (which mysql appears to lack). See http://msdn.microsoft.com/en-us/library/ms188783.aspx http://msdn.microsoft.com/en-us/library/ms188783.aspx