Re: 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 <tgol@kinetix.gr> 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@kinetix.gr, support@kinetix.gr > 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 > > > > >