CODEWITHSUNDEEP
mysql
Andrew G. Johnson
Andrew G. Johnson

Reputation: 26993

Slow MySQL query -- possibly an index issue?

So firstly here's my query: (NOTE:I know SELECT * is bad practice I just switched it in to make the query more readable)

SELECT pcln_cities.*,COUNT(pcln_hotels.cityid) AS hotelcount
  FROM pcln_cities
  LEFT OUTER JOIN pcln_hotels ON pcln_hotels.cityid=pcln_cities.cityid
  WHERE pcln_cities.state_name='California' GROUP BY pcln_cities.cityid
  ORDER BY hotelcount DESC
  LIMIT 5

So I know that to solve things like that you add EXPLAIN to the beginning of the query but I'm not 100% sure how to read the results, so here they are:

alt text http://www.andrew-g-johnson.com/query-results.JPG

Bonus points to an answer that tells me what to look for in the EXPLAIN results

EDIT The cities tables has the following indexes (or is it indices?)

The hotels tables has the following indexes (or is it indices?)

Upvotes: 0

Views: 290

Answers (3)

mson
mson

Reputation: 7824

Your query looks fine. Is there a chance that something else has a lock on a record that you need? Are the tables especially big? I doubt that data is the problem as there are not that many hotels...

I've run in to similar issues with MySQL. After spending over a year tuning, patching, and thinking I'm a SQL dummy, I switched to SQL Server Express. The exact same queries with the exact same data would run 2-5 orders of magnitude faster in SQL Server Express. MySQL seemed to have an especially difficult time with moderately complex queries (5+ tables). I think the MySQL optimizer became retarded after SUN bought the organization...

Upvotes: 0

Paul Tomblin
Paul Tomblin

Reputation: 182782

It looks like you don't have an index on pcln_cities.state_name, or pcln_cities.cityid? Try adding them.

Given that you've updated your question to say that you do have these indexes, I can only suggest that your database currently has a preponderance of cities in California, so the query optimizer decided it would be easier to do a table scan and throw out the non-California ones than to use the index to pick out the California ones.

Upvotes: 0

Frederik Gheysels
Frederik Gheysels

Reputation: 56934

Hmm, there's something not very right in your query. You use an aggregate function (count), but you simply group by on id. Normally, you should group on all columns in your select list, that are not an aggregate function.

As you've specified the query now, IMHO, the DBMS can never correctly determine which values he should display for those columns that are not an aggregate ...

It would be more correct if your query was written like:

select cityname, count(*)
from city inner join hotel on hotel.city_id = city_id
group by cityname
order by count(*) desc

If you do not have an index on the cityName, and you filter on cityname, it will improve performance if you put an index on that column.

In short: adding an index on columns that you regularly use for filtering or for sorting, may improve performance. (That is simply put offcourse, you can use it as a 'guideline', but every situation is different. Sometimes it can be helpfull to add an index which spans multiple columns. Also, remember that if you update or insert a record, indexes need to be updated as well, so there's a slight performance cost in adding/updating/deleting records)

Another thing that could improve performance, is using an inner join instead of an outer join. I don't think that it is necessary to use an outer join here.

Upvotes: 3

Related Questions