mysql slow query on indexed column

Question

Running a simple query on an indexed column, yet it's taking over 500ms. These queries run often so it's affecting performance in a big way.

Just under 1M rows in the table, a very simple table. Using MyISAM. I don't understand why it's examining all rows, seems like it's ignoring the index! I tried adding a second index on the field, a normal one instead of a unique index, didn't make any difference. Thanks for looking.

# Time: 130730 22:00:07
# User@Host: engine[engine] @ engine [10.0.0.6]
# Query_time: 0.511209  Lock_time: 0.000050 Rows_sent: 1  Rows_examined: 932048
SET timestamp=1375236007;
SELECT * FROM `marketplacesales`.`sales_order` WHERE `marketplace_orderid` = 823123693003;

+---------------------+-----------------------+------+-----+---------+----------------+
| Field               | Type                  | Null | Key | Default | Extra          |
+---------------------+-----------------------+------+-----+---------+----------------+
| id                  | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| marketplace_id      | smallint(5) unsigned  | NO   | MUL | NULL    |                |
| marketplace_orderid | varchar(255)          | NO   | UNI | NULL    |                |
| datetime            | datetime              | NO   |     | NULL    |                |
| added               | datetime              | NO   | MUL | NULL    |                |
| phone               | varchar(255)          | YES  |     | NULL    |                |
| email               | varchar(255)          | YES  |     | NULL    |                |
| company             | varchar(255)          | NO   |     | NULL    |                |
| name                | varchar(255)          | NO   |     | NULL    |                |
| address1            | varchar(255)          | NO   |     | NULL    |                |
| address2            | varchar(255)          | NO   |     | NULL    |                |
| city                | varchar(255)          | NO   |     | NULL    |                |
| state               | varchar(255)          | NO   |     | NULL    |                |
| zip                 | varchar(255)          | NO   |     | NULL    |                |
| country             | varchar(255)          | NO   |     | NULL    |                |
+---------------------+-----------------------+------+-----+---------+----------------+

EXPLAIN SELECT * FROM `marketplacesales`.`sales_order` WHERE `marketplace_orderid` = 823123693003;
+----+-------------+-------------+------+-------------------------------------------+------+---------+------+--------+-------------+
| id | select_type | table       | type | possible_keys                             | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------+------+-------------------------------------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | sales_order | ALL  | marketplace_orderid,marketplace_orderid_2 | NULL | NULL    | NULL | 932053 | Using where |
+----+-------------+-------------+------+-------------------------------------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

Answer 1

You need to use explain to see what is happening. My guess is that the index is not being used.

The where clause is:

WHERE `marketplace_orderid` = 823123693003;

As explained here, the conversion will take place as floating point numbers. This requires a conversion on marketplace_orderid.

Either fix the field in the table so it is numeric. Or, put the value in quotes in the where clause:

WHERE `marketplace_orderid` = '823123693003';

The problem with quotes is that the actual value might have leading zeros, which would cause the match to fail.

Answer 2

Try using:

SELECT * FROM `marketplacesales`.`sales_order` WHERE `marketplace_orderid LIKE '823123693003%'

That should force the use of the index.

Also, if possible, I would consider changing the field to bigint intead of varchar