hi, mls_num is not in a key, have you tried index creation on (zip,price desc,mls_num) ?
mathias Selon Scott Gifford <[EMAIL PROTECTED]>: > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
