EllisLab text mark
Advanced Search
     
Keyword search through text DB fields
Posted: 01 September 2008 07:41 AM
Joined: 2008-01-04
301 posts

Hi there,

I’m probably being lazy here as I’m sure I can find the same results on Google, but I wonder if anybody could point me in the right direction of how to perform fulltext keyword searches, a la krop.com.

At a guess, I imagine the entered string is split at the spaces to make an array of keywords, then that array is used to create multiple OR LIKE ‘%array_value%’ statements.

Is this really the most efficient method? I can imagine it gets especially intensive when you need to fire a query every keystroke (even though I will cache using the usual CI way).

Any points would be appreciated.

Dan.

 
Posted: 01 September 2008 07:49 AM   [ # 1 ]   [ Rating: 0 ]
Avatar
Joined: 2007-06-25
298 posts

To create a custom search from database it might better you write different queries for different sections. Specially when all page urls are not same. I mean, for example we have product section with the url http://www.mysite.com/product/prod_name and services http://www.mysite.com/services/service_id

So the same key word result is in two places(services and product). That’s why i feel, this is wise to make separate query for sections.

Now lets consider the search result. Its a long listing with only three common fields:
1. Result title
3. A short brief (200 character or so)
3. A link to get enter that page/url

So using your search key word you can catch these three values for any section easily.

Hope this idea help you.

 Signature 

Md. Aminul Islam
http://www.shopno-dinga.com (CodeIgnited Site)

 
Posted: 01 September 2008 07:52 AM   [ # 2 ]   [ Rating: 0 ]
Joined: 2008-01-04
301 posts

Thanks for that, but I’m only going to be searching from one url, on one db table. My question is more about how to query the text fields in the db efficiently.

 
Posted: 01 September 2008 11:43 AM   [ # 3 ]   [ Rating: 0 ]
Joined: 2007-06-19
1002 posts

@Daniel - You have investigated MySql’s Full Text Search Functionality right?

Randy

 Signature 

My new therapist is working with me every day, the third one gave up… ohh

 
Posted: 01 September 2008 11:49 AM   [ # 4 ]   [ Rating: 0 ]
Joined: 2008-01-04
301 posts

I only heard of it but ignored it because I use InnoDB; is it not only compatible with myisam? Can you think of anything similar for innodb?

 
Posted: 01 September 2008 12:47 PM   [ # 5 ]   [ Rating: 0 ]
Joined: 2007-06-19
1002 posts
Daniel H - 01 September 2008 03:49 PM

I only heard of it but ignored it because I use InnoDB; is it not only compatible with myisam? Can you think of anything similar for innodb?

So your design is as closed as your mind is?  What I mean is you have requirements to do something, technology is available that will assist you, and you choose to ignore (your words) that technology simply because “you use innodb”.  Database design means you actually design you database based upon your requirements.  Ignoring a key requirement doesn’t seem logical to me…but hey…it’s your thing.

Good luck,

Randy

 Signature 

My new therapist is working with me every day, the third one gave up… ohh

 
Posted: 01 September 2008 12:59 PM   [ # 6 ]   [ Rating: 0 ]
Joined: 2008-01-04
301 posts

Ha ha nice reply! But actually I have a much more overriding requirement to have a neat, normalised db and to maintain foreign keys which myisam doesn’t support…

So essentially my question is how to develop full text search that doesn’t require innodb?

 
Posted: 01 September 2008 01:15 PM   [ # 7 ]   [ Rating: 0 ]
Joined: 2007-06-19
1002 posts
Daniel H - 01 September 2008 04:59 PM

Ha ha nice reply! But actually I have a much more overriding requirement to have a neat, normalised db and to maintain foreign keys which myisam doesn’t support…

So essentially my question is how to develop full text search that doesn’t require innodb?


And you also said this in your first post:

Is this really the most efficient method?

The answer is ... NO.

So again, good luck.

Randy

p.s.  A single table holding your full text indexed (MyISAM engine) column is the efficient (reasonable) solution. Everything else can remain InnoDB.  There are many, many case studies everywhere.  Don’t take my word for this, please go do the research.  If you’ve really gone to Third Normal Form, this should be extremely easy for you.

 Signature 

My new therapist is working with me every day, the third one gave up… ohh

 
Posted: 01 September 2008 01:23 PM   [ # 8 ]   [ Rating: 0 ]
Joined: 2008-01-04
301 posts

Right…(!)

So can anyone else be help me out here, given the limitations that we have unearthed? Is my approach *functionally* appropriate (albeit with some performance hit)?

 
Posted: 02 September 2008 10:28 AM   [ # 9 ]   [ Rating: 0 ]
Avatar
Joined: 2007-09-28
47 posts
Daniel H - 01 September 2008 05:23 PM

...given the limitations that we have unearthed? Is my approach *functionally* appropriate (albeit with some performance hit)?

I ran into this same situation a few weeks back (I too prefer InnoDB for its FK goodness) and the separate table was the best way I found to do this without having to convert everything to MyISAM.  Your use of the like clause should work for you, but MySQL’s full-text searching brings some good stuff to the table—enough to make me not mind adding one table that didn’t run on InnoDB.

 
Posted: 02 September 2008 10:33 AM   [ # 10 ]   [ Rating: 0 ]
Joined: 2008-01-04
301 posts

Hmm yeah maybe I just worry about reconciling the tables. Do you use a trigger to keep the two in sync?

 
Posted: 02 September 2008 10:42 AM   [ # 11 ]   [ Rating: 0 ]
Avatar
Joined: 2008-07-28
511 posts

Well, from personal experience I can highly recomend .

http://framework.zend.com/manual/en/zend.search.lucene.html
Full indexing, searching, file based. Uses segments and the like during indexing. If you don’t mind dropping in a small piece of the Zend framework on top of CI, have at it. It has no dependencies on any other part of the framework so can be ran as a standalone.

My only issue with the entire setup is that you can’t update documents; however you can delete then re-create it. So a tiny bit of an inconvenience, but well worth troubling with. Also it has a maximum segment size of 2GB if you are on a 32 bit system (this is a limitation of the file system, not lucene)

 Signature 

~ 4 All the Right Reasons ~

 
Posted: 02 September 2008 10:45 AM   [ # 12 ]   [ Rating: 0 ]
Avatar
Joined: 2007-09-28
47 posts

@Daniel Yep, that’s how I addressed that concern, no way I’d want to do that manually in my code.