… what it’s trying to do. You can’t fully optimize a query unless you know how to consider alternative ways… possibly, the number of distinct values in that column. You see, there’s another form for COUNT(): select count(distinct col1) from table; So when I see a query that just does COUNT(col1) I am…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…that quickly at extent to affect optimizer plans. If you look at …count(distinct c) from antest; +——————-+ | count(distinct c) | +——————-+ | 101 | +——————-+ 1 row in set (0.36 sec) mysql> select count(distinct i) from antest; +——————-+ | count(distinct…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…*** Y *All aggregate functions supported, except GROUP_CONCAT and AVG(distinct). **non-deterministic functions like RAND(),NOW(), etc, are not supported… use MIN/MAX/COUNT_DISTINCT, a secondary view will be built in the flexviews schema, transparently, to manage the distinct values for each…, and the refresh will be more expensive. This is an optimization which is required to efficiently refresh MV which use those…
Comment: A common problem when optimizing COUNT()
September 22, 2008 By 18 Comments
…). Is COUNT(col1) a good idea? Did the user mean COUNT(DISTINCT col1) or was it someone trying to outsmart the optimizer? Are you still convinced that it’s just as good if not better? Assuming the user wanted to count rows… perform as well as COUNT(*) but never better. What happens when you alter the table’s schema? The optimizer needs choices, and…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
… char(3)) returns integer deterministic > begin > declare res int; > select count(distinct co.Code) into res from Country as co inner join… to compare apples and uhm, apples… please comment). The poorly-optimized-subquery portion of the query essentially happens inside that function…
Post: Air traffic queries in LucidDB
October 26, 2009 By 20 Comments
… queries and times for all systems. – Lame query “count start” LucidDB: SELECT count(*) FROM otp.”ontime”; 1 row selected (55.165 seconds…: 8.59 sec MonetDB: 27.9 sec – Q8: SELECT “DestCityName”, COUNT( DISTINCT “OriginCityName”) FROM “ontime” WHERE “Year” BETWEEN 2008 and 2008 GROUP…, which may signal that there some low-hanging fruits for optimization. OProfile can show clear places to fix.
Post: Sphinx: Going Beyond full text search
July 23, 2007 By 18 Comments
… it does not solve all the problems – result counting and number of distinct sites were still pretty slow and we did not…). It is especially great as there is still room for optimization – Sphinx stores word positions in the index, while we do… counting number of distinct sites pointing to the given url or domain etc. Honestly this is where we cheated a bit and distinct…
Post: Using flow control functions for performance monitoring queries
May 24, 2008 By 12 Comments
… readable as well as can hardly be optimized well by MySQL Optimizer. One way I find IF statement …,0))/count(*) soso, sum(if(wtime>1,1,0))/count(*) poor, sum(if(wtime>5,1,0))/count(*) fatal … cache all together). Bots however tend to visit distinct pages which tends to have significantly lower cache …
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
…val” column in this table has 10000 distinct value, so range 1..100 selects …scan by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row …disk bound workloads. There are certain optimizations in works which would improve …
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 4 Comments
…Warnings: 0 mysql> SELECT COUNT(*) FROM test_table; — 3 Tickets Used +———-+ | COUNT(*) | +———-+ | 3 | +———-+ 1 row … As with any performance optimization effort, you will want to optimize for the common case… the case where I have two distinct access patterns: single row primary-key…
