• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Keyword search through text DB fields

#1
[eluser]Daniel H[/eluser]
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.

#2
[eluser]Sumon[/eluser]
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 www.mysite.com/product/prod_name and services 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.

#3
[eluser]Daniel H[/eluser]
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.

#4
[eluser]Randy Casburn[/eluser]
@Daniel - You have investigated MySql's Full Text Search Functionality right?

Randy

#5
[eluser]Daniel H[/eluser]
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?

#6
[eluser]Randy Casburn[/eluser]
[quote author="Daniel H" date="1220298576"]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?[/quote]

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

#7
[eluser]Daniel H[/eluser]
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?

#8
[eluser]Randy Casburn[/eluser]
[quote author="Daniel H" date="1220302765"]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?[/quote]


And you also said this in your first post:

Quote: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.

#9
[eluser]Daniel H[/eluser]
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)?

#10
[eluser]parrots[/eluser]
[quote author="Daniel H" date="1220304221"]...given the limitations that we have unearthed? Is my approach *functionally* appropriate (albeit with some performance hit)?[/quote]

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.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.