0

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)

0.3s vs. 32s

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?

2 Answers 2

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.

0

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
2
  • MariaDB was probably able to do the optimization for you, and make both queries equivalent, MySQL wasn't.
    – joanolo
    Jun 4, 2017 at 9:19
  • This query may yield inconsistent results and is not equivalent to the original. Jun 4, 2017 at 20:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.