Re: [PHP-DB] PHP and MySQL design question
Chris wrote: My point here was the if you index on (a, b), you don't need to index on (b, a) if both a and b are present in your where clause. The index is read from left to right -- not the where clause. Sure you do. Look at the OP's problem and you'll see you still do. To quote: As you can see that the user can select the columns in any arbitrary order and a query like: select name from benchmarks where logic = AUFLIA and status = sat returns result after sometime. I added another index like (logic, status) and the query returns result in blazing speed but then a query like: select name from benchmarks where status = sat and logic = AUFLIA takes more time to return the result as index were not created in that order. He has both fields included in the where and the index isn't used because it's defined in the opposite order. I find the OP's results difficult to believe. There must be something else going on besides the index. The mysql docs don't agree with this behavior for version 3.x and up. I also couldn't replicate this behavior in one of our tables on a 4.x server with ~2 million rows. EXPLAIN indicated the same (a,b) index would be used regardless of the order of the fields in the where clause. Query times were equally fast as well. -- Roberto Mansfield Institutional Research and Application Development (IRAD) SAS Computing -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PHP and MySQL design question
robertom wrote: Chris wrote: Roberto Mansfield wrote: It shouldn't matter what order the columns are referenced. Mysql is smart enough to optimize the query based on the available indexes. In some cases yes but as with anything there are exceptions :) mysql (and every other db) gets it wrong sometimes. In fact, it should be good enough just to create an index on each column that will be searched -- not on combinations of columns. Multicolumn indexes definitely have their uses. But as the OP found out, they are read left to right based on the idx definition. http://dev.mysql.com/doc/refman/5.1/en/multiple-column-indexes.html http://www.postgresql.org/docs/8.2/interactive/indexes-multicolumn.html are two documents explaining this. My point here was the if you index on (a, b), you don't need to index on (b, a) if both a and b are present in your where clause. The index is read from left to right -- not the where clause. Sure you do. Look at the OP's problem and you'll see you still do. To quote: As you can see that the user can select the columns in any arbitrary order and a query like: select name from benchmarks where logic = AUFLIA and status = sat returns result after sometime. I added another index like (logic, status) and the query returns result in blazing speed but then a query like: select name from benchmarks where status = sat and logic = AUFLIA takes more time to return the result as index were not created in that order. He has both fields included in the where and the index isn't used because it's defined in the opposite order. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PHP and MySQL design question
Roberto Mansfield wrote: It shouldn't matter what order the columns are referenced. Mysql is smart enough to optimize the query based on the available indexes. In some cases yes but as with anything there are exceptions :) mysql (and every other db) gets it wrong sometimes. In fact, it should be good enough just to create an index on each column that will be searched -- not on combinations of columns. Multicolumn indexes definitely have their uses. But as the OP found out, they are read left to right based on the idx definition. http://dev.mysql.com/doc/refman/5.1/en/multiple-column-indexes.html http://www.postgresql.org/docs/8.2/interactive/indexes-multicolumn.html are two documents explaining this. Do you have any performance numbers to believe that this is not the case? Mysql will actually only use one index per table. I was surprised to find this out but it's mentioned in http://www.amazon.com/High-Performance-MySQL-Jeremy-Zawodny/dp/0596003064/ - page 64 (just looked it up to include a page ref). No idea if this is mentioned anywhere on the mysql site (doubt it). -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PHP and MySQL design question
Chris wrote: Mysql will actually only use one index per table. I was surprised to find this out but it's mentioned in http://www.amazon.com/High-Performance-MySQL-Jeremy-Zawodny/dp/0596003064/ - page 64 (just looked it up to include a page ref). No idea if this is mentioned anywhere on the mysql site (doubt it). A friend at Mysql just sent me this. It is the portion of the mysql docs which discusses the new index optimization in 5.x and later. http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PHP and MySQL design question
Chris wrote: Roberto Mansfield wrote: It shouldn't matter what order the columns are referenced. Mysql is smart enough to optimize the query based on the available indexes. In some cases yes but as with anything there are exceptions :) mysql (and every other db) gets it wrong sometimes. In fact, it should be good enough just to create an index on each column that will be searched -- not on combinations of columns. Multicolumn indexes definitely have their uses. But as the OP found out, they are read left to right based on the idx definition. http://dev.mysql.com/doc/refman/5.1/en/multiple-column-indexes.html http://www.postgresql.org/docs/8.2/interactive/indexes-multicolumn.html are two documents explaining this. My point here was the if you index on (a, b), you don't need to index on (b, a) if both a and b are present in your where clause. The index is read from left to right -- not the where clause. Do you have any performance numbers to believe that this is not the case? Mysql will actually only use one index per table. I was surprised to find this out but it's mentioned in http://www.amazon.com/High-Performance-MySQL-Jeremy-Zawodny/dp/0596003064/ - page 64 (just looked it up to include a page ref). No idea if this is mentioned anywhere on the mysql site (doubt it). This is the case in 4.x and earlier. In 5.x and later, mysql can use multiple indexes per table in a query. You can verify this with EXPLAIN assuming the optimizer considers using multiple indexes to be fastest. (Sometimes, one restriction will limit the result considerably and using multiple indexes isn't necessary.) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PHP and MySQL design question
It shouldn't matter what order the columns are referenced. Mysql is smart enough to optimize the query based on the available indexes. In fact, it should be good enough just to create an index on each column that will be searched -- not on combinations of columns. Do you have any performance numbers to believe that this is not the case? Roberto Byte Smokers wrote: Hello I did look into the info from EXPLAIN. I can create the indexes also but then I have to create indexes with all permutation of column order if I want to get good performance from all search query regardless of what order user enters the column. On 10/23/07, Theodoros Goltsios [EMAIL PROTECTED] wrote: I guess EXPLAIN will do the job for you. First of all in order to ensure what is the index used by your queries and then how to improve performance by making the right indexes. Theodoros Goltsios Kinetix Tele.com Support Center email: [EMAIL PROTECTED], [EMAIL PROTECTED] Tel. Fax: +30 2310556134 WWW: http://www.kinetix.gr/ O/H Byte Smokers ??: Hello all I have a table like: CREATE TABLE `benchmarks` ( `name` varchar(50) NOT NULL default '', `logic` varchar(50) NOT NULL default '', `status` varchar(50) NOT NULL default '', `difficulty` int(11) NOT NULL default '0', `xmldata` longblob, PRIMARY KEY (`name`), KEY `logic` (`logic`), KEY `status` (`status`), KEY `difficulty` (`difficulty`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have a search form like: http://craig.cs.uiowa.edu/smt/index.php where each field corresponds to each field in the table. Now user can select any column arbitrality and I generate the select statement depending upon that by looping through each listbox. As you can see that the user can select the columns in any arbitrary order and a query like: select name from benchmarks where logic = AUFLIA and status = sat returns result after sometime. I added another index like (logic, status) and the query returns result in blazing speed but then a query like: select name from benchmarks where status = sat and logic = AUFLIA takes more time to return the result as index were not created in that order. I can get all the possible combination by having indexes like: abc bc c ac (where a,b,c are columns) but it dosnt scale well. If later on I decide to add another column, I have to add all permutation in the indexes too. How can I solve this problem? Thank you. Ritesh -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PHP and MySQL design question
I guess EXPLAIN will do the job for you. First of all in order to ensure what is the index used by your queries and then how to improve performance by making the right indexes. Theodoros Goltsios Kinetix Tele.com Support Center email: [EMAIL PROTECTED], [EMAIL PROTECTED] Tel. Fax: +30 2310556134 WWW: http://www.kinetix.gr/ O/H Byte Smokers ??: Hello all I have a table like: CREATE TABLE `benchmarks` ( `name` varchar(50) NOT NULL default '', `logic` varchar(50) NOT NULL default '', `status` varchar(50) NOT NULL default '', `difficulty` int(11) NOT NULL default '0', `xmldata` longblob, PRIMARY KEY (`name`), KEY `logic` (`logic`), KEY `status` (`status`), KEY `difficulty` (`difficulty`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have a search form like: http://craig.cs.uiowa.edu/smt/index.php where each field corresponds to each field in the table. Now user can select any column arbitrality and I generate the select statement depending upon that by looping through each listbox. As you can see that the user can select the columns in any arbitrary order and a query like: select name from benchmarks where logic = AUFLIA and status = sat returns result after sometime. I added another index like (logic, status) and the query returns result in blazing speed but then a query like: select name from benchmarks where status = sat and logic = AUFLIA takes more time to return the result as index were not created in that order. I can get all the possible combination by having indexes like: abc bc c ac (where a,b,c are columns) but it dosnt scale well. If later on I decide to add another column, I have to add all permutation in the indexes too. How can I solve this problem? Thank you. Ritesh -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] PHP and MySQL design question
Hello I did look into the info from EXPLAIN. I can create the indexes also but then I have to create indexes with all permutation of column order if I want to get good performance from all search query regardless of what order user enters the column. On 10/23/07, Theodoros Goltsios [EMAIL PROTECTED] wrote: I guess EXPLAIN will do the job for you. First of all in order to ensure what is the index used by your queries and then how to improve performance by making the right indexes. Theodoros Goltsios Kinetix Tele.com Support Center email: [EMAIL PROTECTED], [EMAIL PROTECTED] Tel. Fax: +30 2310556134 WWW: http://www.kinetix.gr/ O/H Byte Smokers ??: Hello all I have a table like: CREATE TABLE `benchmarks` ( `name` varchar(50) NOT NULL default '', `logic` varchar(50) NOT NULL default '', `status` varchar(50) NOT NULL default '', `difficulty` int(11) NOT NULL default '0', `xmldata` longblob, PRIMARY KEY (`name`), KEY `logic` (`logic`), KEY `status` (`status`), KEY `difficulty` (`difficulty`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have a search form like: http://craig.cs.uiowa.edu/smt/index.php where each field corresponds to each field in the table. Now user can select any column arbitrality and I generate the select statement depending upon that by looping through each listbox. As you can see that the user can select the columns in any arbitrary order and a query like: select name from benchmarks where logic = AUFLIA and status = sat returns result after sometime. I added another index like (logic, status) and the query returns result in blazing speed but then a query like: select name from benchmarks where status = sat and logic = AUFLIA takes more time to return the result as index were not created in that order. I can get all the possible combination by having indexes like: abc bc c ac (where a,b,c are columns) but it dosnt scale well. If later on I decide to add another column, I have to add all permutation in the indexes too. How can I solve this problem? Thank you. Ritesh
