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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have following query with explain:

mysql> explain 
     select from dane dd 
       join test1.tag1 t1 on = 
       join test1.tag2 t2 on = 
       join test1.tag3 t3 on = 
       join test1.tag4 t4 on = 
       join test1.tag5 t5 on = 
       join test1.tag6 t6 on = 
       join test1.tag7 t7 on = 
       join test1.tag8 t8 on = 
       join test1.tag9 t9 on = 
       join test1.tag10 t10 on = 
     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      |      |    1|                        |
| 1|SIMPLE     |t8   |eq_ref|PRIMARY      |PRIMARY|8      |,const|    1|Using index             |
| 1|SIMPLE     |t1   |eq_ref|PRIMARY      |PRIMARY|8      |,const |    1|Using where; Using index|
| 1|SIMPLE     |t2   |eq_ref|PRIMARY      |PRIMARY|8      |,const |    1|Using where; Using index|
| 1|SIMPLE     |t6   |eq_ref|PRIMARY      |PRIMARY|8      |,const |    1|Using where; Using index|
| 1|SIMPLE     |t4   |eq_ref|PRIMARY      |PRIMARY|8      |,const |    1|Using where; Using index|
| 1|SIMPLE     |t3   |eq_ref|PRIMARY      |PRIMARY|8      |,const |    1|Using where; Using index|
| 1|SIMPLE     |t7   |eq_ref|PRIMARY      |PRIMARY|8      |,const |    1|Using where; Using index|
| 1|SIMPLE     |t9   |eq_ref|PRIMARY      |PRIMARY|8      |,const|    1|Using index             |
| 1|SIMPLE     |t5   |eq_ref|PRIMARY      |PRIMARY|8      |,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

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:

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 '14 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 '14 at 16:00
@MichałHerman, the type: index in your EXPLAIN describes how it's accessing the 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 '14 at 16:12

Your Answer


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.