Although SQL queries are simple commands that are easy to learn, not all queries and database functions operate with the same efficiency. This becomes increasingly important as the amount of information you are storing grows and if your database is backing a website, as your site’s popularity increases.
One of the most fundamental ways to improve querying speed begins with the table structure design itself. This means that you need to begin considering the best way to organize your data before you begin using the software.
Sometimes, it can save you significant time in the long run if you can provide some restraints for your data sizes upfront.
For instance, if there are a limited number of valid entries for a specific field that takes string values, you could use the “enum” type instead of “varchar”. This data type is compact and thus quick to query.
For instance, if you have only a few different kinds of users, you could make the column that handles that “enum” with the possible values: admin, moderator, poweruser, user.
MySQL allows you to index database tables, making it possible to quickly seek to records without performing a full table scan first and thus significantly speeding up query execution. You can have up to 16 indexes per table, and MySQL also supports multi-column indexes and full-text search indexes.
mysql> CREATE INDEX idx_username ON users(username);
When analyzing query performance, it’s also useful to consider the EXPLAIN keyword. This keyword, when placed in front of a SELECT query, describes how MySQL intends to execute the query and the number of rows it will need to process to successfully deliver a result set.
mysql> EXPLAIN SELECT city.name, city.district FROM city, country WHERE city.countrycode = country.code AND country.code = 'IND';
Internal variables Settings
MySQL is so open that it’s fairly easy to further fine-tune its default settings to obtain greater performance and stability. Some of the key variables that should be optimized are listed below.
- Altering Index Buffer Size (key_buffer)
- Altering Table Buffer Size (read_buffer_size)
- Setting The Number Of Maximum Open Tables (table_cache)
- Deciding A Time Limit For Long Queries (long_query_time)
How to debug the slow queries
Query time is very much dependent on the requests. Sometimes they are very obvious but sometimes you have to dig deep to find the culprit query. There is an easy way to find and that is logging the culprit queries. You can do it by setting the logging variables in my.cnf file. You might have to restart the mysql server to take effect.
log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes
MyISAM or InnoDB
Most MySQL databases out there run on MyISAM. I don’t have the figures and I wouldn’t know how to go about acquiring them but since it’s the default MySQL table type I’d wager that it is the case.
MyISAM is fine at many things and for many situations. You don’t have to worry about queries that COUNT(*) for example. It’s a very useful query to perform but on InnoDB it isn’t fast: you’ll need to find an alternative. MyISAM is more efficient in terms of disk space, in part due to it making physically smaller indexes which gives you all the more chance of having them fit into memory. On databases where inserts and updates rarely feature it’s faster. But you better make sure that’s the case.
For query optimisation, You have to have a good level of experience working in database administration. I have provided a little guide which steps to follow to make your query optimised. In this, you might have to do a couple of steps and your query will be optimised. In some, you might have to take all the steps to make the query optimised.