php mysql while loop query optimization

Question

I have php/mysql script running which is like this:

$sql = "select a.column1 from table1 a";
$query = mysql_query($sql);

while ($fec = mysql_fetch_assoc($query)) {
    $sub1 = "select column1,column2 from subtable1 where id=" . $fec['a.column1'];
    $subquery1 = mysql_query($sub1);

    while ($subfec1 = mysql_fetch_assoc($subquery1)) {
        //all data .....
    }

    $sub1 = "select column1,column2 from subtable2 where id=" . $fec['a.column1'];
    $subquery2 = mysql_query($sub2);

    while ($subfec2 = mysql_fetch_assoc($subquery2)) {
        //all data .....
    }

    $sub2 = "select column1,column2 from subtable3 where id=" . $fec['a.column1'];
    $subquery3 = mysql_query($sub3);

    while ($subfec3 = mysql_fetch_assoc($subquery3)) {
        //all data .....
    }
}

Now I've many many many records in table1, subtable1, subtable2 and subtable3. And problem is that it takes around 7 hours to display the results. Moreover the CPU Usage is 100%

Please suggest the optimization tips to overcome this issue.

Please don't use mysql functions in PHP. For more see "why shouldn't I use mysql_* functions in PHP?" - http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — John Skoumbourdis, Sep 01 '14 at 07:20
Have you tried to optimized all these 4 queries to one into your mySQL browser? — John Skoumbourdis, Sep 01 '14 at 07:23
what are you doing in `//all data .....` part? Use MySql `WHERE id IN array()`, use PDO or mysqli_* prepared statements. — Justinas, Sep 01 '14 at 07:23
will you post your php profile result here so only we can tell where to optimize — gvgvgvijayan, Sep 01 '14 at 07:26
What is your actual data model? Are those “subtables” actually different in structure? — CBroe, Sep 01 '14 at 07:37

score 1 · Answer 1 · answered Sep 01 '14 at 07:31

Use a single query to get all records

SELECT 
    a.column1,
    s.column1,
    s.column2,
    s2.column1,
    s2.column2,   
    s3.column1,
    s3.column2
FROM table1 a
LEFT JOIN subtable1 s ON s.id = a.column1
LEFT JOIN subtable2 s2 ON s.id = a.column1
LEFT JOIN subtable3 s3 ON s.id = a.column1

Here

$sql="
    SELECT 
        a.column1 acolumn1,
        s.column1 scolumn1,
        s.column2 scolumn2,
        s2.column1 s2column1,
        s2.column2 s2column2,   
        s3.column1 s3column1,
        s3.column2 s3column2
    FROM table1 a
    LEFT JOIN subtable1 s ON s.id = a.column1
    LEFT JOIN subtable2 s2 ON s.id = a.column1
    LEFT JOIN subtable3 s3 ON s.id = a.column1";
$query=mysql_query($sql);

while ($fec=mysql_fetch_assoc($query)) {
    // display any info here
}

Use aliases for accessing different table columns

php mysql while loop query optimization

1 Answers1