Select Query Optimization

[Duskwalker]

Whoa I havent been here in awhile. Im reached my breaking point on trying to fix my site heh. Need some outside help. Could anyone help me with his? My site runs lots of queries and lags like crazy with only 30 players in it.

$checkr = mysql_fetch_array(mysql_query("select * from rpg_map_coords where map=5 and x=$x and y=$y"));

$check = mysql_fetch_array(mysql_query("select * from rpg_map_tiles where name='$checkr[tile]'"));

$map = mysql_fetch_array(mysql_query("select * from rpg_map where id=2"));

$map = mysql_fetch_array(mysql_query("select * from rpg_map where id=2"));

$nump = @mysql_num_rows(mysql_query("select * from random_items where place='Cave'"));

$etel = mysql_query("select * from random_items where place='Cave'");

Geez Ive never noticed. There are about 10 other queries on teh same page. And this page is getting loaded at least once everyone 30 seconds.

Regards,
-Dusk

[AstroTeg]

Read up on inner and outer (or left) joining. Your goal would be to try to combine as many of your queries as possible into one query. Trick is this can be over done and make things worse. But from what you posted, that doesn't look like it'll be a problem.

Think of each query call as an expense, much like how it costs money to place a call from a phone booth. Instead of making a collection of 1 minute phone calls to the same person, why not stay on the phone line by using one larger query? The trick will be finding a way to link your tables together so you can create joins.

The 2nd thing I see is you have a couple queries being called twice. One appears to be because you're attempting to find the number of rows BEFORE executing the query. This is like the phone call problem stated above. Instead of making the same call twice, just make it once and use a counter to keep track of how many rows. If you truely only need the number of rows for a particular query, you might try watering the query down in some way to only return 1 row with a value that tells you how many rows there would have been (this is done by using a GROUP BY clause). But without knowing why you're doing this and what your application is doing, its hard to say if these approaches would be a fit for you.

And of course, there's the classic debug approach which is TIME YOUR APPLICATION. Use [man]microtime[/man] and check out the "microtime_diff" function that's posted in the user comments section. Then time how long it takes the entire page to load. Then time all the individual queries. Then as you make changes, you'll have a benchmark to work off of (you'll know if you're making things better or worse). If this is a live web app, then bury the times in hidden tags or have them emailed to you. But without any times, you'll have a rough time figuring out where the bottleneck is at and how well your changes worked.

[Weedpacket]

And don't use "SELECT *" unless you really do want every single field returned no matter what it is. Take the first one, for instance. Every row returned will contain fields map, x and, y, and they'll all have exact same values.

Make sure you've got your tables sufficiently indexed.

$nump = @mysql_num_rows(mysql_query("select * from random_items where place='Cave'"));

Use SQL's COUNT() function to count rows.

[ScubaKing22]

Out of curiosity is using SELECT * FROM faster or slower than using SELECT field1,field2,field3,etc FROM? Does it depend on how many fields are available and how many you're selecting?

[gregmiller]

go to the mysql prompt and type in those queries.
My exp. says SELECT * takes more time than SELECT field1, field2 etc.

Like SELECT * from customers took 0.64 secs
SELECT cust_name,cust_city from customers took 0.06 secs

Greg

[brockn]

I am no expert on mysql but if mysql cached the first request the second would come ALOT faster. Thus true speed for that specific query would only be found after doing both querys with an empty cache.

[Drakla]

There's not actually a lot in it - and it seems to fluctuate either way.

I ran a set of queries twice with benchmark...

select benchmark(100000000, 'select from posts');
select benchmark(100000000, 'select postid, author, subject from posts');
select benchmark(100000000, 'select from posts');
select benchmark(100000000, 'select postid, author, subject from posts');

this is on my local machine, so no worry about server load or anything - results:

Query: ' select benchmark(100000000, 'select from posts');'benchmark(100000000, 'select from posts')

• Time: 1.59562206268 - Results: 1
  Query: ' select benchmark(100000000, 'select postid, author, subject from posts');'benchmark(100000000, 'select postid, author, subject from posts')

• Time: 1.59437322617 - Results: 1
  Query: ' select benchmark(100000000, 'select from posts');'benchmark(100000000, 'select from posts')

• Time: 1.59819602966 - Results: 1
  Query: ' select benchmark(100000000, 'select postid, author, subject from posts');'benchmark(100000000, 'select postid, author, subject from posts')

• Time: 1.59087514877 - Results: 1

The table has varchar columns - but as you can see with 100 million iterations it's still not worth worrying about. I do know that count(*) is supposed to be quicker than count(name) as it says somewhere in the manual.

But saying that, it might be on the retrieval part that the lag, if it exists comes in... dammit! Got to go and try it - I'll let you know what happens.

[... time passes]

Ok - it IS a completely different kettle of beans when you do actually start recovering the data. With an inner test loop of

$time2=getmicrotime();

for($i=0; $i<100; $i++)
{
	$q='select postid, author, subject from posts';
	$db->query($q);

while( $row = $db->fetch_row() )
{
	echo '';	//just for the sake of it
}
}

$time2=getmicrotime() - $time2;

And similarly with just * in the first part:

Time 1: 26.3147809505
Time 2: 14.3847360611

So select * is much slower. I also did a search with just author and subject and the time2 dropped by a second.

Man I'm going to sleep better in my bed tonight.