CODEWITHSUNDEEP
mysqloptimization
kalpaitch
kalpaitch

Reputation: 5271

mysql optimisation

I have an 'events' table with 400+ events in it with the following relationships - one to many with event_dates (45,000 rows), one to many with event_categories (1,000 rows).

I would be grateful for all suggestions re improving this longwinded and slow query (my sql skills are pretty poor):

SELECT 
    events.id AS id, 
    events.name AS event, 
    events.listing_type AS TYPE , 
    GROUP_CONCAT( DISTINCT event_categories.category_id ) AS category_id,                 
    events.location AS location, 
    events.summary AS summary, 
    counties.name AS county, 
    events.postcode AS postcode, 
    events.picture AS picture, 
    events.venue AS venue, 
    events.lat AS lat, 
    events.lng AS lng
FROM 
    EVENTS , 
    event_dates, 
    event_categories, 
    counties, 
    categories
WHERE 
    events.preview =0
AND event_dates.start_date > DATE( NOW( ) )
AND events.id = event_dates.event_id
AND events.id = event_categories.event_id
AND events.county_id = counties.id
AND event_categories.category_id = categories.id
AND events.city LIKE '%London%'
GROUP BY id
ORDER BY events.id DESC

EDIT: This is an events search query, and it needs to search by both categories table and dates table. Table structures below:

event_dates: id start_date end_date start_time end_time event_id

event_categories: id event_id category_id

Upvotes: 0

Views: 83

Answers (3)

OMG Ponies
OMG Ponies

Reputation: 332541

I re-wrote your query as:

  SELECT e.id, 
         e.name AS event, 
         e.listing_type AS TYPE , 
         ec.category_id,                 
         e.location,
         e.summary, 
         co.name AS county, 
         e.postcode, 
         e.picture, 
         e.venue, 
         e.lat, 
         e.lng 
    FROM EVENTS e
    JOIN EVENT_DATES ed ON ed.event_id = e.id
    JOIN (SELECT t.event_id,
                 GROUP_CONCAT( DISTINCT t.category_id ) AS category_id
            FROM EVENT_CATEGORIES t
        GROUP BY t.event_id) ec ON ec.event_id = e.id
    JOIN COUNTIES co ON c.id = e.county_id 
   WHERE e.preview = 0
     AND ed.start_date > DATE( NOW( ) )
     AND e.city LIKE '%London%'
ORDER BY e.id DESC

You had:

  • a join to the CATEGORIES table, but wasn't using it
  • though the GROUP BY in the OP in valid on MySQL, this version is SQL standard
  • searching via LIKE with a wildcard on the left side won't be able to use an index if there is one on EVENTS.city (IE: '%London%')

Suggestions/Recommendations

  • index the join criteria columns if they aren't already, then look at indexing the columns in the WHERE clause

Upvotes: 3

Jonathan Leffler
Jonathan Leffler

Reputation: 753585

I think you should do the GROUP_CONCAT operation in a much smaller scope (as a sub-query in the FROM clause) which may well improve performance overall. So, too, will removing the table categories from the query because it does not contribute anything except confusion (unless your tables are in such a state of semantic disintegrity that you have category id values in event_categories that do not appear in the defining categories table).

Those observations lead to:

SELECT e.id AS id,
       e.name AS event,
       e.listing_type AS type,
       cat.category_id AS category_id,
       e.location AS location,
       e.summary AS summary,
       counties.name AS county,
       e.postcode AS postcode,
       e.picture AS picture,
       e.venue AS venue,
       e.lat AS lat,
       e.lng AS lng
  FROM Events AS e
  JOIN event_dates AS d ON e.id = d.id AND d.start_date > DATE(NOW())
  JOIN (SELECT id, GROUP_CONCAT(category_id) AS category_id
          FROM event_categories
         GROUP BY id
       ) AS cat ON e.id = cat.id
  JOIN counties ON e.county_id = counties.id
 WHERE e.preview = 0
   AND e.city LIKE '%London%'
 ORDER BY events.id DESC

If you do need the categories table in the query, then it belongs in the sub-query:

SELECT e.id AS id,
       e.name AS event,
       e.listing_type AS type,
       cat.category_id AS category_id,
       e.location AS location,
       e.summary AS summary,
       counties.name AS county,
       e.postcode AS postcode,
       e.picture AS picture,
       e.venue AS venue,
       e.lat AS lat,
       e.lng AS lng
  FROM Events AS e
  JOIN event_dates AS d ON e.id = d.id AND d.start_date > DATE(NOW())
  JOIN (SELECT id, GROUP_CONCAT(category_id) AS category_id
          FROM event_categories AS ec
          JOIN categories AS ct ON ec.category_id = ct.category_id
         GROUP BY id
       ) AS cat ON e.id = cat.id
  JOIN counties ON e.county_id = counties.id
 WHERE e.preview = 0
   AND e.city LIKE '%London%'
 ORDER BY events.id DESC

Upvotes: 2

coolgeek
coolgeek

Reputation: 913

For one thing, get rid of the AND events.city LIKE '%London%' and make your location targeting dependent on a county_id (which I hope is a foreign key)

Upvotes: 0

Related Questions