June 19, 2013

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

…After running an analyze on a table with 11M rows I’ve seen cardinality range from 19 to …into a lot more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL…may apply, for example you may benefit to convert BETWEEN into IN-ranges in some cases for better index usage…

Filed Under: Insight for DBAs, MySQL

Post: Timezone and pt-table-checksum

September 25, 2012 By 1 Comment

… the manual, here is how MySQL handles timezone locality with timestamp fields: Values for TIMESTAMP columns are converted from the current time zone… node2 is configured in EDT time.  Now, lets insert some rows into a table with timestamp field configured to use CURRENT_TIMESTAMP…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: How to recover deleted rows from an InnoDB Tablespace

February 20, 2012 By 4 Comments

…rows from the ibd file and import them into the database. The time between the deletion of rows…mysql (employees) > CREATE TABLE innodb_table_monitor (id int) ENGINE=InnoDB; TABLE: name employees/salaries, id 18, flags 1, columns… To help us in the task of converting the schema definition to a C …

Filed Under: Insight for DBAs, MySQL

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

…convert SQL to the Flexviews API (see convert…and/or “INSERT INTO db.schema …column. It is used to prevent wide innodb primary keys on the MV. mysql…mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql…

Filed Under: Insight for DBAs, MySQL

Post: On Character Sets and Disappearing Tables

June 21, 2012 By 1 Comment

… following 3 tables, each one with a single row inserted into it: SET FOREIGN_KEY_CHECKS=0; …expecting. Whatever the reason, we decide to convert these columns to be ASCII.  We might assume that since…an inconsistency present when MySQL won’t allow us to change an FK-referenced column from INT to…

Filed Under: Insight for DBAs, MySQL

Post: Innodb row size limitation

April 7, 2011 By 12 Comments

… insert some test data into it: mysql> INSERT INTO example -> VALUES ( -> …row on a page. If your row has variable length columns and the complete row size exceeds this limit, InnoDB chooses variable length columns…byte characters into a VARCHAR(500) column, …this error after converting from a native…

Filed Under: Insight for Developers, MySQL Tagged With: , ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…column named “value”. OK, sounds easy enough: mysql: SHOW CREATE TABLE innodb_myisam_stopword\G *************************** 1. row…rows affected (0.00 sec) mysql: INSERT INTO innodb_ft_list2 SELECT * FROM innodb_myisam_stopword; Query OK, 543 rows…MyISAM full-text parser code, convert it to a plugin, …

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Implementing efficient counters with MySQL

July 1, 2007 By 16 Comments

… the most trivial way – they convert read load to write load. … you run update for the same row which just was fetched it …First separate them into special counter table, having say “id” and “counter” columns. Using … use two tables insert into one and process and truncate another. MySQL offers atomic …

Filed Under: Insight for Developers Tagged With: ,

Post: When is MIN(DATE) != MIN(DATE) ?

September 4, 2012 By 5 Comments

… (uid, update_time), INDEX `bar` (some_other_columns) …. ) ENGINE=InnoDB; When he ran… doing a CAST() or a CONVERT() on the update_time field…rows with the 0024-06-21 date. I then proceeded to dump a few thousand random datetimes into…. On one hand, it seems like MySQL is using date windowing for…

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: A recovery trivia or how to recover from a lost ibdata1 file

June 3, 2011 By 8 Comments

…spaceid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) Filling…| tail -1 | awk ‘{print $6$7}’`; mysql -u root -e “insert into tables (name,spaceid) values … dev or staging) contained an extra column. With a modified schema, the recovery … MyISAM format and then he just convert them …

Filed Under: MySQL
« Older Posts