CODEWITHSUNDEEP
mysqlindexingexplain
Heru S
Heru S

Reputation: 1323

MySQL EXPLAIN statement shows ALL on type

I have a quick and very simple question.

I have a table with the following SQL:

CREATE TABLE `users` (
 `id` int(20) NOT NULL AUTO_INCREMENT,
 `username` char(100) NOT NULL,
 `password` char(100) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `password` (`password`),
 KEY `username_2` (`username`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

When I tried to run an EXPLAIN statement as follow:

EXPLAIN SELECT * FROM users WHERE username = 'xx' OR `password` = 'xx'

I am receiving the result showing that I am having a type = ALL.

However, when I tried to run the EXPLAIN statement:

EXPLAIN SELECT * FROM users WHERE username = 'xx' AND `password` = 'xx'

I am receiving the result showing that I am having a type = REF.

I guess my question is why the first query is showing ALL while the second one is showing REF.

If anybody can shed a light on this, it is much appreciated! Thank you!!

Upvotes: 0

Views: 804

Answers (2)

Barmar
Barmar

Reputation: 781068

MySQL is not able to optimize OR conditions. So even though both parts of the OR can be satisfied by an index, it doesn't use them.

You should be able to solve this by using a UNION:

SELECT * FROM users WHERE username = 'xx'
UNION
SELECT * FROM users WHERE password = 'xx'

Each of the subqueries can use an index, and then the results will be merged.

Upvotes: 1

Jens
Jens

Reputation: 2075

ALL means, that the whole table has to be scanned. The indexes on password and username do not help in this case (there is no key on "(username, password)").

The second query uses both keys.

A more detailed explanation can be found here: http://dev.mysql.com/doc/refman/5.1/en/explain.html

Upvotes: 0

Related Questions