Mysql Index On columns of table

Question

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?

You should first clarify what queries the dashhoard will send to mysql. Most dashboards do not simply query the data source for each and every filter change, that would be very inefficient. — Shadow
– Shadow, Commented Oct 20, 2020 at 2:53
@Shadow, Yeah question updated, you right but in this case, we are sending queries based on selected fields. — 5a01d01P
– 5a01d01P, Commented Oct 20, 2020 at 3:03
INDEX(a) for the first query. INDEX(b,c) or INDEX(c,b) for the second. But I suspect your question wanted more than that?? — Rick James
– Rick James, Commented Oct 20, 2020 at 3:08
@RickJames thanks for your suggestion I followed your blog for indexing. it was just an example that I questioned. The client can use any field from 12 fields for filtering records from the dashboard. They can select like a and b and c , e and g, k and a and d. — 5a01d01P
– 5a01d01P, Commented Oct 20, 2020 at 3:13
@5a01d01P you should optimise your dashboard not to do this or use an established charting solution that already has this feature! Trust me, you can bring down your database server if you are not careful! — Shadow
– Shadow, Commented Oct 20, 2020 at 6:18

Rick James · Accepted Answer · 2020-10-20 03:23:13Z

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.

Collectives™ on Stack Overflow

Mysql Index On columns of table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related