adding FULLTEXT index : didn't make much difference , is it going to index older data?

Question

i have a query which is using a like condition and it's killing my server (this query is what i get frequently in the slow-query-log ) btw my table has about 120k rows - pleas ignor the syntax errors

select * from `images` where `category` like `%,3,%` and ( `tags` like '%,tag1,%' or `tags` like '%,tag2,%' or `tags` like '%,tag3,%' or `tags` like '%,tag4,%')

i don't want to change the query and database design fro now , so i've decided to switch to myisam and use fulltext index for tags column .

afterward server load hasn't change that much , mysql still using up to 90% of cpu (1 out of 8 of curse) from time to time .

so i was wondring , this fulltext indexing ... is it going to index the older data ( before adding this index ) ? cuz it happend very fast and my table is kinda big .

or it's only going to work on the newly stored data ?

Answer 1

Existing data was indexed, but as advised by Pyrce, a query with LIKE %[token]% is unable to leverage a fulltext index.

Rewrite your condition like this, this strictly equivalent to your inital query (ignoring stopwords and the likes):

WHERE MATCH(tags) AGAINST ('tag1 tag2 tag3 tag4' IN BOOLEAN MODE)

However you should rather focus on normalizing your structure. Storing non-scalar values (such as coma-separated values) in a field violates the very first normal form.

Create a new image_tag table and establish a 1-N relationship with images. A regular index will allow instant querying.

CREATE TABLE image_tags (
    image_id INT,
    tag VARCHAR(50),
    PRIMARY KEY (image_id, tag),
    FOREIGN KEY (image_id) REFERENCES images(id), -- replace with "images" table's actual primary key
    KEY(tag, image_id) -- this index may be superfluous, check EXPLAIN
);

SELECT images.*
FROM images
JOIN image_tags ON image_id = images.id
WHERE tag IN ('tag1', 'tag2', 'tag3', 'tag4');

Repeat with images.category.