Forum Moderators: open
I thought about putting the zipcode database (which includes things such as zip, city, state, lat, lon) into a text file, but I'm thinking that parsing that information would take longer than a sql query.
Here's the query:
<?php
$query_getAds = sprintf("SELECT * FROM `_ads` join `_images` ON `_ads`.adId = _images.adId join `_zipcodes` ON `_ads`.zipcodeId = _zipcodes.zipcodeId WHERE _zipcodes.zipcodeId IN ($searchzip) AND `_ads`.categoryId = %s GROUP BY `_ads`.adId ORDER BY `_ads`.adId LIMIT %s,10", $browseCat, $start);
?>
Is there a way to better assemble this and optimize for speed?
Have you properly indexed the tables?
Also, do you really need to select everything? It's usually best to just select the specific fields that you're going to use.
Also, is the _zipcodes.zipcodeId field INT or VARCHAR? Assuming you can use INT (Canada, the U.K. and some other countries use letters in their ZIP codes, so it won't work for those countries), INT searches are usually faster than CHAR or VARCHAR searches.
Proper data types, indexes and using the correct JOIN will definitely help smooth things out.
While you're at it, take the time to read the MySQL manual section on optimization [dev.mysql.com], especially optimizing SELECT statements [dev.mysql.com]. EXPLAIN is your friend. :)
I'm with LifeInAsia, flat files are absolutely out.
$searchzip = "65721,65802,65803,65753";into regular SQL? I'm running a benchmark on my select statement through regular SQL, and I think this is where the problem is because it seems to process the simple joins rather quickly.
Well, this is my query:
SELECT * FROM `_ads` join `_images` ON `_ads`.adId = _images.adId join `_zipcodes` ON `_ads`.zipcodeId = _zipcodes.zipcodeId WHERE _zipcodes.zipcodeId IN (65721, 65714, 65669, 65753, 65728, 65754, 65630, 65810, 65631, 65804, 65809, 65610, 65619, 65807, 65742, 65771, 65653, 65675, 65620, 65806, 65657, 65629, 65802, 65633, 65740, 65738, 65720, 65656, 65652, 65759, 65737, 65616, 65705, 65803, 65757, 65731, 65612, 65624, 65614, 65686, 65746, 65781, 65747, 65605, 65701, 65672, 65680, 65679, 65604, 65611, 65648, 65725, 65627, 65706, 65769, 65739, 65681, 65744, 65712, 65617, 65608, 65755, 65770, 72660, 65646, 65710, 65704, 65658, 65733, 65762, 65708, 65707, 65625, 65644, 72662, 65713, 65761, 72630, 65641, 65623, 65734, 65752, 72644, 65756, 65632, 65702, 65723, 65773, 65715, 65663, 65601, 64848, 65676, 65622, 64873, 65667, 72668, 65717, 65590, 65635, 65613, 65662, 65647, 65661, 64862, 72615, 64874, 65729, 65745, 65649, 65722, 64842, 72616, 72638, 72631) AND `_ads`.categoryId = 81 GROUP BY `_ads`.adId ORDER BY `_ads`.adId LIMIT 0,10
And it runs straight through SQL in about 0.16 seconds. So it doesn't seem that it's my select statement. I must have another script holding everything up.
Do any of you know a way to debug a slow script other than echoing variables as it writes them?
Did you know this site runs on flat files?
Although I agree using flat files is a detriment and is why this site has no built in search tool.
The great thing about a DB is you get to use the t-sql language which is a time saver, plus the built in functions your db provides will save you time when you need to do reporting.
And it runs straight through SQL in about 0.16 seconds. So it doesn't seem that it's my select statement. I must have another script holding everything up.
Do any of you know a way to debug a slow script other than echoing variables as it writes them?
I was wrong! I placed some time markers in my script, and the big hold up occurs during this select statement. So, why would it run in 0.16 seconds as a straight SQL command but take 14 seconds to run from my PHP script?
I don't know the difference between "plain old MySQL extension, or a db abstraction layer", so I'm not sure what to say there.
This statement isn't part of a loop, but it does exist in an IF, THEN clause. The script is not slow if the SQL statement doesn't run according to the clause.
I ran EXPLAIN straight through SQL, and this is what I got:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE _images ALL NULL NULL NULL NULL 290 Using temporary; Using filesort
1 SIMPLE _ads eq_ref PRIMARY PRIMARY 4 hometow_fas._images.adId 1 Using where
1 SIMPLE _zipcodes ALL PRIMARY NULL NULL NULL 32236 Using where
If it is that big a difference between executing it directly and executing it in PHP I would look to the connection object.
$hostname_USER = "my.sqlsite.com";
$database_USER = "dbname";
$username_USER = "username";
$password_USER = "password";
$USER = mysql_pconnect($hostname_USER, $username_USER, $password_USER) or trigger_error(mysql_error(),E_USER_ERROR);
Fixes depend on the host machine OS.
One that I found that seems to have helped a few people was this...
On your server’s host file (normally found in c:\windows\system32\drivers\etc\hosts), add your server’s IP address (in my case it was the WAN IP) and map it to your server’s machine name. For example:123.456.123.456 server_name
My sql connect times went from 5-6 seconds down to less then 1 second!
I am not sure that it is your connection object that is the problem but some testing should be able to prove to you if it is or not.
Just google for something like....
mysql_pconnect slow
