Mysql – Primary index is being used, not the one which is on the field present in the where condition

clustered-indexindexindex-tuningMySQL

I have 2 tables, one is category table and has below coulmns and has a primary index on cat_id column.

cat_id (primary key), acc_id, islive

Another table is cat_brand table which has below fields and has the composite primary key on fields cat_id and brand_id and fk relation on cat_id column.

cat_id brand_id acc_id

Indexes on cat_brand are(cat_id,brand_id) which is primary index and another index on acc_id column, which I created for below SQL query.

select distinct cat_brand.brand_id 
FROM  category cat  
inner join  cat_brand on cat.cat_id = cat_brand.cat_id 
where cat_brand.acc_id=1 
  and cat.islive in ('true' )  
  and cat.acc_id=1;

Using explain query shows me, that above query uses the primary index of table cat_brand which is cat_id,brand_id while where condition in the SQL query is cat_brand.acc_id=1 and cat_brand has an index on the same field, then why it doesn't use the index on acc_id field?

Best Answer

Possibly these composite indexes will speed it up:

cat: (acc_id, islive_in, cat_id)
cat_brand: (acc_id, cat_id, brand_id)

SHOW CREATE TABLE may give more clues as to why the Optimizer picked what it did.

Related Solutions

Foreign Keys to tables where primary key is not clustered index

Taken from the MSDN article on FOREIGN KEY Constraints

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

I think it is perfectly fine to define a UNIQUE CLUSTERED INDEX on an IDENTITY column and define the PRIMARY KEY on different columns, often with a NONCLUSTERED INDEX but you don't have to specify an index.

Mysql might have too many indexes

Rather than try to guess, you should download Percona Tools.

You should use the tool pt-duplicate-key-checker. It will compare all the indexes for you and give your the ALTER TABLE commands to drop the ones you do not need and still maintain fully compliance with all your index search needs. Here is the code to do it:

ETL_INDX_SCRIPT=/tmp/remove_indexes.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB=mydb
pt-duplicate-key-checker ${MYSQL_CONN} -d ${DB} | grep "^ALTER TABLE " >> ${ETL_INDX_SCRIPT}
mysql ${MYSQL_CONN} -AN 2>/dev/null < ${ETL_INDX_SCRIPT}

If you not sure, look at the content of the index removal SQL script first

ETL_INDX_SCRIPT=/tmp/remove_indexes.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB=mydb
pt-duplicate-key-checker ${MYSQL_CONN} -d ${DB} | grep "^ALTER TABLE " >> ${ETL_INDX_SCRIPT}
vi -R ${ETL_INDX_SCRIPT}

If the resulting script has no ALTER TABLE commands, you don't need anything done.

If there are ALTER TABLE commands and if you are ready, run the script with this

mysql ${MYSQL_CONN} -AN 2>/dev/null < ${ETL_INDX_SCRIPT}

Best Answer

Related Solutions

Foreign Keys to tables where primary key is not clustered index

Mysql might have too many indexes

GIVE IT A TRY !!!

Related Question