Does instant search take a lot of computing power?

Question

I was wondering whether the instant search using jQuery would cause a massive load on a database (MySQL). I know there are a lot of factors to consider, but for argument's sake let's say you have 30,000 records to flick through?

Answer 1

Searching through 30,000 records, in any SQL database, should be very fast if the tables are properly indexed. You need to make sure that you have usable index(es) for your queries. You may also want to consider using Full-Text Indexing.

Answer 2

That depends. How many more queries does your instant search implementation generate than a plain search model?

Most Javascript libraries I've seen that do this don't actually try to run the query unless the cursor has been inactive for some time. These result in only 3 - 4 times as many queries as plain search. If you truly run the search on every character entered, then you're going to have to deal with as many searches as characters are input.

That all said, it depends entirely on how much work is involved in a query.

Answer 3

It depends on how you created your database. Indexes would matter and the columns you selected will do also. The last thing you should consider is the searching algorithm. If you're just using where or like clause on let's keywords I think it's gonna be fine, but if you're after a big content then probably you should consider Full-Text Search feature of MySQL.

Answer 4

Indexing

Indexing helps, but indexing also slows down INSERT/UPDATE/DELETE statements...

MySQL also limits the amount of space you can use to index columns in a table, and it depends on the engine type:

• MyISAM: 1000 bytes
• InnoDB: 767 bytes

Data types

Choose the right data type for the job.
Not long ago, I noticed that a question on SO was listing TEXT as the data type but only using ~100 characters. They reported back to say they saw the query time decrease to a third tenth of the original when they changed the data type to VARCHAR.

Optimal Query

Be aware that wildcarding the left side of a LIKE expression renders an index on the column useless. If you need that sort of search, look at leveraging Full Text Search (FTS) using either MySQL's native MATCH .. AGAINST syntax or 3rd party support like Sphinx. It's hard to offer more insight without seeing a query.