MYSQL-TOOLS

Comparison of graphical tools for MySQL

Home Blog MySQL Optimizing Count (*) and Limit queries using slow JOIN.

Optimizing Count (*) and Limit queries using slow JOIN.

Print PDF
User Rating: / 32
PoorBest 

In many Search/Browse applications you would see main (things) table which contains search fields and dimension tables which contain more information about things and which need to be joined to get query result.

If you’re executing count(*) queries for such result sets MySQL will perform the join even if you use LEFT JOIN so it is not needed which slows down things considerably. In similar way MySQL generates full rows while executing queries with limit before throwing them away which makes queries with high offset values very expensive.

To get better performance you can “Help” MySQL and remove JOIN for count(*) and do JOIN after limiting result set for retrieval queries.

Lets look at following simple example with one dimension table. In real life you will usually have several of these so performance improvements can be even higher.

 
 
CREATE TABLE `things` (
  `i` int(11) unsigned NOT NULL,
  `val` int(11) unsigned NOT NULL,
  KEY `i` (`i`,`val`)
) 
 
CREATE TABLE `dims` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `pad` varchar(110) NOT NULL,
  PRIMARY KEY  (`id`)
)
 
mysql> select count(*) from dims;
+----------+
| count(*) |
+----------+
|    30720 |
+----------+
1 row in set (0.00 sec)
 
mysql> select count(*) from things;
+----------+
| count(*) |
+----------+
|  7340012 |
+----------+
1 row in set (0.00 sec)
 
mysql> select count(*) from things where i<10000;
+----------+
| count(*) |
+----------+
|   733422 |
+----------+
1 row in set (0.44 sec)
 
mysql> select count(*) from things left join dims on val=id where i<10000;
+----------+
| count(*) |
+----------+
|   733422 |
+----------+
1 row in set (2.15 sec)
 
mysql> select i,pad from things left join dims on val=id where i<10000 limit 500000,10;
+------+------------------------------------------+
| i    | pad                                      |
+------+------------------------------------------+
| 5633 | 06bfea523be29a1230488ee66e874dffa170de76 |
| 5633 | 3baf40c2d76998654f8954bedda386b5021e0624 |
| 5633 | 35ad5c3a9d0763acc305263327864bed1af34167 |
| 5633 | 81de98a3ef74ddc0fa4f2365a27e3dbebca8df0d |
| 5633 | 11cde5d0bd8ffe1eda86364d05a58c525e8fac8f |
| 5633 | 25c474b380388c23b1de772c4255612e1233e14e |
| 5633 | 1d32b5ba28a513097fc8343efd91155b2697aeec |
| 5633 | bdc9a39cdfafda26fc2f41158abd3bc5f051a4ea |
| 5633 | d2e6cb9ca6aa2dd2bc3d0332e45579a76ccdafdf |
| 5633 | 0130c708083d743227255bd8f5e01a15fbb24212 |
+------+------------------------------------------+
10 rows in set (3.88 sec)
 
mysql> select i,pad from (select i,val from things where i<10000 limit 500000,10) res left join dims on val=id;
+------+------------------------------------------+
| i    | pad                                      |
+------+------------------------------------------+
| 5633 | 06bfea523be29a6070488ee66e874dffa233de76 |
| 5633 | 3baf40c2d76998270f8954bedda38643121e0624 |
| 5633 | 35ad5c3a9d0763acc305992327864bed1af34167 |
| 5633 | 81de98a3ef74ddc0fa4f7c95a27e3dbeb111df0d |
| 5633 | 11cde5d0bd8ffe1eda86b39d05a58c525e8fac8f |
| 5633 | 25c4733380388c23b1de730c42155612e111e14e |
| 5633 | 1d32b5ba111513097fc88f3efd91155b2337aeec |
| 5633 | bdc9a39cdfafda26fc2f48a48abd3bc5f051a4ea |
| 5633 | d2e6cb9ca12341231234123de45579a76ccdafdf |
| 5633 | 0130c708083d77377255bd1234123415fbb24212 |
+------+------------------------------------------+
10 rows in set (0.30 sec)

So as you can see using this trick we get 5 times speed up for count(*) query and 12 times. This is of course for extremely high offset value but it is also for example with only one dimension which fully fits in memory. for IO bound workload performance difference can be much higher than that.

You may also notice one more trick I'm using here - things table has covered index on which has val column in it this allow to get join query a bit more optimal.

So right now performance gain may be worth the trick, in the future I hope MySQL Optimizer will be improved so it does these transformations automatically.

 

Add comment


Security code
Refresh


Selection options GUI tool for mysql for purchase.

'mysql.proc' doesn't exist fixed problem

_

Table 'mysql.proc' doesn't exist. Fix error 1146 Table doesn't exist here...