[MySQL 5.5] Simpele query soms traag

[Olaf van der Spek]

Waarom is query 1 consequent zo traag, terwijl ie eigenlijk het snelst zou moeten zijn, er zijn namelijk geen topics met forumid 1.
Bug in de optimizer of zie ik iets simpels over het hoofd?

SQL:

mysql> select id, subject from topics where forumid = 1 order by lastpost desc limit 1;  
Empty set (0.58 sec)

mysql> select id, subject from topics where forumid = 118 order by lastpost desc limit 1;
+---------+---------+
| id      | subject |
+---------+---------+
| 1542189 | test    |
+---------+---------+
1 row in set (0.00 sec)

mysql> explain select id, subject from topics where forumid = 1 order by lastpost desc limit 1;
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | topics | index | NULL          | lastpost | 4       | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select id, subject from topics where forumid = 118 order by lastpost desc limit 1;  
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | topics | index | NULL          | lastpost | 4       | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

 CREATE TABLE `topics` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `forumid` int(10) unsigned NOT NULL DEFAULT '0',
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `locked` enum('yes','no') NOT NULL DEFAULT 'no',
  `sticky` enum('yes','no') NOT NULL DEFAULT 'no',
  `subject` varchar(255) NOT NULL,
  `posts` int(11) NOT NULL,
  `lastpost` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `userid` (`userid`),
  KEY `subject` (`subject`),
  KEY `lastpost` (`lastpost`)
) 

MySQL 5.5.47-0+deb8u1   

[GlowMouse]

De query gebruikt de index op lastpost. Dit betekent dat hij alle topics afgaat (op volgorde 'lastpost desc') totdat hij er eentje vindt met forumid=1.

[Olaf van der Spek]

Het aantal topics is 600.000, dat mag toch geen halve seconde duren? Alles zou in het geheugen moeten zitten.

[Morrar]

Geen antwoord op je vraag, maar waarom zet je geen index op forumid? Nu moet ie alle forumid's af om te ontdekken dat er geen forumid 1 is.

[GlowMouse]

Een halve seconde is vrij normaal voor 600.000 rijen. Bedenk wel dat subject en forumid niet in de index zijn opgeslagen, en de db-server dus ook de primary key in niet-sequentiële volgorde moet doorlopen.

Je kunt eenvoudig controleren of er disk i/o plaatsvindt, maar ik vermoed van niet. Let wel op dat je test met SQL_NO_CACHE, anders komt de query tijdens het testen wellicht in de querycache terecht en wordt hij niet uitgevoerd.

Morrar schreef op woensdag 16 maart 2016 @ 18:19:
Geen antwoord op je vraag, maar waarom zet je geen index op forumid? Nu moet ie alle forumid's af om te ontdekken dat er geen forumid 1 is.

Dan heb je weer een probleem in een druk forum. Een index op (forumid,lastpost) zou voor deze specifieke query het beste zijn, al zou ik ervoor pleiten om te denormaliseren en deze query helemaal nooit uit te voeren.

[ Voor 34% gewijzigd door GlowMouse op 16-03-2016 18:21 ]

[Morrar]

Alleen een index op forumid valt toch wel mee qua zwaarte? Gecombineerde index forumid + lastpost lijkt me niet per se nodig, aangezien de where filtering eerst plaatsvindt en het resultaat daarna waarschijnlijk wel te behappen is qua grootte.

[Olaf van der Spek]

Morrar schreef op woensdag 16 maart 2016 @ 18:19:
Geen antwoord op je vraag, maar waarom zet je geen index op forumid? Nu moet ie alle forumid's af om te ontdekken dat er geen forumid 1 is.

Oeps... daar hoort natuurlijk wel een index op. En die index op subject slaat nergens op zo, aangezien ie niet fulltext is. Bedankt!

[Olaf van der Spek]

GlowMouse schreef op woensdag 16 maart 2016 @ 18:19:
Een halve seconde is vrij normaal voor 600.000 rijen. Bedenk wel dat subject en forumid niet in de index zijn opgeslagen, en de db-server dus ook de primary key in niet-sequentiële volgorde moet doorlopen.

Daar is een key toch juist voor?

Je kunt eenvoudig controleren of er disk i/o plaatsvindt, maar ik vermoed van niet.

Hoe doe ik dat?
Query cache staat al uit, of die cache nou positief of negatief is is niet iedereen het over eens geloof ik.

Cachen van de lastpost info is een optie.

[ Voor 3% gewijzigd door Olaf van der Spek op 16-03-2016 19:30 ]

[Voutloos]

Nog sterker: Forumid is duidelijk een foreign key. Als je die gewoon altijd netjes aangeeft is je datamodel beter gegarandeerd en heb je bovendien het gros van de te indexen kolommen al op de automatische piloot geregeld.

Eerst je datamodel op orde hebben en daarna naar query/gebruikspatroon specifieke tweaks aan indexen kijken. Pas daarna heb je misschien onderbouwd dat je naar geavanceerder oplossingen als caching moet kijken.

Hint: voor een query zonder joins, met een where op 1 enkele vaste kolom plus eenvoudige order by kan het probleem en dus de oplossing never nooit niet in je app zelf zitten. Dat soort queries zou met 100x zoveel rows nog steeds maar enkele ms moeten zijn als de db structuur in orde is.

[ Voor 24% gewijzigd door Voutloos op 16-03-2016 22:05 ]

[Olaf van der Spek]

MyISAM doet niet aan foreign keys, helaas. InnoDB zou voor deze tabel wel een optie zijn maar voor andere tabellen loop ik dan weer tegen deadlocks aan.
Het gaat hier om bestaande oude slechte code en ook het datamodel is soms een puinhoop.
Ik ging er vanuit dat forumid wel een index had.