Re: PHP and MySQL design question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux