[SQL] Grote database, errrrg trage query

[Verwijderd]

Ik loop hier ondertussen de hele dag mee te kloten, dus ik vraag het even. Ik werk in CodeIgniter voor het maken van een boeken website. De boeken database heeft meer dan 1 miljoen rijen.

Een index maken gaat niet (behalve primary key), want verder zijn er geen unieke velden. Ik heb de query gecached die traag is en toch blijft het heeeeel erg traag.

Dit is de query:
SELECT title, description, image, isbn FROM boeken WHERE category = '$cat' ORDER BY title ASC LIMIT $page,10

$page = bijv. 20 of 50 (pagination).

Het vreemde is dat de query in de shell binnen een seconde of minder wordt uitgevoerd, in PMA iets minder snel, maar op de web site duurt het zeker 20 seconden voordat de pagina geladen wordt.

Wat er gebeurt in pseudo-code:

HAAL ALLE BOEKEN OP IN RUBRIEK %RUBRIEK%. GEEF TIEN RIJEN TERUG.
--

Even de functie:

PHP:

function show_cat_list($page=0,$perpage=10,$cat='') {
       $this->db->cache_on();   
       $this->output->cache(1000);    
       $theme_url        = $this->lib_frontend->get_classVariable('theme_url');
       $theme_folder     = $this->lib_frontend->get_classVariable('theme_folder');
       $base_url         = base_url();
       $result           = array();
       $category_details= $this->common_model->category_row($cat);
       $catid            = (isset($category_details['id']))?$category_details['id']:0;
       $catname          = (isset($category_details['name']))?$category_details['name']:$cat;
       $total_records    = $this->common_model->books_in_category_count($cat); // Can be fastened by a cron which saves the number
                                                                              // of records by category.
       $htmData          = '';
         

       if($total_records > 0){
            $this->db->cache_on();  
            $query = $this->db->query("SELECT title, description, image, isbn FROM boeken WHERE category = '$cat' LIMIT ".$this->uri->segment(3).",2"); 
            $records = $query->result_array(); 

            //$records    =    $this->common_model->books_in_category_list($cat,0,10);
            ### paging creation
            $config['base_url']         =    $base_url."welcome/listing/";
            $config['total_rows']       =    $total_records;
            $config['per_page']         =    $perpage;
            $config['cur_page']         =    $page;
            $config['filter']           =    "category";
            $config['value']            =    $cat;
            $this->pagination->initialize($config);    
            $links                      =    $this->pagination->create_links();
            $bookTitles                 =   array();
            
            foreach($records as $rkey => $rval){ 
                $bookTitles[] = $rval['title'];

                /*if($rval['image'] != '' && is_file($theme_folder."images/".$rval['image'])){
                    $bk_img1 = $theme_url."images/".$rval['image'];
                }
                else{
                    $bk_img1 = $theme_url."images/nopicture.png";
                }*/
                /*if($rval['image'] != ''){
                    $bk_img1 = $rval['image'];
                }
                else{
                    $bk_img1 = $theme_url."images/nopicture.png";
                } */
                //$bk_img1 = $rval['image']; 
                $htmData .= "<div style='width:479px; height:auto; overflow:hidden; margin:5px;' class='content'>";
                $htmData .= "<table style='width: 479px;' class='content'>";
                $htmData .= "<tr>";
                $htmData .= "<td colspan='2' style='background:url(".$theme_url."images/title.jpg) no-repeat;width:479px;height:30px;line-height:25px;padding-left:10px;' valign='middle'>";
                $htmData .= "<a href='".$base_url."show-book/ibsn/".$rval['isbn'].".html' style='font-weight:bold;color:white;'>".$rval['title']."</a>";
                $htmData .= "</td>";
                $htmData .= "</tr>";
                $htmData .= "</table>";
                $htmData .= "<table style='width: 479px; border: 1px solid #253044;border-top:none;' class='content'><tr>";
                $htmData .= "<td width='299' align='left'><table style='width:98%;' class='content'>";
                //$htmData .= "<tr><th style='width:100%;' align='left'><a href='".$base_url."show-book/ibsn/".$rval['isbn'].".html'>".$rval['title']."</a></th></tr>";
                $htmData .= "<tr><td style='width:100%; padding-top:10px; padding-bottom:10px;' class='content alignJustify'>";
                                
                if(strlen($rval['description']) < 10){
                    $htmData .= "Er is geen beschrijving van dit boek beschikbaar";
                }
                else if(strlen($rval['description']) > 150){
                    $htmData .= substr($rval['description'],0,150) . "...";
                }
                else{
                    $htmData .= $rval['description'];
                }
                $htmData .= "</td></tr>";
                //$htmData .= "<tr><td style='width:100%;' class='content alignJustify'>Merchant : ".$rval['merchant']."</td></tr>";
                $htmData .= "<tr><td style='width:100%; padding-bottom:10px;' class='content alignJustify'>ISBN : <a href='".$base_url."show-book/ibsn/".$rval['isbn'].".html'>".$rval['isbn']."</a></td></tr>";
                $htmData .= "</table></td></tr></table></div>";
            }    
            $htmData .= "<table style='width:479px;' class='content'><tr><td width='85'>&nbsp;</td>";
            $htmData .= "<td width='394' align='right'>".$links."</td></tr></table>";
        }
        else{
             $htmData .= "Foutmelding. Het boek is niet gevonden.";
        }
        $result['data']             = $htmData;
        $result['page_title']         = $catname;
        $result['meta_description'] = "Vergelijk boeken in de rubriek ".$result['page_title'];
        $result['meta_keywords']     = (!empty($bookTitles))?implode(',',$bookTitles):$result['page_title'];
        return $result;
    }

Weet iemand wat het probleem hier is?

[Tharulerz]

En waarom zou je niet gewoon een index kunnen maken op category?

Een veld moet geen unieke waarden hebben om geindexeerd te kunnen worden...

[DigiK-oz]

Als de query in de shell wel snel klaar is, maar je pagina laadt factoren trager, weet je dan wel zeker dat de traagheid aan de query te wijten is? Even wat debug code met timestamps zal je dat vertellen. Misschien roep je deze functie (onbedoeld) wel 20 keer aan in je pagina.....

En inderdaad kan je uiteraard indexen aanmaken op niet-unieke velden. Het nut ervan kan soms beperkt zijn, afhankelijk van het aantal verschillende waarden in het veld, Maar als de traagheid niet in je query blijkt te zitten maar ergens anders, heeft geen enkele index zin. Dus ik zou dat eerst verifiëren.

[ Voor 6% gewijzigd door DigiK-oz op 16-04-2010 19:03 ]

[cariolive23]

Verwijderd schreef op vrijdag 16 april 2010 @ 18:14:
Een index maken gaat niet (behalve primary key), want verder zijn er geen unieke velden.

Dat is nieuw, dit heb ik echt nog helemaal nooit gehoord! Waarom zou je geen index kunnen maken wanneer een veld niet uniek is? Unieke waardes in een index, dat is één van de vele mogelijkheden.

Dit is de query:
SELECT title, description, image, isbn FROM boeken WHERE category = '$cat' ORDER BY title ASC LIMIT $page,10

Hier is dus duidelijk niet genormaliseerd, wat al een probleem zou kunnen zijn. Je slaat hierdoor in elk geval al veel te veel informatie op. Aangenomen dat je hier de naam van de categorie opslaat, mocht het een nummertje zijn die verwijst naar een id in de tabel categoriën, dan is het geen probleem.

Deze query smeekt in elk geval om een covering index op de kolommen category en title:

SQL:

CREATE INDEX idx_naampje ON boeken (category, title);

Verder hebben we geen resultaten van EXPLAIN gezien en hebben we dus geen flauw idee wat nu de reden is waarom de query langzaam is.

Een miljoen records is niet zo spannend, mits je de juiste indexen gebruikt, de boel hebt genormaliseerd en de database voldoende RAM heeft en mag gebruiken.

[BrZ]

cariolive23 schreef op vrijdag 16 april 2010 @ 19:18:
Deze query smeekt in elk geval om een covering index op de kolommen category en title:

SQL:

CREATE INDEX idx_naampje ON boeken (category, title);

title in deze index toevoegen heeft weinig meerwaarde, je zoekt er niet eens op in deze query. Enige nut zou zijn als er boeken met dezelfde titel in meerdere categorieen zou staan, en je dus specifiek op een titel + categorie zoekt.

[Voutloos]

BrZ: Het zou kunnen helpen met de order by.

Maar goed, ts moet beter profilen, explain van queries bekijken en gewoon lekker beide indexen proberen.

[Verwijderd]

Dit is een typisch voorbeeld van een slecht genormaliseerde database, waarmee je nu op de blaren moet zitten. Ik verwacht dat je met je huidige datamodel weinig kan verbeteren, ik verwacht dat je je datamodel om moet gooien en een boel moet herschrijven om het goed te krijgen...

edit: /me heeft verkeerd gelezen...

[ Voor 8% gewijzigd door Verwijderd op 16-04-2010 20:59 ]

[cariolive23]

BrZ schreef op vrijdag 16 april 2010 @ 19:30:
[...]

title in deze index toevoegen heeft weinig meerwaarde, je zoekt er niet eens op in deze query. Enige nut zou zijn als er boeken met dezelfde titel in meerdere categorieen zou staan, en je dus specifiek op een titel + categorie zoekt.

Blijkbaar heb je nauwelijks ervaring met indexen, anders had je wel geweten dat je hier de plank volkomen mis slaat. Een index is zéér bruikbaar bij sorteren en hier wordt gesorteerd. Er is ook een reden dat de kolom category éérst in de index wordt opgenomen en dan pas de titel. Zou je het andersom doen, wordt de index weer onbruikbaar.

Ga je eens verdiepen in databases en indexen, kun je een hoop van leren.

En vergeet niet, EXPLAIN is je grote vriend.

Ps. Ik heb het hier over de BTREE-indexen, de variant die in veel databases als standaard index wordt gebruikt. BTREE is gesorteerd, doe er je voordeel mee.

[ Voor 8% gewijzigd door cariolive23 op 16-04-2010 20:59 ]

[Voutloos]

offtopic:
Cariolive, het mag wellicht iets minder denigrerend, misschien keek BrZ gewoon over de order by heen. Foutje moet kunnen, net als dat ik jou niet afbrand op je index kennis omdat je de term covering index verkeerd gebruikt.

[BrZ]

cariolive23 schreef op vrijdag 16 april 2010 @ 20:57:
[...]

Blijkbaar heb je nauwelijks ervaring met indexen, anders had je wel geweten dat je hier de plank volkomen mis slaat. Een index is zéér bruikbaar bij sorteren en hier wordt gesorteerd. Er is ook een reden dat de kolom category éérst in de index wordt opgenomen en dan pas de titel. Zou je het andersom doen, wordt de index weer onbruikbaar.

Ga je eens verdiepen in databases en indexen, kun je een hoop van leren.

En vergeet niet, EXPLAIN is je grote vriend.

Ps. Ik heb het hier over de BTREE-indexen, de variant die in veel databases als standaard index wordt gebruikt. BTREE is gesorteerd, doe er je voordeel mee.

Ik las over de order by heen

[Verwijderd]

Wow, wat een reacties, heel erg bedankt! Ik ben niet zo'n SQL/UML/Database expert zoals jullie al hadden gemerkt.

Dat is nieuw, dit heb ik echt nog helemaal nooit gehoord! Waarom zou je geen index kunnen maken wanneer een veld niet uniek is? Unieke waardes in een index, dat is één van de vele mogelijkheden.

Sorry mijn fout, ik bedoelde eigenlijk niet unieke waardes, bijv. bij het veld ISBN zijn er er 5 dezelfde ISBN nummers (omdat het een vergelijkings website is).

Ik hoop dat ik mensen op dit forum kan helpen met mijn expertise, SEO en Adwords. Dus als jullie daar vragen over hebben hoor ik het graag.

Nogmaals bedankt voor alle zeer nuttige reacties en geniet van de zon

Johan

[Voutloos]

Verwijderd schreef op zaterdag 17 april 2010 @ 10:13:
Sorry mijn fout, ik bedoelde eigenlijk niet unieke waardes

Nogmaals: op elke kolom kan een index.

bij het veld ISBN zijn er er 5 dezelfde ISBN nummers(omdat het een vergelijkings website is).

Nee, dat is omdat je datamodel rot is.

Doe jezelf een plezier en zoek eerst een paar goede tutorials of een goed boek en leer onder ander wat normaliseren inhoudt. Overigens heb je geluk met de data die je opslaat: een database voor boeken of cd's is een van de populairste voorbeelden.

[michelzwarts]

http://codeigniter.com/user_guide/general/profiling.html

$this->output->enable_profiler(TRUE);

[Verwijderd]

Ik heb gelukkig nog een goed UML boek liggen die ik met mijn AMBI opleiding heb gekregen (blijkbaar niet al te veel van opgestoken), maar ik heb eerlijk gezegd de database ook niet zo opgezet. Ik ga sowieso de database structuur vandaag aanpassen, eerst een goed UML model enz.

Maar ik ben nu eerst bezig met CodeIgniter AJAX Pagination om het sowieso al wat te versnellen, maar daar loop ik nu ondertussen ook al een halve dag mee te kloten omdat ik net met CodeIgniter ben begonnen en Smarty gewend ben maar daar ga ik jullie niet mee lastig vallen, eerst maar eens zelf proberen....

[michelzwarts]

Heb je trouwens gekeken wat er gebeurd qua performance wanneer je die query caching uit zet?

Edit: Ajax maakt het per definitie niet sneller, je kunt hiermee wel al resultaten tonen terwijl er nog dingen geladen worden.

[ Voor 41% gewijzigd door michelzwarts op 17-04-2010 10:36 . Reden: Ajax ]

[Voutloos]

Dat zijn allemaal pleisters. Mogelijk leuke ideeen voor de toekomst, maar zonde van je tijd zolang je alles in 1 ongenormaliseerde monstertabel hebt.

[Verwijderd]

Ik denk dat ik maar de volgende (even basaal) structuur op ga zetten:

Tabellen: boeken_%cat%
Tabel: isbn
Tabel: rubriek
Tabel: boekwinkel

Moet binnen enkele uren met een paar goede queries op te zetten zijn.

Ik weet dat AJAX het niet echt sneller zal maken (of eigenlijk helemaal niet), maar ik vind het wat netter (zolang het SEO friendly is).

[Verwijderd]

Hoi TS,

het is jammer dat de totale functionaliteitswens mij niet bekend is. Misschien kon ik dan gerichter sturen/vragen waarom het lijkt alsof je per categorie je een eigen tabel van maakt. Dit zou dan zeker niet correct genormaliseerd zijn nog.

[disclaimer :+]
Of ik begrijp je uitleg van je (nieuwe) databasestructuur niet goed.

[Verwijderd]

Misschien kan ik beter eerst eens wat boeken/e-books of tutorials lezen over normaliseren. Nogmaals hartelijk dank voor alle goede en aardige reacties.

[GlowMouse]

DigiK-oz schreef op vrijdag 16 april 2010 @ 18:50:
En inderdaad kan je uiteraard indexen aanmaken op niet-unieke velden. Het nut ervan kan soms beperkt zijn, afhankelijk van het aantal verschillende waarden in het veld

Dat is van veel meer factoren afhankelijk, cardinaliteit alleen zegt niets.

cariolive23 schreef op vrijdag 16 april 2010 @ 19:18:
[...]
Deze query smeekt in elk geval om een covering index op de kolommen category en title:

SQL:

CREATE INDEX idx_naampje ON boeken (category, title);

Dat is helemaal geen covering index voor de query die je gaf

Voutloos schreef op zaterdag 17 april 2010 @ 10:38:
Dat zijn allemaal pleisters. Mogelijk leuke ideeen voor de toekomst, maar zonde van je tijd zolang je alles in 1 ongenormaliseerde monstertabel hebt.

Normaliseren helpt om wat meer data in je werkgeheugen te krijgen, maar als het eenmaal past dan helpt een ander datamodel echt niet bij deze query hoor. Met de genoemde index is deze query binnen een duizendste van een seconde te draaien.

[dik_voormekaar]

@Voutloos:

Ok, ik start hiervoor een nieuw topic.

[ Voor 96% gewijzigd door dik_voormekaar op 17-04-2010 14:15 ]

[Voutloos]

@dikvoormekaar: Haal aub je post leeg en start een nieuw topic.

GlowMouse schreef op zaterdag 17 april 2010 @ 12:05:
Normaliseren helpt om wat meer data in je werkgeheugen te krijgen, maar als het eenmaal past dan helpt een ander datamodel echt niet bij deze query hoor. Met de genoemde index is deze query binnen een duizendste van een seconde te draaien.

Uiteraard zou een index al voldoende kunnen helpen en moet er sowieso een index gaan komen, maar jij voelt toch hopelijk ook wel aan je water je met zo'n datamodel tegen problemen aan blijft lopen.

[JaQ]

cariolive23 schreef op vrijdag 16 april 2010 @ 20:57:
Ga je eens verdiepen in databases en indexen, kun je een hoop van leren.

dude, chill... Hoe meer je weet, hoe meer je beseft dat er veel is dat je niet weet

Het is iig naar mijn mening erg gevaarlijk om adviezen over verbouwing van een datamodel te geven, zonder dat je ook maar enig inzicht hebt in wat er allemaal in die database gebeurt. De go_fast parameter bestaat immers niet

dik_voormekaar schreef op zaterdag 17 april 2010 @ 12:22:
Ik heb eigenlijk een vergelijkbaar probleem.

Je ziet dus dat bij de tweede query er een filesort zit. Hierdoor duurt de query erg lang.
Hoe kan ik dit verbeteren?

Eventueel kan je een index organized table (clustered index) aanmaken, maar dat heeft allerlei andere nadelen die wellicht niet opwegen tegen de voordelen voor deze specifieke query. Zonder meer informatie over het systeem waar je op werkt kan ik daar weinig over zeggen. Een andere optie is de order by uit je query halen, maar iets zegt me dat die order by er niet voor niets in hebt gezet

Voutloos schreef op zaterdag 17 april 2010 @ 12:45:
Uiteraard zou een index al voldoende kunnen helpen en moet er sowieso een index gaan komen, maar jij voelt toch hopelijk ook wel aan je water je met zo'n datamodel tegen problemen aan blijft lopen.

Dan heb ik ander water dan jij. Ik heb helemaal geen inzicht in groeicijfers en functionaliteit van dit systeem, jij wel? Wellicht kost het herontwerpen + aanpassen van alle functionaliteit in een GUI wel een manjaar werk. Sterker nog, het zou maar zo eens kunnen dat een genormaliseerde situatie in deze casus meer I/O maakt dan een niet genormaliseerde situatie. Je weet immers niets over de cardinaliteit, maar ook niets over de selectiviteit. Misschien haalt de TS wel 80% van de data op met zijn query

[ Voor 25% gewijzigd door JaQ op 17-04-2010 12:54 ]

[roy-t]

Iedereen heeft het over het datamodel, maar helemaal in het begin werd genoemd dat via de console de query <1sec duurt en in het PHP script zo'n 20 seconden. Hoewel het datamodel slecht zou kunnen zijn, ligt het probleem volgens mij ergens anders.

[DigiK-oz]

Eens, zie ook mijn eerste reactie. Het datamodel verdient enige aandacht, maar zoals ik al zei, als de traagheid niet aan de query ligt, helpt geen enkele index of aanpassing aan het datamodel.

[YopY]

Een mogelijke oorzaak dat het PHP script zo langzaam is is dat hij alle records eerst in een variabele $htmlData zet. Ik weet niet hoeveel rijen die query ophaalt, maar als dat in de honderden loopt kan het maar zo zijn dat het geheugen volloopt - ik weet niet hoeveel geheugen er beschikbaar is voor PHP.

Ook: Wat doet die cache en waarom heb je voor een grootte van 1000 gekozen? Wat is daar het effect van op de performance?

Met een debug tool zou je zo kunnen kijken waar de meeste tijd in zit als het goed is.