trying to optimize table and query

[pedalpete]

I'm fairly new to sql, and am having a problem with a query taking forever to run.
it's 8 seconds to run each query.

I think I have my indexes correct, but I can't seem to find the output which describes the indexes.

I have done an 'explain' on the query, and this is what I got

 id | select_type | table   | type | possible_keys          | key            | key_len | ref                      | rows   | Extra                           |
+----+-------------+---------+------+------------------------+----------------+---------+--------------------------+--------+---------------------------------+
|  1 | SIMPLE      | artists | ALL  | link_bandid_idx        | NULL           | NULL    | NULL                     | 920243 | Using temporary; Using filesort |
|  1 | SIMPLE      | shows   | ref  | lat_idx,get_bandid_idx | get_bandid_idx | 4       | hear.artists.bandid |      7 | Using where          
 

does this look REALLY bad? the 9202043 is worrysome, but I have an UNIQUE index on the bandid field in the artists table. bandid i not unique in shows.

any idea on how i can improve the performance on this?

[code green]

Well you could start by showing us the query....

[pedalpete]

Sorry about that code green.
I can understand how that would help :) it was late, and I had been looking at this for WAY too long.

the query is

SELECT shows.bandid, shows.lat, shows.long, shows.address, shows.title, shows.city, shows.state, shows.zip, shows.date, shows.time, shows.accuracy, artists.name, artists.img, artists.genre, artists.genre FROM shows JOIN artists ON shows.bandid=artists.bandid WHERE `long` > '-98.70172684058' AND `long` < '-97.97708915942' AND `lat` > '29.14524415942' AND `lat` < '29.86988184058' AND shows.date > '2008-05-21' ORDER BY date LIMIT 0,30
 

yesterday I had tried removing the limit, and then order, stripping things down, but wasn't noticing a huge difference.

I just tried it again without the ORDER BY, and leaving the limit, and that quickens it up considerably. Unfortunately the order is incredibly important. I could try to do the order client-side, but that would mean i would need to get the entire list back, and it seems the LIMIT speeds up the results considerably.

The time it is taking to complete the queries is as follows (though I don't exactly understand why there are two figures.
1) query with ORDER BY and LIMIT - 30 rows fetched in 0.0151(8.8205s)
2) query without ORDER BY with LIMIT - 30 rows fetched in 0.0175s(0.3888s)
3) query without ORDER BY without LIMIT - 1671 rows fetched in 8.1671s(0.3986s)

[code green]

It is a fairly straightforward query.
It may be more a case of optimising the tables rather than the query:
INDEXes, VARCHAR fields, segmenting.

SELECT shows.bandid, shows.lat, shows.long, shows.address,
shows.title, shows.city, shows.state, shows.zip, shows.date, shows.time,
shows.accuracy, artists.name, artists.img, artists.genre, artists.genre 
FROM shows JOIN artists 
ON shows.bandid = artists.bandid 
WHERE `long` > '-98.70172684058' 
AND `long` < '-97.97708915942' 
AND `lat` > '29.14524415942' 
AND `lat` < '29.86988184058' 
AND shows.date > '2008-05-21' 
ORDER BY date LIMIT 0,30

OK. If the fields are numeric then remove the quotation marks, they are not needed and will slow down.
If the same results are obtained with LEFT JOIN use that.
Not much but worth a try.
Also I sometimes experiment by moving the WHERE conditions into the ON clause

ON (shows.bandid = artists.bandid 
AND `long` > -98.70172684058 
AND `long` < -97.97708915942 
AND `lat` > 29.14524415942 
AND `lat` < 29.86988184058 
AND shows.date > '2008-05-21')

[pedalpete]

Thanks Code Green,

I suspected that it was the indexes which was causing the problem, which is why I started out with the 'EXPLAIN' response in my first post.
I thought from that, it should point out if I have the right indexes in place. Maybe I'm wrong.

I also just realized that the bandid field in artists is VARCHAR instead of INT, that would make a BIG difference...
Now my response times are 0.03 seconds!!!

I REALLY appreciate your help.
pete

[code green]

Well done.
Strings are notoriously slow to check.
Go for INT wherever possible.
Don't forget to remove the quotes