…. mysql> SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL\G *************************** 1. row *************************** …TABLE_NAME = ‘LineItems’, COLUMN_NAME = ‘order_id’, ORDINAL_POSITION = 1, POSITION_IN_UNIQUE_CONSTRAINT = 1, REFERENCED_TABLE_SCHEMA = ‘…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
…: | Handler_read_next | 1305742982 | mysql> select count(*) from trunc where i=4147483647; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set, 1 warning (0…. use “int unsigned not null” for not overly large positive integers. Consider using strict mode Storing different data when …
Post: Emulating global transaction ID with pt-heartbeat
November 4, 2011 By 6 Comments
… for a future version of MySQL. A global transaction ID lets you determine a server’s replication position reliably, among other benefits… to illustrate: mysql> select * from heartbeat\G *************************** 1. row *************************** ts: 2011-08-03T18:12:18.002500 server_id: 5150 file: ginger-bin.000033 position: 7936…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 26 Comments
…metadata tables and capture the initial binary log position. php ./setup_flexcdc.php –ini consumer.ini $ …mysql> select * from flexviews.test_demo\G *************************** 1. row *************************** dml_type: 1 uow_id: 10 fv$server_id: 999 c1: 2 c2: 2 *************************** 2. row…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…SELECT * FROM TBL WHERE hash=crc32(‘string’) AND string=’string’ The other thing you need to consider is string comparison in MySQL… add indexes only in cases which provides positive impact to performance. At some point the… an analyze on a table with 11M rows I’ve seen cardinality range from 19…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By 3 Comments
…=decode-rows –start-position 336796712 –stop-position 336797133 mysql-bin.000022 | mysql [root@sb logs]# mysqlbinlog –verbose –base64-output=decode-rows –start-position 336797275 mysql-bin.000022 | mysql mysql> select count(*) from salaries where emp_no = 10001; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set (0.00 sec) mysql…
Post: Debugging problems with row based replication
May 6, 2010 By 12 Comments
…play with: select count(*) from t1; +———-+ | count(*) | +———-+ | 65536 | +———-+ 1 row in set (0.02 sec) My test setup includes a MySQL slave. After…is currently executing at relay log file: mysql_sandbox25162-relay-bin.000002 at position: 340718. MySQL 5.1 ships with a new …
Post: Can MySQL temporary tables be made safe for statement-based replication?
May 26, 2008 By 8 Comments
…select * from test.t *************************** 6. row *************************** Log_name: mysql…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… graph: MRR doesn’t really make any positive difference to the query times for MySQL 5.6, when the workload fits entirely…_read 120548 123868 100551 123592 100566 Innodb_rows_read 799239 914146 912318 914146 912318 Select_scan 1 1 1 1 1 Sort… where b. Good Plan id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE orders range…
Post: Multi-Column IN clause - Unexpected MySQL Issue
April 4, 2008 By 21 Comments
…positives…mysql> explain select url from 124pages.124pages where url_crc=484036220 and url=”http://www.dell.com/”; +—-+————-+———-+——+—————+———+———+——-+——+————-+ | id | select… 1 row…
