mysql composite index thoery

Question

my sql is:select id,name,city,type from company where city=? and type=?. so I created two indexes: one is on city field and another is on type field.So my question is:Is it necessary that I create a composite index on city and type?if need,why?how is the two composite index btree index structure VS single index btree structure. Thanks in advance

Answer 1

Yes, in this case you need to have a composite city + type index to get the maximum possible performance.

As long as mysql can only use one index at once (with few exceptions, not the case here) - it will only check city or type to reduce all the table data and then will perform scan to find the exact rows.

If you had composite index - then mysql would be able to resolve both city=? and type=? conditions thus no additional scans would be required to return the result set.

how is the two composite index btree index structure VS single index btree structure.

There is no 2 composite btree structures. If you create a composite index, then single b-tree structure with data from both columns is created. That's why order in composite indexes matters.