MySQL Forums
Forum List  »  Optimizer & Parser

Need to optimise complex query
Posted by: Ben Sebborn
Date: April 29, 2007 04:13PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Need to optimise complex query
3187
April 29, 2007 04:13PM
1985
April 29, 2007 10:21PM
2046
April 30, 2007 05:00AM
2102
April 30, 2007 08:36PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.