[RESOLVED] faster page load?

jasondavis

I am trying to speed this code up, I just need the 2 latest ID's from this table in my DB is there a faster way to get these?

 <?   $sql_latest="select auto_id from friend_reg_user where status='Active' and auto_id!='$info[auto_id]' order by auto_id DESC  limit 0,2";
      $result_latest=executequery($sql_latest);  ?>

<?  if($line_latest=mysql_fetch_array($result_latest)){ ?>
										<?=show_user_photo($line_latest[auto_id],$photo_type='small',$user_name='yes') ?> <? } ?>

<?  if($line_latest=mysql_fetch_array($result_latest)){ ?>
							<?=show_user_photo($line_latest[auto_id],$photo_type='small',$user_name='yes') ?> <? } ?>

higher up on the page I already have 1 DB call to this same table like this

$sql="SELECT
		status
		,uniqid
		,view
		,last_visit
		,photo_view
		,online
		,featured
		,topfriendslist
		,topfriendsno
		,publicview
		,IP_ADD
		,refnumber
FROM
		friend_reg_user
WHERE
	auto_id='$info[auto_id]' and status='Active'";
//$sql="select * from friend_reg_user where auto_id='$info[auto_id]' and status='Active'";
$result_user=executeQuery($sql);

MarkR

Your first job should be to profile to code to determine why the page isn't loading fast

Is it a client side or network problem (too many images, slow javascript etc?)
If it's a server side problem, what part of the code is slow?
Is it a single slow query, many moderate queries, or something else?
Which query is slow?

To do all of this you will need to install some profiling / instrumentation on your test server(s).

After you've determined the problem, if it is a single query, you will need to understand how MySQL does queries, and be able to use the EXPLAIN command. You can then tune indexes etc, as necessary.

In order to get realistic profiling data, you will need production-like data on your test system. If getting production data on your test system is either logistically difficult (e.g. you have 10Tb of data and a 24k modem) or difficult for other reasons (you're contractually not allowed to have customers' data on test systems, etc), then you will have to simulate some similar data instead.

Mark

jasondavis

On my page I have singled it out to this quiry listed above

MarkR

In which case, you need to take the steps I said above:

Reproduce problem on your test server
Instrument the page
Optimise the query by executing it manually in the mysql CLI, examining the output from EXPLAIN etc (there are books on this subject, as well as a chapter in the mysql docs)
Test your modified query and/or database structure appropriately.

Remember that adding indexes can increase storage requirements.

Mark

jasondavis

So can anyone help me to improve this

Showing rows 0 - 1 (2 total, Query took 0.2848 sec)
SQL query:

SELECT auto_id
FROM friend_reg_user
WHERE STATUS = 'Active'
AND auto_id != '1'
ORDER BY auto_id DESC
LIMIT 0 , 2

here it is with explain

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	friend_reg_user 	ref 	PRIMARY,status 	status 	1 	const 	12220 	Using where; Using filesort

And I already have index on status

jasondavis

i got it down to .002 now