CODEWITHSUNDEEP
mysql
Chris Muench
Chris Muench

Reputation: 18338

Do I need to have a multicolumn index?

EXPLAIN SELECT * 
FROM (
`phppos_items`
)
WHERE (
name LIKE  'AB10LA2%'
OR item_number LIKE  'AB10LA2%'
OR category LIKE  'AB10LA2%'
)
AND deleted =0
ORDER BY  `name` ASC 
LIMIT 16

+----+-------------+--------------+-------+-----------------------------------+------+---------+------+------+-------------+
| id | select_type | table        | type  | possible_keys                     | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------------+-------+-----------------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | phppos_items | index | item_number,name,category,deleted | name | 257     | NULL |   32 | Using where |
+----+-------------+--------------+-------+-----------------------------------+------+---------+------+------+-------------+

This query takes 9 seconds to run (the table has 1 million + rows).

I have an index on item_number,name,category,deleted separately. How can I speed up this query?

Upvotes: 3

Views: 64

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 221275

The OR operator can be poison for an execution plan. You could try to re-phrase your query replacing the OR clauses by an equivalent UNION:

SELECT * 
FROM (
  SELECT * FROM `phppos_items`
  WHERE name LIKE 'AB10LA2%'
  UNION
  SELECT * FROM `phppos_items`
  WHERE item_number LIKE 'AB10LA2%'
  UNION
  SELECT * FROM `phppos_items`
  WHERE category LIKE  'AB10LA2%'
)
WHERE deleted =0
ORDER BY  `name` ASC 
LIMIT 16

This will allow MySQL to run several sub-queries in parallel before applying the UNION operator to each of the subqueries' results. I know this can help a lot with Oracle. Maybe MySQL can do similar things? Note: I assume that LIKE 'AB10LA2%' is quite a selective filter. Otherwise, this might not improve things due to late ordering and limiting in the execution plan. See Denis's answer for a more general approach.

In any case, I think a multi-column index won't help you because you have '%' signs in your search expressions. That way, only the first column in the multi-column index could be used, the rest would still need index-scanning or a full table scan.

Upvotes: 1

Denis de Bernardy
Denis de Bernardy

Reputation: 78561

Best I'm aware, MySQL doesn't know how to perform bitmap OR index scans. But you could rewrite it as the union of three queries to force it to do such a thing, if you've an index on each field. If so, this will be very fast:

select *
from (
  select * from (
           select *
           from phppos_items
           where name like 'AB10LA2%' and deleted = 0
           order by `name` limit 16
           ) t
  union
  select * from (
           select *
           from phppos_items
           where item_number like 'AB10LA2%' and deleted = 0
           order by `name` limit 16
           ) t
  union
  select * from (
           select *
           from phppos_items
           where category like 'AB10LA2%' and deleted = 0
           order by `name` limit 16
           ) t
  ) as top rows
order by `name` limit 16

Upvotes: 3

Related Questions