List:General Discussion« Previous MessageNext Message »
From:mos Date:October 7 2011 9:38pm
Subject:Re: MySQL Indexes
View as plain text  
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.)

Thread
MySQL IndexesTompkins Neil6 Oct
  • Re: MySQL IndexesMichael Dykman6 Oct
    • Re: MySQL IndexesNeil Tompkins6 Oct
      • Re: MySQL IndexesMichael Dykman7 Oct
        • Re: MySQL IndexesNuno Tavares7 Oct
          • Re: MySQL IndexesBrandon Phelps7 Oct
            • Re: MySQL IndexesMichael Dykman7 Oct
              • Re: MySQL IndexesReindl Harald7 Oct
                • Re: MySQL IndexesMichael Dykman7 Oct
                  • Re: MySQL IndexesNeil Tompkins7 Oct
                    • Re: MySQL IndexesMichael Dykman7 Oct
        • Re: MySQL IndexesTompkins Neil7 Oct
          • Re: MySQL IndexesMichael Dykman7 Oct
            • Re: MySQL IndexesNeil Tompkins7 Oct
              • Re: MySQL Indexesmos7 Oct