Is there a way to optimize this mysql query?

Question

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 has 150,000, T2 has 15,000. (Static tables!) I have indexes for the "name" for these tables.

Is there a way to optimize this mysql query? I also want to do → LIKE '% term1%', but very slowly.

Answer 1

The first step is to rewrite the request using the 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 various combinations.

Answer 2

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

, , , , .

"OMG Ponies".

Answer 3

One way to improve performance is to put the full text in the name columns of these tables.

Answer 4

I also want to do -> LIKE '% term1%', but very slow

Perhaps name LIKE '$term%' OR reverse_name LIKE '$reverse_term%'faster than name LIKE '%$term%'. Of course, with the corresponding indexes.

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