…table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct UniqueCarrier): 29 1 row in … an example of such a query. . Query details: — Q1 SELECT DayOfWeek, count(*) AS c from ontime_fact…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…query would take significantly longer (40+ minutes): mysql> SELECT -> customer_id as `customer_id`, -> SUM(price * quantity) as `total_price`, -> COUNT…
Post: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
…querying INFORMATION_SCHEMA. For instance, counting the number of sleeping connections can be done with: mysql> SELECT COUNT…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…: 0 Killed: 0 # Query_time: 9.031233 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 0 # Bytes_sent… as only actual rows which are found and returned up to the top level MySQL part for processing are counted the Rows_examined remains… count(*) from sbtest group by k; This only sends 2 rows while scanning 10 million, while we can’t really optimize this query…
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
… the edit command. Let’s say you have the following query: mysql> select count(*) from film left join film_category using(film_id… count(*) from film; +———-+ | count(*) | +———-+ | 1000 | +———-+ 1 row in set (0.00 sec) mysql> exit And now if you look at the content of the queries…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
… rows in set (0.34 sec) (Just some ranfom data. The only row we really need is with 2147483647) mysql> explain select count(*) from…” 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…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
…MySQL Query Cache is the same as Oracle Query Cache – meaning cache where execution plans are cached. MySQL Query…queries, which you know do not need to be cached. Counting query cache efficiency There are few ways you can look at query…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 45 Comments
… MySQL has cached number of rows in this table. This is why it is able to instantly answer COUNT(*) and COUNT(val2) queries, but not COUNT… more queries: mysql> select count(*) from fact where i explain select count(*) from fact where i select count(val) from fact where i explain select count… key(i,val); Query OK, 7340032 rows affected (37.15 sec) Records: 7340032 Duplicates: 0 Warnings: 0 mysql> select count(val) from fact where…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
…): mysql_connect(“127.0.0.1″, “root”); mysql_select_db(“test”); for ($i = 0; $i < 10000000; $i++) { $b = $i % 1000; mysql_query(“INSERT INTO count… by the I/O which required for this query – mysql accesses all 10k rows this query could produce without LIMIT clause. Let’s check… for original query (2-100 sec). Let’s take a look at EXPLAINs: mysql> explain SELECT SQL_CALC_FOUND_ROWS * FROM count_test WHERE…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… and using MySQL VIEWs ? mysql> create view user_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…
