Karam,
If you look closely you'll see that it is not using the index in either of
your examples. Each query claims to scan all 33914 rows in the table, which
seems like the correct behavior. MySQL does not have an index built on the
substrings, so it has to generate each substring before it can check if that
value is less than 500.
-Rob
-----Original Message-----
From: Karam Chand [mailto:[EMAIL PROTECTED]
Sent: Monday, July 28, 2003 3:51 PM
To: [EMAIL PROTECTED]
Subject: Why the query is not using index?
Greetings
Having a table with the following structure -
+---------+---------------+-------------------+------+-----+---------+------
----
------+
| Field | Type | Collation | Null |
Key | Default | Extra
|
+---------+---------------+-------------------+------+-----+---------+------
----
------+
| email | char(50) | latin1_swedish_ci | YES |
MUL | NULL |
|
| is_sent | enum('Y','N') | latin1_swedish_ci | YES |
| NULL |
|
| id | int(10) | binary | |
PRI | NULL | auto_incr
ement |
+---------+---------------+-------------------+------+-----+---------+------
----
------+
If I issue a command like this -
explain select substring(id,1,4) from email_table
where substring(id,1,4) < 300 limit 500;
The result returned is -
+----+-------------+-------------+-------+---------------+---------+--------
-+--
----+-------+--------------------------+
| id | select_type | table | type |
possible_keys | key | key_len | r
ef | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+--------
-+--
----+-------+--------------------------+
| 1 | SIMPLE | email_table | index | NULL
| PRIMARY | 4 | N
ULL | 33914 | Using where; Using index |
+----+-------------+-------------+-------+---------------+---------+--------
-+--
----+-------+--------------------------+
This means it is using the index.
But, if issue a command like -
explain select email from email_table where
substring(id,1,4) < 300 limit 500;
+----+-------------+-------------+------+---------------+------+---------+--
----
+-------+-------------+
| id | select_type | table | type |
possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+--
----
+-------+-------------+
| 1 | SIMPLE | email_table | ALL | NULL
| NULL | NULL | NULL
| 33914 | Using where |
+----+-------------+-------------+------+---------------+------+---------+--
----
+-------+-------------+
the KEY column is NULL i.e. it is not using Index?
Why the query is not using index?
Thanks in advance.
Karam
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
