Sorted index pages - physically reorder on disk?

MySQL & MariaDB Other MySQL® Questions
1

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 in advance.

2

During index range scans there are in fact two accesses one is index access and other is row data access.

Sorting index will help with having disk accesses more sequential but data may well require a lot of seeks.

This is one of the reasons covering indexes (when all referenced and retrieved columns are in the index) are so helpful.

In MyISAM you can also sort data in the same order as and index by using myisamchk --sort-records

But it of course only can be used for one index/access order.

3

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.

4

Some questions:
How large is your DB actually?

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.

5

Sterin,

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.

6

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?