| Byte Smokers 2007-10-23, 3:59 am |
| 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
|