here are some common cases where indexes should be used:
join clauses
columns that are part of joins should be indexed in both tables. for example:
Code:
select a.foo
, b.foo
from tablea a
join tableb b
on a.bar = b.tablea_bar
the columns tablea.bar and tableb.tablea_bar should both have indexes. this can improve query execution speed from minutes down to seconds.
where clauses
columns that are used in where clauses should generally be indexed. for example:
Code:
select foo
from tablea
where bar = 5
the column tablea.bar should be indexed. additionally, you could create a compound index on (bar, foo) to really speed things up. with that index, mysql will not have to pull any data records off the disk to find the corresponding value of foo. most likely, it will have already been brought in to memory when mysql pulled the index off disk. this is how to create a compound index:
Code:
alter table tablea
add index (bar, foo)
a compound index will also help queries with multiple items in the where clause:
Code:
select wee
, ping
from tablea
where bar = 5
and foo = 11
this query could benefit from the index created above. note that mysql will only sometimes recognize that reordering elements in the where clause will result in an index being able to be used. rule of thumb: try order your where clause so that the elements match the order of an compound indexes you have.
group by
Code:
select bar
, count(*)
from tablea
group
by bar
if bar is indexed in the above query, mysql can quickly consult the index and return the answer without ever having to read data rows.
Code:
select bar
, count(*)
from tablea
where foo = 11
group
by bar
in this case, creating independent indexes on foo and bar would be somewhat helpful, but a compound index on (foo, bar) would be most helpful. to understand why, you need to know that: a) mysql processes the WHERE clause before the GROUP BY clause, b) mysql will only use one index per table, and c) compound indexes can only be used in the order they are created. so create your compound indexes so that mysql can take advantage of more than one part.
in all cases...
make sure that any columns that you have indexes for appear on the left side of any equality operators by themselves. for example, in the following query, an index can not be used:
Code:
select foo
from tablea
where year(some_date_column) = 2004
because some_date_column is being processed by the year function, mysql can not consult an index. rewriting your query like this will allow an index to be used:
Code:
select foo
from tablea
where some_date_column between '2004-01-01' and '2004-12-31'
Reply With Quote
Originally Posted by bimalpoudel
Bookmarks