MySQL Forums
Forum List  »  Performance

Re: How MySQL Uses Indexes
Posted by: Harrison Fisk
Date: June 20, 2005 06:33PM

Hi,

Please see my responses below.

mathieu wrote:
> Hi,
>
> I don't know if I'd rather post this topic in the
> newbie section or in this one, I hope you don't
> mind if I'm mistaken.
>
> I'm reading the mysql documention about indexes,
> and I don't understand the following part of the
> "How MySQL Uses Indexes" chapter.
>
> "without an index, MySQL has to start with the
> first record and then read through the whole table
> to find the relevant rows."
>
> Does it mean that, without an index, mysql will
> seek the data through the whole row, even the
> fields that are not selected or that don't appear
> in the where clause ?

Yes, generally. The reason is because it is faster to read extra data, than it would be to seek past it. Imagine I had a table with 10 columns with an average of 10 bytes per column, for 100 bytes per row. Now if I was only affecting three of them, it would be very inefficient to read 30 bytes, seek 70, then read 30, seek 70, etc.. (this assumes that the 3 I need are continous, they might be spread out which would be even worse!)

Instead it is much faster to just read large chunks of data, such as 128k, and then manipulate it in memory. The only time it would be faster to skip the extra columns is if the column is very large. InnoDB has the optimization to possibly do this, MyISAM does not. InnoDB can store large columns (such as large blobs) off row, so it does not always need to read them.

> Cause, as I don't understand this part, I can
> neither understand the following one :
> "If a table has 1,000 rows, this is at least 100
> times faster than reading sequentially. Note that
> if you need to access almost all 1,000 rows, it is
> faster to read sequentially, because that
> minimizes disk seeks."
>
> thanks

Harrison Fisk, Trainer and Consultant
MySQL AB, www.mysql.com

Options: ReplyQuote


Subject
Views
Written By
Posted
2113
June 20, 2005 09:58AM
Re: How MySQL Uses Indexes
1437
June 20, 2005 06:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.