Covering an entire table with an index?

#1

I've found myself in the curious situation where it makes sense for me
to cover an entire database table with an index, like so:

create table test(id integer primary key, first integer, second
integer, third integer);
create index idx on test(id, first, second, third);

Admittedly, I'm currently using sqlite rather than mysql but this is
just a stop-gap solution and I'm planning to migrate to mysql in the
future. However, I'm sure they will have similar issues...
The database with the covering index is more than twice the size as one
without it. The size, itself, is not necessarily a concern but I've
heard that this can hurt performance, even to the point of being more
efficient without the covering index. I was hoping that sqlite would be
clever enough to do without the original table and only use the index
but it appears not to be.
Would mysql handle this better?
Would this covering index be worth it?
Would be better to compromise and just cover half the table?
More generally, when are covering indeces most effective?
Thank you...

#2

In article <NH%Bq.76784$ql3.52770 (AT) en-nntp-12 (DOT) dc1.easynews.com>,
Just Another Victim of the Ambient Morality <spam_receptacle_ (AT) hotmail (DOT) com> wrote:

Quote:

I've found myself in the curious situation where it makes sense for me
to cover an entire database table with an index, like so:

Could you explain how you came to this conclusion? What do you see as
the purpose for having such an index? What problem does it solve?

Quote:

create table test(id integer primary key, first integer, second
integer, third integer);
create index idx on test(id, first, second, third);

Since id is unique, adding first, second and third doesn't provide
any further qualification; it just makes those values available
directly from the index instead. However, it also makes the index
entries larger, so potentially fewer of them could be cached.

Quote:

Admittedly, I'm currently using sqlite rather than mysql but this is
just a stop-gap solution and I'm planning to migrate to mysql in the
future. However, I'm sure they will have similar issues...
The database with the covering index is more than twice the size as one
without it.

It would be, since you are storing every data item twice: once in the
table row and again in the index. And the index will also have structure
information for efficient lookup (e.g. a tree).

Quote:

The size, itself, is not necessarily a concern but I've
heard that this can hurt performance, even to the point of being more
efficient without the covering index.

It depends on your use case - whether you have more reads or writes.
Writes are slower the more indexes you have and the more you store in
the index. An UPDATE on any column that is in the index will need both
the row and the index to be rewritten. An UPDATE on a column that is
not in an index only needs the row to be rewritten, not the index.

Reads will be faster, IF the index gets used to avoid scanning
the whole table for a condition.

Quote:

I was hoping that sqlite would be
clever enough to do without the original table and only use the index
but it appears not to be.
Would mysql handle this better?

Don't know, sorry. The online manual (dev.mysql.com) might have some
information about it.

Quote:

Would this covering index be worth it?

That depends why you might want it.

Quote:

Would be better to compromise and just cover half the table?

Ditto.

Quote:

More generally, when are covering indeces most effective?

They only help for queries that (a) can use the index to locate the required
data, AND (b) only want to fetch columns that are in the index, so that they
don't have to also fetch the row pointed to by the index entry.

Cheers
Tony
--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

#3

Just Another Victim of the Ambient Morality <spam_receptacle_ (AT) hotmail (DOT) com> wrote:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
get a name!

Quote:

create table test(id integer primary key, first integer, second
integer, third integer);
create index idx on test(id, first, second, third);

I too doubt that this makes much sense.

Quote:

The database with the covering index is more than twice the size as one
without it.

Sure, what else?

Quote:

Would mysql handle this better?

If this would be an InnoDB table, then you could get the same at a
lower cost by putting all columns into the PRIMARY KEY.

But you would lose the UNIQUE constraint on `id` that way. Adding
a secondary index for this would again double the table size.

Quote:

Would this covering index be worth it?
Would be better to compromise and just cover half the table?

Only you can answer this. We don't know the queries you intend to run

XL

#4

On 12/2/2011 3:08 AM, Just Another Victim of the Ambient Morality wrote:

Quote:

I've found myself in the curious situation where it makes sense for me
to cover an entire database table with an index, like so:

create table test(id integer primary key, first integer, second
integer, third integer);
create index idx on test(id, first, second, third);

That makes no sense under any circumstances.

Since id is the primary key, it is by definition unique -- and
therefore, so is the combination of (id, first, second, third).

So make that (id, first, second, third) the primary key instead of id
alone, and get rid of that silly index.

#5

On 2011-12-02 3:59 AM, Tony Mountifield wrote:

Quote:

In article<NH%Bq.76784$ql3.52770 (AT) en-nntp-12 (DOT) dc1.easynews.com>,
Just Another Victim of the Ambient Morality<spam_receptacle_ (AT) hotmail (DOT) com> wrote:
I've found myself in the curious situation where it makes sense for me
to cover an entire database table with an index, like so:

Could you explain how you came to this conclusion? What do you see as
the purpose for having such an index? What problem does it solve?

My actual problem is a little verbose to describe so, for now, I'll
give you an abstract...
I'm trying to implement a tree structure in a database...

Quote:

create table test(id integer primary key, first integer, second
integer, third integer);
create index idx on test(id, first, second, third);

id is the identifier of each tree node. first is the input that
represents a branch to another node. second is the identifier of the
next node, so it's a foreign key to test.id. third is some data
associated with each node...

Quote:

Since id is unique, adding first, second and third doesn't provide
any further qualification; it just makes those values available
directly from the index instead. However, it also makes the index
entries larger, so potentially fewer of them could be cached.

This is exactly the trade off I was expecting! The covering index is
so that another search doesn't have to occur to get those columns but at
the cost of having a larger table to search. I'm not sure which will be
faster...

Quote:

Admittedly, I'm currently using sqlite rather than mysql but this is
just a stop-gap solution and I'm planning to migrate to mysql in the
future. However, I'm sure they will have similar issues...
The database with the covering index is more than twice the size as one
without it.

It would be, since you are storing every data item twice: once in the
table row and again in the index. And the index will also have structure
information for efficient lookup (e.g. a tree).

As I said, I was hoping that sqlite (and mysql) would be clever enough
to discard the original table if there's a covering index that covers it
in its entirety...
There will surely be more reads than writes...

Quote:

They only help for queries that (a) can use the index to locate the required
data, AND (b) only want to fetch columns that are in the index, so that they
don't have to also fetch the row pointed to by the index entry.

These may be necessary conditions for covering indeces to help but I've
read that they're not sufficient as the increased size may end up
hindering performance overall...

#6

On 2011-12-02 3:37 PM, Doug Miller wrote:

Quote:

On 12/2/2011 3:08 AM, Just Another Victim of the Ambient Morality wrote:
I've found myself in the curious situation where it makes sense for me
to cover an entire database table with an index, like so:

create table test(id integer primary key, first integer, second
integer, third integer);
create index idx on test(id, first, second, third);

That makes no sense under any circumstances.

Since id is the primary key, it is by definition unique -- and
therefore, so is the combination of (id, first, second, third).

So make that (id, first, second, third) the primary key instead of id
alone, and get rid of that silly index.

Oh, you're right! Obviously, id can't be unique. It's the combination
(id, first) that's unique...
second will also be unique given (id, first)... as will third...
I don't know SQL well enough to understand how you would make a
_primary_ key of several columns. How does that work?
Thank you very much!

#7

On 12/2/2011 4:52 PM, Just Another Victim of the Ambient Morality wrote:

Quote:

I don't know SQL well enough to understand how you would make a
_primary_ key of several columns. How does that work?

It works the same way as creating a primary key with only one column.
Have you looked at the documentation yet?

#8

On 2011-12-02 5:26 PM, Doug Miller wrote:

Quote:

On 12/2/2011 4:52 PM, Just Another Victim of the Ambient Morality wrote:
I don't know SQL well enough to understand how you would make a
_primary_ key of several columns. How does that work?

It works the same way as creating a primary key with only one column.
Have you looked at the documentation yet?

I've looked at the documentation for sqlite and all I saw was the
declaration for "primary key" as part of a column definition, so it's
hard to imagine how to extend that to multiple columns.
I've seen the mysql documentation a while ago but I don't recall
primary key declaration...

#9

On 12/2/2011 3:37 PM, Doug Miller wrote:

Quote:

On 12/2/2011 3:08 AM, Just Another Victim of the Ambient Morality wrote:
I've found myself in the curious situation where it makes sense for me
to cover an entire database table with an index, like so:

create table test(id integer primary key, first integer, second
integer, third integer);
create index idx on test(id, first, second, third);

That makes no sense under any circumstances.

Since id is the primary key, it is by definition unique -- and
therefore, so is the combination of (id, first, second, third).

So make that (id, first, second, third) the primary key instead of id
alone, and get rid of that silly index.

Since id is already unique, all this will do is needlessly waste space,
buffer, etc. Terrible idea.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#10

On 12/2/2011 7:09 PM, Jerry Stuckle wrote:

Quote:

On 12/2/2011 3:37 PM, Doug Miller wrote:
On 12/2/2011 3:08 AM, Just Another Victim of the Ambient Morality wrote:
I've found myself in the curious situation where it makes sense for me
to cover an entire database table with an index, like so:

create table test(id integer primary key, first integer, second
integer, third integer);
create index idx on test(id, first, second, third);

That makes no sense under any circumstances.

Since id is the primary key, it is by definition unique -- and
therefore, so is the combination of (id, first, second, third).

So make that (id, first, second, third) the primary key instead of id
alone, and get rid of that silly index.

Since id is already unique, all this will do is needlessly waste space,
buffer, etc. Terrible idea.

He wants an index on all those columns, that's how he can get one.

I'm not going to waste time explaining why it's not a good idea to
someone who hasn't bothered to read the documentation far enough to find
out how to create a primary key that spans multiple columns.