LeSpotted44 LeSpotted44 - 2 months ago 11
MySQL Question

How to quickly search through on a very large database

I try through CodeIngiter make queries as quickly as possible on a table that contains approximately 1.200.000 of records ...

I have created 3 indexes on name , tags and categories

*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for videos
-- ----------------------------
DROP TABLE IF EXISTS `videos`;
CREATE TABLE `videos` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`name_url` varchar(255) DEFAULT NULL,
`categories` varchar(255) DEFAULT NULL,
`embed` tinytext,
`description` text,
`tags` varchar(255) DEFAULT NULL,
`hd` smallint(6) DEFAULT '0',
`views` int(11) NOT NULL DEFAULT '0',
`likes` int(11) DEFAULT '0',
`dislikes` int(11) DEFAULT '0',
`cover` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`,`name`),
FULLTEXT KEY `mega_search` (`name`,`tags`,`categories`)
) ENGINE=MyISAM AUTO_INCREMENT=1273355 DEFAULT CHARSET=latin1;


But the response is too long =>
Total Execution Time 7.0999 for this query

SELECT *
FROM `videos`
WHERE `categories` LIKE '%Reality%' ESCAPE '!'
ORDER BY `id` DESC
LIMIT 20

Answer

As the comments mention, your predicate LIKE '%Reality%' cannot be optimized with a conventional index. You need to use a full-text search solution.

I wrote a presentation about this:

Comments