Dotaz: Pomalý SELECT

[Jirka]

Předem se omlouvám, za delší popis situace. Mám dvě tabulky, jedna je velká (milion záznamů) a druhá je číselník.
Struktura tabulek:

CREATE TABLE `tab` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `login` char(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `login` ( `login` )
) ENGINE=MyISAM;

CREATE TABLE `user` (
  `login` char(1) NOT NULL,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY ( `login` ) 
) ENGINE=MyISAM;

Naplním daty číselník:

INSERT INTO `user` (`login`, `name`) VALUES ('a', 'AAA');
INSERT INTO `user` (`login`, `name`) VALUES ('b', 'BBB');
INSERT INTO `user` (`login`, `name`) VALUES ('c', 'CCC');
INSERT INTO `user` (`login`, `name`) VALUES ('d', 'DDD');
INSERT INTO `user` (`login`, `name`) VALUES ('e', 'EEE');
INSERT INTO `user` (`login`, `name`) VALUES ('f', 'FFF');
INSERT INTO `user` (`login`, `name`) VALUES ('g', 'GGG');
INSERT INTO `user` (`login`, `name`) VALUES ('h', 'HHH');
INSERT INTO `user` (`login`, `name`) VALUES ('i', 'III');
INSERT INTO `user` (`login`, `name`) VALUES ('j', 'JJJ');

A pomocí PHP velkou tabulku náhodnými údaji:

for ($i = 0; $i < 1000000; $i++)
     mysql_query("INSERT INTO `tab` (`login`) VALUES ('".chr(rand(97,106))."')");

Nyní potřebuji provést následující dotaz:

SELECT SQL_CALC_FOUND_ROWS `tab`.`id`, `tab`.`login`, `user`.`name` 
FROM `tab` 
LEFT JOIN `user` ON `tab`.`login` = `user`.`login` 
ORDER BY `tab`.`id` DESC 
LIMIT 1;
+---------+-------+------+
| id      | login | name |
+---------+-------+------+
| 1000000 | i     | III  | 
+---------+-------+------+
1 row in set (2.58 sec)

Co mě vadí, je doba trvání dotazu. Pokud odstraním SQL_CALC_FOUND_ROWS, dotaz se zrychlí:

SELECT `tab`.`id`, `tab`.`login`, `user`.`name` 
FROM `tab` 
LEFT JOIN `user` ON `tab`.`login` = `user`.`login` 
ORDER BY `tab`.`id` DESC 
LIMIT 1;
+---------+-------+------+
| id      | login | name |
+---------+-------+------+
| 1000000 | i     | III  | 
+---------+-------+------+
1 row in set (0.01 sec)

Nebo když odstraním LEFT JOIN, dotaz se opět zrychlí:

SELECT SQL_CALC_FOUND_ROWS `tab`.`id`, `tab`.`login` 
FROM `tab` 
ORDER BY `tab`.`id` DESC 
LIMIT 1;
+---------+-------+
| id      | login |
+---------+-------+
| 1000000 | i     | 
+---------+-------+
1 row in set (0.32 sec)

Kupodivu, když přidám WHERE, tak se dotaz také zrychlí:

SELECT SQL_CALC_FOUND_ROWS `tab`.`id`, `tab`.`login`, `user`.`name` 
FROM `tab` 
LEFT JOIN `user` ON `tab`.`login` = `user`.`login` 
WHERE `tab`.`login`='a' 
ORDER BY `tab`.`id` DESC 
LIMIT 1;
+--------+-------+------+
| id     | login | name |
+--------+-------+------+
| 999998 | a     | AAA  | 
+--------+-------+------+
1 row in set (0.17 sec)

Jenže já bych potřeboval zrychlit ten první dotaz, ale nevím jak na to. Může mi někdo poradit, či vysvětlit proč dostávám tak rozdílné časy?

Odpovědi

[Jirka]

Ještě dodávám, že to testuji na openSUSE 11.1 a na  MySQL 5 z distribuce.

[Vojtěch Horký]

Nejde náhodou o to, že s tou klauzulí SQL_CALC_FOUND_ROWS server vykonává ten dotaz jako by tam nebyl ten LIMIT 1, aby zjistil, kolik bude řádků (tudíž bez toho WHERE to bude asi full-scan) a teprve pak to ořeže?

Nevyšlo by rychleji ptát se na počet řádků pomocí dalšího COUNT(*) dotazu (to by měl stačit průchod přes index)?

BTW - indexy máte vytvořené?

[Filip Jirsák]

Spojovat tabulky přes řetězce je, řekněme, suboptimální – daleko lépe se bude databázi pracovat, pokud v té tabulce vytvoříte číselný primární klíč a spojovat budete přes něj. Dotaz bez SQL_CALC_FOUND_ROWS je rychlejší, protože v tom druhém dotazu používáte LIMIT – s ním databázi stačí, když najde první výsledek, a ten vám vrátí. Když ale musí spočítat SQL_CALC_FOUND_ROWS, musí stejně dotaz provést celý, jako by tam LIMIT nebyl.

[Jirka]

Díky za náměty a rady. Všechny jsem je postupně vyzkoušel a navíc jsem ještě zkusil změnit engine na InnoDB. Změna enginu zrychlila dotaz více jak dvakrát. Provést dotaz bez SQL_CALC_FOUND_ROWS a následně použít COUNT(*) opět zrychlilo dotaz dvakrát. Pokud jsem tabulky spojil přes TINYINT došlo je k malému zrychlení. Vítězem je tedy kombinace všech návrhů:

CREATE TABLE `tab` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `login` TINYINT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `login` ( `login` )
) ENGINE=InnoDB;

CREATE TABLE `user` (
  `login` TINYINT NOT NULL,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY ( `login` ) 
) ENGINE=InnoDB;

Naplnit daty

SELECT `tab`.`id`, `tab`.`login`, `user`.`name` 
FROM `tab` 
LEFT JOIN `user` ON `tab`.`login` = `user`.`login` 
ORDER BY `tab`.`id` DESC 
LIMIT 1;
+---------+-------+------+
| id      | login | name |
+---------+-------+------+
| 1000000 |     5 | EEE  | 
+---------+-------+------+
1 row in set (0.00 sec)

SELECT count(*) 
FROM `tab` 
LEFT JOIN `user` ON `tab`.`login` = `user`.`login` 
ORDER BY `tab`.`id` DESC;
+----------+
| count(*) |
+----------+
|  1000000 | 
+----------+
1 row in set (0.49 sec)

Což je pětinásobné zrychlení. Nevýhodou je u enginu InnoDB delší vkládání dat a absence fulltexty. Všem díky.

[AraxoN]

 

SELECT count(*) 
FROM `tab` 
LEFT JOIN `user` ON `tab`.`login` = `user`.`login` 
ORDER BY `tab`.`id` DESC;
+----------+
| count(*) |
+----------+
|  1000000 | 
+----------+
1 row in set (0.49 sec)

Což je pětinásobné zrychlení. Nevýhodou je u enginu InnoDB delší vkládání dat a absence fulltexty. Všem díky.

A ešte sa Ti to urýchli asi miliónkrát, keď odtiaľ vyhodíš ten zbytočný LEFT JOIN a ORDER BY:

SELECT count(*) 
FROM `tab`;
+----------+
| count(*) |
+----------+
|  1000000 | 
+----------+
1 row in set (0.00 sec)

[Jirka]

Špatný nápad to není, on je dokonce skvělý. Celé je to ve třídě, která se stará o zobrazení jakéhokoli SQL dotazu v prohlížeči, ta třída se stará o stránkování. K tomu potřebuji znát i celkový počet řádků. Takže se ten SQL dotaz musí upravit programem. Zatím je postup následující:

přidám k SQL dotazu LIMIT a provedu dotaz

odstraním vše mezi SELECT a FROM a dám tam COUNT(*)

A teď ještě vyhodit všechny LEFT JOIN a ORDER BY, ale nechat všechny WHERE, GROUP, HAVING. Nepopletl jsem to?

[AraxoN]

Ak je ten SELECT vyrobený automaticky, tak to takto fungovať nebude... Teda pri tomto jednom by to fungovalo, ale nie je to univerzálne a ani to univerzálne byť nemôže.

Niečo podobné na zobrazovanie listingov používame aj my (komponent, ktorý robí najprv SELECT ... LIMIT OFFSET, a potom z toho odvodí ešte SELECT COUNT), a riešime to tak, že ten druhý SELECT na zistenie počtu riadkov sa tam dá nanútiť, ak automaticky vyrobený SELECT nie je optimálny, alebo nefunguje správne.