Re: PHP and MySQL design question

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

 



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


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

  Powered by Linux