Announcement

Announcement Module
Collapse
No announcement yet.

optimize query - how can I get rid of temporary/filesort?

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

  • optimize query - how can I get rid of temporary/filesort?

    I'm trying to optimize a query that often appears in the slow query log. Some background info: we have a site where we publish news articles.
    Every article (nieuwsbericht) is linked to one country (land), one category (categorie) and several sectors, of which one is marked as 'head' sector.
    Countries are linked to regions (regio), which are linked to a continent (werelddeel). This query is on a page which displays articles in a selected
    region or continent. So the query contains the following restrictions:
    - articles should be linked to a country in a particular contintent or region
    - articles with a certain sector as 'head' sector (gerelateerd = 1) should not be retrieved
    - articles should have the 'published' (gepubliceerd) status
    - articles will be displayed in chronological order, starting with the latest, so the query is ordered by date (datum_public) and time (tijd_public).


    The query:


    SELECT n.nieuwsbericht_id, n.datum_public, n.tijd_public, n.titel, n.ondertitel, n.bericht, n.bericht_afkappen, n.afbeelding, n.afb_plaatsing, n.afb_breedte, n.afb_hoogte, n.link, n.aantal_reacties, c.omschrijving AS categorie, r.naam_en AS regio, w.naam_en AS werelddeel FROM regio r INNER JOIN land l ON (l.regio_id = r.regio_id) INNER JOIN werelddeel w ON (w.werelddeel_id = r.werelddeel_id) INNER JOIN nieuwsbericht n ON (n.land_id = l.land_id) INNER JOIN categorie c ON (c.categorie_id = n.categorie_id) INNER JOIN nieuwsbericht_has_sector nhs ON (nhs.nieuwsbericht_id = n.nieuwsbericht_id) WHERE r.regio_id = 22 AND nhs.sector_id < 50 AND nhs.gerelateerd = 1 AND n.gepubliceerd = 1 ORDER BY n.datum_public DESC, n.tijd_public DESC LIMIT 14;


    The explain output:


    +----+-------------+-------+--------+-----------------------------------------------------------------------------+------------------------+---------+--------------------------------------+------+---------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+-----------------------------------------------------------------------------+------------------------+---------+--------------------------------------+------+---------------------------------+| 1 | SIMPLE | r | const | PRIMARY,fkindex_werelddeel | PRIMARY | 1 | const | 1 | Using temporary; Using filesort | | 1 | SIMPLE | w | const | PRIMARY | PRIMARY | 1 | const | 1 | | | 1 | SIMPLE | l | ref | PRIMARY,fkindex_regio | fkindex_regio | 1 | const | 9 | Using index | | 1 | SIMPLE | n | ref | PRIMARY,fkindex_categorie,fkindex_land | fkindex_land | 6 | db.l.land_id | 59 | Using where | | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 1 | db.n.categorie_id | 1 | | | 1 | SIMPLE | nhs | ref | PRIMARY,fkindex_nieuwsbericht,fkindex_sector | PRIMARY | 3 | db.n.nieuwsbericht_id | 1 | Using where | +----+-------------+-------+--------+-----------------------------------------------------------------------------+------------------------+---------+--------------------------------------+------+---------------------------------+


    First thing I was thinking of was to make the query work without a filesort and temporary table. Can this be achieved by adding indices or modifying the
    query? Are there any other ways in which the query can be optimized?

    Or should I look for optimization on application or schema design level? For example by dropping some of the restrictions in the query and filter the
    records in the application. Or, for example, have the region and continent id's as duplicated rows in the news article table.

  • #2
    Can you please also show 'SHOW CREATE TABLE' for all tables which are used in query?

    Comment


    • #3
      Sure thing, here they are:

      NIEUWSBERICHT (news article)

      CREATE TABLE `nieuwsbericht` ( `nieuwsbericht_id` mediumint(8) unsigned NOT NULL auto_increment, `land_id` varchar(3) default NULL, `categorie_id` tinyint(3) unsigned default NULL, `medewerker_id` smallint(5) unsigned default NULL, `datum_tijd_upload` datetime NOT NULL, `datum_public` date default NULL, `uren_online` tinyint(3) unsigned default NULL, `titel` varchar(300) NOT NULL, `ondertitel` varchar(300) default NULL, `bericht` text NOT NULL, `bericht_afkappen` tinyint(1) unsigned NOT NULL default '0', `link` varchar(700) default NULL, `afbeelding` varchar(200) default NULL, `afb_plaatsing` enum('','default','left','right') default NULL, `gevalideerd` tinyint(1) unsigned NOT NULL default '0', `gepubliceerd` tinyint(1) unsigned NOT NULL default '0', `hoofd_positie` tinyint(2) unsigned default NULL, `dringend` tinyint(1) unsigned default '0', `tijd_public` time default NULL, `afb_breedte` smallint(5) unsigned default NULL, `afb_hoogte` smallint(5) unsigned default NULL, `flag` tinyint(1) unsigned default '0', `titel_en` varchar(300) default NULL, `ondertitel_en` varchar(300) default NULL, `aantal_reacties` smallint(5) unsigned default '0', PRIMARY KEY (`nieuwsbericht_id`), KEY `fkindex_medewerker` (`medewerker_id`), KEY `fkindex_categorie` (`categorie_id`), KEY `fkindex_land` (`land_id`), KEY `index_val_pub` (`gevalideerd`,`gepubliceerd`), KEY `index_datetime` (`datum_public`,`tijd_public`), CONSTRAINT `nieuwsbericht_ibfk_1` FOREIGN KEY (`medewerker_id`) REFERENCES `medewerker` (`medewerker_id`) ON UPDATE CASCADE, CONSTRAINT `nieuwsbericht_ibfk_2` FOREIGN KEY (`categorie_id`) REFERENCES `categorie` (`categorie_id`) ON DELETE NO ACTION ON UPDATE CASCADE, CONSTRAINT `nieuwsbericht_ibfk_3` FOREIGN KEY (`land_id`) REFERENCES `land` (`land_id`) ON DELETE NO ACTION ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=22712 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=FIXED;


      NIEUWSBERICHT_HAS_SECTOR

      CREATE TABLE `nieuwsbericht_has_sector` ( `nieuwsbericht_id` mediumint(8) unsigned NOT NULL, `sector_id` tinyint(3) unsigned NOT NULL, `gerelateerd` tinyint(1) unsigned default NULL, PRIMARY KEY (`nieuwsbericht_id`,`sector_id`), KEY `fkindex_nieuwsbericht` (`nieuwsbericht_id`), KEY `fkindex_sector` (`sector_id`), CONSTRAINT `nieuwsbericht_has_sector_ibfk_1` FOREIGN KEY (`nieuwsbericht_id`) REFERENCES `nieuwsbericht` (`nieuwsbericht_id`) ON UPDATE CASCADE, CONSTRAINT `nieuwsbericht_has_sector_ibfk_2` FOREIGN KEY (`sector_id`) REFERENCES `sector` (`sector_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=FIXED;


      LAND (country)

      CREATE TABLE `land` ( `land_id` varchar(3) NOT NULL, `regio_id` tinyint(3) unsigned NOT NULL, `naam_nl` varchar(40) NOT NULL, `naam_en` varchar(40) NOT NULL, `actief` tinyint(1) unsigned default '1', `land` tinyint(1) unsigned default NULL, `special` tinyint(1) unsigned default '0', PRIMARY KEY (`land_id`), KEY `fkindex_regio` (`regio_id`), KEY `index_land` (`land`), CONSTRAINT `land_ibfk_1` FOREIGN KEY (`regio_id`) REFERENCES `regio` (`regio_id`) ON DELETE NO ACTION ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=FIXED;


      REGIO (region)

      CREATE TABLE `regio` ( `regio_id` tinyint(3) unsigned NOT NULL auto_increment, `werelddeel_id` tinyint(3) unsigned NOT NULL, `naam` varchar(25) NOT NULL, `naam_en` varchar(25) default NULL, `regio` tinyint(1) unsigned default NULL, `positie` tinyint(2) unsigned default NULL, PRIMARY KEY (`regio_id`), KEY `fkindex_werelddeel` (`werelddeel_id`), KEY `index_regio` (`regio`), CONSTRAINT `regio_ibfk_1` FOREIGN KEY (`werelddeel_id`) REFERENCES `werelddeel` (`werelddeel_id`) ON DELETE NO ACTION ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=FIXED;


      WERELDEEL (continent)

      CREATE TABLE `werelddeel` ( `werelddeel_id` tinyint(3) unsigned NOT NULL auto_increment, `naam` varchar(15) NOT NULL, `naam_en` varchar(15) default NULL, `positie` tinyint(1) unsigned default NULL, PRIMARY KEY (`werelddeel_id`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=FIXED;


      CATEGORIE (category)

      CREATE TABLE `categorie` ( `categorie_id` tinyint(3) unsigned NOT NULL auto_increment, `omschrijving` varchar(30) NOT NULL, `actief` tinyint(1) unsigned default '1', PRIMARY KEY (`categorie_id`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

      Comment

      Working...
      X