News for hackers. Stuff that matters.
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).

See http://msdn.microsoft.com/en-us/library/ms188783.aspx