…in the fact table, respectively. There are nearly 400 major airports included in the data set. mysql> select count(*) from dim_airport; +———-+ | count(*) | +———-+ | 396 | +———-+ 1 row in set…of parallelism is controlled by the number of shards. — Q9 select Year ,count…
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
… record) is the number of rows which MySQL used to generate result set. Comparing this number with number of…have extra column divisible_by_hundred and keep it indexed. Of course in all …COUNT(*) FROM PEOPLE GROUP BY COUNTRY) C WHERE COUNTRY=’USA’ As of MySQL 5.0 MySQL will materialize the subquery in…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
… – to get a result set, another – to count total number of rows. In this post I’ll …records): mysql_connect(“127.0.0.1″, “root”); mysql_select_db(“test”); for ($i = 0; $i < 10000000; $i++) { $b = $i % 1000; mysql…column b in where clause: mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
…recording. I’d like to invite folks who are interested in…number of…of this interim result set is relatively small, after the GROUP BY reduces it to one row per distinct value in the grouping column…
Post: Impact of the sort buffer size in MySQL
October 25, 2010 By 8 Comments
… column without an index: CREATE…COUNT=0 while [ "$NUMROW" -gt "$COUNT" ] do UUID=`uuidgen` mysql test -e “insert into sorttest value (‘$UUID’);” let “COUNT=COUNT…of 32 KB and I recorded…in `seq 1 1000` do START=`date +%s.%N` OUT=`mysql -e “set…in addition to output the total time, output the number of…
Comment: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 29, 2007 By 57 Comments
…in “EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 999 ORDER BY c LIMIT 5;”, mysql uses index to determine number of…set by picking physical data which takes some time (because columns are not included in index). I think use SQL_CALC_FOUND_ROWS in…stores number of records before …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5… new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… which range condition is applied cannot be used for filtering records. For example, suppose you have a key defined as: KEY…
Post: Debugging problems with row based replication
May 6, 2010 By 12 Comments
…of data to play with: select count(*) from t1; +———-+ | count(*) | +———-+ | 65536 | +———-+ 1 row in set (0.02 sec) My test setup includes a MySQL…record in ‘t1′, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql…
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 18 Comments
…number of concurrent queries which each examine large amounts of data. Since a single query is single threaded in MySQL…set of queries tests the most basic aggregation (count(*)) on a range of records. This table is partitioned by month which means that MySQL…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
…column j was populated by md5(rand()) values) : mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select count…in share mode; +———-+ | count(*) | +———-+ | 102216 | +———-+ 1 row in set (4.44 sec) Looking at SHOW INNODB STATUS we can see: Total number of lock structs in…
