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.
精彩评论