Geek asks and answers

Do I need to have a multi-column 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 (the table has 1 million + rows).

I have a pointer to item_number, name, category, deleted separately. How to speed up this request?

+3
source share
2 answers

Best of all, I know that MySQL does not know how to perform raster or index scanning. But you can rewrite it as a union of three queries to make it do such a thing if you have an index for each field. If so, it 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
+3
source

An operator ORcan be poison to an execution plan. You can try to rephrase your query by replacing the sentences with ORequivalent 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

MySQL , UNION . , Oracle. , MySQL ? . , LIKE 'AB10LA2%' . - . . .

, , , '%'. , , - .

+1
source

More articles:


All Articles