June 18, 2013

Post: Implementing SchemaSpy in your MySQL environment

June 4, 2013 By 2 Comments

…TABLE `parent` ( `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE…displayed: Table child_A can be made to display…

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

Post: On Character Sets and Disappearing Tables

June 21, 2012 By 1 Comment

…TABLE bar ( i INT NOT NULL PRIMARY KEY, j INT NOT NULL, INDEX(j) ) ENGINE=INNODB; trying to do something like “ALTER TABLE…to get the actual filename of the temporary table rather than the temporary table name that was displayed…

Filed Under: Insight for DBAs, MySQL

Post: AUTO_INCREMENT and MERGE TABLES

September 11, 2008 By Leave a Comment

…the Merge Table itself. Neither of these expectations really true: mysql> create table a1(i int unsigned not null auto_increment primary key); Query OK, 0… TABLE there is no warning displayed but the MERGE TABLE may continue having old file descriptors open not seeing changes done to original table…

Filed Under: Insight for DBAs Tagged With: ,

Post: EXPLAIN EXTENDED can tell you all kinds of interesting things

June 15, 2010 By 7 Comments

… displays additional information, including the rewritten query. To take a look at EXPLAIN EXTENDED, I’ll start with three empty tables. It is important to…’ tables. A ‘const’ table is a table that contains 0 or 1 rows, or a table on which all parts of a primary key or unique key lookup…

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

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

April 4, 2011 By 20 Comments

…Query OK, 0 rows affected (41 min 52.04 sec) Data dictionary Here is a quick example of the list of tables… column. It is used to prevent wide innodb primary keys on the MV. …to display the total sales amount for any particular customer. Notice how you can use this summary to calculate the number of…

Filed Under: Insight for DBAs, MySQL

Post: SHOW INNODB STATUS walk through

July 17, 2006 By 40 Comments

…TO BE GRANTED: RECORD LOCKS space id 0 page no 16403 n bits 72 index `PRIMARY` of table…query id 188161264 localhost root update insert into child values(2,2) Foreign key constraint fails for table…

Filed Under: Insight for DBAs Tagged With:

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… of sense to me. Why does InnoDB need to add a hidden column (similar to GEN_CLUST_INDEX when you don’t define a PRIMARY KEY… indexes on that table by querying the various I_S.INNODB_FT_* tables. In particular, the INNODB_FT_CONFIG table is supposed to “display metadata about…

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

Comment: Long PRIMARY KEY for Innodb tables

April 10, 2009 By 8 Comments

…an InnoDB table with a long varchar primary key, and when I do a “select id…” query, the results do not return ordered by id. I can think of…to assert an index’s prefix length on MySQL 5.1. It does not display in a “show create table…

Filed Under: Insight for DBAs Tagged With: ,

Post: Are you designing IO bound or CPU bound application ?

December 20, 2006 By 10 Comments

…of messaging between users. You may want to display to the user number of unread messages as well as use total number of…tables are used having simply auto_increment id on messages would likely be much slower than (user_id,sub_id) combined primary key…

Filed Under: Insight for Developers Tagged With:

Comment: Database access Optimization in Web Applications.

September 16, 2006 By 10 Comments

…actually required for some “display” This generally has zero impact on the performance of a query of the SELECT * FROM table where primary_key=x (with usual caveats about BLOBs and the like). It is relatively easy to …

Filed Under: Insight for Developers Tagged With: