Handling large data in MySQL

Posted: June 6, 2017 in All Category, Database

Before starting adding numerous indexes in the tables, try to analyze the performance of the whole database, the whole set of slow queries, examine again the choices of the datatypes, the engine you use and the configuration settings.

In a very large DB, very small details in indexing and querying make the difference between smooth sailing and catastrophe.

Although it is useful from a conceptual standpoint to think about which fields should be indexed during creation, it is simple to add indices to pre-existing tables as well. You can add one like this:

CREATE INDEX index_name ON table_name(column_name);

Another way of accomplishing the same thing is this:

ALTER TABLE table_name ADD INDEX ( column_name );

Now, we can run a simple query on a large dataset:

SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

Advertisements

Comments are closed.