CODEWITHSUNDEEP
mysql
ac73
ac73

Reputation: 71

What is the most efficient way to use like on join statement?

When using join statement, I would like to join the tables using like condition. What is the most efficient way to do that?

Using fielda like CONCAT ('%', fieldb, '%') does not seem to be very efficient

Upvotes: 2

Views: 112

Answers (2)

invertedSpear
invertedSpear

Reputation: 11054

If you want to do actual searching, your are going to want to learn how to do full text indexes and queries. I'm sure you can find some primers on the subject by searching google, but here is the MySQL docs on it: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Upvotes: 0

Breezer
Breezer

Reputation: 10490

Using 2 wild char renders the index useless, what you could do is removing the one in the front, than mysql will still use the index to find whatever your searching for. If you don't have a index, to create one use the following code:

CREATE INDEX indexname ON tablename(columnname);

And then change the query to

fielda like CONCAT (fieldb, '%')

to make use of the index, other things to consider is the column a text field or simple just a varchar field, cause if it contains longer text you should consider a fulltext search.

Here is a more indepth article on the subject with sample's and good explanation etc.

http://devzone.zend.com/26/using-mysql-full-text-searching/

Upvotes: 1

Related Questions