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
– Mike, Commented Dec 6, 2010 at 12:52
You might also want to look at mysql_query_unbuffered - but be sure to read the documentation carefully, as there are some caveats. — Mike
– Mike, Commented Dec 6, 2010 at 12:59
Same server, i'm using phpMyAdmin and its being a pain saying i'm not using it the right context!? — benhowdle89
– benhowdle89, Commented Dec 6, 2010 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: stackoverflow.com/questions/2454071/… — Mike
– Mike, Commented Dec 6, 2010 at 13:20

Breezer · Accepted Answer · 2010-12-06 13:26:15Z

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 6, 2010 at 13:26

answered Dec 6, 2010 at 12:47

Breezer

10.5k6 gold badges32 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

21 Comments

benhowdle89 Over a year ago

That was my prev question with the Ob_start stuff

Breezer Over a year ago

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

benhowdle89 Over a year ago

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.

Breezer Over a year ago

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

benhowdle89 Over a year ago

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!

|

Collectives™ on Stack Overflow

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

1 Answer 1

21 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

21 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related