This blag was originally posted at http://cafuego.net/2010/05/26/fast-paging-real-world Some time ago I attended the “Optimisation by Design” course from Open Query¹. In it, Arjen teaches how writing better queries and schemas can make your database access much faster (and more reliable). One such way of optimising things is by adding appropriate query hints or flags. These hints [...]
Filed under: Uncategorized by cafuego on Monday, May 31, 2010 | Social tagging: COUNT > drupal > InnoDB > mysql > SQL_CALC_FOUND_ROWS
6 Comments »
So this is about a SELECT COUNT(*) FROM tblname without a WHERE clause. MyISAM has an optimisation for that since it maintains a rowcount for each table. InnoDB and PBXT can’t do that (at least not easily) because of their multi-versioned nature… different transactions may see a different number of rows for the table table! [...]
Filed under: Good practice / Bad practice by arjen on Thursday, May 27, 2010 | Social tagging: COUNT > index scan > InnoDB > mariadb > MyISAM > mysql > pbxt > reporting
4 Comments »