+- 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: Searching Large MySQL DB via CI - Best Way? (/showthread.php?tid=7831)
Searching Large MySQL DB via CI - Best Way? - El Forum - 04-24-2008
[eluser]dimensionmedia[/eluser]
Using CI to try to 'filter' out records from a database to dynamically show a few rows of content "live" on a site when certain keywords or tags are used. For example, I might be searching through 10,000 records in a single table in a particular field for tags:
apple,banana,strawberry,orange
I know I can do a SELECT statement with WHERE TAG LIKE %% with "OR" statements but is there any better way? Since these tags can change, I can't see how caching can be applied. So database pros, what's the most efficient way to do this?
As an extension, I would also like to search for tags across MULTIPLE tables. Same question. :-)
I'm not looking for straight CI code (although that would be nice), but general advise and/or some mysql statements would be great. Running PHP5 and latest Mysql on Mediatemple if that helps.
Searching Large MySQL DB via CI - Best Way? - El Forum - 04-24-2008
[eluser]GSV Sleeper Service[/eluser]
keep your tags in a different table, using the content they apply to as a foreign key.
eg
table_blog
----------
id
title
body
table_tags
----------
tag
blog_id
select title, body from table_blog inner join table_tags on id = blog_id where table_tags like '%monkey%'
Searching Large MySQL DB via CI - Best Way? - El Forum - 04-24-2008
[eluser]Sam Dark[/eluser]
If your database is _really_ large try using something like Sphinx or Lucene.
Searching Large MySQL DB via CI - Best Way? - El Forum - 04-24-2008
[eluser]dimensionmedia[/eluser]
@Sam: Got links/urls? No idea what those are (server solutions?)...
@GSV: Basically what you're getting at is when (in your example) the blog records are added to the database then at the same time add a tag record to the table_tags table. Interesting. But in theory this would create multiple duplicate tags (but they would be going to different id's in the table_log table). so for 100 table_blog records you would potentially have 500 table_tag records (1 record x 5 tags). seems counter productive unless the inner join makes it more efficient... am i understanding that correctly?
thanks!
Searching Large MySQL DB via CI - Best Way? - El Forum - 04-24-2008
[eluser]Sam Dark[/eluser]
Sphinx
Apache Lucene
Searching Large MySQL DB via CI - Best Way? - El Forum - 04-24-2008
[eluser]NBrepresent[/eluser]
I would do:
entries
----------
id
title
body
tags
----------
id
tag
entries_tags_rel
---------------
id
entryID
tagID
also on topic: http://ellislab.com/forums/viewreply/362313/
Searching Large MySQL DB via CI - Best Way? - El Forum - 04-25-2008
[eluser]Sean Murphy[/eluser]
It would be helpful to understand more clearly what your table schema is like, what data you're searching, and what your search requirements are.
You mention tags and keywords. When someone submits a search, on the back-end are you able to distinguish between what is a tag or keyword. Basically, are tags and keywords submitted using different form fields or a special syntax?
One thing to note is that MySQL cannot use an index when you use LIKE with % at the beginning of the search string (e.g. LIKE %foo%)
If you need a little more complexity than LIKE provides and you're using the MYISAM storage engine FULLTEXT search might be a possible solution.
If you need more than that, like Sam said, Sphinx and Lucene are good options.