CODEWITHSUNDEEP
phpmysql
Hacker
Hacker

Reputation: 7896

Performance issue in a php and mysql page

I am running into some performance issue with php and mysql page.

i have a code like

mysql statement
    while loop fetching the record
    {
        foreach($somearray)
          {
            Another mysql statement while fetch records using a where clause using $somearray key and 1st while loop result row data.
           }
     }
    /*end while*/

the mail statement takes 462 records and second foreach runs 12 times. The page loads very slowly and when i comment the second mysql statement it loads fast.

So what measures should i take so as to fetch data faster and any solution for this?

/** Question Update **/

one table is in row manner i.e say primary ID is main for this.

second table the data is in multiple rows i.e same ID from 1st table will have multiple rows and i need to fetch only particular months data from the second table and join both of this and show in a table.

So i am using that foreach to loop through months.

Upvotes: 0

Views: 93

Answers (3)

James Williams
James Williams

Reputation: 4216

If each row in query runs 12 seperate queries you are running over 5500 queries in that time frame. if each query took 10ms (optimistic) you are still looking at 55 seconds for just MySQL Queries. I would try to remove or optimize your code/queries to minimize the load on the server.

General idea is not to nest queries if you cannot help it.

EDIT

To get information from 2 tables at once you can use a join for the MySQL and it will show all information from both tables and you can run a where clause or put it through your loop to get all specified data

SELECT * FROM tableName AS t1 INNER JOIN tableName2 AS t2 ON t1.name = t2.name;

Upvotes: 1

Sebastian Wramba
Sebastian Wramba

Reputation: 10127

You're doing some heavy operations on the MySQL result many, many times. Maybe you want to fetch your first record into a PHP data structure which you can then use to perform the second query.

You can also think about using PDO, maybe things can be accelerated, when statemens are cached. The statements in the foreach loop are always the same and only the parameters change, I suppose.

Upvotes: 0

cherouvim
cherouvim

Reputation: 31921

You probably issue N+1 (in ORM terms) queries for each top level entity. You should join the data and at least avoid the nested loop.

Upvotes: 1

Related Questions