MySQL Forums
Forum List  »  PHP

Re: Indexing is done for single table, but its not working for most of the queries.
Posted by: Rick James
Date: June 14, 2013 11:31PM

Use SHOW CREATE TABLE, it is more DESCriptive than DESCribe.

When much of the table has a particular value (domain=1), then it is actually faster to scan the table then to bounce back and forth between the index and the table.

To further elaborate... An INDEX is stored in a separate structure from the data. Using the index involves locating the starting point in the index and scanning through part of the index. For each item in the index, the query reaches (randomly) into the data structure.

More specifically...
> select * from videos_ctgryDomain where domain = 1;

In the case of "WHERE domain=1", it would find the first occurrence of domain=1 in the index, scan through the 4 occurrences, plus one more to discover that it is finished. 4 times, it would reach into the data to get the other information ('SELECT *').

It would be faster ignore the index and simply scan through the entire table (only 8 rows), and throw out any for which "domain=1" is false.

If you had 100 rows, but only 4 had domain=1, then the index would be beneficial, and would be used.

Where is the cutoff? It depends on the phase of the moon. Seriously, it is usually between 10% and 30%. That is, if the number of index matches is less than 10-30% of the the total number of rows, then it will use the index. Otherwise, it will do a table scan. (The actual percentage depends on various detailed analyses done by the query optimizer.)

Options: ReplyQuote


Subject
Written By
Posted
Re: Indexing is done for single table, but its not working for most of the queries.
June 14, 2013 11:31PM


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.