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 <tgol@xxxxxxxxxx> 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: tgol@xxxxxxxxxx, support@xxxxxxxxxx >> 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
