Robert,
actually, InnoDB always internally adds the PRIMARY KEY to every secondary
index record:
http://dev.mysql.com/doc/refman/5.0/en/innodb-table-and-index.html
If a column has just four different values, then in most cases an index on
that column does not help at all. And every index slows down inserts. That
is why you normally do not create an index on such a column.
But a query of the following type would get a speedup, assuming that the
index tree completely fits in the buffer pool (main memory):
SELECT COUNT(*) FROM t WHERE low_selectivity_column = 2;
The speedup would be 4X compared to a table scan.
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php
----- Original Message -----
From: ""Robert DiFalco"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Thursday, March 09, 2006 8:41 PM
Subject: RE: InnoDB Indices
=20
++ I can't see it helping with insert, but depending on the where
clause on your updates and deletes it could.
I guess I was thinking that if an index with otherwise low selectivity
added a rightmost column that was completely unique that it would
improve key distribution and therefore make deletes faster. But every
database engine handles this stuff differently.
R=20
-----Original Message-----
From: David Turner [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 09, 2006 10:13 AM
To: Robert DiFalco; mysql@lists.mysql.com
Subject: Re: InnoDB Indices
----- Original Message ----
From: Robert DiFalco <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Sent: Thursday, March 9, 2006 9:32:44 AM
Subject: InnoDB Indices
I have some questions regarding InnoDB indices.
=20
Say I have a table with millions of records. On of the fields is a type
field that has a possible value of 1,2,3, or 4. I sometimes query by the
type field and may at other times order on it.
=20
Do queries benefit from an index with this low of a selectivity?
=20
++ For the most part no. I come from Oracle where you can use
histograms to help. So, someone feel free to correct me if I'm wrong.
=20
If through the query browser I find that they do, should I increase the
selectivity of the index by making it a compound index and adding my
Primary Key to the TYPE index?
=20
++ If your primary key will be included in the where clause then
definitely include it.
=20
If I make it a compound index, does it have any positive impact on
INSERT, UPDATE, or maybe just DELETE?
=20
++ I can't see it helping with insert, but depending on the where
clause on your updates and deletes it could.
=20
Dave
=20
R.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: =
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
