Apache Error Log Help: PHP notices galore

[GWestphal]

I have a website that searches a database of experiments and their parameters. The page takes forever to load, like 21 secs. The query looks really bad to me, but I'm not very knowledgeable in sql or php. How can I tell if its the sql or php that is taking most of the time?

UPDATE: Apache log

http://www.mediafire.com/file/7341amhe5b4m3w2/apache.log

note: its like 37 MB after one query to the database.

 

[Ka0t1x]

Copy the query, run it in phpmyadmin or mysql command line. It looks fairly decent, as long as you have some indexes on those tables (I'm assuming they're huge). Although, it looks like a LOT of data is getting pulled from the DB.

The PHP could be doing loops on the data to parse it, which sounds like is the problem.

 

[GWestphal]

It must be the php (which also looked pretty bad to me) because the query only takes about 5 secs. Would inner joins make the query even faster?

 

[IHateMyJob2004]

Inner Joins might be faster. But is the left side required? Inner Joins could be faster i nthat they would drop rows with non-matching data on the right side. So the joins that occur later would not have as many joins to do. Better explained with an example if you'd like. To much writing for now though.

I would verify that there are indexes for all columns that are being used to join on.

If it is taking 16 seconds to do the PHP side of things, you arn't doing much be optimizing SQL more. Even if you got it down to one second, you still havea 17 second page load.

If you are using oracle, you could consider materialized views. So long as the data is relatively stale. If the database tables change alot, that could be a bad idea.

I feel your pain to an extent. I have some querries that take 10 seconds to run.

See if maps could be used in PHP to speed things up. People might have been doing some silly things that work but are slow.

 

[drebo]

I see no conditions in that SQL query...does the PHP page really need every single record?

If your PHP script is running through the entirety of the dataset to get the relevant data, that could be your problem. Make MySQL do that part.

You also might consider some views to simplify your query a bit.

 

[GWestphal]

Update: I am wondering if anyone might be able to look at my apache log and tell me how to stop the notices I'm getting. Is there a compatibility option somewhere in php.ini or is the code just bad. Lots of notices for undefined indexes and undeclared variables. I have logging turned off for the time being because the log was getting in the GB range in size.

http://www.mediafire.com/file/7341amhe5b4m3w2/apache.log

note: it's about 37MB after one query to the database.

 

[uclabachelor]

Update: I am wondering if anyone might be able to look at my apache log and tell me how to stop the notices I'm getting. Is there a compatibility option somewhere in php.ini or is the code just bad. Lots of notices for undefined indexes and undeclared variables. I have logging turned off for the time being because the log was getting in the GB range in size.

http://www.mediafire.com/file/7341amhe5b4m3w2/apache.log

note: it's about 37MB after one query to the database.

You'll have to solve the query yourself and figure out what's going on there, but to supprse warnings and notices, you can do error_reporting(E_ALL ^ E_NOTICE ^ E_WARNING) to turn off notices and warnings.

Edit: Just so you know, if you want additional help, post the actual query and/or php code. No one is going to download a 37MB file and look through there.