[PHP/MySQL] insert query extreem langzaam

[enveekaa]

Ik ben bezig met een eenvoudig mailinglist script en loop tegen het volgende probleem aan.
Tijdens een naar mijn mening eenvoudige SQL query krijg ik een timeout. Nu kan ik natuurlijk met set_time_limit de timeout opschroeven, maar dat is geen oplossing.

Bij de eerste select trek ik er dus 600 email adressen uit, welke ik in een while loop toevoeg aan de queue tabel. Ik haal nu ongeveer 19 inserts per seconde, wat me uitzonderlijk weining lijkt?

PHP:

databaseConnect();

// Haal 600 email adressen op..
$query = "SELECT email FROM subscribers";
$result_subscribers = mysql_query($query) or die(errorShow(mysql_error()));

// Mik die 600 email adressen in de queue tabel met wat additionele info    rmatie..
while ($row_subscribers = mysql_fetch_array($result_subscribers)) {
    $query = "
    INSERT INTO queue (`queueid`,`added`,`mailingid`,`recipient`,`sent`) 
    VALUES (NULL,'".time()."','".$_GET['mailingid']."','".$row_subscribers['email']."','FALSE')"; 
    $result = mysql_query($query) or die(errorShow(mysql_error()));
} 

// Update de mailings tabel dat mailing X in de queue staat..
$query = "
UPDATE quandasmailer.mailings 
SET queued = 'TRUE' WHERE mailingid = '".$_GET['mailingid']."'";

$result = mysql_query($query) or die(errorShow(mysql_error()));
    
header("Location: ?mod=mailings");

[ Voor 4% gewijzigd door enveekaa op 09-10-2008 11:31 ]

[MueR]

Je wil dan ook niet in een while loop 600 inserts gaan doen. Dit wil je bufferen en per 100 ofzo gaan inserten.

[sky-]

Zoals MueR zegt, bufferen en dan runnen.

Maar, 'TRUE' stop je natuurlijk niet in je database lijkt me, true lijkt me beter. (dus zonder haakjes) Booleans gebruiken dus

[enveekaa]

Ik had een helder moment, de tabel queue had InnoDB als storage engine. Dit heb ik aangepast naar MyISAM en dan gaat het binnen een seconde

Maar ik ben wel geintresseerd in je tip, wat is een goede constructie om de inserts te bufferen naar 100 per keer?

'TRUE' is inderdaad een beetje stom Heb de quotes weggehaald en nu wordt het 1. Thanks.

[ Voor 15% gewijzigd door enveekaa op 09-10-2008 11:40 ]

[Exception]

Precies, zet eerst alle query's achter elkaar in een variabele. Pas daarna, stuur je de query naar de database met mysql_query(..)

[leuk_he]

Afgezien ^^^muer kun je statemnet ook "preparen" zodat hij niet elke keer het insert statement hoeft te parsen. Verder de vraag hoe "queue" gedefinieerd is.(en "gewoon" is geen goed antwoord)

[bigbeng]

Waarom bufferen en/of loopen als het ook in 1 query kan:

SQL:

INSERT INTO queue
(queueid, added, mailingid, recipient, sent)
SELECT null, <time>, <mailingid>, email, 'FALSE'
FROM subscribers

In je php code even de <time> en <mailingid> vervangen door de juiste waarde en klaar is klara.

Zie ook: http://dev.mysql.com/doc/refman/5.0/en/insert.html

[enveekaa]

He dat is helemaal mooi

[iH8]

waarom gooi je al die output van je select niet in een array en genereer zo'n query:

SQL:

INSERT INTO
    table (col1,col2,col3,col4,col5)
VALUES
    (value1,value2,value3,value4,value5),
    (value1,value2,value3,value4,value5),
    (value1,value2,value3,value4,value5),
    (value1,value2,value3,value4,value5),
    etc...

of je gebruikt multi:

PHP:

$query  = "INSERT INTO table (col1,col2,col3,col4,col5) VALUES (value1,value2,value3,value4,value5);";
$query  .= "INSERT INTO table (col1,col2,col3,col4,col5) VALUES (value1,value2,value3,value4,value5);";
$query  .= "INSERT INTO table (col1,col2,col3,col4,col5) VALUES (value1,value2,value3,value4,value5);";
$query  .= "INSERT INTO table (col1,col2,col3,col4,col5) VALUES (value1,value2,value3,value4,value5);";
etc...

$mysqli->multi_query($query));

enveekaa schreef op donderdag 09 oktober 2008 @ 11:47:
He dat is helemaal mooi

idd dan heb ik denk ik niets gezegd ;):D

[ Voor 10% gewijzigd door iH8 op 09-10-2008 12:01 ]

[enveekaa]

Hmm die query van bigbeng ziet er leuk uit maar ik krijg het niet 1 2 3 voor elkaar
Volgens mij mis ik ook het eea.

PHP:

$query = "
INSERT INTO queue queueid, added, mailingid, recipient, sent
SELECT null,'".time()."','".$_GET['mailingid']."', email, false
FROM subscribers
";

In de insert naar de tabel queue heb ik alle entries nodig in de tabel subscribers, plus was extra informatie.

[ Voor 16% gewijzigd door enveekaa op 09-10-2008 13:02 ]

[Voutloos]

Dan debug je dat. INSERT INTO SELECT debuggen is simpel: Hou het eerst gewoon een select query en pas als alles goed is plemp je er insert into ... voor.

(en ga eerst maar het eea lezen inderdaad, met als extra topics tabel structuur, indexes, insert syntax, insert optimalisatie en sql injection... )

[ Voor 33% gewijzigd door Voutloos op 09-10-2008 13:23 ]

[MueR]

Je mist niks, je hebt te veel meuk. Die link naar de docs is misschien nuttig voor je. Lees eerst even over de syntax voordat je gaat lopen prutsen, dat scheelt je 95% van het werk.

[enveekaa]

Ik ken dit principe nog niet, en snap het niet
RTFM

[XWB]

INSERT kan met één query.

PHP:

$query = 'SELECT email FROM subscribers';
$result_subscribers = mysql_query ( $query );
$result = array ();

while ( $row =  mysql_fetch_array ( $result_subscribers ) )
    $result[] = $row['email'];

$qryData = array ();

foreach ( $query as $email )
    $qryData[] = sprintf ( '(NULL, %u, %u, "%s", "FALSE")', time (), $_GET['mailingid'], $email );

$prepare = sprintf ( 'INSERT INTO queue (queueid, added, mailingid, recipient, sent) VALUES %s', implode ( ',', $qryData ) );
mysql_query ( $prepare );

Of je kijkt naar bigbeng zijn oplossing

Overigens is je script erg onveilig. GET en POST vars moet je escapen, en ook checken of ze bestaat.

[ Voor 4% gewijzigd door XWB op 09-10-2008 13:41 ]

[MueR]

Jij kent je prepared statements niet Hacku.

[XWB]

Waar gebruik ik dan prepared statements Kijk nog eens heel goed

[ Voor 24% gewijzigd door XWB op 09-10-2008 14:09 ]

[enveekaa]

Ik heb het inmiddels voor elkaar met één insert query.
In de eerste instantie zat ik veelste ingewikkeld te denken, maar het is kinderlijk eenvoudig.

Bedankt voor de tips Erg leerzaam.

PHP:

$query = "
INSERT INTO queue (queueid, mailingid, added, recipient, sent)
SELECT null,'".$_GET['mailingid']."','".time()."', email, false
FROM subscribers";

[leuk_he]

enveekaa schreef op donderdag 09 oktober 2008 @ 14:03:

PHP:

$query = "
INSERT INTO queue (queueid, mailingid, added, recipient, sent)
SELECT null,'".$_GET['mailingid']."','".time()."', email, false
FROM subscribers";

Inderdaad veel simpeler... Moet je die GET nou nog escapen?

[pedorus]

enveekaa schreef op donderdag 09 oktober 2008 @ 14:03:
Ik heb het inmiddels voor elkaar met één insert query.
In de eerste instantie zat ik veelste ingewikkeld te denken, maar het is kinderlijk eenvoudig.

Bedankt voor de tips Erg leerzaam.

PHP:

$query = "
INSERT INTO queue (queueid, mailingid, added, recipient, sent)
SELECT null,'".$_GET['mailingid']."','".time()."', email, false
FROM subscribers";

Kijk dan ook eens naar sql injection en kijk eens naar bijvoorbeeld dit bericht over prepared statements.

[paulh]

enveekaa schreef op donderdag 09 oktober 2008 @ 11:39:
Ik had een helder moment, de tabel queue had InnoDB als storage engine. Dit heb ik aangepast naar MyISAM en dan gaat het binnen een seconde

Is dat wel zo verstandig? Moet die tabel misschien niet ergens anders ook nog transacties ondersteunen? Opzich is er niks mis met InnoDB. Je kan beter kijken naar commit opties, misschien moet je autocommit uitzetten. Of juist de tabel vullen binnen een transactie.

[osorkon!]

Hacku schreef op donderdag 09 oktober 2008 @ 14:02:
Waar gebruik ik dan prepared statements Kijk nog eens heel goed

Bedoeld waarschijnlijk dat je een prepared statement kan gebruiken ipv van sprintf..

[MueR]

Hacku schreef op donderdag 09 oktober 2008 @ 14:02:
Waar gebruik ik dan prepared statements Kijk nog eens heel goed

Klopt, dat is het hem nu juist. Nu moet MySQL alsnog 600 keer een query parsen. Voor dit soort dingen gebruik je bulk queries of prepared statements, waarbij de eerste de minste queries kost, de laatste minder processortijd voor de query parser.

[Voutloos]

Vergeleken met de cost van de inserts zelf is het besparen op de query parse tijd een micro-optimalisatie. Grootste besparing is iig om minstens bulk inserts te doen in plaats van idioot veel losse inserts.

[Kettrick]

paulh schreef op donderdag 09 oktober 2008 @ 19:57:
[...]
Is dat wel zo verstandig? Moet die tabel misschien niet ergens anders ook nog transacties ondersteunen? Opzich is er niks mis met InnoDB. Je kan beter kijken naar commit opties, misschien moet je autocommit uitzetten. Of juist de tabel vullen binnen een transactie.

Je zou iig alle inserts binnen een transactie moeten doen, ik vermoed dat mysql nu voor elke insert een transactie start ( ken mysql niet erg goed, maar dat zou logisch zijn).

[MueR]

Daar zijn de prepared statements van mysql nou zo handig voor, die doen alles binnen 1 transactie.