Query speed problems??

furious5

Hi guys, just wondering if anyone can offer me a bit of advice.

I've recently built an SQL statement which is built on the fly, upon testing the statement I am experiencing very long query times of anything up to around 2 minutes once it is put live!!!

The SQL I am querying with looks something like this:

SELECT * FROM villas, pricing WHERE villas.resort = 'cala_dor' OR villas.resort ='pollenca' AND villas.beds LIKE '%%' AND pricing.week = '1085180400' AND pricing.price != '0' AND pricing.price LIKE '%%' AND pricing.availability = '1' AND villas.id = pricing.id GROUP BY villas.id ORDER BY villas.owner DESC, villas.beds ASC 
[code]

I have set up indexes on:

Villas > Resort, Beds
Pricing > Week, Price, Availability

Does anyone have any ideas why it could be taking so long to run???

bubblenut

Try prepending the keyword explain to the query (I find this more convenient at the comand line) to give you details of how the query is working. You can get more info on this from MySQL.com . If you are still having problems with this post up the explain results.
HTH
Bubble

furious5

Here is the output of the explain statement, not entirely sure what it is eluding to

table type
villas
pricing

type
ALL
ALL

possible_keys
PRIMARY,resort
availability,week

key
NULL
NULL

key_len

NULL
NULL

ref

NULL
NULL

rows
255
15288

Extra
where used
where used

bubblenut

Right the type ALL bit's mean that every element in table 1 is being compared to every element in table 2 (this is the worst possible join; an easy trap to fall into and one which I do very often) . This case that will be 25515288 (3898440) comparisons. Now I find it difficult to debug queries even when I know what they're for and have worked with them so I may not be a great help here. One thin that seems to just out is the lack of braketing in the WHERE. Also remember that LIKE is very slow compared to other comparisons. In this case you don't reall need the villas.beds or the pricing.price as they're not restricting the resultset at all. Also try playing around with different joins, I once reduced a query time to about 1 10th of it's original time by just changing some of the straight joins to left joins, however sometimes this can make it even longer I believe.
Also, select is very untidy and I think not as efficient.
HTH
Bubble
ps, Please anyone point out any misconseptions I may have.