CODEWITHSUNDEEP
mysqlindexing
kshitij
kshitij

Reputation: 155

Best possible indexing strategy for MySQL DB

I have a table with 5 columns,say - A(Primary key), B, C, D and E. This table has almost 150k rows and there are no indices on this table. As expected the select queries are very slow.

These queries are generated by the user search requests so he can enter values in any of the fields (B, C, D and E) and these are 'IN' kind of queries. I am not sure what should be the good indexing strategy here - having indexes on each of these columns or have them in some combinations.

Selectivity of each of these columns is the same (around 50). Any help would be appreciated.

Upvotes: 1

Views: 620

Answers (2)

Kusalananda
Kusalananda

Reputation: 15603

Are you running the same query regardless of what the user gives you? In that case, that query should tell you what indexes to use.

For example, if your query might look like

SELECT * FROM mytable WHERE
  B IN (...) AND
  C IN (...) AND
  D IN (...) AND
  E IN (...)

In this case, where you restrict on all columns, a combined index with all five columns would probably be ok.

Otherwise, create one index per column, or combine columns that you always restrict on together in separate indexes.

Remember that if you have a combined index on e.g. B and C, then a query that does not restrict on B will not use that combined index.

Upvotes: 3

madkitty
madkitty

Reputation: 1675

if you can group two columns in one index that would okay. Having an index on each column is not so bad as long as you don't query Cartesian product like cross join. But better not too ..

Upvotes: 0

Related Questions