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?

Community
  • 1
  • 1
Tom
  • 25
  • 5
  • 1
    Caching results for a short time can be helpful. Is the column you are searching properly indexed? – drew010 Feb 21 '12 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 Feb 21 '12 at 18:14
  • it is indexed via fulltext, is there anything else i could do? how do i go about caching results? – Tom Feb 21 '12 at 18:23

2 Answers2

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.

Aurimas Ličkus
  • 9,886
  • 4
  • 24
  • 26
  • I don't need to speed up searches, just lessen the memory load on the database – Tom Feb 21 '12 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 Feb 21 '12 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 Feb 21 '12 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 Feb 21 '12 at 19:59
  • what exactly are you referring to as an "intermediate layer"? – Tom Feb 21 '12 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)

aletzo
  • 2,471
  • 1
  • 27
  • 31
  • 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 Feb 21 '12 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 Feb 21 '12 at 18:41
  • interesting, i'll try this out for sure – Tom Feb 21 '12 at 18:49
  • Can you explain why a text file vs an xml sheet? – Tom Feb 21 '12 at 19:03
  • it will take less disk space, and if you store a result per row it's easier to parse. – aletzo Feb 21 '12 at 19:05
  • how would you parse it, something like $rows = explode("\n", $content); ? – Tom Feb 21 '12 at 20:22