At 01:58 PM 10/7/2011, you wrote:
Do you have any good documentation with regards creating indexes.
Also information for explain statement and what would be the desired
result of the explain statement?
This might help:
http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
http://www.sitepoint.com/optimizing-mysql-application/
http://hackmysql.com/case2
There is one more advantage about compound indexes like "index on
(field_a, field_b)". If you are retrieving just field_a and field_b,
in a select statement :
select field_a, field_b from table1 where field_a="abc"
even though you only references field_a in the Where clause, it of
course uses that index to find rows with field_a="abc", but it also
retrieves field_b from the SAME index so MySQL doesn't have to go to
the data file to get field_b. This can dramatically reduce disk I/O
in heavily used queries and occasionally you may want to create a
compound index even though the second field won't be used in a Where clause.
There is a "yin and yang" approach to creating indexes. Newbies will
try and index all possible columns that are used in a Where clause
which results in a huge index file and very slow table updates. The
more indexes you have on a table, the slower it takes to add or
update a row. You really only want to index the columns of the most
frequent queries.
As to which fields to index, on a test database I would remove all
indexes from the table except for the primary keys and have the slow
query log turned on. Run your queries for an hour and examine the
slow query log to see which queries are slow. Copy and paste the
slow Select query to a MySQL administrator like SqlYog Community
Edition v9.2 (http://code.google.com/p/sqlyog/downloads/list) and do
an explain on the query to see what indexes it is (not) using. Then
alter the table and add an index to try and speed up the query. You
may have to repeat this several times to finally get the proper index
defined. Remember to "Reset Query Cache" between tests. Only by
judiciously adding indexes one by one and testing the performance,
will you have the proper "yin and yang" so your tables are in harmony.
Mike
(If you can't achieve harmony, then buy more hardware.)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
