… referring to the amazing MySQL manual, especially the option and variable reference table….privblogpercona.commanual Select_full_joinblogpercona.commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select_scanblogpercona….
Post: Debugging problems with row based replication
May 6, 2010 By 12 Comments
…mode. The row-based format does offer advantages particularly if triggers or stored procedures are used, or if non deterministic …to play with: select count(*) from t1; +———-+ | count(*) | +———-+ | 65536 | +———-+ 1 row in set (0.02 sec) My test setup includes a MySQL slave. …
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
… looking for. Here are couple of examples: GROUP BY Consider SELECT COUNT(*) cnt, page FROM log GROUP BY page ORDER BY cnt… as summary table. With MySQL 5.0 it is also easy to implement one by use of triggers. From Schema Design standpoint… index COUNT(*) query can cause a lot of troubles. Imagine for example some sort of profile browsing at social networking site – SELECT COUNT…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By 3 Comments
…the same logical volume, if not, you could potentially trigger crash recovery when an LSN mismatch occurs and …336797275 mysql-bin.000022 | mysql mysql> select count(*) from salaries where emp_no = 10001; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set (0.00 sec) mysql> show…
Post: Are you designing IO bound or CPU bound application ?
December 20, 2006 By 10 Comments
… but it is extremely important for MySQL Performance Optimization. In fact I probably …select count(*) from messages where user_id=134 or use SQL_CALC_FOUND_ROWS flag for your main select…counts for all messages, read messages etc, make sure they are updated (ie use triggers) and make sure all selects…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
…Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are…Counting query cache efficiency There are few ways you can look at query_cache efficiency. First looking at number of your selects…due to fragmentation lowmem_prunes can be triggered even if there is some free …
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
… InnoDB, we also need to count the data dictionary. This …triggers, stored routines, etc) uses memory to execute; so do temporary tables. The following query creates at least 2 temporary tables: select…mysql> set @a := repeat(‘a’, 1024 * 1024 * 100); Query OK, 0 rows affected (2.34 sec) mysql> select…
Comment: How to calculate a good InnoDB log file size
December 22, 2008 By 38 Comments
mysql> SHOW GLOBAL VARIABLES LIKE ‘innodb_log_file_size’; +———————-+———–+ …. It is one particular transaction that seems to trigger the error and that has a total of …The total number of rows is 1286113 (determined with SELECT COUNT(*)). The file containing the data for the table …
Comment: My Innodb Feature wishes
May 19, 2006 By 30 Comments
… select max(id) is frequently good and fast way to get the count. You also could use counter table especially now with Triggers in MySQL 5.0 Yes I know…
