On Thu, Oct 18, 2012 at 12:39 PM, Muhammed Shariq <[email protected]> wrote:
> Hi folks, > > While reading on some material on MySql and indexing I came across > $subject. In our DB scripts we use hash indexes with Innodb engine right ?! > According to mysql docs [1]; > > ============================================ > > Some storage engines permit you to specify an index type when creating an > index. The permissible index type values supported by different storage > engines are shown in the following table. Where multiple index types are > listed, the first one is the default when no index type specifier is given. > Storage Engine Permissible Index Types > InnoDB<http://dev.mysql.com/doc/refman/5.6/en/innodb-storage-engine.html> > BTREE > MyISAM<http://dev.mysql.com/doc/refman/5.6/en/myisam-storage-engine.html> > BTREE > MEMORY<http://dev.mysql.com/doc/refman/5.6/en/memory-storage-engine.html> > /HEAP HASH, BTREE > NDB<http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster.html> > HASH, BTREE (see note in text) > > If you specify an index type that is not valid for a given storage engine, > but there is another index type available that the engine can use without > affecting query results, the engine uses the available type. The parser > recognizesRTREE as a type name, but currently this cannot be specified > for any storage engine. > ============================================ > > According to the second section, I wonder if mysql silently converts the > indexes to BTREE even though we specify it as Hash?! > Yes.. It does. And luckily "B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons...for additional reading[2] ". Most of our queries are limited to above. So we have't seen any problems. As of now we have following references[3] where HASH index type is used. HASH index type is intended to be used & highly performant with MEMORY[4] table types (i.e. *CREATE TABLE test ENGINE=MEMORY) *. But our tables are using InnoDB engine. So we have not seen any adverse performance as the fall back index type (i.e. BTREE) is friendly with type of queries we do. I cannot recall any specific reason for adding this & should be fixed to 'USING BTREE'. [2] http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html [3] ./mysql.sql:31:CREATE INDEX REG_PATH_IND_BY_PATH_VALUE USING HASH ON REG_PATH(REG_PATH_VALUE, REG_TENANT_ID); ./mysql.sql:32:CREATE INDEX REG_PATH_IND_BY_PATH_PARENT_ID USING HASH ON REG_PATH(REG_PATH_PARENT_ID, REG_TENANT_ID); ./mysql.sql:67:CREATE INDEX REG_RESOURCE_IND_BY_NAME USING HASH ON REG_RESOURCE(REG_NAME, REG_TENANT_ID); ./mysql.sql:68:CREATE INDEX REG_RESOURCE_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID); ./mysql.sql:89:CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_NAME USING HASH ON REG_RESOURCE_HISTORY(REG_NAME, REG_TENANT_ID); ./mysql.sql:90:CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID); ./mysql.sql:111:CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_COMMENT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); ./mysql.sql:112:CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_VERSION USING HASH ON REG_RESOURCE_COMMENT(REG_VERSION, REG_TENANT_ID); ./mysql.sql:133:CREATE INDEX REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_RATING(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); ./mysql.sql:134:CREATE INDEX REG_RESOURCE_RATING_IND_BY_VERSION USING HASH ON REG_RESOURCE_RATING(REG_VERSION, REG_TENANT_ID); ./mysql.sql:156:CREATE INDEX REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_TAG(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); ./mysql.sql:157:CREATE INDEX REG_RESOURCE_TAG_IND_BY_VERSION USING HASH ON REG_RESOURCE_TAG(REG_VERSION, REG_TENANT_ID); ./mysql.sql:177:CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_PROPERTY(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); ./mysql.sql:178:CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_VERSION USING HASH ON REG_RESOURCE_PROPERTY(REG_VERSION, REG_TENANT_ID); ./mysql.sql:216:CREATE INDEX REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_SNAPSHOT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID); [4]http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html I am totally new to database indexes so please correct me if I have > misunderstood the whole subject matter :) > > [1] - http://dev.mysql.com/doc/refman/5.6/en/create-index.html > > -- > Thanks, > Shariq. > Phone: +94 777 202 225 > > -- /sumedha m: +94 773017743 b : bit.ly/sumedha
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
