Hi all,

Below is the user_delivery table structure.

CREATE TABLE `user_delivery` (
`user_id` decimal(22,0) NOT NULL default '0',
`delivery_id` decimal(22,0) NOT NULL default '0',
`send_to_regulator` char(1) default NULL,
PRIMARY KEY (`user_id`),
KEY `user_delivery_comp1` (`user_id`,`delivery_id`),
CONSTRAINT `fk_user_delivery` FOREIGN KEY (`user_id`) REFERENCES
`user_info` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

According to me user_delivery_comp1 index can be dropped and new index can
be created on delivery_id column. I would to know that the changes will work
or not. Yours suggestion regarding this table structure.

Thanks,
--
Krishna Chandra Prajapati

On Tue, May 13, 2008 at 1:30 AM, Krishna Chandra Prajapati
<prajapatikc@gmail.com> wrote:
> Hi all,
>
> Below is the user_delivery table structure.
>
> CREATE TABLE `user_delivery` (
> `user_id` decimal(22,0) NOT NULL default '0',
> `delivery_id` decimal(22,0) NOT NULL default '0',
> `send_to_regulator` char(1) default NULL,
> PRIMARY KEY (`user_id`),
> KEY `user_delivery_comp1` (`user_id`,`delivery_id`),
> CONSTRAINT `fk_user_delivery` FOREIGN KEY (`user_id`) REFERENCES
> `user_info` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> According to me user_delivery_comp1 index can be dropped and new index can
> be created on delivery_id column. I would to know that the changes will work
> or not. Yours suggestion regarding this table structure.
>
> Thanks,
> --
> Krishna Chandra Prajapati
>

Define work.

The effect should be something like:
Queries that have where clauses for delivery_id but not user_id would
be able to use an index.
Queries that have where clauses for delivery_id and user_id might not
be able to use as much of an index. Depending on your version of mysql
merge index may apply, but I am not knowledge enough to comment of the
performance differences between the two. I would assume a composite
index when available would generally be more ideal.


--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)

Hi,

Since user_id is a primary key. It should work either with any of the column
and with both the column.

Any suggestion.

Thanks

On Thu, May 15, 2008 at 1:22 AM, Rob Wultsch <wultsch@gmail.com> wrote:

> On Tue, May 13, 2008 at 1:30 AM, Krishna Chandra Prajapati
> <prajapatikc@gmail.com> wrote:
> > Hi all,
> >
> > Below is the user_delivery table structure.
> >
> > CREATE TABLE `user_delivery` (
> > `user_id` decimal(22,0) NOT NULL default '0',
> > `delivery_id` decimal(22,0) NOT NULL default '0',
> > `send_to_regulator` char(1) default NULL,
> > PRIMARY KEY (`user_id`),
> > KEY `user_delivery_comp1` (`user_id`,`delivery_id`),
> > CONSTRAINT `fk_user_delivery` FOREIGN KEY (`user_id`) REFERENCES
> > `user_info` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
> > ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> >
> > According to me user_delivery_comp1 index can be dropped and new index
> can
> > be created on delivery_id column. I would to know that the changes will
> work
> > or not. Yours suggestion regarding this table structure.
> >
> > Thanks,
> > --
> > Krishna Chandra Prajapati
> >
>
> Define work.
>
> The effect should be something like:
> Queries that have where clauses for delivery_id but not user_id would
> be able to use an index.
> Queries that have where clauses for delivery_id and user_id might not
> be able to use as much of an index. Depending on your version of mysql
> merge index may apply, but I am not knowledge enough to comment of the
> performance differences between the two. I would assume a composite
> index when available would generally be more ideal.
>
>
> --
> Rob Wultsch
> wultsch@gmail.com
> wultsch (aim)
>



--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 500003
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: prajapatikc@gmail.com

If both (user_id,delivery_id) can be made primary key, then the second index
would not be required. But the performance of the query would be better if
it reads one index rather than indexes on each COLUMN in the "WHERE CLAUSE"

On 5/15/08, Krishna Chandra Prajapati <prajapatikc@gmail.com> wrote:
>
> Hi,
>
> Since user_id is a primary key. It should work either with any of the
> column
> and with both the column.
>
> Any suggestion.
>
> Thanks
>
> On Thu, May 15, 2008 at 1:22 AM, Rob Wultsch <wultsch@gmail.com> wrote:
>
> > On Tue, May 13, 2008 at 1:30 AM, Krishna Chandra Prajapati
> > <prajapatikc@gmail.com> wrote:
> > > Hi all,
> > >
> > > Below is the user_delivery table structure.
> > >
> > > CREATE TABLE `user_delivery` (
> > > `user_id` decimal(22,0) NOT NULL default '0',
> > > `delivery_id` decimal(22,0) NOT NULL default '0',
> > > `send_to_regulator` char(1) default NULL,
> > > PRIMARY KEY (`user_id`),
> > > KEY `user_delivery_comp1` (`user_id`,`delivery_id`),
> > > CONSTRAINT `fk_user_delivery` FOREIGN KEY (`user_id`) REFERENCES
> > > `user_info` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
> > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> > >
> > > According to me user_delivery_comp1 index can be dropped and new index
> > can
> > > be created on delivery_id column. I would to know that the changes will
> > work
> > > or not. Yours suggestion regarding this table structure.
> > >
> > > Thanks,
> > > --
> > > Krishna Chandra Prajapati
> > >
> >
> > Define work.
> >
> > The effect should be something like:
> > Queries that have where clauses for delivery_id but not user_id would
> > be able to use an index.
> > Queries that have where clauses for delivery_id and user_id might not
> > be able to use as much of an index. Depending on your version of mysql
> > merge index may apply, but I am not knowledge enough to comment of the
> > performance differences between the two. I would assume a composite
> > index when available would generally be more ideal.
> >
> >
> > --
> > Rob Wultsch
> > wultsch@gmail.com
> > wultsch (aim)
> >
>
>
>
> --
> Krishna Chandra Prajapati
> MySQL DBA,
> Ed Ventures e-Learning Pvt.Ltd.
> 1-8-303/48/15, Sindhi Colony
> P.G.Road, Secunderabad.
> Pin Code: 500003
> Office Number: 040-66489771
> Mob: 9912924044
> URL: ed-ventures-online.com
> Email-id: prajapatikc@gmail.com
>

Hi,

I would like to know which is faster out of the below.

Primary Key
Unique Key
Indexing
Give the numbering 1, 2 and 3

Thanks a lot

On Thu, May 15, 2008 at 5:49 PM, Ananda Kumar <anandkl@gmail.com> wrote:

> If both (user_id,delivery_id) can be made primary key, then the second
> index would not be required. But the performance of the query would be
> better if it reads one index rather than indexes on each COLUMN in the
> "WHERE CLAUSE"
>
>
> On 5/15/08, Krishna Chandra Prajapati <prajapatikc@gmail.com> wrote:
>>
>> Hi,
>>
>> Since user_id is a primary key. It should work either with any of the
>> column
>> and with both the column.
>>
>> Any suggestion.
>>
>> Thanks
>>
>> On Thu, May 15, 2008 at 1:22 AM, Rob Wultsch <wultsch@gmail.com> wrote:
>>
>> > On Tue, May 13, 2008 at 1:30 AM, Krishna Chandra Prajapati
>> > <prajapatikc@gmail.com> wrote:
>> > > Hi all,
>> > >
>> > > Below is the user_delivery table structure.
>> > >
>> > > CREATE TABLE `user_delivery` (
>> > > `user_id` decimal(22,0) NOT NULL default '0',
>> > > `delivery_id` decimal(22,0) NOT NULL default '0',
>> > > `send_to_regulator` char(1) default NULL,
>> > > PRIMARY KEY (`user_id`),
>> > > KEY `user_delivery_comp1` (`user_id`,`delivery_id`),
>> > > CONSTRAINT `fk_user_delivery` FOREIGN KEY (`user_id`) REFERENCES
>> > > `user_info` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
>> > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>> > >
>> > > According to me user_delivery_comp1 index can be dropped and new index
>> > can
>> > > be created on delivery_id column. I would to know that the changes
>> will
>> > work
>> > > or not. Yours suggestion regarding this table structure.
>> > >
>> > > Thanks,
>> > > --
>> > > Krishna Chandra Prajapati
>> > >
>> >
>> > Define work.
>> >
>> > The effect should be something like:
>> > Queries that have where clauses for delivery_id but not user_id would
>> > be able to use an index.
>> > Queries that have where clauses for delivery_id and user_id might not
>> > be able to use as much of an index. Depending on your version of mysql
>> > merge index may apply, but I am not knowledge enough to comment of the
>> > performance differences between the two. I would assume a composite
>> > index when available would generally be more ideal.
>> >
>> >
>> > --
>> > Rob Wultsch
>> > wultsch@gmail.com
>> > wultsch (aim)
>> >
>>
>>
>>
>> --
>> Krishna Chandra Prajapati
>> MySQL DBA,
>> Ed Ventures e-Learning Pvt.Ltd.
>> 1-8-303/48/15, Sindhi Colony
>> P.G.Road, Secunderabad.
>> Pin Code: 500003
>> Office Number: 040-66489771
>> Mob: 9912924044
>> URL: ed-ventures-online.com
>> Email-id: prajapatikc@gmail.com
>>
>
>


--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 500003
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: prajapatikc@gmail.com

it goes without saying

1. primary key and unique key would do unique scan which is fastest of all
scan
2 Index would do range scan, which would be comparitivly slower.

regards
anandkl


On 5/16/08, Krishna Chandra Prajapati <prajapatikc@gmail.com> wrote:
>
> Hi,
>
> I would like to know which is faster out of the below.
>
> Primary Key
> Unique Key
> Indexing
> Give the numbering 1, 2 and 3
>
> Thanks a lot
>
> On Thu, May 15, 2008 at 5:49 PM, Ananda Kumar <anandkl@gmail.com> wrote:
>
>> If both (user_id,delivery_id) can be made primary key, then the second
>> index would not be required. But the performance of the query would be
>> better if it reads one index rather than indexes on each COLUMN in the
>> "WHERE CLAUSE"
>>
>>
>> On 5/15/08, Krishna Chandra Prajapati <prajapatikc@gmail.com> wrote:
>>>
>>> Hi,
>>>
>>> Since user_id is a primary key. It should work either with any of the
>>> column
>>> and with both the column.
>>>
>>> Any suggestion.
>>>
>>> Thanks
>>>
>>> On Thu, May 15, 2008 at 1:22 AM, Rob Wultsch <wultsch@gmail.com> wrote:
>>>
>>> > On Tue, May 13, 2008 at 1:30 AM, Krishna Chandra Prajapati
>>> > <prajapatikc@gmail.com> wrote:
>>> > > Hi all,
>>> > >
>>> > > Below is the user_delivery table structure.
>>> > >
>>> > > CREATE TABLE `user_delivery` (
>>> > > `user_id` decimal(22,0) NOT NULL default '0',
>>> > > `delivery_id` decimal(22,0) NOT NULL default '0',
>>> > > `send_to_regulator` char(1) default NULL,
>>> > > PRIMARY KEY (`user_id`),
>>> > > KEY `user_delivery_comp1` (`user_id`,`delivery_id`),
>>> > > CONSTRAINT `fk_user_delivery` FOREIGN KEY (`user_id`) REFERENCES
>>> > > `user_info` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
>>> > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>>> > >
>>> > > According to me user_delivery_comp1 index can be dropped and new
>>> index
>>> > can
>>> > > be created on delivery_id column. I would to know that the changes
>>> will
>>> > work
>>> > > or not. Yours suggestion regarding this table structure.
>>> > >
>>> > > Thanks,
>>> > > --
>>> > > Krishna Chandra Prajapati
>>> > >
>>> >
>>> > Define work.
>>> >
>>> > The effect should be something like:
>>> > Queries that have where clauses for delivery_id but not user_id would
>>> > be able to use an index.
>>> > Queries that have where clauses for delivery_id and user_id might not
>>> > be able to use as much of an index. Depending on your version of mysql
>>> > merge index may apply, but I am not knowledge enough to comment of the
>>> > performance differences between the two. I would assume a composite
>>> > index when available would generally be more ideal.
>>> >
>>> >
>>> > --
>>> > Rob Wultsch
>>> > wultsch@gmail.com
>>> > wultsch (aim)
>>> >
>>>
>>>
>>>
>>> --
>>> Krishna Chandra Prajapati
>>> MySQL DBA,
>>> Ed Ventures e-Learning Pvt.Ltd.
>>> 1-8-303/48/15, Sindhi Colony
>>> P.G.Road, Secunderabad.
>>> Pin Code: 500003
>>> Office Number: 040-66489771
>>> Mob: 9912924044
>>> URL: ed-ventures-online.com
>>> Email-id: prajapatikc@gmail.com
>>>
>>
>>
>>
>
>
>
> --
> Krishna Chandra Prajapati
> MySQL DBA,
> Ed Ventures e-Learning Pvt.Ltd.
> 1-8-303/48/15, Sindhi Colony
> P.G.Road, Secunderabad.
> Pin Code: 500003
> Office Number: 040-66489771
> Mob: 9912924044
> URL: ed-ventures-online.com
> Email-id: prajapatikc@gmail.com
>

At 2:49a -0400 on Fri, 16 May 2008, Ananda Kumar wrote:
> it goes without saying

Eh, not to those who don't know. Hence the list and question. :-D

Krishna, the short answer is that it depends on your data, and the
queries against it that you run. Test/benchmark on your own DB and data
to see what solves your issue.

> 1. primary key and unique key would do unique scan which is
> fastest of all scan

Sort of.

First, let's get terminology correct: keys, indexes.

Terminology: keys are nothing more than data. Keys are used to identify
rows for manipulation, either directly by a SQL statement/request, or
indirectly through the DB SQL parser. A key is not necessarily unique.
For example

SELECT name FROM person WHERE height = 70;

is a request for the names of people who are 5'10" tall. The
identifying key in this statement then, is height. All people who are
70 inches tall are "keyed" for selection.

A special case of a key is a primary key. This tells the DB that the
data in this column(s) uniquely identifies each row. Assuming that 'id'
was created as a PRIMARY KEY,

SELECT name FROM person WHERE id = 7;

will return exactly 1 or 0 rows because id is guaranteed to be unique by
the DB. Further, a primary key is also implicitly guaranteed to be not
null.

A unique key is a bit of a misnomer, and I'm sorry the MySQL
documentation perpetuates it. It is more accurate to think of the
column as being constrained such that every row in that column(s) is
unique. Thus, unique is a /property/ of a column, not it's defining
characteristic.

Terminology: indexes are an /implementation/ of the DB, usually for
speed and to help maintain data integrity. When you designate a column
(or columns) as the primary key for the rows of a table, MySQL will
automatically create an unique index on that column. This way, for
example, when you insert data into the table, it won't have to scan the
entire table to make sure that the new data is unique. It can just walk
a couple of levels in the index to ensure uniqueness.

For speed however, it depends on what the DB is asked, and how the index
was defined. MySQL is somewhat limited in that you are pretty much
limited to the index type the DB has: b-tree index. But that's
implementation specific. Oracle, for example, has other index types,
(hash, bitmap). Modulo the internal specifics of the MySQL
implementation, the rough idea of a unique not null b-tree index (e.g. a
primary key column) is that the DB ostensibly need only walk O(lg(n))
steps to find a key. A hash index, then, would presumably be the
fastest at O(1).

> 2 Index would do range scan, which would be comparitivly slower.

Not exactly. This, again, depends on implementation and the data
against which queries are run. An index is an index. Remember, a
primary key is implemented by the MySQL engine as a unique not null
b-tree index. In fact, trying to get low-cardinality data from a table
via indexes could even be faster than the primary key. Consider a table
with 1,000,001 rows of favorite colors of people. Let's say 1 million
people like the color blue, but only 1 person who likes the color puce.
An index for that single person would be a huge win. Depending on the
DB (read: implementation) one could make this particular style of skewed
data even faster. Postgres, for example, has partial-indexes.

As I said at the beginning of this email, I suggest the OP do some
personal investigation and tests, because understanding indexes /and
your own data/ is not as simple as a checklist.

Kevin

P.S. I highly suggest you Google some of the concepts about which I
wrote. Wikipedia is fairly good start. Also, don't be afraid to read
other DB's docs. I've learned a great deal from comparing different
DBs, documentation sources, and interacting with multiple communities.

Hi Kevin,

> 2 Index would do range scan, which would be comparitivly slower.

Not exactly. This, again, depends on implementation and the data
against which queries are run. An index is an index. Remember, a
primary key is implemented by the MySQL engine as a unique not null
b-tree index. In fact, trying to get low-cardinality data from a table
via indexes could even be faster than the primary key. Consider a table
with 1,000,001 rows of favorite colors of people. Let's say 1 million
people like the color blue, but only 1 person who likes the color puce.
An index for that single person would be a huge win. Depending on the
DB (read: implementation) one could make this particular style of skewed
data even faster. Postgres, for example, has partial-indexes.

As stated by you as above. Here puce is a clear case of just one record
being read, which is very much similar to reading a record with a PRIMARY
KEY, which is supposed to be fast for HIGH CARDINALITY, if there were 1/4
million people who liked puce, then the index would a RANGE SCAN and would
be slow.


On 5/16/08, Kevin Hunter <hunteke@earlham.edu> wrote:
>
> At 2:49a -0400 on Fri, 16 May 2008, Ananda Kumar wrote:
> > it goes without saying
>
> Eh, not to those who don't know. Hence the list and question. :-D
>
> Krishna, the short answer is that it depends on your data, and the
> queries against it that you run. Test/benchmark on your own DB and data
> to see what solves your issue.
>
> > 1. primary key and unique key would do unique scan which is
> > fastest of all scan
>
> Sort of.
>
> First, let's get terminology correct: keys, indexes.
>
> Terminology: keys are nothing more than data. Keys are used to identify
> rows for manipulation, either directly by a SQL statement/request, or
> indirectly through the DB SQL parser. A key is not necessarily unique.
> For example
>
> SELECT name FROM person WHERE height = 70;
>
> is a request for the names of people who are 5'10" tall. The
> identifying key in this statement then, is height. All people who are
> 70 inches tall are "keyed" for selection.
>
> A special case of a key is a primary key. This tells the DB that the
> data in this column(s) uniquely identifies each row. Assuming that 'id'
> was created as a PRIMARY KEY,
>
> SELECT name FROM person WHERE id = 7;
>
> will return exactly 1 or 0 rows because id is guaranteed to be unique by
> the DB. Further, a primary key is also implicitly guaranteed to be not
> null.
>
> A unique key is a bit of a misnomer, and I'm sorry the MySQL
> documentation perpetuates it. It is more accurate to think of the
> column as being constrained such that every row in that column(s) is
> unique. Thus, unique is a /property/ of a column, not it's defining
> characteristic.
>
> Terminology: indexes are an /implementation/ of the DB, usually for
> speed and to help maintain data integrity. When you designate a column
> (or columns) as the primary key for the rows of a table, MySQL will
> automatically create an unique index on that column. This way, for
> example, when you insert data into the table, it won't have to scan the
> entire table to make sure that the new data is unique. It can just walk
> a couple of levels in the index to ensure uniqueness.
>
> For speed however, it depends on what the DB is asked, and how the index
> was defined. MySQL is somewhat limited in that you are pretty much
> limited to the index type the DB has: b-tree index. But that's
> implementation specific. Oracle, for example, has other index types,
> (hash, bitmap). Modulo the internal specifics of the MySQL
> implementation, the rough idea of a unique not null b-tree index (e.g. a
> primary key column) is that the DB ostensibly need only walk O(lg(n))
> steps to find a key. A hash index, then, would presumably be the
> fastest at O(1).
>
> > 2 Index would do range scan, which would be comparitivly slower.
>
> Not exactly. This, again, depends on implementation and the data
> against which queries are run. An index is an index. Remember, a
> primary key is implemented by the MySQL engine as a unique not null
> b-tree index. In fact, trying to get low-cardinality data from a table
> via indexes could even be faster than the primary key. Consider a table
> with 1,000,001 rows of favorite colors of people. Let's say 1 million
> people like the color blue, but only 1 person who likes the color puce.
> An index for that single person would be a huge win. Depending on the
> DB (read: implementation) one could make this particular style of skewed
> data even faster. Postgres, for example, has partial-indexes.
>
> As I said at the beginning of this email, I suggest the OP do some
> personal investigation and tests, because understanding indexes /and
> your own data/ is not as simple as a checklist.
>
> Kevin
>
> P.S. I highly suggest you Google some of the concepts about which I
> wrote. Wikipedia is fairly good start. Also, don't be afraid to read
> other DB's docs. I've learned a great deal from comparing different
> DBs, documentation sources, and interacting with multiple communities.
>

At 5:32a -0400 on Fri, 16 May 2008, Ananda Kumar wrote:
>>> 2 Index would do range scan, which would be comparitivly slower.
>>
>> Not exactly. This, again, depends on implementation and the
>> data against which queries are run. An index is an index.
>> Remember, a primary key is implemented by the MySQL engine as
>> a unique not null b-tree index. In fact, trying to get
>> low-cardinality data from a table via indexes could even be
>> faster than the primary key. Consider a table with 1,000,001
>> rows of favorite colors of people. Let's say 1 million
>> people like the color blue, but only 1 person who likes the
>> color puce. An index for that single person would be a huge
>> win. Depending on the DB (read: implementation) one could make
>> this particular style of skewed data even faster. Postgres,
>> for example, has partial-indexes.
>
> As stated by you as above. Here puce is a clear case of just one
> record being read, which is very much similar to reading a record
> with a PRIMARY KEY, which is supposed to be fast for HIGH
> CARDINALITY, if there were 1/4 million people who liked puce, then
> the index would a RANGE SCAN and would be slow.

Eh, again, it's hard to say. In my contrived example, puce is a clear
case of an index win via the fact that it's unique, but it's *not* like
the primary key because every other key isn't unique. A primary key
enforces high-cardinality, so selecting individual rows is ostensibly
fast, but now lets select a range of rows. What if we made ten people
like the color red and SELECTed them? How about 2,000 green? 100,000
yellow? An index perusal would still weed out >= 900,000 rows, so is it
a win? Try it an find out. I don't know the answer.

Getting data from the index isn't free. It /can/ be a win when it helps
remove a statistically strong portion of the data, dependent on disk
speed, cpu speed, and a data cardinality. Sometimes, depending on the
what rows and data it takes to satisfy a query, it's actually better to
ignore the indexes and do a range scan of the table.

The point is that it isn't as simple as snap-your-fingers,-you're-done,
which I gather we both understand. I suggest again to the OP to do some
tests on /personal data sets/ and see what answers needs.

Besides, who likes puce anyway? ;-)

Kevin