…mysql-bin.000022 | mysql mysql> select count(*) from salaries where emp_no = 10001; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set (0.00 sec) mysql> show tables; +———————+ | Tables_in…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By Leave a Comment
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
…employees/salaries, id 18, flags 1, columns 7, indexes 2, appr.rows 2844513 [...] INDEX: name PRIMARY, id …the tool. To help us in the task of converting the schema definition to a …it in our database: mysql (employees) > LOAD DATA INFILE ‘/root/recovery-tool/data/salaries.recovery’ REPLACE INTO TABLE…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…convert SQL to the Flexviews API (see convert…row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select sum(total_lines) cnt from dashboard_top_customers\G *************************** 1. row *************************** cnt: 155187034 1 row in…
Post: Implementing efficient counters with MySQL
July 1, 2007 By 16 Comments
…most trivial way – they convert read load to write…in this case ? First separate them into special counter table, having say “id” and “counter” columns…rows in average. 10mil of posts result in 20GB table which may be too much to fit into… tables insert into one and process and truncate another. MySQL …
Post: MySQL automatic data truncation can backfire
January 9, 2007 By 10 Comments
… into the problem I found out PHP and MySQL are both to blame. PHP is to blame because in… did not fit in this column any more so MySQL was silently converting them to 2^32-1, in just about …MySQL truncating this value to 2^32-1, then performing index ref lookup (traversing about half of the rows in…
Post: A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 7 Comments
… ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec)…1 | awk ‘{print $6$7}’`; mysql -u root -e “insert into tables (name,…column. With a modified schema, the recovery completed. The customer was able to download the tables in MyISAM format and then he just convert…
Post: Innodb row size limitation
April 7, 2011 By 11 Comments
… Now you insert some test data into it: mysql> INSERT INTO example -> VALUES ( ->Â Â NULL,…row has variable length columns and the complete row size exceeds this limit, InnoDB chooses variable length columns for off-page storage. In…you hit this error after converting from a native language character…
Post: Extended EXPLAIN
July 24, 2006 By 9 Comments
…row in set (0.00 sec) As you see after query transformation “*” was expanded to full column list, each column was fully quantified plus MySQL optimizer converted c=”a” and pad=c; to c=”a” and pad=”a”. MySQL….k” and place it into the query. This …
Post: Merge Tables Gotcha
May 9, 2007 By 4 Comments
…) mysql> insert into t2 values(1); Query OK, 1 row affected (0.00 sec) mysql> select * from tm where id=1; +—-+ | id | +—-+ | 1 | +—-+ 1 row in… ask to change the column type instead). There is in fact the note about converting primary key to the key in MySQL manual Here is…
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
… All queries fall into X IN (…) AND Y IN (…) AND … Some conditions fall into X = const -…converted to IN instead of using BETWEEN [1]. As all boolean operators are ‘AND’, mysql can use either indexes built on multiple columns…, but it can select few rows or many that depends. Final set? 1…
