0

I am running a live search through a text box that when a user types it returns matching rows from a mysql database. The problem is this is one of the main features of the site and has really increased the memory load on my mysql database. Because every key stroke sends a request to the php script to query the database.

I have php ignore any search term less than 3 characters long, but besides that what else could I do?

There are some options here: Live search optimisation in Javascript

But I was wondering if I should pull from a cached xml sheet, or is there somehow some way to cache mysql itself.

What does google, or some of the other large sites that rely on this feature heavily do?

Improve this question
3
  • 1
    Caching results for a short time can be helpful. Is the column you are searching properly indexed?
    – drew010
    Commented Feb 21, 2012 at 18:11
  • 1
    One thing that Google does is purchase warehouses full of computers, thousands and thousands of them, arrayed in such numbers as to require their location near cheap sources of electrical power.
    – Pointy
    Commented Feb 21, 2012 at 18:14
  • it is indexed via fulltext, is there anything else i could do? how do i go about caching results?
    – Tom
    Commented Feb 21, 2012 at 18:23

2 Answers 2

Reset to default
1

Use indexing engines to index your data and speed up your search results. Like: http://sphinxsearch.com/ or http://lucene.apache.org/core/

Setup cron job to index data, there is PHP API for sphinx, and Zend Framework Module. Indexing speed uo things a lot, if used correctly.

Improve this answer
5
  • I don't need to speed up searches, just lessen the memory load on the database
    – Tom
    Commented Feb 21, 2012 at 18:24
  • And you will get def less memory used and lessen load, yes it's a bit more work. If you cache somewhere you will still need to load that document to memory, unless you will use event based reading like sax parser.
    – Aurimas Ličkus
    Commented Feb 21, 2012 at 18:27
  • If you create an intermediate layer (of cached data), your queries will speed up AND the load on the DB will decrease (because there're not as many queries reaching DB, they are served with cache)
    – Alfabravo
    Commented Feb 21, 2012 at 19:36
  • Yes but caching search results is hard, and it's harder if data becomes big, you will get a lot of missed cache.
    – Aurimas Ličkus
    Commented Feb 21, 2012 at 19:59
  • what exactly are you referring to as an "intermediate layer"?
    – Tom
    Commented Feb 21, 2012 at 20:33
0

I would try to optimize the SQL query as much as possible:

  • no SELECT *
  • no JOIN
  • use the WHERE only in indexed fields

Also, in the PHP side:

  • cache the search results for the most frequently searched terms (the more frequently the searched data get updated, the shorter the cache lifetime) in plain text files
  • use redis, memcached if possible

Also, consider a parallel NoSQL db

And

Zend_Search_Lucene is great for low/medium traffic sites (reportedly has issues when scaling)

Improve this answer
6
  • the first part I'm good with, i have that in place. For the php side of it are you suggesting I search a text file instead of the mysql database, if so how would i determine when to search the text file vs the mysql database?
    – Tom
    Commented Feb 21, 2012 at 18:36
  • Just a quick thought: Name the file 'search_term.txt' and then do if is_file() && filemtime() - time() < CACHE_LIFETIME else execute the SQL query and create the file. Something like that.
    – aletzo
    Commented Feb 21, 2012 at 18:41
  • interesting, i'll try this out for sure
    – Tom
    Commented Feb 21, 2012 at 18:49
  • Can you explain why a text file vs an xml sheet?
    – Tom
    Commented Feb 21, 2012 at 19:03
  • it will take less disk space, and if you store a result per row it's easier to parse.
    – aletzo
    Commented Feb 21, 2012 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.