PHP and MySQL design question

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

Re: PHP and MySQL design question

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

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: PHP and MySQL design question

------=_Part_2994_7123435.1193164724475
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_2994_7123435.1193164724475--

Re: PHP and MySQL design question

It shouldn't matter what order the columns are referenced. Mysql is
smart enough to optimize the query based on the available indexes. In
fact, it should be good enough just to create an index on each column
that will be searched -- not on combinations of columns. Do you have any
performance numbers to believe that this is not the case?

Roberto


Byte Smokers wrote:
> 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 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
>>>
>>>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: PHP and MySQL design question

Roberto Mansfield wrote:
> It shouldn't matter what order the columns are referenced. Mysql is
> smart enough to optimize the query based on the available indexes.

In some cases yes but as with anything there are exceptions :)

mysql (and every other db) gets it wrong sometimes.

> In fact, it should be good enough just to create an index on each column
> that will be searched -- not on combinations of columns.

Multicolumn indexes definitely have their uses. But as the OP found out,
they are read left to right based on the idx definition.

http://dev.mysql.com/doc/refman/5.1/en/multiple-column-index es.html
http://www.postgresql.org/docs/8.2/interactive/indexes-multi column.html

are two documents explaining this.

> Do you have any performance numbers to believe that this is not the case?

Mysql will actually only use one index per table. I was surprised to
find this out but it's mentioned in
http://www.amazon.com/High-Performance-MySQL-Jeremy-Zawodny/ dp/0596003064/
- page 64 (just looked it up to include a page ref).

No idea if this is mentioned anywhere on the mysql site (doubt it).

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: PHP and MySQL design question

Chris wrote:
> Roberto Mansfield wrote:
>> It shouldn't matter what order the columns are referenced. Mysql is
>> smart enough to optimize the query based on the available indexes.
>
> In some cases yes but as with anything there are exceptions :)
>
> mysql (and every other db) gets it wrong sometimes.
>
>> In fact, it should be good enough just to create an index on each column
>> that will be searched -- not on combinations of columns.
>
> Multicolumn indexes definitely have their uses. But as the OP found out,
> they are read left to right based on the idx definition.
>
> http://dev.mysql.com/doc/refman/5.1/en/multiple-column-index es.html
> http://www.postgresql.org/docs/8.2/interactive/indexes-multi column.html
>
> are two documents explaining this.

My point here was the if you index on (a, b), you don't need to index on
(b, a) if both a and b are present in your where clause. The index is
read from left to right -- not the where clause.


>> Do you have any performance numbers to believe that this is not the case?
>
> Mysql will actually only use one index per table. I was surprised to
> find this out but it's mentioned in
> http://www.amazon.com/High-Performance-MySQL-Jeremy-Zawodny/ dp/0596003064/
> - page 64 (just looked it up to include a page ref).
>
> No idea if this is mentioned anywhere on the mysql site (doubt it).

This is the case in 4.x and earlier. In 5.x and later, mysql can use
multiple indexes per table in a query.

You can verify this with EXPLAIN assuming the optimizer considers using
multiple indexes to be fastest. (Sometimes, one restriction will limit
the result considerably and using multiple indexes isn't necessary.)

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: PHP and MySQL design question

> Chris wrote:
>> Mysql will actually only use one index per table. I was surprised to
>> find this out but it's mentioned in
>> http://www.amazon.com/High-Performance-MySQL-Jeremy-Zawodny/ dp/0596003064/
>> - page 64 (just looked it up to include a page ref).
>>
>> No idea if this is mentioned anywhere on the mysql site (doubt it).

A friend at Mysql just sent me this. It is the portion of the mysql docs
which discusses the new index optimization in 5.x and later.

http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimizat ion.html

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: PHP and MySQL design question

robertom wrote:
> Chris wrote:
>> Roberto Mansfield wrote:
>>> It shouldn't matter what order the columns are referenced. Mysql is
>>> smart enough to optimize the query based on the available indexes.
>> In some cases yes but as with anything there are exceptions :)
>>
>> mysql (and every other db) gets it wrong sometimes.
>>
>>> In fact, it should be good enough just to create an index on each column
>>> that will be searched -- not on combinations of columns.
>> Multicolumn indexes definitely have their uses. But as the OP found out,
>> they are read left to right based on the idx definition.
>>
>> http://dev.mysql.com/doc/refman/5.1/en/multiple-column-index es.html
>> http://www.postgresql.org/docs/8.2/interactive/indexes-multi column.html
>>
>> are two documents explaining this.
>
> My point here was the if you index on (a, b), you don't need to index on
> (b, a) if both a and b are present in your where clause. The index is
> read from left to right -- not the where clause.

Sure you do. Look at the OP's problem and you'll see you still do.

To quote:

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.


He has both fields included in the where and the index isn't used
because it's defined in the opposite order.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: PHP and MySQL design question

Chris wrote:
>> My point here was the if you index on (a, b), you don't need to index on
>> (b, a) if both a and b are present in your where clause. The index is
>> read from left to right -- not the where clause.
>
> Sure you do. Look at the OP's problem and you'll see you still do.
>
> To quote:
>
> 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.
>
> He has both fields included in the where and the index isn't used
> because it's defined in the opposite order.

I find the OP's results difficult to believe. There must be something
else going on besides the index. The mysql docs don't agree with this
behavior for version 3.x and up.

I also couldn't replicate this behavior in one of our tables on a 4.x
server with ~2 million rows. EXPLAIN indicated the same (a,b) index
would be used regardless of the order of the fields in the where clause.
Query times were equally fast as well.

--
Roberto Mansfield
Institutional Research and Application Development (IRAD)
SAS Computing

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php