MySQL Forums
Forum List  »  Optimizer & Parser

Re: Performance of Select (pk) vs Select(*) with order by
Posted by: KimSeong Loh
Date: November 08, 2005 07:15PM

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.

Options: ReplyQuote

Written By
Re: Performance of Select (pk) vs Select(*) with order by
November 08, 2005 07:15PM

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.