Optimizing MySQL Queries (Adding indexes, re-writing queries, using explain, etc) for beginners?

Question

I'm looking at the slow query log and running explain on queries, now how do I go about interpreting the output to make improvements?

Example:

EXPLAIN SELECT corecountry, corestatus 
        FROM daydream_ddvalpha.propcore 
        WHERE corecountry = '7'    AND corestatus >= '100'

Output:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'propcore', 'ALL', NULL, NULL, NULL, NULL, '1532', 'Using where'

Show index:

SHOW INDEX FROM daydream_ddvalpha.propcore = 
# Table, Non_unique, Key_name, Seq_in_index, Column_name, Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, Index_comment
'propcore', '0', 'PRIMARY', '1', 'coreref', 'A', '1773', NULL, NULL, ”, 'BTREE', ”, ”

Describe:

describe daydream_ddvalpha.propcore
# Field, Type, Null, Key, Default, Extra
'coreref', 'varchar(10)', 'NO', 'PRI', '', ''
'coretitle', 'varchar(75)', 'NO', '', '', ''
'coreprice', 'int(25) unsigned', 'NO', '', '0', ''
'corecurr', 'tinyint(1)', 'NO', '', '0', ''
'coreagent', 'varchar(10)', 'NO', '', '0', ''
'corebuild', 'smallint(4)', 'NO', '', '0', ''
'coretown', 'varchar(25)', 'NO', '', '', ''
'coreregion', 'varchar(25)', 'NO', '', '', ''
'corecountry', 'smallint(4)', 'NO', '', '0', ''
'corelocation', 'smallint(4)', 'NO', '', '0', ''
'corestatus', 'smallint(4)', 'NO', '', '0', ''
'corelistsw', 'char(1)', 'NO', '', '', ''
'corepstatus', 'tinyint(4)', 'NO', '', '0', ''
'coreseq', 'mediumint(10)', 'NO', '', '0', ''
'coreviews', 'mediumint(10)', 'NO', '', '0', ''
'coreextract', 'char(1)', 'NO', '', 'n', ''

EDIT: NEW EXAMPLE

I found a more complex query:

EXPLAIN SELECT coreref, coretitle, coreprice, corecurr, corebuild, coretown,    corecountry, corepstatus, corestatus FROM daydream_ddvalpha.propcore 

    WHERE coretown = 'Torrepacheco' 

    AND corestatus >= '100'

    ORDER BY coreprice ASC

    LIMIT 135, 10

Output:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'propcore', 'ALL', NULL, NULL, NULL, NULL, '1579', 'Using where; Using    filesort'

I understood the answers given to the first example regarding the indexes, but how about this? Should I create an index to cover coretown and corestatus and coreprice. I kind of get the impression I'll end up with lots of indexes with duplicate values, or is that normal?

[How do you interpret a query's explain plan](http://stackoverflow.com/questions/79266/how-do-you-interpret-a-querys-explain-plan) — Mark Baker, Dec 21 '13 at 16:43
[How to Design Indexes, Really](http://www.slideshare.net/billkarwin/how-to-design-indexes-really) — Bill Karwin, Dec 21 '13 at 17:54

Gordon Linoff · Accepted Answer · 2013-12-21T16:49:23.140

This is your query:

SELECT corecountry, corestatus
FROM daydream_ddvalpha.propcore
WHERE corecountry = '7' AND corestatus >= '100'

You have two conditions in the where clause. One is equality one is not. The index that will help is daydream_ddvalpha.propcore(corecountry, corestatus). corecountry has to go first, because equality conditions need to be the left most columns in the index. Then you get one inequality, which is corecountry.

You are only selecting these two fields. The above index is said to be a covering index for the query, because all columns needed for the query are in the index. In other words, only the index is read for the query, rather than the original data.

As a note: if the fields are numeric, then you don't need to put quotes around the values. Using single quotes makes them look like strings, which can sometimes confuse both SQL optimizers and people reading the code.

EDIT:

As noted in the comments, the syntax for adding an index is:

create index idx_propcore_country_status ON propcore(corecountry, corestatus);

I usually name indexes with the name of the table followed by the columns (but the name can be any valid identifier).

So with what you've written can I simply do: CREATE INDEX country_status ON propcore (corecountry, corestatus)? Is that correct. — Richard, Dec 21 '13 at 16:46
After adding this index my explain now gives: # id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra '1', 'SIMPLE', 'propcore', 'range', 'country_status', 'country_status', '4', NULL, '27', 'Using where; Using index' # id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra '1', 'SIMPLE', 'propcore', 'range', 'country_status', 'country_status', '4', NULL, '27', 'Using where; Using index' — Richard, Dec 21 '13 at 16:54

Optimizing MySQL Queries (Adding indexes, re-writing queries, using explain, etc) for beginners?

1 Answers1