scvinodkumar Posted June 1, 2009 Share Posted June 1, 2009 Hi, I have news site, where currently i have 782,692 records in the database. Its become very complicated when searching this database. So i planned to partition this table. But, i dont know how to do this. Could you please help me on this and also i would like you suggest me some good ideas to make the search easy and to load the page fastly. Waiting for your replys... Quote Link to comment Share on other sites More sharing options...
Daniel0 Posted June 1, 2009 Share Posted June 1, 2009 How are you searching now? Quote Link to comment Share on other sites More sharing options...
scvinodkumar Posted June 1, 2009 Author Share Posted June 1, 2009 Hi, This is my general query, and $search_query will contain the search item using LIKE command $query = " SELECT i.flag as item_read, f.title as feed_title, f.link as feed_link,f.rating, i.id as item_id, i.link as item_link, i.title as item_title, i.cache_time, i.content as item_content, i.publish_time, i.dccreator as dccreator, i.dcsubject as dcsubject, i.latitude as latitude, i.longitude as longitude, cat.id as category_id, cat.title as category_title, i.enclosure_link as enclosure_link, i.enclosure_type as enclosure_type, f.url as feed_url FROM fr_feeds f JOIN fr_categories cat ON (f.category_id = cat.id) JOIN fr_items i ON (i.feed_id = f.id) WHERE $query_search (i.flag is NULL OR i.flag = 0) "; Quote Link to comment Share on other sites More sharing options...
Daniel0 Posted June 1, 2009 Share Posted June 1, 2009 That LIKE is slow and doesn't scale well. You'd want to look into using some sort of index instead. You could use MySQL's full text searching (only in MyISAM), Sphinx, or Lucene (one PHP implementation is Zend_Search_Lucene). This forum uses Sphinx and search queries complete in under a second searching in 841,296 (and counting) posts. Quote Link to comment Share on other sites More sharing options...
scvinodkumar Posted June 1, 2009 Author Share Posted June 1, 2009 Hi, If u dont mind, could you please explain me little briefly? And, Where can i found notes on this? Quote Link to comment Share on other sites More sharing options...
Daniel0 Posted June 1, 2009 Share Posted June 1, 2009 You can find info about MySQL fulltext searching here: http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html As for Sphinx and Lucene, I already provided you with links for that. Fulltext searching will be easiest but least efficient way of doing this. It'll of course still be more efficient than using LIKE, but one potential drawback is that it's only available for the MyISAM storage engine. Quote Link to comment Share on other sites More sharing options...
scvinodkumar Posted June 1, 2009 Author Share Posted June 1, 2009 Hi, Thanks for your help. I will go through this and try to apply this on my site. Once again, thanks Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.