I have 2 tables, one is category table and has below coulmns and has a primary index on
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
brand_id and fk relation on
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?