Code Speed Help

[driver_x]

i have a social website that has a friendslist. my problem is when i click on a profile that has over 1000 friends it takes a massive amount of time for the page to load. the code for the friendslist application is as follows (IS THIS CODE WRITTEN CORRECTLY? CAN IT BE SPED UP?) Thanks in advance Driver_x

 

function ShowFriendList( $id )
{
global $ID_PIC_DIR;
global $ID_PIC_URL;
global $site;
$thumb_height = getParam('thumb_height');
$thumb_width = getParam('thumb_width');

$id = (int)$id;
$friend_list_query = "SELECT `Profiles` .`ID`, `Profiles`.`NickName`, `Profiles`.`Pic_0_addon`, `Profiles`.`Picture` FROM `FriendList`
LEFT JOIN `Profiles` ON (`Profiles`.`ID` = `FriendList`.`Profile` AND `FriendList`.`ID` = '$id' OR `Profiles`.`ID` = `FriendList`.`ID` AND `FriendList`.`Profile` = '$id') WHERE (`FriendList`.`Profile` = '$id' OR `FriendList`.`ID` = '$id' AND `FriendList`.`Check` = '1') ORDER BY `Profiles`.`Picture` DESC LIMIT 12";

$friend_list_res = db_res("$friend_list_query");

while ( $friend_list_arr = mysql_fetch_assoc( $friend_list_res ) )
{

$src_dir = $ID_PIC_DIR . $friend_list_arr['ID'] . '_0_' . $friend_list_arr['Pic_0_addon'] . '.jpg';

if( file_exists( $src_dir ) )
	{
	$src = "{$ID_PIC_URL}{$friend_list_arr['ID']}_0_{$friend_list_arr['Pic_0_addon']}.jpg";
		}
else
	{
	if( 'couple' == $friend_list_arr )
	{
	$src = $ID_PIC_URL . 'couple.gif';
	}
else
	{
	$src_sex = ( 'male' == $friend_list_arr['Sex'] ) ? 'man.gif' : 'woman.gif';
	$src = $ID_PIC_URL . $src_sex;
	}

	}

	$ret .= '<div class="thumbnail_block" style="width:' . $thumb_width . 'px; height:' . $thumb_height . '">';

	$ret .= '<a href="' . $site['url'] . 'profile.php?ID=' . $friend_list_arr['NickName'] . '">';
	$ret .=$friend_list_arr['NickName'];
	$ret .= '<img src="' . $site['images'] . 'spacer.gif"  div class="thumb_profile1" style="width:' . $thumb_width . 'px; height:' . $thumb_height . 'px; background-image:url(' . $src . ');">';'px;" alt="' . $friend_list_arr['NickName'] . '" title="' . $friend_list_arr['Headline'] . '" />';

	$ret .= '</a>';

	$ret .= '</div>';
}
return $ret;
}

[abazoskib]

are you using indexes? could you possibly sort the data with php instead of in your query?

[Highlander]

I whould say that the first thing to check is to see how much time the database query takes, and if the query uses correct indexes.

 

Look up  EXPLAIN SELECT with your MySQL manual

[driver_x]

thanks for the reply.

i did take a look at the indexes and looked at the query. below it seems to be refering to two seperate tables being profiles and friendlist, would that be why its so slow? i attached the query below

 

$friend_list_query = "SELECT `Profiles` .`ID`, `Profiles`.`NickName`, `Profiles`.`Pic_0_addon`, `Profiles`.`Picture` FROM `FriendList`

LEFT JOIN `Profiles` ON (`Profiles`.`ID` = `FriendList`.`Profile` AND `FriendList`.`ID` = '$id' OR `Profiles`.`ID` = `FriendList`.`ID` AND `FriendList`.`Profile` = '$id') WHERE (`FriendList`.`Profile` = '$id' OR `FriendList`.`ID` = '$id' AND `FriendList`.`Check` = '1') ORDER BY `Profiles`.`Picture` DESC LIMIT 12";

 

[abazoskib]

well thats obvious because you are performing a join on two tables.

 

the critical part is here:

 

ON (`Profiles`.`ID` = `FriendList`.`Profile` AND `FriendList`.`ID` = '$id' OR `Profiles`.`ID` = `FriendList`.`ID` AND `FriendList`.`Profile` = '$id') WHERE (`FriendList`.`Profile` = '$id' OR `FriendList`.`ID` = '$id' AND `FriendList`.`Check` = '1') ORDER BY `Profiles`.`Picture` DESC LIMIT 12";

 

You are requiring a few different conditions( are these columns indexed? ) and then you are ordering by a column that might not be indexed. My suggestion is could you possibly order the results yourself? You might see a speed increase because the MySQL server wont have to order it before you get your data.

[driver_x]

hi abazoskib,

you were absolutely right, it is that line of query.  i think it because it has so many AND's / Or features. i have to admit its a bit too technical for this novice to rectify. I think i may need to get someone with the right knowledge to fix this for me

[driver_x]

i actually ran an EXPLAIN infront of the full query and this was the result.

 

Showing rows 0 - 1 (2 total, Query took 0.0007 sec)

SQL query:

 

EXPLAIN SELECT `Profiles`.`ID` , `Profiles`.`NickName` , `Profiles`.`Pic_0_addon` , `Profiles`.`Picture`

FROM `FriendList`

LEFT JOIN `Profiles` ON ( `Profiles`.`ID` = `FriendList`.`Profile`

AND `FriendList`.`ID` = '1'

OR `Profiles`.`ID` = `FriendList`.`ID`

AND `FriendList`.`Profile` = '1' )

WHERE (

`FriendList`.`Profile` = '1'

OR `FriendList`.`ID` = '1'

AND `FriendList`.`Check` = '1'

)

ORDER BY `Profiles`.`Picture` DESC

LIMIT 12 

 

 

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra 

1    SIMPLE  FriendList ALL FriendPair,ID,Profile NULL NULL NULL 8685 Using where; Using temporary; Using filesort

1 SIMPLE Profiles ALL PRIMARY NULL NULL NULL 22380 

 

         

 

[abazoskib]

driver, dont worry, its not something you cant fix yourself. i can help. first off, have a look here:

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

 

and then here for how to actually do it:

 

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

 

Basically the thing is, you dont want to have an index on every column(because that would be counter productive), but you want to have an index on the fields that you are most likely going to be searching on.

 

Also, I can furthur explain your ORDER BY clause. When you do an ORDER BY, on a non-indexed column, and if I remember correctly, the whole table is sorted. So even though you are using LIMIT 12, the whole table is still being sorted. And on a table where there are 1000s of rows, you could imagine how slow this could be. Now, since your result set is 12 rows, why not sort them with PHP? If you would like to sort them alphbetically, put the values into an array and use sort(). This along with good use of indexes will transform your script.

[BLaZuRE]

I have a burning question right now that I think was overlooked:

Is it taking a long time to load because of the MySQL or because of the file size of the images loaded?

Jpegs take a while to load, especially if they're high resolution images.

Yeah, it's not really MySQL-related, but it seems like the query takes 1 second for 1000 people.

 

and ...

hi abazoskib,

you were absolutely right, it is that line of query.  i think it because it has so many AND's / Or features. i have to admit its a bit too technical for this novice to rectify. I think i may need to get someone with the right knowledge to fix this for me

fix it for you? >.> ... the wording doesn't feel right when people make a living from database creation and maintenance

[driver_x]

Hi blazure,

 

I know what you main about file size, and no thats not the problem here. It is actually the specific line of code that abazoskib noticed. Still trying to figure out how to write it better

[driver_x]

ok thanks for the reading material guys i do greatly appreciate it. i have learnt alot. i just checked the table for the friendslist section and i have 8685 rows within this table with indexes for

 

Keyname      Type          Cardinality        Field

 

FriendPair      UNIQUE            8685          ID 

                                                          Profile 

 

ID                INDEX              4342            ID 

 

Profile            INDEX              1085      Profile 

 

 

does this mean i need to create another index to sort results?

[driver_x]

i think i will need to get some professional help with this cause its beyond me