Re:MySQL and partitioning tables with millions of rows
by lsb on Sunday January 31, 2010 @04:25PM
(#1091133)
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.
Re:MySQL and partitioning tables with millions of rows
by kogir on Sunday January 31, 2010 @04:32PM
(#1091138)
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).
Partitioning is a rather complex solution to his problem. See the limitations at 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.
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