Hiya
I've got a query which is putting a bit of strain on the server. I'd appreciate some help optimising it. The field types are appropriate, but as you can see it's a bit of a monster. Unfortunately I do need all the info that it currently returns:
SELECT Listings.ListingID, Listings.GuestlistAttached,Listings.TicketsAttached,
CityCode, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(DateAdded) AS Old, UNIX_TIMESTA
MP(ListingInstance.iEventDate) AS iEventDate, ListingInstance.iListingInstance,L
istingInstance.iListingID, Ent.c_Type, Ent.Town, Ent.Name, Ent.Address, Ent.EntI
D, Listings.EventName, Listings.EventDesc,Listings.EventDescShort, DayOfWeek(Lis
tingInstance.iEventDate) AS iEventDay, Listings.Feature, Ent.Logo, Ent.Logo_Feat
, Ent.Regional, ListingInstance.iCoverage, Listings.TicketURL, Ent.latitude, Ent
.longitude, Listings.DoorsOpen, Listings.DoorsClose, Listings.EntryPrice, GROUP_
CONCAT(Genres.GenreName SEPARATOR 'XXX') as genresStr FROM (Ent INNER JOIN Listi
ngs ON Ent.EntID = Listings.EntID) INNER JOIN ListingInstance ON Listings.Listin
gID = ListingInstance.iListingID left JOIN ListingGenre ON ListingGenre.Listing
ID = Listings.ListingID LEFT JOIN Genres ON ListingGenre.GenreID = Genres.GenreI
D
WHERE iEventDate>=NOW() AND iEventDate<= DATE_ADD(NOW(), INTERVAL XXX DAY) AND
UCASE(Ent.CityCode) IN ('XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX',
'XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','X
XX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX
','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX',
'XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','X
XX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX
','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX',
'XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','X
XX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX
','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX',
'XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','X
XX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX','XXX
','XXX') AND Ent.CityCode != 'XXX' AND Ent.ACTIVE=XXX AND Listings.ACTIVE=XXX G
ROUP BY iListingInstance ORDER BY ListingInstance.iEventDate, Listings.Feature D
ESC, Listings.EventName, Ent.Town;
Here's the result of EXPLAIN:
---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+--------+-----------------------------------------+---------+---------+------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | ListingInstance | range | PRIMARY,iListingID,iEventDate | PRIMARY | 3 | NULL | 2862 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | Listings | eq_ref | PRIMARY,ListingID,ACTIVE,EntID | PRIMARY | 4 | guidedb.ListingInstance.iListingID | 1 | Using where |
| 1 | SIMPLE | Ent | eq_ref | PRIMARY,ACTIVE,citycode,ent_active_city | PRIMARY | 4 | guidedb.Listings.EntID | 1 | Using where |
| 1 | SIMPLE | ListingGenre | ref | PRIMARY | PRIMARY | 4 | guidedb.Listings.ListingID | 4 | Using index |
| 1 | SIMPLE | Genres | eq_ref | PRIMARY | PRIMARY | 4 | guidedb.ListingGenre.GenreID | 1 | |
+----+-------------+-----------------+--------+-----------------------------------------+---------+---------+------------------------------------+------+----------------------------------------------+
This query is taking 3-15 seconds to run and has been run 10,000+ times this month so it's key that I can sort it out :)
Things I suspect are causing issues:
* GROUP_CONCAT(Genres.GenreName SEPARATOR 'XXX') as genresStr (I need to return the values of a link table, so this gets rid of the need for a seperate query per result returned, as this query might return 200 results)
* Ent.CityCode IN ('PR1','PR2', etc) - string checking, would it make it much quicker if I gave each citycode an integer value and did a search on that instead?
Any other suggestions?
Ben