Performance

[Kryptix]

As you probably know by now I run a game and I'm trying to get the most effient queries as possible.  When they login it queries the database about 8 times and I'm sure after the help from you I can reduce this to 4 queries with JOINs, but is less queries always better?

 

How would I test which is quicker and more effient? Is it a case of just querying and adding up the total query time and do a side-by-side comparison?

[Kryptix]

Original queries:

 

SELECT * FROM `rscd_players` WHERE `user` = '13201441';

SELECT * FROM `rscd_experience` WHERE `user` = '13201441';

SELECT * FROM `rscd_curstats` WHERE `user` = '13201441';

 

New query:

 

SELECT * FROM `rscd_players`

JOIN `rscd_experience` ON `rscd_players`.`user` = `rscd_experience`.`user`

JOIN `rscd_curstats` ON `rscd_players`.`user` = `rscd_curstats`.`user`

WHERE `rscd_players`.`user` = '13201441'

 

However, I believe they're pretty much the same. The first two queries (rscd_players and rscd_experience) were being averaging 0.0007 to query, whilst the 3rd (rscd_curstats) was taking an average of 0.0063-0.0080 even though there's the same amount of columns and rows, and every single column is a INT(2). That doesn't make sense to me considering rscd_players has over 15 more columns and lots of VARCHAR(255)'s yet only takes 0.0007 on average.

 

Anyway, the JOIN query was taking 0.0067-0.0090 on average. Still, that is very, very little difference. Is there really much difference when joining queries or should I just make life easier and use multiple queries every time?

[Kryptix]

I've now selected each column rather than using a wildcard and it seems to be taking a fraction longer. I thought the whole point was to avoid bringing back data you didn't use, thus making it quicker? I've knocked off possibly 20 columns by doing it this way:

 

SELECT `owner`, `group_id`, `login_date`, `login_ip`, `combat`, `x`, `y`, `fatigue`, `combatstyle`, `block_chat`, `block_private`, `block_trade`, `block_duel`, `block_global`, `cameraauto`, `onemouse`, `soundoff`, `showroof`, `autoscreenshot`, `combatwindow`, `haircolour`, `topcolour`, `trousercolour`, `skincolour`, `headsprite`, `bodysprite`, `male`, `skulled`, `kills`, `deaths`, `exp_attack`, `exp_defense`, `exp_strength`, `exp_hits`, `exp_ranged`, `exp_prayer`, `exp_magic`, `exp_cooking`, `exp_woodcut`, `exp_fletching`, `exp_fishing`, `exp_firemaking`, `exp_crafting`, `exp_smithing`, `exp_mining`, `exp_herblaw`, `exp_agility`, `exp_thieving`, `cur_attack`, `cur_defense`, `cur_strength`, `cur_hits`, `cur_ranged`, `cur_prayer`, `cur_magic`, `cur_cooking`, `cur_woodcut`, `cur_fletching`, `cur_fishing`, `cur_firemaking`, `cur_crafting`, `cur_smithing`, `cur_mining`, `cur_herblaw`, `cur_agility`, `cur_thieving` FROM `rscd_players`

JOIN `rscd_experience` ON `rscd_players`.`user` = `rscd_experience`.`user`

JOIN `rscd_curstats` ON `rscd_players`.`user` = `rscd_curstats`.`user`

WHERE `rscd_players`.`user` = '13201441'

 

It seems like everything I thought made stuff more effient actually doesn't unless it's possibly on a lot larger scale? I have 12,000 records in each 3 tables making 36,000 records total, I'm not sure if that makes any difference.

[fenway]

If you're not actually profiling these, it's meaningless.q