PHP MySQL Stored Procedures still slow to output (almost crashes browser)

Question

Hi I asked a question on here a couple of weeks ago about speeding up mysql output for my db of about 5000 records. I used the advice to use ob_start() and stored procedures. However its still almost crashing the browser and being extremely slow to output the records, any ideas how to optimise this:

    ob_start();
    $conn = new Mysqli("xxxxxxxxxx", "xxxxxxxxx", "xxxxxxxxx", "xxxxxxxxxx");

    $result = $conn->query(sprintf("call list_products(%d)", 6000));

    while($row = $result->fetch_assoc()){
        echo "<tr>";
        echo "<td>" . $row['xxxxxxx'] . "</td>";
        echo "<td>" . $row['xxxxx'] . "</td>";
        echo "<td>" . $row['xxxxx'] . "</td>";
        echo "<td>" . $row['xxxxxx'] . "</td>";
        echo "<td>" . $row['xxxx'] . "</td>";
        echo "<td>" . $row['xxx'] . "</td>";
        echo "<td>" . $row['xx'] . "</td>";
        echo "<td>" . $row['xxxx'] . "</td>";
        echo "</tr>";
    }
    echo "</tbody>";
    echo "</table>";

    $result->close();   
    $conn->close();

    ob_end_flush();

Have you determined how much of the delay is caused by MySQL and PHP? For example, how quickly does your stored procedure return results if you call it directly from the MySQL console? What does your stored procedure do - can you supply the code? Is MySQL on the same server as PHP, or a different one? — Mike, Dec 06 '10 at 12:52
You might also want to look at [mysql_query_unbuffered](http://www.php.net/manual/en/function.mysql-unbuffered-query.php) - but be sure to read the documentation carefully, as there are some caveats. — Mike, Dec 06 '10 at 12:59
Same server, i'm using phpMyAdmin and its being a pain saying i'm not using it the right context!? — benhowdle89, Dec 06 '10 at 12:59
I think that the context problem is a bug in some versions of PHPMyAdmin. Have a look at the accepted answer to this post, and its comments: http://stackoverflow.com/questions/2454071/how-do-i-write-an-sp-in-phpmyadmin-mysql — Mike, Dec 06 '10 at 13:20

Breezer · Accepted Answer · 2010-12-06T13:26:15.847

0

using ob isn't good at all whoever said that have misinformed you... what you should do isn't to much about how you're outputting your data but look in to your mysql query and how you could optimize it as possible, using key columns to find whatever you looping, try to limit the rows as much as possible, using index all of these has great importance when you want to optimize your database script

You can use the EXPLAIN word to find out where the bottleneck might be, where you might need to index and so on

edited Dec 06 '10 at 13:26

answered Dec 06 '10 at 12:47

Breezer

10,410
6
29
50

That was my prev question with the Ob_start stuff – benhowdle89 Dec 06 '10 at 12:53
yeah i understood that, but what ob does is saving the printed content to the memory, that will create a bad bottleneck which will make it unscalable for more visitors, try removing ob and see how fast it takes then, i'm pretty sure if there's something it's the query that's bad – Breezer Dec 06 '10 at 12:55
pretty much the same. the thing is i need to display all 5000 records so i cant really think of a way to limit or offset the results. – benhowdle89 Dec 06 '10 at 13:06
if you can get the query printed copy it and then if you have access to phpmyadmin run a query with EXPLAIN before the query you just copied and post what you get here – Breezer Dec 06 '10 at 13:08
i run this: EXPLAIN list_products and it says the SP doesnt exist (!?) but i just did SHOW PROCEDURE STATUS; and it definitely came up! – benhowdle89 Dec 06 '10 at 13:25
no no you put the explain infront of the entire query... for example if you had SELECT * FROM TABLE WHERE COLUMN = VALUE you would do EXPAIN SELECT * FROM TABLE WHERE COLUMN = VALUE – Breezer Dec 06 '10 at 13:31
result: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE parts ALL NULL NULL NULL NULL 5120 – benhowdle89 Dec 06 '10 at 13:36
Sorry its a bit garbled. No formatting in the comments section – benhowdle89 Dec 06 '10 at 13:36
that look's really bad you have no keys no ref no nothing could you also post the query itself and i'll tell ya what to do – Breezer Dec 06 '10 at 13:42
well the query is just select * from parts – benhowdle89 Dec 06 '10 at 13:45
the stored procedure is roughly the same. As you can tell my mysql admin skills are shocking – benhowdle89 Dec 06 '10 at 13:46
do you output every kolumn? if you output everything that's in the dabase all the columns all the rows then you're kind of at a dead end – Breezer Dec 06 '10 at 13:51
yeah, what i'm doing is showing all 5000 records for an ordering system so employees click on a row and it adds it to a "basket". So i need to display all the records, is that going to be a problem? – benhowdle89 Dec 06 '10 at 13:52
i can but the thing is i'm using jQuery to append items to a DIV. so if they leave the page (to get more records) i lose the items. I'm aware this is a crap way to do this, but i havent got the heart to redesign the entire application – benhowdle89 Dec 06 '10 at 13:55
well you could use ajax to append them saves you from redesigning everything and fastens things up a notch – Breezer Dec 06 '10 at 13:56
Its all ajaxed up! but i guess your saying use ajax to do the PHP paginate – benhowdle89 Dec 06 '10 at 13:57
yeah, that's what im saying :P – Breezer Dec 06 '10 at 13:58
got it, well i'll give that a try! Just so far into it, very reluctant to go back and re-do stuff! A very bad attitude to have i know! – benhowdle89 Dec 06 '10 at 13:59
well that's why you should have taken sometime to think through what you're doing when you're creating your code =P – Breezer Dec 06 '10 at 14:00
Well i did sort of, i used jQuery's Datatables plugin. Actually slowed things down as it client side pagination – benhowdle89 Dec 06 '10 at 14:02
yeah it would probably but you have to find a good balance between how many rows you have for each page that doesn't strain the server and doesn't strain the user browser – Breezer Dec 06 '10 at 14:07

PHP MySQL Stored Procedures still slow to output (almost crashes browser)

1 Answers1