CODEWITHSUNDEEP
sqlmysqlquery-optimization
Ben
Ben

Reputation: 249

there is a way to optimize this mysql query?

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 -> LIKE '%term1%' but its very very slow.

Upvotes: 3

Views: 122

Answers (4)

rik
rik

Reputation: 8612

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.

Upvotes: 0

jzd
jzd

Reputation: 23629

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.

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332531

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.

Upvotes: 7

Bob Baddeley
Bob Baddeley

Reputation: 2262

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

Upvotes: 0

Related Questions