… real system: mysql> select count(*) from INNODB_SYS_TABLES; +———-+ | count(*) | +———-+ | 48246 | +———-+ 1 row in set (8.04 sec) mysql> select count(*) from INNODB_SYS_INDEXES; +———-+ | count(*) | +———-+ | 451773 | +———-+ 1 row in set (2.75 sec) In this case The memory stats from SHOW INNODB…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…this column. It is used to prevent wide innodb primary keys on the MV. mysql> select mview$pk as rank, customer_id…quickly than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select …
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
… 0 mysql> SELECT COUNT(*) FROM test_table; — 3 Tickets Used +———-+ | COUNT(*) | +———-+ | 3 | +———-+ 1 row in set (0.00 sec) mysql> UPDATE test…300 –mysql-user=root –mysql-socket=/var/lib/mysql/mysql.sock run Applicable my.cnf settings: innodb_buffer_pool_size=24G innodb_data_file…
Post: Copying InnoDB tables between servers
July 31, 2009 By 11 Comments
… and order_line.exp in directory /data/vadim/mysql/export/tpcc. ibd is regular InnoDB ® file, and exp is file with special… 98 99 100 done. When finished quick check mysql> select count(*) from order_line; +———-+ | count(*) | +———-+ | 32093604 | +———-+ 1 row in set (3 min 29…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… found and returned up to the top level MySQL part for processing are counted the Rows_examined remains zero. It looks simple… because the access type to the tables is “const” MySQL does not count it as access to two tables. In case of…_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 12F24 SET timestamp=1347399108; select count(*) from sbtest group by k; This only…
Post: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
…/mysql# And restart MySQL: root@localhost:/var/lib/mysql# /etc/init.d/mysql restart After the restart all InnoDB tables are reachable: mysql> select count(*) from sbtest; +———-+ | count(*) | +———-+ | 1000000 | +———-+ 1 row in set (0.19 sec) Conclusions Add to your monitoring system checks that InnoDB files…
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 17 Comments
… trivial WHERE clause add to a MySQL query? To find out, I set my InnoDB buffer pool to 256MB and created… table scan with no WHERE clause takes: mysql> select sql_no_cache count(*) from t; +———-+ | count(*) | +———-+ | 8388608 | +———-+ 1 row in set (5.23… seconds. Next I ran it with a trivial WHERE clause: mysql> select count(*) from t where a = current_date; (Pop quiz: do…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…fragmented. I also would note there are some MySQL optimizer restrictions in how well it can …. Would doing a SELECT competition_id, COUNT(user_id) AS user_count FROM user_competition_entry GROUP BY…”order by desc” indicates a double linked list. Innodb has double linked list – each leaf page contains…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select count(pad) …index ranges are scanned. There are also clustered keys in Innodb which combine index access with data access, saving you …
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… | | INNODB_PURGE_BATCH_SIZE | 20 | 300 | | PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES | 1000 | 224 | | SOCKET | /tmp/mysql_sandbox5530.sock | /tmp/mysql_sandbox5610.sock | | INNODB_FILE…be scaled down compared to MySQL 5.5 default values. Such as performance_schema auto scales to count up to 445 tables…
