StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL: 'EXPLAIN' showing different explanations for similar queries
text
Body
copied!<p>I have a table 'posts' in database which has non-unique index on user_id (Key: MUL).</p> <pre><code>mysql> show columns from posts; +---------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user_id | int(11) | YES | MUL | NULL | | | post | varchar(140) | NO | | NULL | | +---------+--------------+------+-----+-------------------+----------------+ </code></pre> <p>For this table, explain gives expected explanation where type is '<strong>REF</strong>'</p> <pre><code>mysql> explain select * from posts where posts.user_id=1; +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | posts | ref | user_id | user_id | 5 | const | 74 | Using where | +----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+ </code></pre> <p>I have a second table 'followers' where 'user_id' and 'follower' are part of non-unique index</p> <pre><code>mysql> show columns from followers; +---------------+-----------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-----------+------+-----+---------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user_id | int(11) | YES | MUL | NULL | | | follower | int(11) | YES | MUL | NULL | | +---------------+-----------+------+-----+---------------------+----------------+ </code></pre> <p>But in this table, type is '<strong>ALL</strong>'. I expected it to be '<strong>REF</strong>' as similar to 'user_id' in previous table, this 'user_id' also has non-unique index. Is there any explanation for this?</p> <pre><code>mysql> explain select * from followers where followers.user_id=1; +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | followers | ALL | user_id | NULL | NULL | NULL | 6 | Using where | +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+ </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload