[mysql] limit doet sloom

[Saven]

Hallo Tweakers,

Ik heb sinds gisteravond een probleempje wat ik echt niet opgelost krijg. Ik kom naar niet achter de oorzaak.
Ik heb vanalles gegoogled, gezocht bij de mysql documentatie maar ik kon niks vinden.

Ik heb namelijk de volgende query:

PHP:

    //----------------------------------------------------------------
    // Get a news replies by newsID
    //----------------------------------------------------------------
    public function __get_replies($nid, $start, $end)
    {
        $query = $this->core->db->prepare
        ('
            SELECT
                r.*,
                m.username, m.group_id, m.avatar, m.ondertitel, m.posts, m.ondertitel
            FROM
                nieuwsreacties r
            INNER JOIN
                members m ON (m.id = r.door)
            WHERE
                nid = :nid
            ORDER BY
                id
            ASC
            LIMIT
                '.$start.', '.$end.'
        ');
        
        $query->bindParam(':nid', $nid, PDO::PARAM_INT);
        
        $query->execute();
        
        if( $query->rowCount() == 0 )
        {
            return false;
        }
        else
        {
            return $query->fetchAll(PDO::FETCH_ASSOC);
        }       
    }

Nu heb ik ontdekt dat als de limit 6 of lager is (dus LIMIT 6) dat de parsetime dan nog 0,04 seconden is.
Is het LIMIT slechts één enkele hoger, (dus LIMIT 7) dan is de parsetime ineens 0,12.
Lijkt mij niet dat een extra resultaat voor zoveel parsetime kan zorgen.
Aangezien als het LIMIT 20 is, hij ook 0,12 parsetime heeft.
Dus van 7 of hoger gaat hij ineens sloom doen.

Heeft iemand hier dan ook een verklaring voor? Of een oplossing?

EXPLAIN `nieuwsreacties`:


Alvast bedankt

EDIT: voor elke nieuwsbericht heeft het aantal limit een ander effect, bij de 1 gaat ie na LIMIT 6 sloom doen, de ander LIMIT 10 of LIMIT 4 ofzo.

[ Voor 7% gewijzigd door Saven op 06-05-2008 17:49 ]

[ChessSpider]

Kan het niet zijn dat de server precies op dat moment met iets anders bezig is?
Ook wil ik je even dit linkje laten zien; http://nl3.php.net/manual/en/language.oop5.magic.php.

Vraagje, ik zie dat je
$this->core->db->prepare
Hoe roep je de klasses core en db aan? Hoe instantieer je die?
Je hoeft het niet uitgebreid uit te leggen, maar een simpel linkje naar een howto ben ik zeer dankbaar voor.

[Saven]

$this->core bevat alle geladen classes zegmaar.
$this->core->user
$this->code->session etc..

Maar de server is niet precies op dat moment bezig beetje toevallig na tig keer doen

stukje van $core->load:

PHP:

    //----------------------------------------------------------------
    // Load a class
    //----------------------------------------------------------------
    private function load_class($class)
    {
        require_once(classes.$class.'.php');
            
        $this->core->$class = new $class();
        $this->core->$class->core =& $this->core;
        
        if( method_exists($this->core->$class, 'init') )
        {
            $this->core->$class->init();
        }
    }

[BCC]

Klinkt als indexen die niet gehit worden oid.
Voer en trage en snelle query eens uit in de mysql console met EXPLAIN ervoor en post hier de resultaten.

Dus:
EXPLAIN SELECT FROM Blabla LIMIT Bla

[ Voor 25% gewijzigd door BCC op 06-05-2008 17:59 ]

[Saven]

De explain:


Alleen het is dus niet per se vanaf 7 is voor elk nieuwsbericht verschillend. Het is gewoon zo dat vanaf een bepaalde limit de parsetime ineens met bijna 0,1 omhoog gaat

De limit 5 is dus de snelle, de limit 10 de "slome"

[ Voor 9% gewijzigd door Saven op 06-05-2008 18:04 ]

[BCC]

Hij doet hier voor beide queries een full table scan van je members (alle 66 regels). Dat is zowieso niet echt efficient (maar bij 66 regels, who cares). Maar omdat ze beide een full scan doen zouden ze exact even snel/traag moeten zijn.

[ Voor 3% gewijzigd door BCC op 06-05-2008 18:08 ]

[Saven]

Ja daarom vind ik het zo vreemd

[BCC]

Is dit op een shared host of op eigen/local host? Index op nid zetten is trouwens ook wel netjes.

[ Voor 32% gewijzigd door BCC op 06-05-2008 18:23 ]

[Saven]

shared host.
op nieuwsreacties.nid staat al een index btw

[ Voor 44% gewijzigd door Saven op 06-05-2008 18:54 ]

[BCC]

Dan zal de database/applicatie server wel druk zijn met andere klanten.

[ Voor 45% gewijzigd door BCC op 06-05-2008 19:03 ]

[Saven]

Lijkt me beetje sterk?

[pedorus]

Het lijkt wel alsof er hier gesorteerd wordt op members.id, wat niet logisch is. Probeer het eens met verbeterde ORDER BY/zonder ORDER BY?

Daarnaast is er wellicht kans op SQL-injection via $start, $end.

[Verwijderd]

pedorus schreef op dinsdag 06 mei 2008 @ 20:06:

Daarnaast is er wellicht kans op SQL-injection via $start, $end.

Dat weet je helemaal niet. Desalniettemin is het wel verstandig expliciet naar integers te casten.

[ Voor 15% gewijzigd door Verwijderd op 06-05-2008 20:08 ]

[Janoz]

Bij beiden zie ik staan dat het sorteren via een filesort gaan. Dat is nogal traag. Het zou heel goed kunnen dat, door die limit, de grootte van de te sorteren file klein genoeg blijft waardoor het net in de file cache blijft zitten terwijl het bij een iets grotere set wel op de hd weggeschreven wordt.

[BCC]

Saven schreef op dinsdag 06 mei 2008 @ 19:35:
Lijkt me beetje sterk?

Tja, het wordt nogal koffiedik kijken als het niet hard te reproduceren is. Suggiestie van query cache hits is inderdaad een goede. Ook weer in combinatie met sharedhosting kan dit nogal eens verschillende resultaten opleveren.

[Verwijderd]

Dergelijke kleine getallen zijn op bijvoorbeeld een windows omgeving veelal verre van secuur. Heb je al eens geprobeerd een fatsoenlijke test op te zetten door herhaaldelijk dezelfde query uit te voeren en daar het gemiddelde uit te berekenen...

[Gomez12]

Idd, waarschijnlijk past limit 6 nog net in de memorytables, limit 7 niet meer.

Qua query is er geen verklaring voor imho, alleen qua resultset grootte.

Probeer eens alleen de velden te selecteren die je nodig hebt en niet * te selecteren ( hint : heb je een adminnote nodig in deze select?)
Uberhaupt selecteer je 2 text velden, hoeveel memory wordt hiervoor gereserveerd, moet je lastedit veld echt 222 karakters zijn ( volstaat een timestamp niet? ).

[Bosmonster]

Hoe meet je eigenlijk?

Want het kan natuurlijk ook gewoon aan je meet-methode liggen. Dat er niet goed afgerond wordt bijvoorbeeld. Als er continu dezelfde waardes uitkomen met grote stappen (0,04 - 0,12 - etc) lijkt me dat het meest logische eerlijk gezegd.

Kan me namelijk niet herinneren ooit een query geschreven te hebben die ALTIJD EXACT bijvoorbeeld 0,12 sec erover deed, elke iteratie

[ Voor 23% gewijzigd door Bosmonster op 07-05-2008 00:24 ]

[Voutloos]

Gomez12 schreef op dinsdag 06 mei 2008 @ 23:26:
Idd, waarschijnlijk past limit 6 nog net in de memorytables, limit 7 niet meer.

6, 7 of desnoods 100 rijen passen zelfs in de meest karige config nog in de memory manier van filesorten.

Het probleem is echter vaak dat mysql meer rijen moet (lees: gaat ) sorteren dan het cijfertje in de limit.

Probeer eens alleen de velden te selecteren die je nodig hebt en niet * te selecteren ( hint : heb je een adminnote nodig in deze select?)

Nog een hint: probeer gewoon altijd expliciet te selecteren. En zet in dit geval ook de tabel alias voor id in de order by, aangezien beide tabellen een id kolom lijken te hebben.

Maar goed, gewoon de create table statement afdrukken is duidelijker dan een phpmyadmin oid screenshot, want dan zie je des te beter ook de indexen en andere eigenschappen zoals engine.

Voor deze query wil je wellicht wel een samengestelde index als index(door, id) proberen, want met zo'n soort index moet die filesort weg te krijgen zijn. En tijdsmeting is idd niet altijd zo nauwkeurig, maar met queries met filesort op tabellen die nog veel zullen gaan groeien kan het sowieso geen kwaad om 5 minuten langer naar mogelijke indexen te zoeken.

LIMIT is overigens een van de weinig query onderdelen welke een query nooit trager maken. Het kan echter niet altijd een trage query voorkomen en soms moet er gewoon uberhaupt veel meer werk verzet worden dan je aan het aantal rows zou aflezen. LIMIT 1000000, 10 is niet zo snel het klinkt, maar ook dat komt omdat er gewoon alsnog veel rijen opgezocht, gefilterd en gesorteerd moeten worden.

[ Voor 17% gewijzigd door Voutloos op 07-05-2008 08:15 ]