MySQL Forums
Forum List  »  InnoDB

Re: why must i specify an index in query?
Posted by: Caleb Pitman
Date: July 02, 2015 12:46PM

Hi Peter, heres the info:

MySQL 5.6.23

(SLOW query sing userId2; low cardinality)
EXPLAIN SELECT * FROM usersMobile LEFT JOIN (SELECT * FROM userHits) tx1 ON tx1.id=(SELECT id FROM userHits WHERE userId2=usersMobile.id ORDER BY id DESC LIMIT 1);
+----+--------------------+-------------+-------+---------------+-------------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------------+-------+---------------+-------------+---------+------+--------+-------------+
| 1 | PRIMARY | usersMobile | ALL | NULL | NULL | NULL | NULL | 132 | NULL |
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 8 | func | 1870 | Using where |
| 3 | DEPENDENT SUBQUERY | userHits | index | userId2 | PRIMARY | 8 | NULL | 2 | Using where |
| 2 | DERIVED | userHits | ALL | NULL | NULL | NULL | NULL | 246856 | NULL |
+----+--------------------+-------------+-------+---------------+-------------+---------+------+--------+-------------+

(FAST query USING userid)
EXPLAIN SELECT * FROM usersMobile LEFT JOIN (SELECT * FROM userHits) tx1 ON tx1.id=(SELECT id FROM userHits WHERE userId=usersMobile.id ORDER BY id DESC LIMIT 1);
+----+--------------------+-------------+------+---------------+-------------+---------+---------------------------------+--------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------------+------+---------------+-------------+---------+---------------------------------+--------+------------------------------------------+
| 1 | PRIMARY | usersMobile | ALL | NULL | NULL | NULL | NULL | 132 | NULL |
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 8 | func | 1870 | Using where |
| 3 | DEPENDENT SUBQUERY | userHits | ref | userId | userId | 5 | usersMobile.id | 158 | Using where; Using index; Using filesort |
| 2 | DERIVED | userHits | ALL | NULL | NULL | NULL | NULL | 246856 | NULL |
+----+--------------------+-------------+------+---------------+-------------+---------+---------------------------------+--------+------------------------------------------+

(FAST query using USE INDEX(userId2))
EXPLAIN SELECT * FROM usersMobile LEFT JOIN (SELECT * FROM userHits) tx1 ON tx1.id=(SELECT id FROM userHits USE INDEX(userId2) WHERE userId2=usersMobile.id ORDER BY id DESC LIMIT 1);
+----+--------------------+-------------+------+---------------+-------------+---------+---------------------------------+--------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------------+------+---------------+-------------+---------+---------------------------------+--------+------------------------------------------+
| 1 | PRIMARY | usersMobile | ALL | NULL | NULL | NULL | NULL | 132 | NULL |
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 8 | func | 1870 | Using where |
| 3 | DEPENDENT SUBQUERY | userHits | ref | userId2 | userId2 | 5 | usersMobile.id | 123400 | Using where; Using index; Using filesort |
| 2 | DERIVED | userHits | ALL | NULL | NULL | NULL | NULL | 246859 | NULL |
+----+--------------------+-------------+------+---------------+-------------+---------+---------------------------------+--------+------------------------------------------+



| userHits | CREATE TABLE `userHits` (
`id` bigint(255) NOT NULL AUTO_INCREMENT,
`ip` varchar(255) DEFAULT NULL,
`userId` int(255) DEFAULT NULL,
`userId2` int(255) DEFAULT NULL,
`time` bigint(255) DEFAULT NULL,
`browser` varchar(255) DEFAULT NULL,
`referer` varchar(255) DEFAULT NULL,
`fileAccessed` varchar(255) DEFAULT NULL,
`guid` int(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `userId` (`userId`),
KEY `userId2` (`userId2`)
) ENGINE=InnoDB AUTO_INCREMENT=252517 DEFAULT CHARSET=latin1 |

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: why must i specify an index in query?
1536
July 02, 2015 12:46PM


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.