CODEWITHSUNDEEP
mysqldatabaseperformanceoptimization
user292010
user292010

Reputation: 35

500000 user DB is being rather slow

I have a database with the following structure:

username,email,ip,hash,salt

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 = '[email protected]'

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.

Upvotes: 0

Views: 107

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 2

gavgrif
gavgrif

Reputation: 15499

you could add an index as already mentioned in the comments, but one thought present itself - you are currently retrieving ALL info for that row - it would be more efficient to target the query to only retrieve that information which is necessary - such as 

SELECT username FROM users WHERE email = '[email protected]';

also - you should investigate PDO and bound parameters for security.

Upvotes: 0

Related Questions