InnoDB Indices
I have some questions regarding InnoDB indices. 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. Do queries benefit from an index with this low of a selectivity? 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? If I make it a compound index, does it have any positive impact on INSERT, UPDATE, or maybe just DELETE? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Indices
Robert DiFalco [EMAIL PROTECTED] wrote on 03/09/2006 12:32:44 PM: I have some questions regarding InnoDB indices. 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. Do queries benefit from an index with this low of a selectivity? 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? If I make it a compound index, does it have any positive impact on INSERT, UPDATE, or maybe just DELETE? R. Hi Robert, I assume you have already read through the entire optimization section in the manual: http://dev.mysql.com/doc/refman/4.1/en/optimization.html -or- http://dev.mysql.com/doc/refman/5.0/en/optimization.html (as appropriate) as this entire chapter deals with the finer details of the questions you are asking. So I will try to just answer you in the general sense so that perhaps you can make better sense of what you already read. One of the most frequent bottlenecks to query performance is related to physically retrieving data from the hard disks. Indexes, though very useful, actually slow down performance if you need to pull more than about 1/3 of any table's rows off of the disks. Columns of data not already included in the indexes themselves must be retrieved from the disk before their values can be used as part of a result or a part of a comparison or as part of a formula. What happens to the performance is that it begins to take longer (mostly due to the random access disk seek operations) to pick lots of individual records (based on an index hit) that it would have taken if you had just found the beginning of the table data and streamed the whole table through memory in one big burst of data. Now, because the indexes to a table are loaded into memory before query evaluation the optimizer can estimate how many rows of a table it would find if it used one index over another (and starting with 5.0 how many it would find based on certain combinations of indexes). If all of the data you need from a table is actually part of an index then the entire read data from disk portion of the query can also be skipped (under the right conditions). So having what is known as a covering index (a multi-column index where some of the right-most columns are listed mostly to avoid actual table reads) can seriously improve the performance of certain queries while providing a normal boost to the performance of a bunch of others. How you execute your queries should not matter. By the time the database server sees it, one query looks just like every other. So I don't see how running a query in the Query Browser would be any faster than if you had issued the same query from the CLI or via any of the other connection APIs. Declaring compound indexes make sense if your query patterns frequently use those columns or if you are trying to create a covering index. Improving the cardinality of any index can only help its performance. The up-side to indexes (keys) is that they improve the chances of quickly finding the data you are looking for thereby improving your overall query performance. The down-side is that the addition of every new record has to create the appropriate additions to every index on the table. The same goes for UPDATES and DELETES. You have to benchmark your performance in your environment to figure out when enough indexes becomes too many for your comfort. Each new index also takes up space in memory and room on the disk so too many indexes can starve your system for resources, too. Like I said above, these are just some general guidelines. The nitty gritty can be found in the manual. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
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. 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. Do queries benefit from an index with this low of a selectivity? ++ 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. 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? ++ If your primary key will be included in the where clause then definitely include it. If I make it a compound index, does it have any positive impact on INSERT, UPDATE, or maybe just DELETE? ++ I can't see it helping with insert, but depending on the where clause on your updates and deletes it could. Dave 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]
RE: InnoDB Indices
++ 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 -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. 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. Do queries benefit from an index with this low of a selectivity? ++ 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. 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? ++ If your primary key will be included in the where clause then definitely include it. If I make it a compound index, does it have any positive impact on INSERT, UPDATE, or maybe just DELETE? ++ I can't see it helping with insert, but depending on the where clause on your updates and deletes it could. Dave 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]
Re: InnoDB Indices
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]
