Hello! There is a table, about 700K lines (test), fighting about 10M lines. When searching by table (sample query):

SELECT `p`.*, (SELECT name FROM brands b WHERE b.id = p.brand_id) brand_name, (SELECT name FROM dealers d WHERE d.id = p.dealer_id) dealer_name FROM `parts` `p` WHERE (`p`.`part_number` LIKE "%123" OR `p`.`part_new` LIKE "%123" OR `p`.`part_old` LIKE "%123" OR `p`.`part_alt` LIKE "%123") ORDER BY `p`.`part_number` ASC LIMIT 100

The request takes about 56.0167 seconds, and sometimes 87.1817 seconds.

EXPLAIN: enter image description here

Types of fields:

 part_number | varchar(100) | latin1_swedish_ci | NOT NULL part_new | varchar(100) | latin1_swedish_ci | NOT NULL part_old | varchar(100) | latin1_swedish_ci | NOT NULL part_alt | varchar(100) | latin1_swedish_ci | NOT NULL

Tried to put the index, but it does not pick it up, picks up the other completely:

 ALTER TABLE `parts` ADD UNIQUE `search_by_partnum_cols` (`part_number`, `part_new`, `part_old`, `part_alt`, `dealer_id`, `brand_id`)

Please advise how to accelerate this request. With indices there is little experience and I cannot find an sensible article how to arrange them: to push everything from a request, or only a part, etc. ...

  • 2
    Doubts plagued that such a LIKE can be optimized. Can make columns with the reverse order of letters, then the condition is rewritten as p.reverse_part_number LIKE '321%' . So exactly there is a probability of using the index on p.reverse_part_number, if it is built. - Sergey
  • @Sergey zayuzal USE INDEX, although I do not like it. Response time was reduced to 4 seconds. True, I do not understand, is it normal practice to use USE INDEX? - ka5itoshka
  • Not understood. it was 88, it became 4 and not satisfied yet? Or up to 4 seconds means -4 seconds. 88 - 4 = 84? - Sergey
  • 2
    He with such a query in any case can not effectively use the indexes. Firstly, the% at the beginning of the line makes this impossible, secondly, you have OR by 4 fields, i.e. if he can even go along certain indices, he will have to go through separate indices for each of the 4 fields and combine the results (if he comes to such a conclusion). Or a complete table lookup. But your execution plan says that it is somehow strangely injecting, and only in one of the 4 fields, which is probably slower than just going through the table - Mike
  • one
    And plus to this, the subqueries that get the names are best executed as join - Mike

0