…Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL …Why can’t MySQL enforce this kind of constraints? For many reasons: the first one because it would break 99% …
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… to folks who asked the great questions. I answered as many as we had time for during the session, but here… there are different types of subqueries (correlated, non-correlated, derived tables, scalar subqueries) and we saw in my presentation that sometimes…-by-case decision. There are other drawbacks to using temporary tables in this way, for example when using replication, a temporary…
Comment: Converting Character Sets
June 12, 2013 By 22 Comments
… – http://dev.mysql.com/doc/refman/5.1/en/alter-table.html “For a column that has a data type of… that the new column is long enough to store as many characters as the original column.” [followed by a complete explanation…
Post: ZFS on Linux and MySQL
May 24, 2013 By 17 Comments
… you google a bit with “zfs snapshot script” you’ll many scripts ready for the task. Snapshots work best with InnoDB…’ll have to start the snapshot while holding a “flush tables with read lock” and the flush operation will take some…
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
… static rows in MySQL */ reclength=max(file->min_record_length(table_options),reclength); if (info_length+(ulong) create_fields.elements*FCOMP… > 65535L || int_count > 255) { my_message(ER_TOO_MANY_FIELDS, ER(ER_TOO_MANY_FIELDS), MYF(0)); DBUG_RETURN(1); } Which is….=’0′ foreach(length $name .. shift); return $name } my $sql= “CREATE TABLE `”.cname(16,63).”` (“; foreach(1..shift @ARGV) { my $n=cname…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… find InnoDB pages which belong to the table‘s index. Which one? A table can have many secondary indexes, but only in the primary index we can find all fields. It must exist for any InnoDB table…_COLUMNS stores fields names and type information of the table. CREATE TABLE `SYS_COLUMNS` ( `TABLE_ID` bigint(20) unsigned NOT NULL, `POS` int…
Comment: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
and one more question “how many tables (MYISAM or INNODB) mysqld can create, the maximum number ?”
Post: Why MySQL Performance at Low Concurrency is Important
March 27, 2013 By 8 Comments
… system scales with increasing concurrency in effect. Second, there are many cases in which single-thread execution is what is going… these apply only to some use cases. Many database maintenance operations such as “alter table” are run in single-thread too. In… of benchmarks, Mark Callaghan published an example where 8 sysbench tables are used which means the lowest concurrency you can run…
Comment: TMP_TABLE_SIZE and MAX_HEAP_TABLE_SIZE
April 4, 2013 By 17 Comments
… script gives below results for temp table , server has 4G ram, which almost uses, as many sites of both drupal and wordpress…_heap_table_size & tmp_table_size) , what values you experts are recomended ? TEMP TABLES Current max_heap_table_size = 128 M Current tmp_table_size = 128 M Of 21559 temp tables, 44% were created…
Post: MySQL 5.6 Compatible Percona Toolkit 2.2 Released
March 25, 2013 By 2 Comments
… for MySQL 5.6. Several months in the making and many new features, changes, and improvements make this a great new… it and how. There’s still one noticeable omission: pt-table-sync. It’s still unclear if or how one would… Toolkit 2.2 for MySQL is an exciting release with many new and helpful features. Users are encouraged to begin upgrading…
