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 > > > > >
