… | 10219 | root | localhost | dumptest | Query | 324 | Sending data | select count(*) from A,B | 0 | 0 | 2359297 | | 10290 | root…SELECT queries to let backup to proceed, but resolving server gridlock one way or another. If you’re just using Innodb…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… –single-transaction to get consistent backup for their Innodb tables without making database read only. In most …(0.00 sec) mysql> select count(*) from A; +———-+ | count(*) | +———-+ | 2359296 | +———-+ 1 row in set (1.73 sec) mysql> select * from C; +——+ | t | +——+ …
Comment: COUNT(*) for Innodb Tables
March 5, 2012 By 29 Comments
… about the folk developer wisdom that “select count(1) …” is better than “select count(*) …” – does that apply? To Innodb? To MyISAM? Or is it actually… set? Or on the right index being invoked in the “select count () … ” statement?
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
….cnf|grep innodb innodb_data_home_dir = innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb_buffer… innodb_log_file_size = 65M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 A count command with approx 3 million records is taking too long and all my transactions are locked. select count(1),field1…
Comment: COUNT(*) vs COUNT(col)
April 3, 2012 By 41 Comments
…, we can do it in two ways:- select count(*) from table where condition1 and condition2; select col1,col2 from table where condition1 and… get all the data and then get the count at PHP end like:- select col1,col2 from table where condition1 and condition2; Please suggest which one is better idea in both INNODB and MyIsam
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…. Suppose you have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE key_column=x This… 100551 103011 77213 Innodb_pages_read 120548 123868 100551 123592 100566 Innodb_rows_read 799239 914146 912318 914146 912318 Select_scan 1 1… 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts how many times the buffer used by MRR had to…
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. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… statements you might want to look at Prepared_stmt_count to see how many prepared statements are allocated on …select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my ENGINE: InnoDB …
Comment: ext4 vs xfs on SSD
April 24, 2012 By 23 Comments
Vadim, Selecting something as important as an Operating System or major component … look at the bug list or patch list for MySQL/InnoDB at any one point in time. The list will be… it, not for a pay check. So in the first count, you are reacting to a patch issue in a way…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
… `a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql> select count(*) from idxitest where a=5 and b=5; +———-+ | count(*) | +———-+ | 60434 | +———-+ 1 row in set (0.69 sec) mysql> explain select count(*) from…
