| Sorted index pages - physically reorder on disk? [message #1584] |
Mon, 13 August 2007 15:53  |
sean
Messages: 6
Registered: January 2007 |
Junior Member |
|
|
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.
|
|
|
| Re: Sorted index pages - physically reorder on disk? [message #1586 is a reply to message #1584 ] |
Thu, 16 August 2007 05:25  |
 Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
| Re: Sorted index pages - physically reorder on disk? [message #1652 is a reply to message #1586 ] |
Mon, 20 August 2007 12:01 |
sean
Messages: 6
Registered: January 2007 |
Junior Member |
|
|
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.
|
|
|
|
| Re: Sorted index pages - physically reorder on disk? [message #1654 is a reply to message #1653 ] |
Mon, 20 August 2007 18:44 |
sean
Messages: 6
Registered: January 2007 |
Junior Member |
|
|
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.
|
|
|
| Re: Sorted index pages - physically reorder on disk? [message #1655 is a reply to message #1584 ] |
Mon, 20 August 2007 19:14 |
sterin
Messages: 324
Registered: March 2007
Location: Sweden |
Senior Member |
|
|
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?
|
|
|
Members
Search
Help
Register
Login
Home
