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.