FULLTEXT search(Mysql) is slow for the first time and then from second time onwards it gets much faster.

Question

Hi,

I have a table with 4000 records(Which is much easier to handle through full text search). when the search query is executed for the first time it is much slower. It takes about 5 to 10 seconds. Then it gets faster. If the site remains inactive for 10 or 15 minutes, and when I try to execute the query again it gets slower.

I am using mysql+PHP. I guess this is caching issue. Our site is hosted in a third party server in which I have no access to cache.

Is there any workaround for my problem.

Thanks in advance!

Edit:

Hi, Thanks for your reply. Here is my query.

SELECT PRODUCT_2_CATEGORY.PRODUCT_ID FROM PRODUCT_2_CATEGORY, PRODUCT, MERCHANT WHERE PRODUCT_2_CATEGORY.PRODUCT_ID = PRODUCT.ID AND PRODUCT.PRO_STATUS='active' AND PRODUCT.MERCHANT_ID = MERCHANT.ID AND MERCHANT.M_STATUS='active' AND ( CAT_ID='1' OR CAT_ID='1004' OR CAT_ID='1005' OR CAT_ID='1006' OR CAT_ID='1007' OR CAT_ID='1008' OR CAT_ID='1002' OR CAT_ID='1003' OR CAT_ID='45' OR CAT_ID='46' OR CAT_ID='74' OR CAT_ID='75' OR CAT_ID='76' OR CAT_ID='49' OR CAT_ID='50' OR CAT_ID='77' OR CAT_ID='78' OR CAT_ID='79' OR CAT_ID='80' OR CAT_ID='81' OR CAT_ID='82' OR CAT_ID='83' OR CAT_ID='84' OR CAT_ID='47' OR CAT_ID='89' OR CAT_ID='51' OR CAT_ID='52' OR CAT_ID='88' OR CAT_ID='87' OR CAT_ID='86' OR CAT_ID='85' OR CAT_ID='48' OR CAT_ID='53' OR CAT_ID='54' OR CAT_ID='90' OR CAT_ID='200' OR CAT_ID='91' OR CAT_ID='92' OR CAT_ID='93' OR CAT_ID='94' OR CAT_ID='11' OR CAT_ID='95' OR CAT_ID='98' OR CAT_ID='99' OR CAT_ID='100' OR CAT_ID='101' OR CAT_ID='96' OR CAT_ID='102' OR CAT_ID='103' OR CAT_ID='104' OR CAT_ID='105' OR CAT_ID='106' OR CAT_ID='97' OR CAT_ID='107' OR CAT_ID='108' OR CAT_ID='109' OR CAT_ID='110' OR CAT_ID='114' OR CAT_ID='119' OR CAT_ID='120' OR CAT_ID='121' OR CAT_ID='115' OR CAT_ID='122' OR CAT_ID='123' OR CAT_ID='124' OR CAT_ID='125' OR CAT_ID='116' OR CAT_ID='127' OR CAT_ID='128' OR CAT_ID='129' OR CAT_ID='117' OR CAT_ID='130' OR CAT_ID='131' OR CAT_ID='118' OR CAT_ID='111' OR CAT_ID='132' OR CAT_ID='136' OR CAT_ID='137' OR CAT_ID='138' OR CAT_ID='139' OR CAT_ID='140' OR CAT_ID='133' OR CAT_ID='141' OR CAT_ID='142' OR CAT_ID='143' OR CAT_ID='144' OR CAT_ID='145' OR CAT_ID='146' OR CAT_ID='134' OR CAT_ID='147' OR CAT_ID='148' OR CAT_ID='149' OR CAT_ID='150' OR CAT_ID='151' OR CAT_ID='135' OR CAT_ID='152' OR CAT_ID='153' OR CAT_ID='154' OR CAT_ID='155' OR CAT_ID='156' OR CAT_ID='64' OR CAT_ID='158' OR CAT_ID='70' OR CAT_ID='164' OR CAT_ID='71' OR CAT_ID='165' OR CAT_ID='159' OR CAT_ID='166' OR CAT_ID='167' OR CAT_ID='168' OR CAT_ID='169' OR CAT_ID='160' OR CAT_ID='73' OR CAT_ID='170' OR CAT_ID='172' OR CAT_ID='173' OR CAT_ID='174' OR CAT_ID='161' OR CAT_ID='175' OR CAT_ID='176' OR CAT_ID='177' OR CAT_ID='178' OR CAT_ID='162' OR CAT_ID='179' OR CAT_ID='180' OR CAT_ID='181' OR CAT_ID='182' OR CAT_ID='183' OR CAT_ID='163' OR CAT_ID='184' OR CAT_ID='192' OR CAT_ID='191' OR CAT_ID='190' OR CAT_ID='189' OR CAT_ID='188' OR CAT_ID='187' OR CAT_ID='186' OR CAT_ID='185' OR CAT_ID='193' OR CAT_ID='112' OR CAT_ID='113' OR CAT_ID='65' OR CAT_ID='66' OR CAT_ID='67' OR CAT_ID='68' OR CAT_ID='69') AND MATCH(PRODUCT.TITLE) AGAINST('shirt' IN BOOLEAN MODE) ORDER BY PRODUCT.TITLE ASC LIMIT 0, 12

Our site is hosted on a third part web server(Plesk control panel). So, I am not sure whether other softwares can be installed.

Our site is 2010.shoppingstrip.com.au . This query will be executed when you search for some thing.

Thanks

Answer 1

MySQL is really bad at full text searching, both in term of performance and ease of use.

I would recomment you use Sphinx for this.

Answer 2

Indexing your table can make a huge difference. On a recent job with 1.5 million records in a MySql database, a basic query with a join was taking upwards of 35 minutes. After indexing the table (which took about 3-4 minutes) the query executed in 4 seconds.

Queries should also be examined for any possible improvements. For example, exotic joins can dramatically lengthen queries. Also, running excessive foreach loops to grab a record at a time can cause excessive lag. Depending on your circumstance, you may be able to hone the query to search for only specific, essential elements to eek out better performance.

To test your query, throw a copy of WAMP server on your local machine. Import the script and the database, and run a few tests. This could isolate whether your query is inefficient or if the problem lies with the server.

Consider the search methods you use too. For example, finding a value in mysql could be accomplished with operators such as "LIKE", "=", "REGEXP", etc. Experiment to improve performance and decrease load times.

Finally, if all else fails, you could resort to a caching solution or more robust database hosting. 4,000 records is not very large in the grand scheme of things, and you shouldn't be experiencing such slow load times. I've had good luck with caching via Zend Framework's built in caching system.

If you've still gotten nowhere, post a sample of your query and we can go to town on it!