0

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?

  • 1
    Show DDL of both tables instead of a description. Show EXPLAIN of the query. – Akina Nov 14 '18 at 17:31
  • In addition to the SHOW CREATE TABLEs, tell us how big the tables are, and what percentage have the indicated values. – Rick James Nov 14 '18 at 20:01
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.