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

MySQL & MariaDB Other MySQL® Questions
1

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?

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;