Menu
I have a MySQL table that has, among other attributes, a timestamp, a type and a user_id.
All of them are searchable and/or sortable.
Is it better to create an index for each one, or create a single compound index with all three, or both?
843
MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on.
You can create indexes that are based on a single field or on multiple fields. You'll probably want to index fields that you search frequently, fields that you sort, and fields that you join to fields in other tables in multiple table queries.
Yes you can have too many indexes as they do take extra time to insert and update and delete records, but no more than one is not dangerous, it is a requirement to have a system that performs well.
Creating Composite IndexCREATE TABLE table_name ( c1 data_type PRIMARY KEY, c2 data_type, c3 data_type, c4 data_type, INDEX index_name (c2,c3,c4) ); In the above statement, the composite index consists of three columns c2, c3, and c4.
If you are going to perform searches on those fields separately, you will probably need separate indexes to make your queries run faster.
If you have an index like this:
mysql> create index my_idx on my_table(tstamp, user_id, type);
And you query is:
mysql> select * from my_table where type = 'A';
Then my_idx won't be that helpful for your query and MySQL will end up doing a full table scan to resolve it.
183
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
