Proper indexes used but query "Using temporary; Using filesort"

[Thanasus]

The query is complex and returns 16,000 out of 240,000 rows. Proper indexes are used and the query is relatively fast however it always uses temp tables and filesort. I am trying to speed it up though. When I do an explain I get:

[tt]

+--------+------+---------------+-------+---------+------------+-------+---------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+--------+------+---------------+-------+---------+------------+-------+---------------------------------------------+
| p | ref | x_mfl | x_mfl | 1 | const | 15889 | where used; Using temporary; Using filesort |
| t5 | ref | pav | pav | 5 | p.id,const | 1 | where used; Using index |
| t6 | ref | pav | pav | 5 | p.id,const | 1 | where used; Using index |
| t33 | ref | pav | pav | 5 | p.id,const | 1 | where used; Using index |
| t56 | ref | pav | pav | 5 | p.id,const | 1 | where used; Using index |
| sorder | ref | pav | pav | 5 | p.id,const | 1 | Using index |
+--------+------+---------------+-------+---------+------------+-------+---------------------------------------------+

[/tt]


Its a MyISAM table and I am running MySQL 3.23 on FreeBSD. The system has 1.5 gigs of ram. I am guessing its a startup variable that needs tweeking to allocate more memory but an not certain to too which one...

 

[Thanasus]

Oh, if the query provides any insight....


SELECT p.id AS propid

FROM prop AS p USE INDEX(x_mfl)
LEFT JOIN prop_int AS t5 ON (t5.fkey_prop=p.id AND t5.fkey_woprattrib=5)
LEFT JOIN prop_int AS t6 ON (t6.fkey_prop=p.id AND t6.fkey_woprattrib=6)
LEFT JOIN prop_int AS t33 ON (t33.fkey_prop=p.id AND t33.fkey_woprattrib=33)
LEFT JOIN prop_int AS t56 ON (t56.fkey_prop=p.id AND t56.fkey_woprattrib=56)
LEFT JOIN prop_int AS sorder ON (sorder.fkey_prop=p.id AND sorder.fkey_woprattrib=1)


WHERE
p.fkey_mlsproptype=1
AND p.last_import>=1392
AND p.first_import<=1392
AND (t5.value >= 2)
AND (t6.value >= 3)
AND (t6.value <= 3)
AND (t33.value >= 1)
AND (t33.value <= 2)
AND (t56.value = 89123)

GROUP BY p.id
ORDER BY sorder.value DESC

;

 

[Thanasus]

I should have posted all in one post... I keep reading my post and realizing I left stuff out.

One row of data from table prop is 44 bytes. One row from prop_int is 69 bytes. Prop_int is joined 5 times, so that means a total row size of (5 * 69) = 345 bytes. Plus the 44 from the prop table, so total of 399 bytes.

This query results in 15,889 results... so 15,889 * 399 = 6,339,711 bytes = 6,191 kilobytes = 6.04 megs. I have a sort_buffer of 16 megs.

When I remove the order by statement, the filesort goes away - which I would assume occurs since I am ordering by a column different from my grouping column.

I am still, however, stuck with a temporary table being generated and for the life of me I can't figure out how to get around that.

 

[hvass]

If your problem is you want more performance look at your query not whether the query optimiser decides to create a temporary table.

Try and avoid left joins unless you really need them, don't use <= and >= use between and ask yourself if your really need to join so many tables together

Lastly if I have a group by especially with a sort by a variable other than the group by variable and I am expecting any number of rows to be returned even 15,000 I often choose to instruct the optimiser to use disk and file sort by explictly specifying

select SQL_BIG_RESULT p.id AS propid etc..

 

[Thanasus]

The table is for real estate data. The table prop_int contains different attributes of homes. Example, the price of a home is attribute #35, its square footage is attribute #47. A person can search the database of 200,000+ homes by specifying a lot of optional criteria. The could put a price range in of <200,000 or a range of between 180,000 to 240,000.

So I figure out what properties are in the prices range by doing left joining the prop_int table onto the prop table. This allows me to do a query of &quot;Give me all properties that are within this price range&quot;.

The customer may instead specify only square footage... or they may specify a combination of the two such as &quot;between $140,00 and $160,000 and at least 2,200 square feet.&quot; So, I would join the prop_int table onto the prop table twice, once for each atribute of price and sqft.

Ther resultset may be 0, 1, 10, 100, or 40,000.

I also am stuck using <= and >= instead of betweens because many times the customer just wants to set a min or max but not both.

The sort by is also dynamic because we let the customer chose how they want to sort the results... show results by price, square footage, age of home, etc...

Considering how dynamic the query is, it may involve one or many attribute restrictions, the only join I could see that made sense was a left join.

 

[cocobingo]

Since your sort is dynamic, it will often be on another field than the one you are grouping by. This always causes a &quot;Using Filesort&quot;. See :

http://www.mysql.com/doc/en/ORDER_BY_optimisation.html

The problem of &quot;Using temporary&quot; seems to be the same as the one I encounter (see thread thread436-714140 always have a temporary table when grouping by. That would be OK if this table was memory based, but unfortunately it is always disk based...
I'll follow this thread with much interest!

Arnaud