On Thu, Oct 18, 2012 at 1:25 PM, Sumedha Rubasinghe <[email protected]>wrote:
> 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. > Yes Hash indexes are used only for equality operators only, so I guess its not suitable for our type of queries anyway ... > > 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'. > +1 and thanks for clarifying! :) > > [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 > > -- Thanks, Shariq. Phone: +94 777 202 225
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
