Reputation: 689

Is MySQL an Efficient System for Searching Large Tables?

Say I have a large table, about 2 million rows and 50 columns. Using MySQL, how efficient would it be to search an entire column for one particular value, and then return the row number of said value? (Assume random distribution of values throughout the entire column)

If an operation like this takes an extended amount of time, what can I do to speed it up?

Upvotes: 0

Answers (6)

Sergio Tulentsev

Reputation: 230326

If the column in question is indexed, then it's pretty fast.

Don't be cavalier with indexes, though. The more indexes you have, the more expensive your writes will be (inserts/updates/deletes). Also, they take up disk space and RAM (and can easily be larger than the table itself). Indexes are good for querying, bad for writing. Choose wisely.

Exactly how fast we're talking here? This depends on configuration of your DB machine. If it doesn't have enough RAM to host indexes and data, operation may become disk-bound and performance will be reduced. Equally will be reduced operation without index. Assuming machine is fine, this further depends on how selective your index is. If you have a table with 10M rows and you index column with boolean values, you will get only a slight increase in performance. If, otherwise, you index a column with many-many different values (user emails), query will be orders of magnitude faster.

Also, by modern standards, table with 2M rows is rather small :-)

Upvotes: 7

BenMorel

Reputation: 36494

There is no easy answer to that question, it depends on more parameters about your data. As many others have advised you already, creating an index on the column you have to search (for an exact match, or starting with a string) will be quite efficient.

As an example, I have a MyISAM table with 27,000,000 records (6.7 GB in size) which holds an index on a VARCHAR(128) field.

Here are two sample queries (real data) to give you an idea:

mysql> SELECT COUNT(*) FROM Books WHERE Publisher = "Hachette";
+----------+
| COUNT(*) |
+----------+
|    15072 |
+----------+
1 row in set (0.12 sec)

mysql> SELECT Name FROM Books WHERE Publisher = "Scholastic" LIMIT 100;
...
100 rows in set (0.17 sec)

So yes, I think MySQL is definitely fast enough to do what you're planning to do :)

Upvotes: 4

Ben D

Reputation: 14479

The structure of the data makes a big difference here, because it will affect your ability to index. Have a look at mysql indexing options (fulltext, etc).

Upvotes: 4