CODEWITHSUNDEEP
pythonmysqlsqldjangosearch
David542
David542

Reputation: 110572

Recommended way to do a basic search

I need to do a search for the title of a video. The search will only be searching in one column of one table, so it is a very basic search. Here is what I am currently using to do the search:

video_set = video_set.filter(title__icontains=search)

and using print connection.queries, it turns into this --

SELECT COUNT(*) FROM `userprofile_videoinfo` WHERE `userprofile_videoinfo`.`title` LIKE %search%

I am very new to search and SQL and it seems using '%like%' is the one thing that is always discouraged. I am searching through about 10,000 records and was wondering if this search would be adequate for such a small record size, or if I need to look to other options for search. And if 10,000 records is too small to make a difference, at what size/when do I need to look towards other options? What options could I look to for a higher performance basic text search? Thank you.

Upvotes: 0

Views: 124

Answers (3)

theoretical
theoretical

Reputation: 420

The reason people tell you to avoid doing like '%blah%' is because it's difficult to use an index when doing such a search. That means that if you don't have any other filters, it will have to do a full table scan and check each record to see if it matches your query instead of using an index. With 10,000 records, it's not going to be a significant performance hit. If you had 10x or 100x that many rows, then you'd start to notice slow downs. Full text search seems like overkill for 10,000 rows. There are other issues to consider though such as capitalization and sound-ex features that you may want to look into (as people often misspell words :) )

Upvotes: 2

fyr
fyr

Reputation: 20869

If full-text-search capabilities of mysql are not enough for your needs. Have a look at Apache Solr http://lucene.apache.org/solr/ or pylucene. They will give you much better results for the purpose of full-text search.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

Assuming MyISAM tables, look into setting up full-text search in your database and use the MATCH() function for your searches.

Upvotes: 1

Related Questions