INDEX on MYSQL query

Question

I have the following MYSQL query:

SELECT statusdate,consigneenamee,weight,productcode,
  pieces,statustime,statuscode,statusdesc,generatingiata,
  shipmentdate,shipmenttime,consigneeairportcode,signatory 
FROM notes 
where (shipperref='180908184' OR shipperref='180908184' 
       OR shipperref='180908184' OR shipperref='180908184 ' 
       OR shipperref like 'P_L_%180908184')  
order by edicheckpointdate asc, edicheckpointtime asc;

I added an index to speed up this query using the MYSQL Workbench but when executing the EXPLAIN command, it still does not show the key and shows as NULL:

+----+-------------+---------------+------+---------------+------+---------+------+---------+-----------------------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows    | Extra                       |
+----+-------------+---------------+------+---------------+------+---------+------+---------+-----------------------------+
| 1  | SIMPLE      | dhltracking_2 | ALL  | index2        | NULL | NULL    | NULL | 3920874 | Using where; Using filesort |
+----+-------------+---------------+------+---------------+------+---------+------+---------+-----------------------------+

Any reason why this is happening and how I can speed up this query?

My Index:

How many rows do you get from this query? The execution plan suggests that MySQL assumes that you get a large portion of your table; if you have large amount of rows starting with P_L_, this could explain it (as MySQL does not know how many will end with 180908184). You can try to use FROM notes force index (index2). Another way might be to clean up your data when you insert it (and not everytime you query it) and have a (new, maybe calculated) column that contains exactly the value 180908184 (so removing spaces,prefixes, ...), then you can use a single, indexfriendly = '180908184'. — Solarflare
– Solarflare, Commented Nov 1, 2018 at 12:07

Anton · Accepted Answer · 2018-11-01 20:15:33Z

1

You have LIKE statement in your query and I think that your index spans on more than 20-30% of table rows (or more..) and that's why MySQL can ignore it for performance reasons. My proposal:

Add FORCE INDEX as @Solarflare proposes
Use FULLTEXT index (works on CHAR and VARCHAR also) and use MATCH ... AGAINST search (https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html)

answered Nov 1, 2018 at 20:15

Anton

1,0852 gold badges11 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

INDEX on MYSQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related