# Sorted index pages - physically reorder on disk?

**URL:** <https://forums.percona.com/t/sorted-index-pages-physically-reorder-on-disk/412>\
**Category:** Other MySQL® Questions\
**Created:** [August 13, 2007, 2:23pm UTC](https://forums.percona.com/t/sorted-index-pages-physically-reorder-on-disk/412 "2007-08-13T14:23:37Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![sean](https://avatars.discourse-cdn.com/v4/letter/s/f0a364/32.png) [@sean](https://forums.percona.com/u/sean)\
**Post date:** [August 13, 2007, 2:23pm UTC](https://forums.percona.com/t/sorted-index-pages-physically-reorder-on-disk/412/1 "2007-08-13T14:23:37Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [August 16, 2007, 3:55am UTC](https://forums.percona.com/t/sorted-index-pages-physically-reorder-on-disk/412/2 "2007-08-16T03:55:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![sean](https://avatars.discourse-cdn.com/v4/letter/s/f0a364/32.png) [@sean](https://forums.percona.com/u/sean)\
**Post date:** [August 20, 2007, 10:31am UTC](https://forums.percona.com/t/sorted-index-pages-physically-reorder-on-disk/412/3 "2007-08-20T10:31:56Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [August 20, 2007, 5:08pm UTC](https://forums.percona.com/t/sorted-index-pages-physically-reorder-on-disk/412/4 "2007-08-20T17:08:39Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![sean](https://avatars.discourse-cdn.com/v4/letter/s/f0a364/32.png) [@sean](https://forums.percona.com/u/sean)\
**Post date:** [August 20, 2007, 5:14pm UTC](https://forums.percona.com/t/sorted-index-pages-physically-reorder-on-disk/412/5 "2007-08-20T17:14:57Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [August 20, 2007, 5:44pm UTC](https://forums.percona.com/t/sorted-index-pages-physically-reorder-on-disk/412/6 "2007-08-20T17:44:33Z")

</div>

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?
