…tests setup and clarifies the scope of descriptors optimization. Server Configuration: [mysqld] user=root port=3306 …-threads=<1..1024> –test=oltp.lua –oltp_tables_count=8 –oltp-table-size=1000000 –rand-init=on…-sum-ranges=0 –oltp-order-ranges=0 –oltp-distinct-ranges=0 –oltp-skip-trx=on –oltp-…
Post: A common problem when optimizing COUNT()
September 20, 2008 By 19 Comments
… 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 20 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…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… up to the top level MySQL part for processing are counted the Rows_examined remains zero. It looks simple so far… count(*) from sbtest group by k; This only sends 2 rows while scanning 10 million, while we can’t really optimize this… to see if query is worth optimizing ? – see how many rows query sends after group by, distinct and aggregate functions are removed…
Comment: A common problem when optimizing COUNT()
September 22, 2008 By 19 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: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…=0 optimizer_switch=’index_condition_pushdown=on’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ optimizer… as follows: SELECT s_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem ON s_suppkey… rows from the table lineitem for each distinct key value, in this test case D,…
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…
