Forum Moderators: open
I have a table of just over 250,000 rows (in six months it will be 300K+, two years it will be a million+). Each row has a longtext field of search terms (description, keywords, etc.) and a full text index. For some reason, I thought it would make sense to slice the longtext field to a separate table so that reads from that table will not lock the main table.
Searching is done with an AJAX widget so that table will get accessed a lot -- more than one would typically expect it to be accessed. For that reason should it be split? Or am I just adding unnecessary complexity?
CREATE TABLE `object` (
`object_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`object_name` VARCHAR( 128 ) NOT NULL ,
`object_w` INT NOT NULL ,
`object_h` INT NOT NULL ,
`object_added` TIMESTAMP NOT NULL ,
...
)
Should I create a second table that has a foreign key to the `object` table and solely holds the full text search data? Or should the `object_keywords` and it's corresponding full text index just be part of the main table?
CREATE TABLE `object_search` (
`object_id` BIGINT UNSIGNED NOT NULL ,
`object_keywords` LONGTEXT NOT NULL ,
PRIMARY KEY ( `object_id` ) ,
FULLTEXT (
`object_keywords`
)
)
I realize this probably makes no sense from a normalization point of view, but I'm thinking more from an optimization point of view. I also realize this is premature optimization. ;)
