…comes to changes that really require table to be rebuilt – adding/dropping columns or indexes, changing data type, converting data to different character set – MySQL master…col1,col2,..) VALUES (id,value1,value2,..)”), deletes or updates of rows that will not exist after schema changes and other …
Post: MySQL automatic data truncation can backfire
January 9, 2007 By 10 Comments
… on 32bit platform initially so “key” column was defined as “int” As it was migrated to 64bit platform we got unsigned 32bit values which did not fit in this column any more so MySQL was silently converting them to… we have MySQL truncating this value to 2^32-1, then performing index ref lookup (traversing about half of the rows in pages…
Post: A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 8 Comments
…mysql -u root test The MyISAM table recording the space ids of the tables mysql> show create table test.tablesG *************************** 1. row…to download the tables in MyISAM format and then he just convert them back to…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
…from the fact it is the only MySQL storage engine which defaults to HASH index type by default, instead …it with 1.000.000 rows ALL of them having same value for c column. Now I’m performing … get just about 80 deletes per second while converting table to MyISAM I get about 600 deletes per second and…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… pk_column IN (…) As you can see by utilizing the buffer for sorting the secondary key tuples by pk_column, we have converted a lot of point primary key lookups to one or more range primary key lookup. Thereby, converting Random access to…. Another interesting thing to note is that MySQL 5.6 and MariaDB 5.5 are both reading more rows than MySQL 5.5, as…
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 optimizer…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… due to amount of extra system calls it has to read row data from OS Cache. So what if we convert i and j columns to varchar while sticking to utf8 character… I decided to do is to convert Innodb table to latin1 character set. I was expected this to shorten some internal buffers MySQL has to allocate for…
Post: Innodb row size limitation
April 7, 2011 By 12 Comments
…Engine=InnoDB; Now you insert some test data into it: mysql> INSERT INTO example -> VALUES ( ->Â Â NULL, ->Â Â … you hit this error after converting from a native language character set to utf8, as it can increase … if the columns aren’t requested as part of a row-read, they don’t have to be touched …
Post: Covering index and prefix indexes
November 23, 2006 By 5 Comments
… the index without reading the row itself. Today I had a chance to do couple of experiments to see when exactly it works…. Notice “Using Index” in Extra column. mysql> explain select j,k from t where i=5 \G *************************** 1. row *************************** id: 1 select_type… will convert it to the full key instead of prefix key. If you would use length longer than column length, lets say 20, MySQL…
Post: What exactly is read_rnd_buffer_size
July 24, 2007 By 4 Comments
… row pointer storage is used and the fields which are being length can be converted to fixed size (basically everything but BLOB/TEXT) MySQL… columns are not selected A lot of rows are retrieved after sort – if you have LIMIT 10 it is unlikely to help as MySQL… number of columns or there are long VARCHAR columns used – it takes only couple of UTF8 VARCHAR(255) to create a row which is…
