Announcement

Announcement Module
Collapse
No announcement yet.

Need help for optimizing query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Need help for optimizing query

    Hello!

    I'm running a website where the members can manage their own music collection. The database is growing by each day and so the results of the queries too. Which makes some of them rather slow by this time. I need special help with one query, who reads all the cds, lps etc. of a members collection and sorts them in a user defined order (artist name first or cd title first etc.). Here are the main tables for this:


    Table for artist information (excerpt):
    ---------------------------------------


    CREATE TABLE `artist` ( `aID` int(10) unsigned NOT NULL auto_increment, `aName` varchar(100) NOT NULL default '', `aFName` varchar(50) NOT NULL default '', `aLand` varchar(3) NOT NULL default 'XXX', `aBanned` tinyint(1) NOT NULL default '0', PRIMARY KEY (`aID`), KEY `aName` (`aName`), KEY `aBanned` (`aBanned`),) ENGINE=InnoDB;



    Table for information about cds, lps etc. (excerpt):
    ----------------------------------------------------


    CREATE TABLE `medium` ( `mID` int(10) unsigned NOT NULL auto_increment, `uID` int(10) unsigned NOT NULL default '0', `lID` int(10) unsigned NOT NULL default '0', `mEAN` varchar(15) NOT NULL default '', `mName` varchar(150) NOT NULL default '', `mTypeName` varchar(30) NOT NULL default '', `mYear` smallint(4) NOT NULL default '0', `mYearOriginal` smallint(4) NOT NULL default '0', `mLand` varchar(3) NOT NULL default '', `mSampler` tinyint(1) NOT NULL default '0', `mBootleg` tinyint(1) NOT NULL default '0', `mBanned` tinyint(1) NOT NULL default '0', PRIMARY KEY (`mID`), KEY `mYear` (`mYear`), KEY `uID` (`uID`), KEY `mSampler` (`mSampler`), KEY `mBootleg` (`mBootleg`), KEY `mEAN` (`mEAN`), KEY `mBanned` (`mBanned`)) ENGINE=InnoDB;



    Table which connects the artists with the media:
    ------------------------------------------------


    CREATE TABLE `rel_am` ( `relAMID` int(10) unsigned NOT NULL auto_increment, `aID` int(10) unsigned NOT NULL default '0', `mID` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`relAMID`), UNIQUE KEY `aID_2` (`aID`,`mID`), KEY `aID` (`aID`), KEY `mID` (`mID`)) ENGINE=InnoDB;



    Table which connects the users with the media:
    ----------------------------------------------


    CREATE TABLE `rel_mc` ( `relMCID` int(10) unsigned NOT NULL auto_increment, `mID` int(10) unsigned NOT NULL default '0', `uID` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`relMCID`), KEY `mID` (`mID`), KEY `uID` (`uID`)) ENGINE=InnoDB;




    Typical query for this:
    -----------------------
    (Sort all media in my collection by: sampler at the end, artist names first, year of release next and than the name of the record and it's type)


    SELECT a.aID, a.aName, a.aFName, a.aLand, m.mID, m.mName, m.mTypeName, m.mYear, m.mYearOriginal, m.mLand, m.mSampler, m.mBootlegFROM rel_mc AS mc LEFT JOIN (`medium` AS m, rel_am AS am, artist AS a) ON (m.mID = mc.mID AND am.mID = mc.mID AND a.aID = am.aID) WHERE mc.uID = 1AND m.mBanned < 4AND a.aBanned < 2GROUP BY m.mIDORDER BY m.mSampler ASC, a.aName ASC, a.aFName ASC, m.mYearOriginal ASC, m.mName ASC, m.mTypeName ASC LIMIT 0, 100;



    A query of this type could take from one up to three or more seconds, depending on how big the collection of one user is.

    The problem is: the ORDER BY can vary from member to member. So I can't put an index on the fields in the ORDER statement.

    Any good hint how to optimize this is well appreciated.
Working...
X