Johan Höök <[EMAIL PROTECTED]> writes: > Hi Scott, > I think you've been lucky so far... > As you're only ordering on listdate, which is the same > for both homes in your example you might definitely get different > results once you put in the limit. > A basic thing about rdb's is that you must never make assumptions > that it returns resultsets in the same order unless you specify what > to order by, of course quite often you'll get it back in the same order > but you must never bank on it.
Hi Johan, I guess I have been lucky. [...] > I guess you somehow have to include the mls_num in your second query > to ensure that you get same resultset. I'm looking into adding mls_num into all queries to fix this problem, but it looks likely to make performance much worse. Here's MySQL's plan for a typical query: mysql> EXPLAIN SELECT * FROM faar_homes WHERE zip = 48503 ORDER BY price DESC LIMIT 10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: faar_homes type: index possible_keys: zip key: price key_len: 4 ref: NULL rows: 5194 Extra: Using where 1 row in set (0.00 sec) When I add in mls_num, it uses a filesort: mysql> EXPLAIN SELECT * FROM faar_homes WHERE zip = 48503 ORDER BY price DESC, mls_num LIMIT 10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: faar_homes type: ALL possible_keys: zip key: NULL key_len: NULL ref: NULL rows: 5194 Extra: Using where; Using filesort 1 row in set (0.00 sec) It seems that this fix will cause nearly all of my queries to use filesort. Any ideas for avoiding this? Thanks! --ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]