… afterwards: mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select table_name, index_name, count(*) pages, … mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue from dim_date join lineorder on lo_orderdatekey = d_datekey where …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… by the above view, as stored in the data dictionary: mysql> select * from flexviews.mview_table where mview_id= flexviews… than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
… would expect “Impossible Where clause” here Lets look at query execution: | Handler_read_next | 1305742982 | mysql> select count(*) from trunc where i=4147483647; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set, 1 warning (0.04 sec) | Handler_read_next | 1305819030 | As you…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
… that load statement is: mysql -S /tmp/mysql-ib.sock -e “LOAD … for any database benchmarker is SELECT count(*) FROM ontime;. Both InforBritgh and…AS (SELECT carrier, count(*) AS c FROM ontime WHERE DepDelay>10 AND YearD=2007 GROUP BY carrier), t2 AS (SELECT carrier, count(*) AS c2 FROM ontime WHERE…
Post: Air traffic queries in LucidDB
October 26, 2009 By 20 Comments
…Lame query “count start” LucidDB: SELECT count(*) FROM otp.”ontime”; 1 row selected (55.165…as c3 FROM (SELECT “Carrier”, count(*) AS c FROM OTP.”ontime” WHERE “DepDelay”>10 AND “Year”=2007 GROUP BY “Carrier”) t JOIN (SELECT “Carrier”, count(*) AS… (there is even access to MySQL via JDBC driver) just …
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
…SIMPLE SELECT to see the actual count of rows: mysql [localhost] {msandbox} (foo2) > select count(*) from test_estimate where type=6 \G *************************** 1. row *************************** count…an estimation.” This seems to be a good fix as sampling more of the intermediate pages is going …
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… left by the given user ? mysql> select count(*) from comments where user_id=5; +———-+ | count(*) | +———-+ | 1818 | +———-+ 1 row in set…counts as select user_id,count(*) cnt from comments group by user_id; Query OK, 0 rows affected (0.00 sec) mysql> select * from user_counts where…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
…mysql> select count(*) from ex1; +———-+ | count(*) | +———-+ | 73027220 | +———-+ 1 row in set (0.00 sec) mysql> select…mysql> delete from data where val=16; Query OK, 1 row affected (3.14 sec) mysql> select val, count(distinct id) from data where…
Post: UDF -vs- MySQL Stored Function
September 6, 2010 By 8 Comments
…mysql> select count(*) from paintings where title != ltrim_junk(title); +———-+ | count(*) | +———-+ | 101533 | +———-+ 1 row in set (6.82 sec) mysql> select count(*) from paintings where author != ltrim_junk(author); +———-+ | count…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
…mysql> SELECT count(*) FROM Country; +———-+ | count(*) | +———-+ |Â Â Â Â Â 239 | +———-+ 1 row in set (0.00 sec) mysql> SELECT count(distinct(population)) FROM Country; +—————————–+ | count…
