Re: How MySQL Uses Indexes
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