Topic awaiting preservation: Php MySQL Website dragging real slow - how do I speed it up??? (Page 1 of 1)

Man we're on to initial deployment and so far my program seems to be really draggin itself real slow like. There doesn't seem to be anything wrong with the SQL for they seem optimised enough.. trhe problem comes on pages where search queries or results are listed out ... how do I optimise my code or do I have to tweak the server ? There are a load of function calls and I'm passing my user objects as parameters.... help guys !!!

Where is it slow ? at generating page or loading it in the browser ?
For the later, gzip'ing the output will help.
For the earlier, have you profiled your queries ? are the things used in the WHERE clauses set as an INDEX key ? if the objects you pass to your functions are big, you should definitely pass them by reference or not pass them at all.

ok... impossible to tell where it's slow without further information...

How many results are you listing? How many kb of html does each row of results generate?
What does mysql do when you do an 'explain select {rest of your search query here}' in phpmyadmin?
How large are those 'user objects' you're passing around? Are we talking php4 - then they'll be copied on each and every
function call...


Good starting points for general slowness:
a) check the size of php that's included... if you have a few hundred kb, that will slow down your loading time
b) hopefully you have a sql wrapper around mysql in place, then you can simply replace

function do_query($strQuery) 
{
   return mysql_query($strQuery);
}

with 
function do_query($strQuery)
{
   $startTime = getmicrotime();
   $result = mysql_query($strQuery);
   $timeNeeded = getmicrotime() - $startTime;
   writeToLog($strQuery,$timeNeeded);
   return $result;
}

and see what queries take so long..

c) if it's only the search result pages... do that 'explain query' thing (post the results, please), check how large the transfered answer is...


that's at least a point to start .
So long,

->Tyberius Prime

Hmmmm after following your advice Tyberius I've found a couple of Queries that seem to be taking a bit more time than they actually should and the interesting part is that both these queries have like LEFT JOINS. But I can't think of a workaround for this - I heard somewhere that JOINS would slow down queries but how do I like make the change. Like for example here is one query :

Select 	c.`id`, 
	    c.`salutation`, 
		c.`first_name`, 
		c.`last_name`, 
		GROUP_CONCAT(d.`contact_comm`  SEPARATOR ','), 
		GROUP_CONCAT(d.`details` SEPARATOR ',')
			
From contacts c LEFT Join cmm_details d ON c.`id` = d.`contact_item` AND d.`contact_item` = 'person' 
 GROUP BY c.`id` 
ORDER BY c.`id` DESC

This is like taking a whopping 171732 microseconds but then again thats not even a fraction of the time delay I'm encountering at the moment for on average the page takes like a long 25 seconds to load and I'm talking on my localhost. even if I add up all the time taken by db query calls.. it hardly adds up to less than one second...

What should I be looking for by the way in my code.. because I'm including the same files on other pages but they don't take so long elsewhere... what should I watch out for guys..

(Edited by paritycheck on 07-23-2007 08:51)

loops.

and go ahead, do a an

explain
Select 	c.`id`, 
	    c.`salutation`, 
		c.`first_name`, 
		c.`last_name`, 
		GROUP_CONCAT(d.`contact_comm`  SEPARATOR ','), 
		GROUP_CONCAT(d.`details` SEPARATOR ',')
			
From contacts c LEFT Join cmm_details d ON c.`id` = d.`contact_item` AND d.`contact_item` = 'person' 
 GROUP BY c.`id` 
ORDER BY c.`id` DESC

to see what mysql does to optimize your query...

so long,

->Tyberius Prime

OK I've changed it a bit so now instead of it reading

contacts c LEFT Join cmm_details d ON c.`id` = d.`contact_item` AND d.`contact_item` = 'person'

It now reads

contacts c LEFT Join cmm_details d ON c.`id` = d.`contact_item` WHERE d.`contact_item` = 'person'

This pretty much reduces the execution time tremendously by putting one condition in the where clause.. but its still going a bit slow with ajax calls.. I'll be back with an update in a while

Have you created indexes on your columns that you are joining on?

It is pretty important. When you run the explain on your select query you will be told the number of rows you are hitting to return the value. Without an index you will most likely see your query hitting all the rows in both the tables you are using in your query. Adding proper indexes will allow you to really reduce the number of rows accessed which is a key to speeding up your queries.

For an example of improvement, a client of mine has a table with 10,000 user records, when performing a simple select query on this table it was showing all 10,000 records being accesses. I added a single index on the username that was being search for and the row access dropped down to 1.

Dan
Code Town | Zombie Head | How Much TP?

Interesting, I wasn't quite aware of this - none of my tables had any indexes apart from Primary keys... I've added an index to the required column in the table worked upon and the result is amazingly quick... The weird part is that the last guy who worked here also didn't put any indexes on the tables in the project he was working on - that was terribly slow man and now I think I know why.

However that took a great load off the site - but again apart from that I'm still working to optimise it as much as possible.... are there any good articles or resources out there to help...

Thanks again for the help guys..

no index keys no surprise it was/is slow.

As I said above : profile your code and queries. Make sure the rows manipulated in the queries ( in the join, where, group by, ... ) are indexed. Pass big objects by reference ...

It is pretty easy to get into MySQL development, and indexes are something that a lot of people are not aware of, or think are some fearful thing to watch out for.

The reason for this most likely comes from the old days where disk-space was expensive. Setting up an index does take additional disk space. The other thing is that if you have too many indexes to check you can decrease performance, but you really need to have a lot of indexes to do that. I have never run into this problem.

So, indexes are easy, you just have to know about them, and use them. I do not have any good reference material for you. Google most likely has some good stuff if you look up optimizing mysql queries. Other than that pick up a textbook on database design, the concepts are useful, especially since relational database development and design is so different from normal OO or functional programming.

Dan
Code Town | Zombie Head | How Much TP?

Indices are a trade off.

An index dramastically increases select speed, because instead of reading in the whole database, you only need to read in ( part of! ) the index and then the relevant rows.
An index also slows down inserts and updates, and it usually slows down more on large tables.
It also takes up disk space - the asylum indices on the main data table (which includes a full text index!) takes up 74 mb for 320 megs of data).


So an index might not be appropriate for a table that's mostly being written to, and read row by row anyhow ( because you're doing a sum(transactionVolume) ),
but most times, it pays to have an index.

so long,

->Tyberius Prime

I get the picture guys... I reindexed all my tables - added indexes on columns which were being use din conditional operations etc and its working great...

Initial Delay time is down from 25 seconds to under a second. One of those rare days when thinsg seem to go pretty right Thanks again guys...