Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have following query with explain:

mysql> explain 
     select dd.data from dane dd 
       join test1.tag1 t1 on dd.id = t1.id 
       join test1.tag2 t2 on dd.id = t2.id 
       join test1.tag3 t3 on dd.id = t3.id 
       join test1.tag4 t4 on dd.id = t4.id 
       join test1.tag5 t5 on dd.id = t5.id 
       join test1.tag6 t6 on dd.id = t6.id 
       join test1.tag7 t7 on dd.id = t7.id 
       join test1.tag8 t8 on dd.id = t8.id 
       join test1.tag9 t9 on dd.id = t9.id 
       join test1.tag10 t10 on dd.id = t10.id 
     where t1.val = 1 
       and t2.val = 2 
       and t3.val = 3 
       and t4.val = 4 
       and t5.val = 5 
       and t6.val = 6 
       and t7.val = 7 
       and t8.val = 8 
       and t9.val = 9 
       and t10.val = 10 ;
+--+-----------+-----+------+-------------+-------+-------+------------------+-----+------------------------+
|id|select_type|table|type  |possible_keys|key    |key_len|ref               |rows |Extra                   |
+--+-----------+-----+------+-------------+-------+-------+------------------+-----+------------------------+
| 1|SIMPLE     |t10  |index |PRIMARY      |PRIMARY|8      |NULL              |94577|Using where; Using index|
| 1|SIMPLE     |dd   |eq_ref|PRIMARY      |PRIMARY|4      |test1.t10.id      |    1|                        |
| 1|SIMPLE     |t8   |eq_ref|PRIMARY      |PRIMARY|8      |test1.t10.id,const|    1|Using index             |
| 1|SIMPLE     |t1   |eq_ref|PRIMARY      |PRIMARY|8      |test1.t8.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t2   |eq_ref|PRIMARY      |PRIMARY|8      |test1.t1.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t6   |eq_ref|PRIMARY      |PRIMARY|8      |test1.dd.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t4   |eq_ref|PRIMARY      |PRIMARY|8      |test1.t8.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t3   |eq_ref|PRIMARY      |PRIMARY|8      |test1.dd.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t7   |eq_ref|PRIMARY      |PRIMARY|8      |test1.dd.id,const |    1|Using where; Using index|
| 1|SIMPLE     |t9   |eq_ref|PRIMARY      |PRIMARY|8      |test1.t10.id,const|    1|Using index             |
| 1|SIMPLE     |t5   |eq_ref|PRIMARY      |PRIMARY|8      |test1.dd.id,const |    1|Using where; Using index|
+--+-----------+-----+------+-------------+-------+-------+------------------+-----+------------------------+
11 rows in set (0.08 sec)

Every tag1 - tag10 tables have the same build: two columns (id,val), id is the primary key, no other indexes.

I have questions about the first line of explain:

  1. Number of rows - are this number exact number of scanned rows or just estimate? Every table has 100,000 rows.
  2. The first row has type index, but Extra shows Using where; Using index. Does it mean, that the first full table scan on where is used, and then index? Where is on the val column which does not have any indexes.
share|improve this question
add comment

1 Answer 1

type: index means it's an index scan. That is, it's scanning through an entire index of that table.

An index scan often goes along with Using index because the latter indicates that the query is able to use the index to satisfy the query, without touching the rows of the table. Using index would be more clearly labeled Using only index.

There are as many entries in an index as there are rows in the table, therefore the rows reported by an index-scan is the same number as rows reported during a table-scan.

The number reported by rows is an estimate, based on InnoDB's statistics collected about the table. It can be inaccurate by 10% or even more. Don't rely on this being a precise number, but treat it as an indicator in the order of magnitude as the true number of rows (or index entries).

You may have no secondary indexes on your tags table, but you have a primary key, which implicitly has an index. The type: eq_ref indicates that the query is using the primary key to find the respective row during the join. Lookups by primary key are good.

To read more about the EXPLAIN output, read: http://dev.mysql.com/doc/refman/5.6/en/explain-output.html

share|improve this answer
    
Thank you for answer. But i still do not fully understand one thing. type: index means it's an index scan. But where clause is on val column, which is not indexed. So how is that possible? –  Michał Herman Mar 10 at 9:07
    
@ypercube But doesn't PRIMARY means, that primary key index is scanned? Where clause is on non-index column, so index scan cannot determine whatever row fullfills where clause. –  Michał Herman Mar 10 at 16:00
    
@MichałHerman, the type: index in your EXPLAIN describes how it's accessing the dd.data table. The primary key lookups (type: eq_ref) are on the tag tables. In a join, each table may respectively be accessed using a different join type. –  Bill Karwin Mar 10 at 16:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.