+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Keyword search through text DB fields (/showthread.php?tid=11247)
Pages:
1
2
Keyword search through text DB fields - El Forum - 09-01-2008
[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.
Keyword search through text DB fields - El Forum - 09-01-2008
[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.
Keyword search through text DB fields - El Forum - 09-01-2008
[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.
Keyword search through text DB fields - El Forum - 09-01-2008
[eluser]Randy Casburn[/eluser]
@Daniel - You have investigated MySql's Full Text Search Functionality right?
Randy
Keyword search through text DB fields - El Forum - 09-01-2008
[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?
Keyword search through text DB fields - El Forum - 09-01-2008
[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
Keyword search through text DB fields - El Forum - 09-01-2008
[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?
Keyword search through text DB fields - El Forum - 09-01-2008
[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.
Keyword search through text DB fields - El Forum - 09-01-2008
[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)?
Keyword search through text DB fields - El Forum - 09-02-2008
[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.