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

Question

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?

Answer 1

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.