…you might want to look at Prepared_stmt_count to see how many prepared statements are allocated… you can do better as you can query temporary tables too: mysql> select sum(data_length+index_length)… ENGINE: MEMORY NAME: #sql516_1c42_1 TABLE_ROWS: 2 AVG_ROW_LENGTH: 257 DATA_LENGTH: 126984 INDEX_LENGTH:…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…Rows_sent | Rows_examined | Rows_read | +——-+——+———–+———-+———+——+————————-+—————————–+———–+—————+———–+ | 10219 | root | localhost | dumptest | Query | 324 | Sending data | select count…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…_read 120548 123868 100551 123592 100566 Innodb_rows_read 799239 914146 912318 914146 912318 Select_…MySQL 5.6 code? As MRR was used in both MySQL 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts… query execution plan is not used, there is no difference in query times between MySQL 5.5 and MySQL…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…sec) mysql> select count(*) from A; +———-+ | count(*) | +———-+ | 2359296 | +———-+ 1 row in set (1.73 sec) mysql> select * from C; +——+ | t | +——+ | test | +——+ 1 row … (before dump has completed) mysql> alter table C add i int not null; Query OK, 1 row affected (0.65 sec…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
…, I am facing major issues with slow query output. The tables are in InnoDB with MySQL 5 and RHEL 5. Its a… = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb_buffer_pool_size = 2000M innodb_additional_mem_pool… are locked. select count(1),field1 from Table where REQUEST_TIME >= ’2012-03-26′ group by field1; 14 rows in set (15…
Post: MySQL Query Cache WhiteSpace and comments
March 20, 2008 By 9 Comments
…row in set (0.00 sec) mysql> select count(*) from fact where val like “%c%”; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set (8.79 sec) mysql> select count… about MySQL 5.0 Query Cache: Whitespace at the start of query does not block query from being cached. Moreover query …
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 41 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: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
…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 12 Comments
…query would take significantly longer (40+ minutes): mysql> SELECT -> customer_id as `customer_id`, -> SUM(price * quantity) as `total_price`, -> COUNT…
