where is the index physically located in MySQL database?

RTree as index structure in mysql?

• Hi all, Need help on deciding Index for my database table. so here are the details. I have a table Source which have 10 million records and growing. Now the web application has search functionality. the user on web application can search for sources by name. this is a new feature which is getting implemented. Now i am using mysql 5.5. while reading there manual. I read they have implemented RTree as there data structure (BTrees are still there). SO i want to add index. Now my question is if i am adding an index name: 1. ALTER TABLE sources ADD INDEX NAMES USING BTREE (NAMES(5)); 2. ALTER TABLE sources ADD INDEX NAMES USING RTREE (NAMES(5)); What will my best bet using BTREE index or RTREE index.

• Answer:

  The purpose of an R-tree index is primarily for indexing multi-dimensional data like points and regions. I don't think an R-tree index is applicable in the usage you show, of indexing a "name" column, which I assume is just a string. mysql> ALTER TABLE sources ADD INDEX NAMES USING RTREE (NAMES(5)); ERROR 1210 (HY000): Incorrect arguments to RTREE INDEX MySQL supports R-Tree indexes only for SPATIAL indexes on geometric data types, and only in the MyISAM storage engine.  For more information see https://dev.mysql.com/doc/refman/5.5/en/optimizing-spatial-analysis.html in the manual. Also see http://dev.mysql.com/doc/refman/5.5/en/create-index.html, which says: The parser recognizes RTREE as a type name, but currently this cannot be specified for any storage engine.