Pomalý SELECT

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?

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.

10.5.2009 18:25 AraxoN | skóre: 47 | blog: slon_v_porcelane | Košice
Rozbalit Rozbalit vše Re: Pomalý SELECT

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: :-D

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

11.5.2009 08:51 Jirka
Rozbalit Rozbalit vše Re: Pomalý SELECT

Š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?

11.5.2009 14:05 AraxoN | skóre: 47 | blog: slon_v_porcelane | Košice
Rozbalit Rozbalit vše Re: Pomalý SELECT

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.

Dotaz: Pomalý SELECT

Odpovědi