开发者

there is a way to optimize this mysql query?

开发者 https://www.devze.com 2023-02-05 19:34 出处:网络
SELECT T1.name AS hotel, T2.name AS city FROM (SELECT * FROM hotel WHERE name LIKE \'$term1%\') T1, (SELECT *
SELECT T1.name AS hotel, 
       T2.name AS city 
  FROM (SELECT * 
          FROM hotel 
         WHERE name LIKE '$term1%') T1,
       (SELECT * 
          FROM city 
         WHERE name LIKE '$term2%') T2
 WHERE T1.city_id = T2.id

T1 have 150000, T2 have 15000. (static tables!) i have indexes on 'name' for this tables.

there is a way to optimize this mysql query? i also want to do -> 开发者_JS百科LIKE '%term1%' but its very very slow.


First step is to re-write the query using ANSI-92 JOIN syntax:

SELECT h.name AS hotel,
       c.name AS city
  FROM HOTEL h
  JOIN CITY c ON c.id = h.city_id
 WHERE h.name LIKE '$term1%'
   AND c.name LIKE '$term2%'

After that, look at indexing:

  • HOTEL.name
  • HOTEL.city_id
  • CITY.name
  • CITY.id

...in a variety of combinations.


Yes, just join directly between hotel and city and move the two LIKE statements to the WHERE clause.

If you can change the table structure and if there is a lot of duplication of names you could normalize name to a key and search a smaller table for the key that matches the name and then lookup the data based on the name's key.

Also add indexes based on "OMG Ponies" answer.


one way to improve the performance is to put a fulltext index on the name columns of those tables.


i also want to do -> LIKE '%term1%' but its very very slow

Maybe name LIKE '$term%' OR reverse_name LIKE '$reverse_term%' is faster than name LIKE '%$term%'. With appropriate indizes of course.

I never tried ... just popped into my head.

0

精彩评论

暂无评论...
验证码 换一张
取 消