2.7.10. Database optimization

Using examples of errors encountered by hosting users, we will consider how you can optimize the database.

An example of a table that consists of more than 40,000 records and to which queries are sent, which create a considerable load on the server, since there are a lot of them:

CREATE TABLE `links` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `source` INT(11) UNSIGNED NOT NULL,
  `category` INT(11) UNSIGNED NOT NULL,
  `title` text NOT NULL,
  `description` text NOT NULL,
  `text` text NOT NULL,
  `link` text NOT NULL,
  `publication` datetime NOT NULL,
  `scan` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique` (`id`) USING BTREE,
  KEY `normal` (`source`,`category`,`title`(100),`publication`,`description`(100),`text`(100),`scan`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=40000 DEFAULT CHARSET=utf8

An example of incoming requests:

SELECT COUNT(*)
FROM links
WHERE link = 'http://www.......com.ua/31-08-2014/trolleybook-podgotovil-prazdnichnuyu-programmu-k-limba-noastr-/n96349/'
AND UNIX_TIMESTAMP(publication) = 1409472019

By executing a request with the prefix EXPLAIN we see that the entire table is scanned to get the results - about 40 thousand rows. In order to optimize table performance:

1. Add a field index publication.
2. Changing part of the request UNIX_TIMESTAMP(publication) = 1409472019 on publication=FROM_UNIXTIME(1409472019)... Only with such a query will MySQL use the index. Since in the first case, the DBMS will have to go through each row in the table and apply the function to it UNIX_TIMESTAMPand then compare the result with the number 1409472019, and in the second - the constant changes once FROM_UNIXTIME(1409472019)followed by an index search.

These two operations are enough for everything to start working quickly and without loading the MySQL server processor.

As a supplement, we recommend:

1. Change field type title, description, link from text on varchar the desired length.
2. Remove index UNIQ on the field id... There is no need for it, since this function is already performed by the index PRIMARY on the same field.
3. Index with title normal even more doubtful, since the server has to work hard to create it. It is simply huge, and it is extremely unlikely that the search occurs in all the fields that are specified in it. This is most likely a "dead" index, which hinders rather than helps. Developers should be aware that the index will only be used if the request contains fields that are located in it from left to right. So, for example, this index will be used if in the condition WHERE fields will be specified category, title, source... If the field source will not be specified in the request, the index will not be used either.

And finally, there is a question regarding the need to use the query itself COUNT(*)... It is very likely that its author wants in this way to get information about the presence of a record in the table, and not the total number of records in the table with the specified URL for the specified date. In this case, it is correct to use a query without COUNT(*) with the condition LIMIT 1:

SELECT id
FROM links
WHERE
link =  'http://www.......com.ua/31-08-2014/trolleybook-podgotovil-prazdnichnuyu-programmu-k-limba-noastr-/n96349/'
AND UNIX_TIMESTAMP(publication) = 1409472019
LIMIT 1

In this case, the database, having found the first record, will stop the search, and in the case of COUNT will scan the entire table.

PS Despite the fact that there is a search in two columns in the SQL query publication and link, the field was not added to the index link... This is due to the fact that for this table the field publication with a date is quite unique and the maximum will contain several lines with different link... Therefore, the overhead of forming an index for the field link significantly exceeds the cost of scanning multiple rows at a single index publicate.