Re: LEFT JOIN changes order of results
[EMAIL PROTECTED] writes: You have a sort because you did an order by. If you had an index with the desired order by, it may be used. Try as you usage of covering indexes. Hi Mathias, Along with your suggestion, a little more thinking about the problem and some experimenting seems to have led to a good solution. There are only a few columns that are commonly sorted by, so I'll create a multi-column index for each of those columns, with mls_num as the second column. Then I'll make the sort on mls_num be in the same direction (ASC or DESC) as the primary sort. According to some quick experiments with EXPLAIN, query plans using this scheme seem to be comparable to plans without the additional mls_num sort. Thanks! ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN changes order of results
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]
Re: LEFT JOIN changes order of results
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]
Re: LEFT JOIN changes order of results
[EMAIL PROTECTED] writes: hi, mls_num is not in a key, have you tried index creation on (zip,price desc,mls_num) ? Hi mathias, mls_num is the primary key, so it does have its own index. I could create a multi-column index covering (zip,price,mls_num), but that was really just one example of many searches; there are about 10 fields that are commonly used for searches, and about 4 that are commonly sorted by, so creating all of those indexes would require 40 indexes, and that's if the searches only use one field. ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN changes order of results
you can also try to increase the value of the tmp_table_size variable. A+ Selon [EMAIL PROTECTED]: You have a sort because you did an order by. If you had an index with the desired order by, it may be used. Try as you usage of covering indexes. you certainly know that one multi-column index is similar to a lot of multi-column others when desired columns are in the right position of columns used in the index. this may let you implement less than 40 indexes. Otherwise force mls_num in all indexes you create an add it in the queries that doesn't use it with an always true condition (nls_num =0 for example) Mathias Selon Scott Gifford [EMAIL PROTECTED]: [EMAIL PROTECTED] writes: hi, mls_num is not in a key, have you tried index creation on (zip,price desc,mls_num) ? Hi mathias, mls_num is the primary key, so it does have its own index. I could create a multi-column index covering (zip,price,mls_num), but that was really just one example of many searches; there are about 10 fields that are commonly used for searches, and about 4 that are commonly sorted by, so creating all of those indexes would require 40 indexes, and that's if the searches only use one field. ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN changes order of results
You have a sort because you did an order by. If you had an index with the desired order by, it may be used. Try as you usage of covering indexes. you certainly know that one multi-column index is similar to a lot of multi-column others when desired columns are in the right position of columns used in the index. this may let you implement less than 40 indexes. Otherwise force mls_num in all indexes you create an add it in the queries that doesn't use it with an always true condition (nls_num =0 for example) Mathias Selon Scott Gifford [EMAIL PROTECTED]: [EMAIL PROTECTED] writes: hi, mls_num is not in a key, have you tried index creation on (zip,price desc,mls_num) ? Hi mathias, mls_num is the primary key, so it does have its own index. I could create a multi-column index covering (zip,price,mls_num), but that was really just one example of many searches; there are about 10 fields that are commonly used for searches, and about 4 that are commonly sorted by, so creating all of those indexes would require 40 indexes, and that's if the searches only use one field. ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN changes order of results
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. I guess what happens in your case is that once you put in an extra LEFT JOIN your query gets optimised differently and tables are read in a different order which gives you a different reslutset. I guess you somehow have to include the mls_num in your second query to ensure that you get same resultset. /Johan Scott Gifford wrote: Hello, I have an application that does searches against a database of homes. A summary of initial search results is displayed by showing a few columns of information about each home. When the user clicks on one of the listings, it retrieves additional information (some from other tables) and displays more detailed information about the house. The summary listings are generated using a normal MySQL query. The detailed views are implemented by specifying which result row to display using a LIMIT clause. For example, if the user clicks on the 3rd listing on a page, the query will use this LIMIT clause: LIMIT 2,1 We do this instead of specifying a value for the primary key so we can have a Next and Previous button that will move between detailed listings. These result rows may pull information in from other tables for display. Sometimes the homes are sorted according to a particular column, and sometimes they aren't. Obviously this whole scheme depends on the homes staying in the same order between the summary queries and the detail queries, even if the ordering is ambiguous. We've had this running for several years, and it's always worked fine. We're now seeing some problems with it, possibly because of a move from a server running MySQL 3.x to one running 4.x. The problem we're seeing is that when additional tables are pulled in for the detailed view, the order is different from the summary view, so the wrong homes are displayed. Here's a simplified example. A summary query might ask: mysql SELECT lutar_homes.mls_num, lutar_images.num_images, lutar_homes_supplemental.address, lutar_homes.listdate FROM lutar_homes, lutar_homes_supplemental LEFT JOIN lutar_images ON lutar_homes.mls_num = lutar_images.mls_num WHERE ((lutar_homes.listdate = (NOW() - INTERVAL '14 00:00:00' DAY_SECOND))) AND lutar_homes.mls_num = lutar_homes_supplemental.mls_num ORDER BY lutar_homes.listdate DESC LIMIT 1; +-++-++ | mls_num | num_images | address | listdate | +-++-++ | 051768 | 1 | 7540 Country Pride Lane | 2005-05-31 | +-++-++ 1 row in set (0.00 sec) When I add one more LEFT JOIN clause (the second one below) to get additional fields for the detailed view, I get a different first home, even though none of the search parameters have changed, and the table hasn't changed: mysql SELECT lutar_homes.mls_num, lutar_images.num_images, lutar_homes_supplemental.address, lutar_homes.listdate FROM lutar_homes, lutar_homes_supplemental LEFT JOIN lutar_images ON lutar_homes.mls_num = lutar_images.mls_num LEFT JOIN lutar_homes_stats ON lutar_homes.mls_num = lutar_homes_stats.mls_num WHERE ((lutar_homes.listdate = (NOW() - INTERVAL '14 00:00:00' DAY_SECOND))) AND lutar_homes.mls_num = lutar_homes_supplemental.mls_num ORDER BY lutar_homes.listdate DESC LIMIT 1; +-++--++ | mls_num | num_images | address | listdate | +-++--++ | 051770 | 9 | 9149 Frankenmuth | 2005-05-31 | +-++--++ 1 row in set (0.02 sec) This change in ordering screws up my system, since if the user clicked on the first result in the summary view, the detailed view may display a completely different home. So my question is: Is this normal behavior for MySQL, or is it a bug? Any suggestions for dealing with it? Thanks! Please let me know if any of this is confusing, and I'll clarify. ---ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN changes order of results
In article [EMAIL PROTECTED], Scott Gifford [EMAIL PROTECTED] writes: The problem we're seeing is that when additional tables are pulled in for the detailed view, the order is different from the summary view, so the wrong homes are displayed. Here's a simplified example. A summary query might ask: mysql SELECT lutar_homes.mls_num, lutar_images.num_images, lutar_homes_supplemental.address, lutar_homes.listdate FROM lutar_homes, lutar_homes_supplemental LEFT JOIN lutar_images ON lutar_homes.mls_num = lutar_images.mls_num WHERE ((lutar_homes.listdate = (NOW() - INTERVAL '14 00:00:00' DAY_SECOND))) AND lutar_homes.mls_num = lutar_homes_supplemental.mls_num ORDER BY lutar_homes.listdate DESC LIMIT 1; +-++-++ | mls_num | num_images | address | listdate | +-++-++ | 051768 | 1 | 7540 Country Pride Lane | 2005-05-31 | +-++-++ 1 row in set (0.00 sec) When I add one more LEFT JOIN clause (the second one below) to get additional fields for the detailed view, I get a different first home, even though none of the search parameters have changed, and the table hasn't changed: mysql SELECT lutar_homes.mls_num, lutar_images.num_images, lutar_homes_supplemental.address, lutar_homes.listdate FROM lutar_homes, lutar_homes_supplemental LEFT JOIN lutar_images ON lutar_homes.mls_num = lutar_images.mls_num LEFT JOIN lutar_homes_stats ON lutar_homes.mls_num = lutar_homes_stats.mls_num WHERE ((lutar_homes.listdate = (NOW() - INTERVAL '14 00:00:00' DAY_SECOND))) AND lutar_homes.mls_num = lutar_homes_supplemental.mls_num ORDER BY lutar_homes.listdate DESC LIMIT 1; +-++--++ | mls_num | num_images | address | listdate | +-++--++ | 051770 | 9 | 9149 Frankenmuth | 2005-05-31 | +-++--++ 1 row in set (0.02 sec) This change in ordering screws up my system, since if the user clicked on the first result in the summary view, the detailed view may display a completely different home. The ordering does not change. You told the syetem to order by listdate DESC, and that's exactly what the system does. Your problem is that the listdate ordering is not complete - there can be multiple homes with the same listdate. LIMIT is only stable if 1. the tables don't change and 2. the ordering is complete If the additional tables joined in don't generate multiple rows for one home, it's enough to add the home's primary key to your ORDER BY. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LEFT JOIN changes order of results
Hello, I have an application that does searches against a database of homes. A summary of initial search results is displayed by showing a few columns of information about each home. When the user clicks on one of the listings, it retrieves additional information (some from other tables) and displays more detailed information about the house. The summary listings are generated using a normal MySQL query. The detailed views are implemented by specifying which result row to display using a LIMIT clause. For example, if the user clicks on the 3rd listing on a page, the query will use this LIMIT clause: LIMIT 2,1 We do this instead of specifying a value for the primary key so we can have a Next and Previous button that will move between detailed listings. These result rows may pull information in from other tables for display. Sometimes the homes are sorted according to a particular column, and sometimes they aren't. Obviously this whole scheme depends on the homes staying in the same order between the summary queries and the detail queries, even if the ordering is ambiguous. We've had this running for several years, and it's always worked fine. We're now seeing some problems with it, possibly because of a move from a server running MySQL 3.x to one running 4.x. The problem we're seeing is that when additional tables are pulled in for the detailed view, the order is different from the summary view, so the wrong homes are displayed. Here's a simplified example. A summary query might ask: mysql SELECT lutar_homes.mls_num, lutar_images.num_images, lutar_homes_supplemental.address, lutar_homes.listdate FROM lutar_homes, lutar_homes_supplemental LEFT JOIN lutar_images ON lutar_homes.mls_num = lutar_images.mls_num WHERE ((lutar_homes.listdate = (NOW() - INTERVAL '14 00:00:00' DAY_SECOND))) AND lutar_homes.mls_num = lutar_homes_supplemental.mls_num ORDER BY lutar_homes.listdate DESC LIMIT 1; +-++-++ | mls_num | num_images | address | listdate | +-++-++ | 051768 | 1 | 7540 Country Pride Lane | 2005-05-31 | +-++-++ 1 row in set (0.00 sec) When I add one more LEFT JOIN clause (the second one below) to get additional fields for the detailed view, I get a different first home, even though none of the search parameters have changed, and the table hasn't changed: mysql SELECT lutar_homes.mls_num, lutar_images.num_images, lutar_homes_supplemental.address, lutar_homes.listdate FROM lutar_homes, lutar_homes_supplemental LEFT JOIN lutar_images ON lutar_homes.mls_num = lutar_images.mls_num LEFT JOIN lutar_homes_stats ON lutar_homes.mls_num = lutar_homes_stats.mls_num WHERE ((lutar_homes.listdate = (NOW() - INTERVAL '14 00:00:00' DAY_SECOND))) AND lutar_homes.mls_num = lutar_homes_supplemental.mls_num ORDER BY lutar_homes.listdate DESC LIMIT 1; +-++--++ | mls_num | num_images | address | listdate | +-++--++ | 051770 | 9 | 9149 Frankenmuth | 2005-05-31 | +-++--++ 1 row in set (0.02 sec) This change in ordering screws up my system, since if the user clicked on the first result in the summary view, the detailed view may display a completely different home. So my question is: Is this normal behavior for MySQL, or is it a bug? Any suggestions for dealing with it? Thanks! Please let me know if any of this is confusing, and I'll clarify. ---ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
