MySQL Forums
Forum List  »  Performance

Re: Simple Query takes 50 seconds with Indexing
Posted by: Rick James
Date: July 21, 2011 10:04PM

Yes, "Using index" is a performance boost. Sometimes it is worthwhile artificially adding fields to a non-UNIQUE index just to get "Using index". (Note: In InnoDB, the PRIMARY KEY is implicitly part of each secondary key. Hence `id` was included in your example.)

> innodb_buffer_pool_size 16777216
That is very small. Suggest making it 1500M. (This may be a reasonable limit for a 4GB Windows box with other stuff going on.)

You fetched about 25922 rows. If each row is about 10456 bytes, that adds up to 300MB -- IF the 25922 rows are adjacent. If they are scattered, then it might have read the entire 4185915392 byte table. That's a lot more than what you had allowed it to cache: 16777216 bytes.

The "Using index" example could reach into one part of one index, then read sequentially to get all the rows needed.

> changing covering indexes to using short left substrings of varchar fields
No, "prefix" indexes are virtually useless.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Simple Query takes 50 seconds with Indexing
868
July 21, 2011 10:04PM


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.