[RESOLVED] optimizing select efficiency

BEFOR

With a rather extensive SELECT query, that has 6 inner joins (two of which have SELECT MAX) it is taking up to 10 or more seconds to process. However this is returning only a single result, with only lookup values (no user data) present and a 'do while' loop has yet to be applied.

Please note that 8 tables total are being joined and one the inner joins is being run on a large lookup (SELECT MAX) having over 70,000 + rows// a zip code lookup table.

The query itself is searchable on two GET variables passed in the url.

Looking at the query, I do need inner joins (left joins may speed the query I realize, but don't beleive they could be used)

The other thing I'm considering is the order of the inner joins.
-Does the ordering of the joins really matter?
(which is performed before the other)

The tables are optimized (indexed columns)

-Can spaces in the query slow the query?

Does changing the htaccess from .php to .htm slow this? - From my observations I'm going to say yes.

Drakla

What do you mean by 'spaces in the query'? If you mean the query formatting like

SELECT
    name,
    address,
    postcode
FROM
   table1, table2
WHERE
   ... etc, etc

rather than having it all on one line, then no, that won't affect your query speed as it gets parsed by the mysql server in microseconds, it's all the hard disk access that'll be slowing things down.

The server also decides what order to do the joins in once the query is given based on a sort of guestimate of indexes and table sizes, but sometimes it right royally falls on its arse and you have to force it to use the right indexes.

Have you ran an EXPLAIN on your query to see how it's optimizing things? Getting a print out of that might be a good starting place.

BEFOR

Yes, the qeury is formatted like your example.

EXPLAIN, no I haven't done that. No sure how that query goes.

mrhappiness

take your complete query, write

EXPLAIN (yes thre's a blank after EXPLAIN)

before it and execute this within phpmyadmin

BEFOR

I did, which then repeated the query back in the same order with

"Your SQL-query has been executed successfully (Query took 6.0205 sec) "

-But this isn't necessarily the optimal order is it?

Now I ran the same exact query and (Query took 0.0004 sec)
Does it somehow hold a cache of prior queries?

another new query: (Query took 6.1988 sec)

It's taking more than 10 seconds on the site. Should I have the page extension set as .php and not .htm?

BEFOR

thanks Drakla

got it to less than a second.