Autor Zpráva
danhill
Profil
Ahoj,
chci se zeptat.
Mám dva takové dotazy do MYSQL vykonávající se "současně" (v návaznosti ihned po sobě jako)
SELECT * FROM final WHERE `archive` = 'NO' AND gc_code NOT IN (SELECT gc_code FROM `finds` WHERE `user` = '".$userID."')
SELECT count(*) as count FROM `final`WHERE `archive` = 'NO' AND gc_code NOT IN (SELECT gc_code FROM `finds` WHERE `user` = '".$userID."')

Z tohoto prvního dotazu si vytvářím array jako vstup pro google mapu
$gc_array = array();
$gc_array[] = array($row['gc_code'].' - '.$row['gc_name'],$sirka.$row['lat'],$delka.$row['lon'],$row['gc_type'],$row['gc_code'],$row['gc_name']);

a druhý dotaz mi dá jen počet výsledků

Je pravda, že obě procházené tabulky obsahují více než 10000 řádků, ale na výsledek čekám cca 30s. (první dotaz 10s a druhý dotaz 20s)
Což už mi příjde hrozně dlouhá doba.

Tak mě napadá, jestli nemám ty dotazy nějaký špatný,nebo co bych mohl udělat pro to abych výsledek dostal rychleji.

Když dám pouze:
SELECT * FROM `final` WHERE `archive` = 'NO'
SELECT count(*) as count FROM `final` WHERE `archive` = 'NO'
Tak mám výsledek mnohem rychleji a to je tam ještě mnohem více výsledků.

Děkuji za jakékoli nápady.
juriad
Profil
danhill:
Ukaž podrobnosti o těch tabulkách v databázi. Jaké jsou tam indexy. A ukaz explain pokládaných dotazů.
danhill
Profil
Nevím jestli to je přesně to co chceš ukázat,nastavení MYSQL jsem se nikdy moc nezabýval,nebylo zatím potřeba, tak jsem v tom doslova loser.


Final:





Finds:

Kajman
Profil
danhill:
Necpěte sem obrázky, špatně se v tom orientuje a navrhují nápravy. Navíc jste nerozklik indexy, takže stále je neznáme.

Pro strukturu tabulky final napište sql příkaz
show create table `final`

A slovo explain dejte před ten dotaz, co trvá dlouho. Před jednoduchými a rychlými dotazy je k ničemu.

Zkusím si tedy tipnout. Staré verze mysql měly problémy s plánováním poddotazů použitým ve where a při přepsání dotazu do joinu problémy mizely. To by pak mohlo být svižnější něco jako

SELECT f.*
FROM   `final` f
       LEFT JOIN `finds` n
              ON f.gc_code = n.gc_code
                 AND n.user = 42
WHERE  f.archive = 'NO'
       AND n.gc_code IS NULL

Pro tento dotaz je dobrý mít v tabulce finds index nad sloupcem user nebo nad kombinací sloupcu user a gc_code. V tabulce final pak buď nad sloupcem archive, nebo nad kombinací sloupců archive a gc_code. Vhodnost indexů si musíte ověřit nad reálnými daty.

Navíc v php nemusíte dělat druhý stejný dotaz s count, počet řádků prvního dotazu zjistíte funkcí mysqli_num_rows.
danhill
Profil
Omlouvám se, no úplně nevím jak to tady vložit jinak :)

Vytvořil: phpMyAdmin 4.1.14.6 / MySQL 5.5.47-0+deb7u1
SQL dotaz: explain SELECT count(*) as count FROM `final`WHERE `archive` = 'NO' AND gc_code NOT IN (SELECT gc_code FROM `finds` WHERE `user` = '487');
Řádky: 2

 Aktuální výběr neobsahuje unikátní klíč. Editování v mřížce, zaškrtávací políčka nebo odkazy na editaci a mazání proto nejsou k dispozici.
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    PRIMARY    final    ALL    NULL    NULL    NULL    NULL    10125    Using where
2    DEPENDENT SUBQUERY    finds    ALL    NULL    NULL    NULL    NULL    8798    Using where



SQL dotaz: show create table `final`;
Řádky: 1

 Aktuální výběr neobsahuje unikátní klíč. Editování v mřížce, zaškrtávací políčka nebo odkazy na editaci a mazání proto nejsou k dispozici.
Table    Create Table
final    CREATE TABLE `final` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `date` datetime NOT NULL,
 `user` int(10) NOT NULL,
 `gc_code` varchar(10) NOT NULL,
 `gc_name` varchar(255) NOT NULL,
 `ns` varchar(1) NOT NULL,
 `lat` varchar(50) NOT NULL,
 `ew` varchar(1) NOT NULL,
 `lon` varchar(50) NOT NULL,
 `gc_type` varchar(20) NOT NULL,
 `gc_country` varchar(50) NOT NULL,
 `gc_region` varchar(50) NOT NULL,
 `drive_in` varchar(5) NOT NULL,
 `verify` varchar(20) NOT NULL,
 `archive` varchar(3) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10085 DEFAULT CHARSET=utf8



SQL dotaz: show create table `finds`;
Řádky: 1

 Aktuální výběr neobsahuje unikátní klíč. Editování v mřížce, zaškrtávací políčka nebo odkazy na editaci a mazání proto nejsou k dispozici.
Table    Create Table
finds    CREATE TABLE `finds` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `gc_code` varchar(10) NOT NULL,
 `user` varchar(50) NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8635 DEFAULT CHARSET=utf8
Vytisknout


Moc nevím co jsou to indexy a kde je rozkliknout,ale zkusím to ... Tuším asi,že to bude o tom, co je v tabulce na vyšší prioritě.
Jdu ještě vyzkoušet ten Váš dotaz s Joinem a taky tu php funkci num_rows ... To mě nenapadlo,pravda, mnohé by to mohlo urychlit ...


Tak jsem použil mysqli_num_rows a tím se zkrátila doba z 30ti na 17s

Časový rozdíl mezi
SELECT * FROM final WHERE `archive` = 'NO' AND gc_code NOT IN (SELECT gc_code FROM `finds` WHERE `user` = '".$userID."')

a

SELECT f.* FROM   `final` f LEFT JOIN `finds` n ON f.gc_code = n.gc_code AND n.user = '".$userID."' WHERE  f.archive = 'NO' AND n.gc_code IS NULL

není vůbec žádný.
V obou případech jsem na 17s

Je to lepší,to každopádně,ale stále mi to příjde hodně.

A s těmi indexy jako nevím ... Jak a kde to správně nastavit :(
Kajman
Profil
PMA moc neznám, podle screenshotů indexům říkají klíče. Nejsem si jistý, jak to tam musíte klikat, aby bylo správné pořadí u složených indexů. Zkuste přímo příkazy k jejich vytvoření
ALTER TABLE `final`  ADD INDEX `archive_gc_code` (`archive`, `gc_code`);
ALTER TABLE `finds` ADD INDEX `user_gc_code` (`user`, `gc_code`);
danhill
Profil
Woooow ... tak tohle opravdu velmi pomohlo ...
Po aplikaci těch indexů se vysledek vrátí do 3s ...
Úžasné, mockrát děkuji za pomoc ...
Hodně mě tahle diskuze posunula ...
DĚKUJI !

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: