Let the web hosting account be of any type either Windows Web Hosting or Linux Hosting, MySQL supports to both type of web hosting services and is very popular as a database. The websites which has large data in their MySQL databases are supposed to be optimize it which enable them for enhanced collation of data and indexing. When the number of cases of slow speed of database, queries, rise in backlogs and user unable to connect the database then you can know that you need to optimize your database as soon as possible. Then main causes behind this are non existed or improperly defined indexes. Common people can sort out these causes and tries for some other solutions like implementation of any other costly system but by fixing these small issues one can feel great improvisation in website functioning.
Following is the example I would like to share to get a brief idea of the above discussed issues.
Suppose there is a table ’emp’ with three different fields as following
emp_code, emp_name, emp_age
For finding the information of an employee the query is written as following
SELECT * FROM from ’emp’ where emp_code is = 3;
Now the above query will be performed for finding the all records of the employee who has been assigned code as 3, but lets imagine if the number of records existed in the table are in billions then the same query will have to through each record for matching the given condition and the execution of that particular query will need more time.
To avoid the above long time process you need to create an Index. As you have a separate field created as emp_code an index would be a separate file which contains the only records you have inserted in a particular field in sorted manner. If you creates an index for emp_code the MySQL search query is able to find the given record very fast. Indexation causes to sort and search the records at an amazing speed. Here index works very similarly to an Index you know in a book.