Slower subquery performance on older MySQL

Question

Same query

SELECT z.userid, z.usermeno, z.userpriezvisko, z.userfirma, z.useriban 
       (SELECT cis_faktury FROM hlavicka_faktury
        WHERE userid = z.userid ORDER BY cis DESC LIMIT 1) as cis_faktury
FROM zmluvy z;

But a lot slower executed on mysql Ver 14.14 Distrib 5.5.44 (32 seconds)

The faster runs under mysql Ver 15.1 Distrib 10.1.24-MariaDB (0.3 seconds)

CREATE TABLE `hlavicka_faktury` (
  `cis` int(11) NOT NULL AUTO_INCREMENT,
  `cis_fakturacii` int(2) NOT NULL,
  `cis_faktury` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `datum_vyst` int(11) NOT NULL,
  `datum_spl` int(11) NOT NULL,
  `datum_dod` int(11) NOT NULL,
  `zaklad` double(14,4) NOT NULL,
  `dph` double(12,4) NOT NULL,
  `zaokruhlenie` double(10,4) NOT NULL,
  `celkom` double(14,4) NOT NULL,
  `sposob_uhrady` int(2) NOT NULL,
  `uhradene` tinyint(1) NOT NULL,
  `stornovane` int(10) NOT NULL,
  `stornofa` tinyint(1) NOT NULL,
  `opakovanie` int(11) NOT NULL,
  `odoslane` tinyint(1) NOT NULL,
  `fo` tinyint(1) NOT NULL,
  `mena` varchar(3) COLLATE utf8_bin NOT NULL DEFAULT 'EUR',
  PRIMARY KEY (`cis`),
  KEY `cis_fakturacii` (`cis_fakturacii`),
  KEY `cis_faktury` (`cis_faktury`),
  KEY `userid` (`userid`),
  KEY `datum_spl` (`datum_spl`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `zmluvy` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `userpass` varchar(15) COLLATE utf8_bin NOT NULL,
  `useraktiv` int(1) NOT NULL,
  `usermeno` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `userpriezvisko` varchar(50) COLLATE utf8_bin NOT NULL,
  `useremail1` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `userfirma` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `userico` varchar(11) COLLATE utf8_bin DEFAULT NULL,
  `userdic` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `usericdph` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `userbanka` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `usercu` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `useriban` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `old_userid` int(11) NOT NULL,
  `user_platdisciplina` double(10,2) NOT NULL,
  `user_efaktura` enum('0','1') COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Any suggestion how to improve without MySQL upgrade?

Answer 1

For further performance, replace

KEY `userid` (`userid`),

With

KEY `ucf` (`userid`, `cis`, `cis_faktury`),

Your Answer has a problem: It is "wrong" to GROUP BY only some of the non-aggregate columns in the SELECT. (Newer versions spit at users for doing so.) And, I can't see how the query would return the same results.

MySQL 5.6 and MariaDB 10.0 added a number of different optimizations. It is hard to say what exactly lead to the timing difference you see.

Moving to InnoDB is also advised.

Answer 2

Nailed it with

SELECT z.userid, z.usermeno, z.userpriezvisko, z.userfirma, z.useriban, cis_faktury
FROM zmluvy z
LEFT JOIN hlavicka_faktury hf USING(userid)
GROUP BY hf.userid
ORDER BY hf.cis DESC