This may apply to other engines but I’m primarily focused on myisam. When using optimize table or myisamchk --sort-index, the manual states “Sort index blocks. This speeds up ‘read-next’ in applications.” During this process, does mysql physically sort the index pages on disk to improve sequential io?
Ex: a range lookup on a single column index… it would be great if this was sequential and only required one seek.
Thanks Peter… currently the bottleneck is IO so anything that can limit the # of seeks and/or perform sequential IO is a benefit.
Next, I’d like to look at the index block sizes and see if changing the value other than 1024 will be a benefit.
How much RAM do you have available on that machine?
What is your key_buffer_size set to?
Are you sure that sorts or group by queries aren’t creating temporary tables on disk?
Reason for asking is that it is pretty common to miss that the default sort_buffer_size setting can be pretty small and then mysql creates an on disk temporary table used for sorting that slows down things a lot.
I was focusing on simple range queries (WHERE, AND, BETWEEN…) not ORDER BY and GROUP BY queries. You are correct that those queries can cause temp tables and the SQL layer variables are important (sort_buffer, read_buffer, etc…) and may require changes for optimal performance.
OK, I read that IO was the bottleneck and didn’t know if you had really boiled it down to what was consuming IO.
Since if your data changes it can be a pretty big overhead to resort it all the time.
Out of curiosity how large is the DB compared to RAM size?