CODEWITHSUNDEEP
mysqlsql
user3831011
user3831011

Reputation: 319

optimizing sql search time

I have a question regarding how to optimize my search time through a mySQL database. The entire database is stored in 1 table laid out with these columns: video_link, image_link, description, length, and site. As of right now I have 1,200,000 rows and will be expanding upwards of 10,000,000 when all is said and done. When I run a search query like: 

SELECT * 
FROM  `VIDEOS` 
WHERE  `DESCRIPTION` LIKE  '%search_string%'

it takes 1.3808 seconds to complete. That is not a terrible search time, but when the row count doubles it will be. I should mention that the database is being used on a website and the search bar runs a query on the database and returns the results. Most sites have way more stored and have a faster search time. Is there any way to improve the performance?

Upvotes: 1

Views: 57

Answers (2)

Xylarax
Xylarax

Reputation: 125

If you want to do the like query you have there you will want to have a full text index on description.

Also in general select * is pretty slow, so if you don't need every column you should trim that down.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425823

CREATE FULLTEXT INDEX
        fx_videos_description
ON      videos (description);

SELECT  *
FROM    videos
WHERE   MATCH(description, '+search_string' IN BOOLEAN MODE);

This only works on MyISAM tables prior to MySQL 5.6

Upvotes: 1

Related Questions