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]