How to make a database faster?
The big pain
Did you ever tried to work with some databases that contain big tables? And by BIG I mean one table at least that has more than 1 million records. No matter you are using MySql, SqlServer, Oracle or other DBMS, the problem is same: finding informations in that table ( records as we all know them) is very very slow. Can take minutes if the database is big and that’s really a pain. Nobody wants to be that slow. Anyways, here it comes the Databases Optimization problem. Off course the database engine is important when it comes to speed. Many peoples tend to say that is mysql’s fault for sites running slow.Though that’s not very true since big sites are mysql sustained. Mysql, starting at version 4 is comparable with other powerful DBMS. Is not that advanced as SqlServer if we speak about procedures, cursors, views etc. but is great for storing/finding data.
Having the above table that stores us phone numbers along with other informations that are not useful for us here.
CREATE TABLE `orders` ( `areacode` smallint(3) default NULL,`phonenumber` int(7) default NULL,) ENGINE=MyISAM DEFAULT CHARSET=latin1;
First note here is that we didn’t put the whole phone number in a field but split it in areacode and the rest. This is very handy and increase speed a lot. When searching using a phone number, the user can be prompt to enter the phone split, or the script can do it before sending the query to sql. If there are 10 millions records, and we are looking for a phone number in area 546, there are likely only a few thousands of records with that. So instead of looking through all 10 millions, we are looking just through some thousands. And I said 10 millions but that can be as well 100 millions and than the speed is very much improved. Would take forever otherwise.
So this is all about?
No :(. Can’t be that easy. Records are stored sequential on the disc, in the insert order. If we insert now 10k records with area code 456 and than add another 100 1 week later and so one there it comes the problem. The records of area code 456 are hardly found. Takes time to locate them.
mysql> select * from orders where areacode=504 limit 5;Empty set (0.96 sec)
It takes almost 1 sec to search for numbers in a specific area code in a 4 millions table. Imagine how much it would take in one having 100 millions records, or if your query contains some JOINS.
Yes! That can be the solution. SQL engines offers the facility of indexing data. The way that is done differ from one DBMS to other but in fact they all do the same thing: store data in a way that is quickly found.
Lets now alter the table adding an index on the two columns
mysql> ALTER TABLE orders ADD INDEX (areacode,phonenumber);
Query OK, 4484168 rows affected (16.87 sec) Records: 4484168 Duplicates: 0 Warnings: 0
Took a lot to add the index ;). You can conclude here that having indexes on your tables will slow down the inserts.
Ok now let’s test again our query and see the results.
mysql> select * from dnc_state where areacode=504 limit 5; Empty set (0.00 sec)
Impressive no? It really is. The time now is almost 0 secs. And remember; that’s only for a 4 millions records table.