MySQL Full-Text Search Extremely Slow on a Large Copy of AWS RDS

Question

I have a table with 14 million rows, and I'm trying to do a full text search on this table. The query for this is very slow, it takes about 9 seconds for a simple binary AND query. The same stuff runs instantly in my private cluster. The size of this table is about 3.1 GB and contains 14 million rows. Can anyone explain this behavior of an RDS instance?

 SELECT count(*) FROM table_name WHERE id=97 AND match(body) against ('+data +big' IN BOOLEAN MODE) 

Answer 1

High I / O speeds often indicate insufficient memory or too small buffers. A 3GB table, including indexes, should fit entirely in the memory of a dedicated server (significantly less) $ 500 per month.

MySQL has many different buffers and many parameters that you can play with. The following buffers are the most important; compare their sizes in two environments:

If InnoDB: innodb_buffer_pool_size

If MyISAM: key_buffer_size and read_buffer_size

Answer 2

Did you FULLTEXT index in the body column, if not, then try this, of course, this will make a big difference.

 ALTER TABLE `table_name` ADD FULLTEXT INDEX `bodytext` (`body`); 

Hope this helps

Answer 3

try it

 SELECT count(1) FROM table_name WHERE id=97 AND match(body) against ('+data +big' IN BOOLEAN MODE) 

This should speed it up, since you do not need to consider all columns as just rows.

Can you post an explanation?

Answer 4

Since the DB version, table, indexes, and execution plans are the same, you need to compare the configurations of machines and clusters. Comparison highlights Accessibility of processor power, cores used in a single transaction, read speed, memory size and read speed / frequency. I see that Amazon provides a lot of configurations, so perhaps your private cluster is much more powerful than the Amazon RDS instance configuration.

To add above, you can balance the load between CPU, IO and Memory to increase throughput.

Answer 5

Using match () versus (), you do your research on the entire 3GB full-text index, in which case there is no way to force another index.

To speed up your query, you need to make the full-text index easier so you can:

1 - clear all useless characters and stop words from your full-text index

2 - create some full-text indexes and look at the corresponding

3 - Change the full-text search to a LIKE clause and force another index, such as "id".

Answer 6

Try putting id in a text index and say:

 match(BODY,ID) against (+big +data +97) and id=97 

You can also look at sphinx, which can be easily used with MySQL.