Optimizing SQL Query for mySQL

Question

I just changed from Access to mySQL. I have a few tables, called "Veranstaltung" and "Veranstaltung_User". I want to get all events (=Veranstaltung) to which users have subsribed, exept the one, the user is viewing.

I did this with the following statment

SELECT VID, Bezeichnung, Datum 
FROM tbl_Veranstaltung 
WHERE year(Datum) = 2012 
    AND VID <> 613 
    AND VID IN (SELECT BewerbID 
                FROM Veranstaltung_User 
                WHERE UserID IN (SELECT UserID 
                                FROM Veranstaltung_User 
                                WHERE BewerbID = 613) 
                    AND UserID <> 0) 
ORDER BY Datum

In Access, this query was quite fast - in mySQL it needs 44,56 seconds - too much ;-)

how can I optimize my statement for faster quering? (mySQL > 5, tables ware MyISAM)

thanks for help

MatBailie · Accepted Answer · 2012-02-21 14:33:17Z

2

Two changes:
1. Uses JOINs instead of nested IN clauses
2. Uses <= and > instead of the YEAR() function

The advantage of change 1 is that sometime IN performance can be worse than expected, and nesting them will exascerabate that.

The advantage of change 2 is that it allows an index seek to find a range of records. Where-as using YEAR() forcfes a scan of the entire table/index.

Both of which assume you have appropriate indexes.

SELECT
  V.VID, V.Bezeichnung, V.Datum
FROM
  tbl_Veranstaltung     V
INNER JOIN
  Veranstaltung_User    VUser
    ON VUser.BewerbID = V.VID
INNER JOIN
  Veranstaltung_User    VUser2
    VUser.UserID = VUser2.UserID
WHERE
      V.Datum >= '20120101'
  AND V.Datum <  '20130101'
  AND V.VID <> 613
  AND VUser.UserID <> 0
  AND VUser2.BewerbID  = 613
ORDER BY
  Datum

answered Feb 21, 2012 at 14:33

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simon at The Access Group · Accepted Answer · 2012-02-21 14:44:33Z

This query may need adjusting, have tried to make some assumptions as to table layout + will update based on any clarification offered

SELECT 
    v.VID, 
    v.Bezeichnung, 
    v.Datum 

FROM tbl_Veranstaltung AS v

INNER JOIN Veranstaltung_User AS vu
ON vu.BewerbID = v.VID
AND vu.UserID = [THIS USER ID]
AND vu.BewerbID <> 613

// Used a BETWEEN so that MySQL does not need to apply YEAR() to every row
WHERE v.Datum BETWEEN DATE('2012-01-01') AND DATE('2012-12-30');

I would also advise the following two indexes

CREATE INDEX dateIndex ON tbl_Veranstaltung (`Datum`);
CREATE INDEX userBewerIndex ON Veranstaltung_User (`BewerbID`,`UserID`);

Following that the query should be practically instant by comparison

Edit If I take the source query verbatim I would use the following

SELECT 
    v.VID, 
    v.Bezeichnung, 
    v.Datum 

FROM tbl_Veranstaltung AS v

INNER JOIN Veranstaltung_User AS vu
ON vu.BewerbID = v.VID
AND vu.BewerbID <> 613
AND vu.UserID <> 0

WHERE v.Datum BETWEEN DATE('2012-01-01') AND DATE('2012-12-30');

Collectives™ on Stack Overflow

Optimizing SQL Query for mySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related