MySQL Forums
Forum List  »  Performance

Re: Looking for understanding of optimizing queries
Posted by: Jørgen Løland
Date: September 08, 2011 06:34AM

Hi Peter,

> 1: Sometimes "explain $query" shows that an index is used (all key columns are
> NOT NULL), when in fact "$query" shows up in the slow log exactly iff
> log-queries-not-using-indexes is set in /etc/mysql/my.cnf. (See below for
> example) Why is that?

It shows up there because type=index, which means that you do an index scan which basically means reading all index entries. This is a bit confusing; index_scan would be a better name for it.

The most important part of EXPLAIN is the type column. If it shows eq_ref, ref or range (index lookup types) or system/const you're good in most cases. If it shows ALL or index all rows are read.

> But for the exact same structure in a "small" database [5] with "few" rows,
> explain tells me that a different set of keys will be used:

The responsibility of the optimizer is to take a great number of information into consideration and then come up with a good plan for execution (the order of which tables are accessed, which access method is used for each table etc). Some important info for the optimizer is:
* the number of rows in the tables
* selectivity of indexes, given the conditions of the query
* whether or not an index provides required ordering for free

Thus, the best access method for a table with few rows may not be the best access method after a bunch of new rows have been inserted.

Have you read the EXPLAIN Output Format documentation page [1]? If not, you should :)

[1] http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Looking for understanding of optimizing queries
1024
September 08, 2011 06:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.