How to optimize MYSQL queries
The MYSQL query optimization takes the advantages of indexes, but it is also uses other information.For example, if we use the following query, MYSQL will execute it very quickly, no matter how large the table is.
SELECT * FROM table_name WHERE 0;
If you execute the query, MYSQL looks at the WHERE clause,realizes that no rows can satisfy the query doesn’t even bother to search the table.You can see this by issuing an EXPLAIN statement, which tells MYSQL to display some information about how its executes a SELECT query without actually executing it. To use EXPLAIN statement, place EXPLAIN in front of the SELECT statement as follows.
EXPLAIN SELECT * FROM table_name WHERE 0;
It returns more information than SELECT, also including non_NULL information about the indexes that will be used to scan the tables, the types of joins that will be used and estimate the number of rows that need to be examined by each table.
Another way to optimize the query is by using indexes.
MYSQL allows you to index the database tables, using this it is possible to quickly retrieve the records without performing a full table scan first and also significantly speeding up query execution. Upto 16 indexes per table is possible with MYSQL database. And MYSQL also supports multi column indexes and full text search indexes.
CREATE INDEX in_username ON users(username);
Here we are adding indexes in_username for column username in the users table.
Importance of closing Database connection
Closing connections is more important, because of garbage collection in PHP you need to worry about the closing connection or cleaning up the resources.
Each and every database engine has a limit on a maximum number of simultaneous connections. If you don’t close the database connection, MYSQL runs out of a available connections. Each connection consumes a memory resource.