How can I make the MySQL table searching executed faster?

36 Views Asked by At

The table contains approximately 42.000.000 rows.

The structure is:

+----+------+-------+
| id | name | descr |
+----+------+-------+
|  1 | blah | blahd |
+----+------+-------+

ID is INT(11) PRIMARY name is VARCHAR(420) INDEX descr is MEDIUMTEXT - FULLTEXT

The search query is:

SELECT id,name,descr FROM infotbl WHERE name LIKE '%la%' LIMIT 20

And the query takes more than 60seconds.

I read various posts and questions but the only I found was KEYS, INDEXES and the use of MATCH () AGAINST () instead of LIKE %%.

MATCH - AGAINST didn't result as desired. Although KEYS and INDEXES make the search much faster it is still extremely slow.

I thought to split the table to smaller tables infotbl_1, infotbl_2 etc, or make partitions but these may result slower results.

I also changed the table TYPE/ENGINE from InnoDB to MyISAM and it made it about 20-30 times faster but it is still extremely slow.

0

There are 0 best solutions below