Alex Lamson Alex Lamson - 21 days ago 7
MySQL Question

Optimizing querying 78,000,000 row table (MySQL)

I have a table called tags_table with an id column of type

INT(11)
and a tag column of type
VARCHAR(200)
.

There are ~3,000,000 unique ids, and ~300,000 unique tags.
Because there are multiple tags per id, there are ~78,000,000 rows, which makes querying quite slow. An example of a query would be
SELECT id FROM tags_table WHERE tag = "flower"


Would my query execute faster if I made a table of unique tags and modified tags_table to contain the indices of those tag strings instead of the strings themselves? And if so, how would I do that? My goal here is minimize query duration.

Here's the create statement for the table:

CREATE TABLE `tags_table` (
`id` int(11) unsigned NOT NULL,
`tag` varchar(200) CHARACTER SET utf8 NOT NULL,
UNIQUE KEY `no_dup_tags` (`id`,`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer

Yes you can just make another table with unique tags and apply unique index on that column of tags and replace all tags ans add one more column in your current table to change tags text to id and then simple index that id column.

If you not want any changes just use fulltext index on tag column

ALTER TABLE table_name  
ADD FULLTEXT(column_name1) 

Note: MyISAM has FULLTEXT search indexes, InnoDB did not until MySQL 5.6 (Feb 2013).

Comments