I work with MySQL (MyISAM) DB.
I have a query like this:
select c1, c2, c3, MAX(c5) from T where c4=#string_value# group by c1, c2, c3
- c[1-4] are varchar(255)
- c5 is an integer
I've tried two indexes:
c1, c2, c3) - that makes query work 3 times slowerc4) - that makes query work 5 times fasterMaybe there is a way to create even more fast index?
I see the following index being optimal:
CREATE INDEX idx ON T (c4, c1, c2, c3, c5);
The index begins with c4, which appears in the WHERE clause, which is the first part of the query execution. Next, we add c1, c2, and c3, to cover the GROUP BY clause. After this, we add c5, which will make it much faster for MySQL to find MAX(c5).
Note that the above index can be said to cover the entire query, because MySQL can use the index alone to satisfy the entire query plan, without needing to seek back to the clustered index (table).
The best index should bd based on where condition ...
create index idx1 on T ( c4)
for avoid the access to data table and use only info contained in index you can also try using a redundant index adding the column involved in others clauses
create index idx1 on T ( c4, c1,c2,c3,c5)
leaving the where column left most