Take the 2-minute tour ×

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

I create a table like this..

  `MerchID` int(10) DEFAULT NULL,
  KEY `MerchID` (`MerchID`) 

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

    SELECT 1
    SELECT 2
    SELECT null

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


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.

share|improve this question

2 Answers 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.

share|improve this answer

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.

share|improve this answer
How will a covering index compare to every column having its own index? –  JBone Mar 5 '12 at 19:34
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. –  Marcus Adams Mar 5 '12 at 20:15

Your Answer


