CODEWITHSUNDEEP
mysqldatabaseindexing
adnan kamili
adnan kamili

Reputation: 9455

MySQL - indexing , order of fields

My database size is growing, so I have decided to add indexes (never done that before). So I need some advice. e.g.

SELECT field1, field2 

FROM mytable1

WHERE account_id = :account_id and product_id = :product_id  and version_name = :version_name

or 

SELECT field1, field2 

FROM mytable1

WHERE version_name = :version_name and  and product_id = :product_id  and account_id = :account_id

An account can have many products and product can have millions of versions, which select order in where is faster. version_name is a string.

If I have version_id available which is primary key should I always put that first.

Should I add index upon account_id and product_id together

Does it get all the rows for first condition in where, and then filters the result as per second condition and so on

or

It scans every row for all the three fields, given no index is added

Upvotes: 1

Views: 32

Answers (1)

Tudor Constantin
Tudor Constantin

Reputation: 26861

The order of the clauses does not matter. What matters is the order of the fields in the index: if you have an index on 1. account_id, 2. product_id, that index will only be used when you have in the WHERE clause account_id.

For your queries you can put an index on account_id, product_id and version_name.

Upvotes: 1

Related Questions