PHP and MySQL: optimize database

Question

I have a database with over 10,000,000 rows. Querying it right now can take a few seconds just to find some basic information. This isn't preferable, I know that the best way to optimize is to minimize the number of rows which is possible, but right now I don't have the time to do this.

What's the easiest way to optimize a MySQL database so that when querying it, the time taken is short?

I don't mind about the size of the database, that doesn't really matter so any optimizations that increase the size are fine. I'm not very good with optimization, right now I have indexes set up, but I'm not sure how much better I can get from there.

I'll eventually trim down the database properly, but is there a quick temporary solution?

Answer 1

Besides indexing which has already been suggested, you may want to also look into partitioning tables if they are large.

Partitioning in MySQL

It's tough to be specific here, because we have very limited information, but proper indexing along with partitioning can go a very long way. Indexing properly can be a long subject, but in a very general sense you'll want to index columns you query against.

For example, say you have a table of employees, and you have your usual columns of SSN, FNAME, LNAME. In addition to those columns, we'll say that you have an additional 10 columns in the table as well.

Now you have this query:

SELECT FNAME, LNAME FROM EMPLOYEES WHERE SSN = 'blah';

Ignoring the fact that the SSN could likely be the primary key here and may already have a unique index on it, you would likely see a performance benefit by creating another composite index containing the columns (SSN, FNAME, LNAME). The reason this is beneficial is because the database can satisfy this query by simply looking at the composite index because it contains all the values needed in a sorted and compact space. (that is, less I/O). Even though the index on SSN only is a better access method to doing a full table scan, the database still has to read the data blocks for the index (I/O), find the value(s) which will contain pointers to the records needed to satisfy the query, then will need to read different data blocks (read: more random I/O) in order to retrieve the actual values for fname and lname.

This is obviously very simplified, but using indexes in this way can drastically reduce I/O and increase performance of your database.

Some other links here you may find helpful:

• MySQL indexes - how many are enough?
• When should I use a composite index?
• MySQL Query Optimization (Particularly the section on "Choosing Indexes")

Answer 2

As I can see you request 40k rows from the database, this load of data needs time just to be transferred.

Also, never ask "how to improve in general". There is no way of "general" optimization. Optimization is always result of profiling and research of your particular case.