Re: Performance of Select (pk) vs Select(*) with order by

[KimSeong Loh]

OK, you do have an index on `birthdate` and this is good.

If you know how InnoDB stores the `birthdate` index, you will understand the reason, the `birthdate` index will have a copy of the PK of the table and not a pointer to the data rows.

So, when selecting only the PK using the index, the data rows is not read, only the index.

When selecting all fields, the PK obtain from the `birthdate` index is used to find the data rows by parsing the PK index tree again, so need to parse 2 index trees to get the full data rows.

I recall the above should be explained in the Innodb documentation.

On further implementation details between mysql and innodb storage engine, I am not sure how the limit is implemented, whether the data rows will be read for those that are skipped with limit is actually read or not, I don't know. Maybe you can ask this in InnoDB forum as the InnoDB developers are there very often.



Edited 1 time(s). Last edit at 11/08/2005 07:17PM by KimSeong Loh.