…mysql> set global innodb_ft_aux_table=’test/dir_test_innodb’; mysql> set global innodb_optimize_fulltext_only=1; mysql> select * from…
Post: InnoDB Full-text Search in MySQL 5.6 (part 1)
February 26, 2013 By 1 Comment
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…separate tables. Then I simply created a view of a UNION DISTINCT of those two tables: mysql> create view used_indexes as (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from…select t.table_schema as TABLE_SCHEMA, t.table_name as TABLE_NAME, i.index_name as INDEX_NAME, i.NON_UNIQUE as NON_UNIQUE, count…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
…mysql tables in use 2, locked 0 MySQL thread id 53038, OS thread handle 0x7ff759b22700, query id 3918786 localhost root Sending data select count(*) from…MySQL level table locks for most queries. Summary: MySQL Table level locks and Innodb Table Level locks are two separate…
Post: Computing 95 percentile in MySQL
November 28, 2008 By 5 Comments
… goals separately for different transactions – the AJAX widget response time requirements may be very different from … table, so how to get the data if MySQL only provides you the avg: mysql> select count(*),avg(wtime) from … the average) and 99% percentile is just a bit over 10 seconds (6 times more …
Post: STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication
February 2, 2012 By 1 Comment
…select * from t1″ master [localhost] {msandbox} (test) > SELECT COUNT(*) FROM t1; +———-+ | COUNT(*) | +———-+ | 2000 | +———-+ 1 row in set (0.00 sec) master [localhost] {msandbox} (test) > SELECT COUNT(*) FROM t2; +———-+ | COUNT…MySQL will log separate…
Post: New patches, new builds
September 5, 2008 By 24 Comments
…tables USER_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS to INFORMATION_SCHEMA mysql> select * from information_schema.table_statistics; +————————-+———–+————–+———————-+ | TABLE…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…a lot. How to get CPU consumption per query ? You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql…from …TABLE_ROWS_READ: 610954 SELECT…select. Wait time is often IO (which you can see separately… # ============ === ======= ======= ======= ======= ======= ======= ======= # Count…
Comment: Heikki Tuuri to answer your in depth Innodb questions
October 8, 2007 By 51 Comments
…count. So people suggest using a separate table with table_name/num_rows. However, couldn’t this easily be fixed in MySQL – if table handler is InnoDB, a select count() (with no WHERE clause, obviously) could read the row count from …
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
…separate queries: mysql> SELECT SQL_NO_CACHE * FROM count…mysql> explain SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666; +—-+————-+————+——+—————+——+———+——-+——+————-+ | id | select_type | table…
Post: How To Test Your Upgrades - pt-upgrade
December 29, 2011 By 7 Comments
…separation of the test table from your production databases and tables. Use a separate account for testing – one that has read (i.e. SELECT…counts 0 # column types 0 # query times 441 # row counts 0 # warning counts…
