Re: Single Column Indexes Vs. Multi Column
Sebastian, MySQL uses only one index for searching. As far as I know this applies to all MySQL versions, right ? Regards, Cor - Original Message - From: Sebastian Mendel [EMAIL PROTECTED] To: Michael Stearne [EMAIL PROTECTED]; MySQL List mysql@lists.mysql.com Sent: Thursday, January 10, 2008 6:57 AM Subject: Re: Single Column Indexes Vs. Multi Column Michael Stearne schrieb: For a query like: SELECT id FROM properties WHERE `Country` = 'USA' AND Type='Residential' Is an multi-column index that is (Country, Type) better or worse or the same as a single index Country and another single index Type. better two single indexes depending on your MySQL version will not be used. -- Sebastian -- 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: Single Column Indexes Vs. Multi Column
C.R.Vegelin wrote: Sebastian, MySQL uses only one index for searching. As far as I know this applies to all MySQL versions, right ? AFAIK, MySQL (atleast from version 5.0 onwards) is capable of using multiple indexes. -- Regards, Anup Shukla -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single Column Indexes Vs. Multi Column
C.R.Vegelin schrieb: Sebastian, MySQL uses only one index for searching. As far as I know this applies to all MySQL versions, right ? IMHO, but i heard elselike too, but cannot find any proof in the manual ... -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single Column Indexes Vs. Multi Column
Anup Shukla schrieb: C.R.Vegelin wrote: Sebastian, MySQL uses only one index for searching. As far as I know this applies to all MySQL versions, right ? AFAIK, MySQL (atleast from version 5.0 onwards) is capable of using multiple indexes. i have heard of something similar too, but cannot find any proof in the manual only counterpart, f.e.: http://dev.mysql.com/doc/refman/6.0/en/order-by-optimization.html In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following: [...] The key used to fetch the rows is not the same as the one used in the ORDER BY: -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single Column Indexes Vs. Multi Column
Perrin Harkins schrieb: On Jan 11, 2008 7:22 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: i can only find one source in the manual, where MySQL is using more than on index: http://dev.mysql.com/doc/refman/6.0/en/index-merge-optimization.html Uh, how many sources do you need? 1? It uses multiple indexes, just like it says. This has been true since 5.0. yes, i can read ... ;-) -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single Column Indexes Vs. Multi Column
On Jan 11, 2008 7:22 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: i can only find one source in the manual, where MySQL is using more than on index: http://dev.mysql.com/doc/refman/6.0/en/index-merge-optimization.html Uh, how many sources do you need? It uses multiple indexes, just like it says. This has been true since 5.0. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single Column Indexes Vs. Multi Column
Sebastian Mendel schrieb: Anup Shukla schrieb: C.R.Vegelin wrote: Sebastian, MySQL uses only one index for searching. As far as I know this applies to all MySQL versions, right ? AFAIK, MySQL (atleast from version 5.0 onwards) is capable of using multiple indexes. i have heard of something similar too, but cannot find any proof in the manual only counterpart, f.e.: http://dev.mysql.com/doc/refman/6.0/en/order-by-optimization.html In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following: [...] The key used to fetch the rows is not the same as the one used in the ORDER BY: i can only find one source in the manual, where MySQL is using more than on index: http://dev.mysql.com/doc/refman/6.0/en/index-merge-optimization.html -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Single Column Indexes Vs. Multi Column
For a query like: SELECT id FROM properties WHERE `Country` = 'USA' AND Type='Residential' Is an multi-column index that is (Country, Type) better or worse or the same as a single index Country and another single index Type. Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single Column Indexes Vs. Multi Column
Michael Stearne schrieb: For a query like: SELECT id FROM properties WHERE `Country` = 'USA' AND Type='Residential' Is an multi-column index that is (Country, Type) better or worse or the same as a single index Country and another single index Type. better two single indexes depending on your MySQL version will not be used. -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
