Indexing in MySQL

Question

I have a table like below

CREATE TABLE `key_word` (
 `primary_key` bigint(20) NOT NULL,
 `indexVal` int(11) NOT NULL,
 `hashed_word` char(3) NOT NULL,
 PRIMARY KEY (`primary_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Here is how I am going to create the index for this.

ALTER TABLE key_word ADD INDEX (hashed_word, indexVal)

This creates a BTREE INDEX. But I am willing to build a RTree index, because I am doing a full text search. How can I create this index in MySQL?

score 0 · Answer 1 · answered Apr 28 '14 at 06:03

0

In my understanding R Trees are for spatial indexing. The idea being that the R Tree allows you to find close values in a 2D plane quickly. Have a look at the Mysql Geospatial reference documentation for more info on this.

answered Apr 28 '14 at 06:03

Jase L

36
3

score 0 · Answer 2 · answered Apr 28 '14 at 06:09

0

For full text search you can create fulltext index as per below:

ALTER TABLE key_word ADD FULLTEXT(hashed_word, indexVal);

answered Apr 28 '14 at 06:09

Zafar Malik

6,734
2
19
30

score 0 · Answer 3 · answered Apr 28 '14 at 16:18

0

R-trees are not at all suitable for text. What made you think you need an Rtree index?

BTrees are good for sorted keys, but I figure you want to have substring search? Then use a FULLTEXT index maybe?

answered Apr 28 '14 at 16:18

Has QUIT--Anony-Mousse

76,138
12
138
194

Thanks for the reply. The column I am going to search for will contain only 1 word per row. That word also a hash like `w1@`. What do you recommend now? – PeakGen Apr 28 '14 at 16:35

Indexing in MySQL

3 Answers3