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]
