MYSQL with Coldfusion - Solutions to create Search Capabilites?

Question

I'm using MySQL & ColdFusion. Currently for searching TEXT fields I'm using LIKE in the database. Luckily my database is empty but soon the table will fill up and I fear I the LIKE SQL query will kill my app.

I'm looking for a solution that works with both MySQL & ColdFusion that will allow me to scalably offer search capabilities with my MySQL & ColdFusion app.

Thanks

Answer 1

Whats makes you think that it will be a problem? Have you done any load testing? What is the worst case scenario max size of the table? Have you filled it to that level and tried it? Finally, do you actually need it to be "text"? MySQL has some very large varchars, would that do instead?

My point being, it sounds like you already have the simplest solution that might possibly work. Maybe you should prove that it does not work before over-engineering something else?

Lastly, to actually answer your question, you could cache the database into a verity search index and then search that (CF 9 offers another index engine as well). But your going to loose it being a live search.

Answer 2

You are right to worry about the LIKE operator's performance having scalability problems. But keep two things in mind.

First: column LIKE 'pattern%' works well if your column is indexed. It's column LIKE '%pattern%' that can cause real performance problems.

Second, mySQL has a good full-text search system built into it. See http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

Answer 3

Consider using ColdFusion's built in Verity search engine or Solr Search engine in ColdFusion 9, which is Apache Lucene. Good Luck!

Answer 4

I don't know if it is an option for your app but what I usually do is reserve like '%pattern%' for advanced searches defined by the user when a performance hit could be expected. When possible I default the search options selected by the user to 'Starts With.' I've searched '%pattern%' in a MySql 5 DB with 1.25 Million records on a low traffic site. The database doesn't seem to be the bottle neck, even on a field that isn't indexed. The customer wants all the records shown on the screen. Showing 10,000+ records seems to be the problem (lol). The DB may be less of a problem than you think depending on traffic.