StefanRSA Posted April 13, 2010 Share Posted April 13, 2010 I am building a search for my system... I have read up so many tutorials regarding this matter and understand that FullText searching is the best... NOT??? I am searching two fields in two tables to get the correct search... The two tables has got 14000 + entries and is growing by the day... up to 200+ per day..... (New site) If I use the following code, the query takes up to 45 seconds and a definite NO NO!!! $adquery=mysql_query("SELECT ads.id AS id, ads.addate , provDirLabel, location, sfield, townFileName, provLabel, text, R_rand, addate, r_option, userid, adnr, username, adcat.id AS catid, adcat.name AS catname, ads.subcatid AS subcatid FROM ads JOIN adfields ON adfields.ad_id=ads.id JOIN adsubcat ON adsubcat.id=ads.subcatid JOIN adcat ON adcat.id=ads.catid JOIN search_town ON search_town.townLabel=ads.location JOIN search_region ON search_region.regionId=search_town.relRegionId JOIN search_prov ON search_prov.provId=search_region.relProvId WHERE ads.adactive !='0' AND MATCH(adfields.f_value, ads.text) AGAINST ('%$trimmed%' IN BOOLEAN MODE) GROUP BY ads.id ORDER BY ads.addate DESC") or died("Record NOT Found"); Now... If I use $adquery=mysql_query("SELECT ads.id AS id, provDirLabel, location, sfield, townFileName, provLabel, text, R_rand, addate, r_option, userid, adnr, username, adcat.id AS catid, adcat.name AS catname, ads.subcatid AS subcatid FROM ads JOIN adfields ON adfields.ad_id=ads.id JOIN adsubcat ON adsubcat.id=ads.subcatid JOIN adcat ON adcat.id=ads.catid JOIN search_town ON search_town.townLabel=ads.location JOIN search_region ON search_region.regionId=search_town.relRegionId JOIN search_prov ON search_prov.provId=search_region.relProvId WHERE ads.adactive !='0' AND (adfields.f_value LIKE '%$thefield%' OR ads.text LIKE '%$thefield%') GROUP BY ads.id") or died("Record NOT Found"); This query takes only 5 seconds... (Think even this is slow.....) but I will get results for affordable if was searching for ford.... :-( What am I doing wrong and what should I change? Please help... Quote Link to comment https://forums.phpfreaks.com/topic/198358-php-mysql-query-takes-time-to-looooooooooong/ Share on other sites More sharing options...
JonnoTheDev Posted April 13, 2010 Share Posted April 13, 2010 Thats because you are using LIKE with the parenthesis on each end of the variable. // this will match: sand, band, hand, and..... WHERE field LIKE '%and' // this will match: andover, andes, and..... WHERE field LIKE 'and%' // this will match bands, sandcastle, and.... WHERE field LIKE '%and%' Using LIKE results in a really poor search function in my opinion. Using MATCH AGAINST on a fulltext field also has its drawbacks. In fact using SQL for search is never the best option in my opinion. If you can live will the speed and accuracy of searching the database through SQL then that's fine, however if you want a proper search index then take a look at either Sphinx or Lucene. Not the easiest things to setup and will take a bit of getting used to but for the speed and accuracy of the results, well worth it. http://sphinxsearch.com/docs/manual-0.9.9.html http://framework.zend.com/manual/en/zend.search.lucene.html Quote Link to comment https://forums.phpfreaks.com/topic/198358-php-mysql-query-takes-time-to-looooooooooong/#findComment-1040822 Share on other sites More sharing options...
Mchl Posted April 13, 2010 Share Posted April 13, 2010 Also use EXPLAIN to check if and what indexes this query is using. Quote Link to comment https://forums.phpfreaks.com/topic/198358-php-mysql-query-takes-time-to-looooooooooong/#findComment-1040838 Share on other sites More sharing options...
StefanRSA Posted April 13, 2010 Author Share Posted April 13, 2010 Does this mean I need to start to learn C++ ? or is the Sphinx php as well? Quote Link to comment https://forums.phpfreaks.com/topic/198358-php-mysql-query-takes-time-to-looooooooooong/#findComment-1040854 Share on other sites More sharing options...
JonnoTheDev Posted April 13, 2010 Share Posted April 13, 2010 Does this mean I need to start to learn C++ ? or is the Sphinx php as well? Read through the documentation. The API supports many languages including php. This is the document I used when first using Sphinx. http://www.ibm.com/developerworks/library/os-php-sphinxsearch/ Quote Link to comment https://forums.phpfreaks.com/topic/198358-php-mysql-query-takes-time-to-looooooooooong/#findComment-1040866 Share on other sites More sharing options...
StefanRSA Posted April 13, 2010 Author Share Posted April 13, 2010 Thanks Neil.... Back to the drawing board and school for me... Quote Link to comment https://forums.phpfreaks.com/topic/198358-php-mysql-query-takes-time-to-looooooooooong/#findComment-1040869 Share on other sites More sharing options...
StefanRSA Posted April 15, 2010 Author Share Posted April 15, 2010 Sphinx is such a nightmare............ Shit... I am now pulling out my hair.... Call me uneducated... Or even STUPID... But this is above my fireplace... I just cannot get this to work! Quote Link to comment https://forums.phpfreaks.com/topic/198358-php-mysql-query-takes-time-to-looooooooooong/#findComment-1042298 Share on other sites More sharing options...
oni-kun Posted April 15, 2010 Share Posted April 15, 2010 Sphinx is such a nightmare............ Shit... I am now pulling out my hair.... Call me uneducated... Or even STUPID... But this is above my fireplace... I just cannot get this to work! ./configure && make && make install vi /usr/local/etc/sphinx.conf Sphinx has three components: an index generator, a search engine, and a command-line search utility: * The index generator is called indexer. It queries your database, indexes each column in each row of the result, and ties each index entry to the row's primary key. * The search engine is a daemon called searchd. The daemon receives search terms and other parameters, scours one or more indices, and returns a result. If a match is made, searchd returns an array of primary keys. Given those keys, an application can run a query against the associated database to find the complete records that comprise the match. Searchd communicates to applications through a socket connection on port 3312. * The handy search utility lets you conduct searches from the command line without writing code. If searchd returns a match, search queries the database and displays the rows in the match set. The search utility is useful for debugging your Sphinx configuration and performing impromptu searches. http://www.sphinxsearch.com/docs/current.html There ye go, Why are you so hard on yourself? Quote Link to comment https://forums.phpfreaks.com/topic/198358-php-mysql-query-takes-time-to-looooooooooong/#findComment-1042306 Share on other sites More sharing options...
StefanRSA Posted April 15, 2010 Author Share Posted April 15, 2010 Thanks oni-kun.... I am sure the sphinx is installed on my server... Now, if I use vi /usr/local/etc/sphinx.conf I get something that says ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ "/usr/local/etc/sphinx.conf" [New File] Now, What I could find for now, is that I need to create a test or something here... So I tried: source src1 { type = mysql sql_host = localhost sql_user = username sql_pass = password sql_db = theDBname sql_port = 3306 sql_query = SELECT ads.id AS id, provDirLabel, location, sfield, townFileName, provLabel, text, R_rand, addate, r_option, userid, adnr, username, adcat.id AS catid, adcat.name AS catname, ads.subcatid AS subcatid FROM ads JOIN adfields ON adfields.ad_id=ads.id JOIN adsubcat ON adsubcat.id=ads.subcatid JOIN adcat ON adcat.id=ads.catid JOIN search_town ON search_town.townLabel=ads.location JOIN search_region ON search_region.regionId=search_town.relRegionId JOIN search_prov ON search_prov.provId=search_region.relProvId sql_query_info = SELECT * FROM ads WHERE id=$id } index example1 { source = src1 path = /usr/local/sphinx/indexes/test1 } indexer { mem_limit = 32M } searchd { port = 3312 pid_file = /usr/local/sphinx/api/libsphinxclient/searchd.pid } And then.... This is where I get stuck... What do i have to do to compile this??? I cannot get out of this???? Apparently I also have to enable it in my php ini.... But did not have the joy to get to that stage as yet... Got info from google ------> http://www.trevsewell.co.uk/php/full-text-searching-with-php-and-sphinx/ Thanks for you help! Quote Link to comment https://forums.phpfreaks.com/topic/198358-php-mysql-query-takes-time-to-looooooooooong/#findComment-1042311 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.