user292010 user292010 - 1 year ago 55
SQL Question

500000 user DB is being rather slow

I have a database with the following structure:


Currently we have around 600.000 users in this database.
Users are complaining that querying this database is rather slow.

In our tests, we found that it takes around 1.15 seconds to retrieve a user record.
This test is based on the following query:

SELECT * FROM users WHERE email = ''

I'm no expert in database management. I know how to get by when using it like a dictionary, however I have no idea on database optimization.

I was hoping I could get some help. Ideally, we'd be able to query the DB like this in under a second on even 10 million users.

Does anyone have any suggestion on optimizing simple queries like this? I'm open to anything right now, even restructuring the database if there's a more logical way to do it. Because right now, they're just ordered in the order that they registered with.

Answer Source

MySQL has two important facilities for improving performance. For your type of query, 500,000 rows or 10,000,000 rows is just not a big deal. Although other technologies such as NOSQL can perform the same actions, applications such as yours typically rely on the ACID properties of databases. A relational database is probably the right solution.

The first facility -- as mentioned elsewhere -- are indexes. In your case:

create index idx_users_email on users(email);

An index will incur a very small amount of overhead for insert and delete operations. However, with the index, looking up a row should go down to well under 0.1 seconds -- even with concurrent queries.

Depending on other queries you are running other indexes may be appropriate.

The second important capability is partitioning the tables. This is not necessary for a users table. However, it can be quite useful for transactions and other types of data.