2

I have a table 

CREATE TABLE `dataFullText` (
`id` int(11) NOT NULL,
`title` char(255) NOT NULL,
`description` text NOT NULL,
`name` char(100) NOT NULL,
`ref` char(50) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `fulltext` (`ref`,`name`,`title`,`description`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Which has around 100k records.

mysql> select * from information_schema.TABLES WHERE TABLE_NAME='jobsFullText'\G
*************************** 1. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: ****
TABLE_NAME: dataFullText
TABLE_TYPE: BASE TABLE
ENGINE: MyISAM
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 79495
AVG_ROW_LENGTH: 791
DATA_LENGTH: 62938804
MAX_DATA_LENGTH: 281474976710655
INDEX_LENGTH: 53625856
DATA_FREE: 51328
AUTO_INCREMENT: NULL
CREATE_TIME: 2011-10-03 13:38:25
UPDATE_TIME: 2011-10-03 13:55:56
CHECK_TIME: 2011-10-03 13:38:48
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS: 
TABLE_COMMENT:

This table is updated every hour with a LOAD DATA INFILE, which has around 8k records.

The time the table is locked is around 30 seconds. Which correspond to the time I make a 

mysql> alter table dataFullText drop index title;
Query OK, 79495 rows affected (1.33 sec)
Records: 79495  Duplicates: 0  Warnings: 0

mysql> alter table dataFullText add fulltext index (ref,name,title,description);
Query OK, 79495 rows affected (22.96 sec)
Records: 79495  Duplicates: 0  Warnings: 0

My problem is that 30seconds is really a long time. This table is queried 5 times/seconds, which make the queue reach 30 x 5 = 150 . Because our max connection limit is set to 100, the mysql server begin to reject some incoming connections.

We plan to have at least 1 Million row in this table in the future, and I guess this won't get faster.

Is there anything I can do to reduce the time mysql uses for updating the index ?

Ant
  • 1,812
  • 5
  • 22
  • 39
  • I finallly decided to use Sphinx for all the search functionalities. I find it much more faster than MySQL. I am using MySQL for storing and organizing my data, and Sphinx to access them. MySQL =>write, Sphinx =>READ . – Ant Jul 16 '12 at 08:08

1 Answers1

0

In a general SQL DBMS, fully indexing a table like this doesn't help you out. The index being in fact larger than the table itself, the time it will take to access it will be even bigger than the time to access the table without the index.

Now, this really depends on the particular installation you have: the amount of RAM, the overall speed of the system. Before to add indexes and, yes, updating/recreating indexes is slow, just be sure it's worth it, in the particular conditions you are in.

BxlSofty
  • 501
  • 4
  • 16