Imagine a table with multiple columns, say, id, a, b, c, d, e
. I usually select by id
, however, there are multiple queries in the client app that uses various conditions over subsets of the columns.
When MySQL executes a query on a single table with multiple WHERE conditions on multiple columns, can it really make use of indexes created on different columns? Or the only way to make it fast is to create multi-column indexes for all possible queries?
Yes, but not with the indexes you've chosen. And that's without having to use multiple index hints.
SQL Server allows us to create up to 999 Non-clustered indexes and one Clustered indexes per each table.
In general, MySQL only allows you to create up to 16 indexes on a given table. If you are using a PRIMARY KEY index, you can only have one primary key per table. FULLTEXT, UNIQUE INDEXes, and INDEXes do not have this limitation.
A multiple-column index can be created using multiple columns. The indexes are formed by concatenating the values of the given columns. CREATE INDEX cannot be used to create a PRIMARY KEY.
Yes, MySQL can use multiple index for a single query. The optimizer will determine which indexes will benefit the query. You can use EXPLAIN
to obtain information about how MySQL executes a statement. You can add or ignore indexes using hints like so:
SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;
I would suggest reading up on how MySQL uses indexes.
Just a few excerpts:
If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows.
If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer will attempt to use the Index Merge optimization (see Section 8.2.1.4, “Index Merge Optimization”), or attempt to find the most restrictive index by deciding which index finds fewer rows and using that index to fetch the rows.
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