MySQL vs TXT Files

Which would be faster?

[inveni0]

I have a query that joins two tables based on matching zipcodes where the zipcode exists within an array of zipcodes. (Confused, yet?) My zipcode table is about 33000 records long, and it takes FOREVER to load these results. What would be the best way to get my data?

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?

[LifeinAsia]

Forget about a flat file!

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.

[maximillianos]

Creating an index on the table for the fields in your "where" clause will probably fix the performance problem.

[blang]

inveni0> Can you show us a 'real' query statement (without the PHP code / variables / etc)? Also, post your table schema and any indexes (SHOW KEYS FROM yourtablename).

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.

[inveni0]

I'm on a shared server, so I'll post all of the information I possibly can, without the variables. A question, though: How do I put this variable

$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.

[inveni0]

Scratch that last question--I got it.

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?

[Demaestro]

Tab bit of WebmasterWorld trivia.

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.

[inveni0]

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?

[blang]

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.

Well, that depends, how are you retrieving and displaying / formatting the data? The SQL statement needs to be optimized as much as possible (and the table, columns, data, etc) but that's only half of the process of getting it on the page. What do you do after the SQL statement? Does the page have alot of JS to load?

Do any of you know a way to debug a slow script other than echoing variables as it writes them?

Not really. Step-by-step commenting things out and seeing the results as you run the script is my personal method. I suppose if you were using some big IDE you could step through breakpoints, but I prefer a plain text editor myself.

[blang]

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?

Interesting. This sort of goes back to my comment about "how are you retrieving the data". Does it grind to a halt on the query execution itself, or afterward? Are you using the plain old MySQL extension, or a db abstraction layer? Are you accidentally (or intentionally) running this statement in a loop of some kind? More information == more help. :)

[inveni0]

Well, I placed an "echo date(s);" command just before the query and then again just after it. There was a 14 second discrepancy there. So I'm assuming the delay is within the query.

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

[Demaestro]

What are you using for a DB conn?

If it is that big a difference between executing it directly and executing it in PHP I would look to the connection object.

[inveni0]

I connect like this:

$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); 

[Demaestro]

There have been several online posts about how mysql_pconnect is slow and ways of fixing it.

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

[inveni0]

I don't expect that it's the connection causing the problem because my site makes dozens of other connections with no delays. This specific query is simply the most complex. But I'll look into that just to cover my bases.

[inveni0]

Still having trouble with this. Anyone have any other ideas?