What can I use for database optimization?
Print Email
Email this article
Close
Email this article
CloseYou are here: Home > Website Support > MySQL
If you are looking into troubleshooting the performance of a database and the queries that run on it, you can make use of the MySQL feature, ‘MySQL indexes’. This feature if used effeciently, by ensuring indexes are configured and being used correctly in your queries, may help in improving query response times and avoid any unnecessary use of more expensive newer hardware.
Database table example:
Table name: customers
| Name | Type | Size | Description |
| customer_id | char | 10 | NOT NULL |
| name | varchar | 40 | |
| surname | varchar | 40 | |
| phone_number | int | 11 |
To find the customer Dave’s phone number with the customer_id = ‘274178’, you would need to:
- Run a SQL query
- SELECT phone_number FROM customers WHERE customer_id = ‘274178’
- To find the correct record MySQL would have to search through all the records in the entire table i.e. possibly searching through thousands of entries.
‘MySQL Indexes’ work in the same way as a book index. The index helps the reader find the relevant information quickly by searching the index i.e. instead of going through each page of the book until the correct information is found. Tables may have multiple indexes applied to them. Each index may reference one or more columns. The best way to see where indexes will provide benefit is to see which columns are referenced in the “WHERE” portion of the query.
Explain command:
EXPLAIN is a command that provides some insight into the methods MySQL will use to optimise a query and its output can be a useful tool to identify and remove obstacles. Using EXPLAIN is as simple as adding the keyword EXPLAIN at the start of a query.
Example: The following will demonstrate the ‘explain command’ by investigating the table above:
EXPLAIN SELECT customer_id,name,surname FROM customers WHERE customer_id = ‘274178’;
| table | type | possible_keys | key | key_len | ref | rows | Extra |
| CUSTOMERS | ALL | NULL | NULL | NULL | 283211 | 2 | where used |
Looking at the possible_keys the output is null, which indicates there are no Indexes currently and that MySQL has to search 283211 records to return the result.
Create index example:
CREATE INDEX indx_customer_id ON customer (customer_id);
If we re-run the EXPLAIN the output changes to:
| table | type | possible_keys | key | key_len | ref | rows | Extra |
| customers | const | PRIMARY | PRIMARY | 10 | const | 1 |
Comparing the output to the previous explain output, you’ll notice that the possible_keys field now says PRIMARY and only had to search 1 row to find the record.
Note: Indices can be on any column on the table
For additional information about MySQL indexing and database optimizing refer to the following:
Was this information helpful?
Yes NoThank you for your feedback
We are delighted to find that our article resolved your query.
Thank you for your feedback
We will resolve your query as soon as possible.
Please take a few moments to comment on your unresolved query. Simply tell us what your problem is. We guarantee that we'll get back to you within two hours (during office hours) in response to your query.
Related articles
-
Login Now
Our award winning proprietary control panel empowers customers to manage their hosting environment.
Take the Tour
