MYSQL indexing issue

Question

I am having some difficulties finding an answer to this question... For simplicity lets create use this situation.

I create a table like this..

CREATE TABLE `test` (
  `MerchID` int(10) DEFAULT NULL,
  KEY `MerchID` (`MerchID`) 
) ENGINE=InnoDB AUTO_INCREMENT=32769 DEFAULT CHARSET=utf8;

I will insert some data into the column of this table...

INSERT INTO test
    SELECT 1
        UNION
    SELECT 2
        UNION
    SELECT null

Now I examine the query using MYSQL's explain feature...

EXPLAIN
SELECT * FROM test
WHERE merchid IS NOT NULL

Resting in ID=1 ,select_type=SIMPLE ,table=test ,type=index ,possible_keys=MerchID ,key=MerchID ,key_len=5 ,ref=NULL ,rows=3 ,Extra= Using where ;Using index

In production in my real procedure something like this takes a long time with this index. If I re declare the table with the index line reading "KEY MerchID (MerchID) USING BTREE' I get much better results. The explain feature seems to return the same results too. I have read some basics about the BTREE, HASH and RTREE storage types for indexes/keys. When no storage type is specified I was unded the assumption that BTREE would be assumed. However I am kinda stumped why when modifying my index to use this storage type my procedure seems to fly. Any ideas?

I am using MYSQL 5.1 and coding in MYSQL Workbench. The part of procedure that appears to be help up is like the one I illustrated above where the column of a joined table is tested for NULL.

Peter Kiss · Accepted Answer · 2012-03-05 17:48:44Z

2

I think you are on the wrong path. For InnoDB storage the only available index method is the BTREE so if you are safe to omit the BTREE keyword from you table create script.Supported index types here along with other useful information.

The performance issue is coming from a different place.

answered Mar 5, 2012 at 17:48

Peter Kiss

9,3292 gold badges25 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marcus Adams · Accepted Answer · 2012-03-05 18:21:32Z

0

Whenever testing performance, be sure to always use the SQL_NO_CACHE directive, otherwise, with query caching, the second time you run a query, your results may be returned a lot faster simply due to caching.

With a covering index (all of the selected and filtered columns are in the index), the query is rather efficient. Using index in the EXPLAIN result shows that it's being used as a covering index.

However, if the index were not a covering index, MySQL would have to perform a seek for each row returned by the index in order to grab the actual table data. While this would still be fast for a small result set, with a result set of 1 million rows, that would be 1 million seeks. If the number of NULL rows were a high percentage, MySQL would abandon the index altogether to avoid the seeks.

Ensure that your real "production" index is a covering index as well.

edited Mar 5, 2012 at 18:21

answered Mar 5, 2012 at 18:14

Marcus Adams

54k9 gold badges98 silver badges145 bronze badges

2 Comments

JBone Over a year ago

How will a covering index compare to every column having its own index?

Marcus Adams Over a year ago

Without a covering index, MySQL must do a lookup (seek) for each row returned from the index in the WHERE. For a covering index, MySQL can get the values directly from the index and does not have to do a lookup. MySQL can only use one index per table select, so having a single column index on each column does not help here unless you are selecting a single column.

Collectives™ on Stack Overflow

MYSQL indexing issue

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related