PHP MySQL Query Returning Rows Very Slow

Question

I am having a problem with a MySQL query. When ever I run this query it takes over 10 seconds to return the rows. However if I change my limit to 29 it returns it in less than 1 second. My Question is my implementation and query in good shape, or am I making a mistake here that would cause this issue?

<?

try
        {
            $con = mysql_connect("XXX.XXX.XXX.XXX","XXX","XXX");
            if (!$con)
                {
                    die('Could not connect: ' . mysql_error());
                }

            mysql_select_db("bis_co", $con);

            $query = "SELECT Name, Value FROM `bis_co`.`departments` LIMIT 31"; 

            $result = mysql_query($query) or die(mysql_error());

            $row = mysql_fetch_array($result);
            while($row = mysql_fetch_assoc($result)){
                echo $row['Name'] . "<br />";
            }


        mysql_close($con);
        }
    catch(Exception $e)
        {
            echo $e;
        }
?>

Try executing this on directly your MYSQL DB and show us the output: EXPLAIN SELECT Name, Value FROM bis_co.departments LIMIT 31 — Virendra
– Virendra, Commented Dec 26, 2011 at 21:35
@ulvund It has 51 Records Right now. @Virendra running that returns 1 SIMPLE Departments ALL NULL NULL NULL NULL 51 With the Columns being id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra(which isnt null but has no value) — atrljoe
– atrljoe, Commented Dec 26, 2011 at 21:44
You should add Indexes to your table. It seems you don't have any indexes on your table. — Virendra
– Virendra, Commented Dec 26, 2011 at 21:46

tetshi · Accepted Answer · 2011-12-26 22:07:15Z

3

You could try getting rid of the

$row = mysql_fetch_array($result)

statement. The if statement will take care of returning the array. You could also change the query string to

"SELECT Name, Value FROM `departments` LIMIT 31"

since you're already setting the database name in the mysql_select_db statement. Though, none of these should be causing your issues. It seems like it's a table issue. Have you tried it with mysqli and prepared statements?

answered Dec 26, 2011 at 22:07

tetshi

505 bronze badges

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

Collectives™ on Stack Overflow

PHP MySQL Query Returning Rows Very Slow

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related