Performance issue in a php and mysql page

Question

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.

Since I don't have much to go on with, I just wanna ask if it's possible for you to integrate parts of the code into your mysql query? I mean, if you could bind your $somearray to another mysql statement that will work with the first result set? — Nonym
– Nonym, Commented Nov 18, 2011 at 10:03
Why don't you show use what your tables look like + more data on what you want. You can probably use some join or a cache to save time. — Rich Bradshaw
– Rich Bradshaw, Commented Nov 18, 2011 at 10:06

cherouvim · Accepted Answer · 2011-11-18 10:00:18Z

1

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.

answered Nov 18, 2011 at 10:00

cherouvim

32k15 gold badges107 silver badges158 bronze badges

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

1 Comment

Hacker Over a year ago

i would have used joins but then i would have to loop 1st mysql statement in the foreach, because u need this foreach to build the query.

James Williams · Accepted Answer · 2011-11-20 04:28:21Z

1

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;

edited Nov 20, 2011 at 4:28

answered Nov 18, 2011 at 10:03

James Williams

4,2261 gold badge22 silver badges35 bronze badges

Comments

Sebastian Wramba · Accepted Answer · 2011-11-18 10:08:24Z

0

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.

answered Nov 18, 2011 at 10:08

Sebastian Wramba

10.2k8 gold badges43 silver badges59 bronze badges

1 Comment

Sebastian Wramba Over a year ago

So you can prepare your statement before the foreach loop and only use bindParams within it. Maybe that could speed things up a little.

Collectives™ on Stack Overflow

Performance issue in a php and mysql page

3 Answers 3

1 Comment

EDIT

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

EDIT

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related