SQLTip1: search database faster without like
SQL tip 2, SQL tip 3
well all of us need to deal with databases and sql query and most of the time we need to provide searching abilities to users.
well the easiest way is using like i.e to search articles containing the word “database” this query is goo enough “Select title from articles where body like ‘%database%’”.
but what if we have the articles table so huge and contains lots of rows and each row has a huge article !!!!, well simply the database work will be slowed down and will effect the work of other functions of the applications.
if this is the situation indexing the body column of the table and searching the index would be much faster (( well yeah i know this is a basic knowledge for database experts but most of the developers dont use it that why i am mentioning it))
so lets see how this is done in MYSQL:
to create the index you have to do this
ALTER TABLE articles ADD FULLTEXT(body);
then while searching the query will be something like
“select title , MATCH (body) AGAINST (‘database’) as rank from articles ORDER BY rank desc;”
this will return some thing like
|title | rank |
—————–
|sqltip | 1.3 |
| eclipse| 0 |
|.NET | 0 |
____________
where rank is 0 where it didnt found the searching keyword
and it is bigger where-ever it finds the searching keyword more (more relivent)
so to get only the good results the query will be
“select title from articles where MATCH (body) AGAINST (‘database’) > 0 ;”
Related posts:
4 comments
Leave a Reply
How about where you need to do partial word matching? Say the requirement is that if you word to be searched for is ‘base’. And that should match against the word ‘database’ in the articles. I believe this is NP-Complete.
Will full text indexing work in this situation do you know?
If it only would be this easy. To begin with, your tables must be of type MyIsam, which has other serious implications (table level locking etc). If you have a LOT of articles, that fulltext index can take a lot of time to create and can take incredible amounts of diskspace. MySQL in its default configuration will also ignore words shorter than 4 characters. It’s a good tip but it needs a bit more reading if you really want to do this right. But thanks for it anyway.
Searching faster is the key here, by omitting like, you can search much faster. Programmers make mistakes in using Like on most search which they actually do not need to. How does like work, the class has to check for each and every letter in each and every word. Thats expensive. In your case, i think you need “like” and thats where you should probably use it if there is not lots of rows.
i couldnt find a way to get database using the word base
but i could get the word database using the word data
see this query
select title , MATCH (body) AGAINST (‘data*’ IN BOOLEAN MODE) as rank from articles ORDER BY rank desc;
thanks Bluy
you are correct “it has to be a trade of between the speed and the space isnt like that every where”
our database instructor on college days used to say so.
but yes what i have mentioned is just a tip which you cant just take and use you have to do more reading to make it work better for you for sure
thanks for your comment bluy