CODEWITHSUNDEEP
mysqlperformanceindexing
5a01d01P
5a01d01P

Reputation: 683

Mysql Index On columns of table

I am using MySQL 8 version. Let suppose I have a table i.e. event and there are approx 15 columns in this. Let suppose column names are from a,b,c ... up to m having varchar datatype. This is a master table so having one-to-one records in this. I provided a dashboard for this table and the client can select fields to filter records as per their need.

There are 12 fields on that filter are applying. The query is creating based on selected fields.

If field a is selected then query will be like select * from event where a = <some value>.

If b and c selected then the query will be like select *from event where b= <some value> and c= <some value>

So Can you suggest to me how can I create an index for better optimization?

Upvotes: 0

Views: 45

Answers (1)

Rick James
Rick James

Reputation: 142208

There is a limit to the number of indexes you can have on a table -- both an absolute limit (64) and a practical limit (much less).

I suggest you start with 12 2-column 'composite' indexes. Have a different starting column for each of the 12. Have a "likely" second column.

Over time, watch what the users typically pick and add/subtract indexes accordingly.

Keep in mind these things:

  • The Optimizer does not care what order the WHERE clause is.
  • The Optimizer does care what order the columns of an index are in.
  • The best index starts with column(s) that are tested for =.
  • Usually, when a column is tested with a range (eg date, or price), further columns in the index are not useful.

More tips (that you seem to have found): http://mysql.rjweb.org/doc.php/index_cookbook_mysql

INDEX(a,b) will do a pretty good job for WHERE a=1 AND B=2 AND c=3. It won't be as good as INDEX(a,b,c). But I am suggesting that you have to make tradeoffs -- Be happy with "pretty good"; you can't achieve "perfect" in all cases.

Upvotes: 1

Related Questions