… 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: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… | 10219 | root | localhost | dumptest | Query | 324 | Sending data | select count(*) from A,B | 0 | 0 | 2359297 | | 10290 | root | localhost | NULL… gridlock one way or another. If you’re just using Innodb tables and you’re not actively changing users, stored procedures…
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_pool_size = 2000M innodb_additional_mem_pool_size = 2M 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…
Comment: COUNT(*) for Innodb Tables
March 5, 2012 By 29 Comments
… the folk developer wisdom that “select count(1) …” is better than “select count(*) …” – does that apply? To Innodb? To MyISAM? Or is it…? Or on the right index being invoked in the “select count () … ” statement?
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
Hey Vishal, I dont know if it can help you: 1. Add proper index 2. When using COUNT – its best in innodb to use a Where clause – ref: http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/ Hope it helps, Cheers
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases … +——————–+ 3 rows in set (0.00 sec) mysql> select count(*) from A; +———-+ | count(*) | +———-+ | 2359296 | +———-+ 1 row in set (1.73 sec) …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… 20920 23734 Innodb_buffer_pool_read_requests 1361851 1264739 1235472 1263290 1235781 Innodb_buffer_pool_reads 120548 102948 76882 102672 76832 Innodb_data….89G 1.53G Innodb_data_reads 120552 123872 100551 103011 77213 Innodb_pages_read 120548 123868 100551 123592 100566 Innodb_rows_read 799239… 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
… prepared statements you might want to look at Prepared_stmt_count to see how many prepared statements are allocated on server… *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my ENGINE: InnoDB NAME: #sql516_1c42_2 TABLE_ROWS: 0 AVG_ROW_LENGTH… *************************** SESSION_ID: 7231 TABLE_SCHEMA: test TABLE_NAME: z ENGINE: InnoDB NAME: #sql516_1c3f_0 TABLE_ROWS: 0 AVG_ROW_LENGTH…
Comment: COUNT(*) vs COUNT(col)
April 3, 2012 By 41 Comments
… huge tables, we can do it in two ways:- select count(*) from table where condition1 and condition2; select col1,col2 from… query and get all the data and then get the count at PHP end like:- select col1,col2 from table where… condition2; Please suggest which one is better idea in both INNODB and MyIsam
