Too many queries and search load time is mega slow

[Mal1]

Removed the RAND section all together for now and it's sped up the search by about half which is good but still a bit slow.

Not been able to use JOIN yet - should that see a speed increase?

[StarLion]

I would need a database person's expertise to back me up, but to my head it SHOULD do this;

I do an implicit join (SELECT ... FROM tablea,tableb,tablec...) I then have to inner join EVERY row of table A and table B. I then inner join that combined table with table C.
Then i take the entire thing, and THEN pair down the results based on the WHERE.

If instead i explicitly define my join conditions (SELECT ... FROM tablea INNER JOIN tableb ON tablea.fileid = 10 AND tablea.bjoin = tableb.ajoin INNER JOIN tablec ON tablea.cjoin = tablec.ajoin ....)
Now my first join only holds rows where fileid is 10. This should make the second (and subsequent) joins faster, because there are less records to join to.

Now, i could be completely talking out of my rear, but thats how it works in my head

[Mal1]

Again, would I see significant speed improvements?

The websites are on a shared server at the moment and we're thinking of switching to a dedicated server. My issue is that our other (original) website works fine and searches are almost instant (there are difference in database tables and therefore the way searches are carried out). The new site is really slow BUT they're both on the same server - so I don't know how much of a benefit moving to a dedicated server would provide.

[SpacePhoenix]

Can you please post the whole, entire "monster query" as it currently stands and the output of a SHOW CREATE TABLE for each of the tables that the query uses?

What information is required to be output?

Approximately how many records are there in the database you're testing it against?

[aaarrrggh]

Yeah... is it at all possible for you to even dump some of the tables complete with their data so we can do some testing?

If the tables are massive (which they probably are if we're dealing with a query that takes a long time), or if the tables contain any sensitive data then scrap that idea and just give us the structure of the tables and an idea of how much data is contained in each table...

Cheers

[DaveMaxwell]

I would need a database person's expertise to back me up, but to my head it SHOULD do this;

I do an implicit join (SELECT ... FROM tablea,tableb,tablec...) I then have to inner join EVERY row of table A and table B. I then inner join that combined table with table C.
Then i take the entire thing, and THEN pair down the results based on the WHERE.

If instead i explicitly define my join conditions (SELECT ... FROM tablea INNER JOIN tableb ON tablea.fileid = 10 AND tablea.bjoin = tableb.ajoin INNER JOIN tablec ON tablea.cjoin = tablec.ajoin ....)
Now my first join only holds rows where fileid is 10. This should make the second (and subsequent) joins faster, because there are less records to join to.

Now, i could be completely talking out of my rear, but thats how it works in my head

I had to look it up to ensure research hadn't come out with some different results, but I can't find any...

There's no negligible performance difference between the two syntaxs. The only real difference between the two is the DBMS will convert the implicit join syntax to the explicit before executing. Otherwise it's exactly the same.

The savings is on having someone look at your sql code - it's faster to establish the PK/FK combinations, PLUS it's easier to ensure the join actually occurs and you're not returning ALL rows from both tables (that happens a lot more than you would think....)

[DaveMaxwell]

Looking at your generated SQL, that subquery looks expensive. I have a feeling that might be what's causing you issues.

Generating the resultset FIRST, then using that resultset as a JOINed table to group by and get the price might be a better approach. But I'd need data to see for sure.