…MySQL…selected for a UNIQUE CHECK * on the base table…INSERT-only workloads See this information about maintaining materialized views: http://scholar.google.com/scholar?hl=en&lr=&q=related…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…tables in MyISAM format in “mysql” database. Plus HEAP/MEMORY tables are used for temporary tables…inserts (multiple value inserts). Really helps if you have really bulk inserts 100-1000+ values per single insert statement. concurrent_insert – Enables concurrent insert (while Selects…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
…it serially. When the relational database was first implemented,…table: CREATE TABLE `search_set` ( `val` bigint(20) DEFAULT NULL, `cnt` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; mysql> insert…mysql> insert into ex2 values (2,1); Query OK, 1 row affected (0.00 sec) mysql> select…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 3 Comments
…mysql> SELECT YEAR(CURDATE()) INTO @this_year; mysql> insert into t VALUES…insert into t VALUES(@this_year) Is important to add a note here. There is a bug related…insert with only one value. What is the behavior if we insert multiple values and we have gaps? Example: Imagine the table has this values…
Post: MySQL Error Message Nonsenses
March 16, 2008 By 7 Comments
…about tokens near by but little details: mysql> select * from user oder by pwd; ERROR 1064 (…transactional storage engines we surely saw lock related tables being far from consistent. Let me give… reached: mysql> insert into yt values (66666); ERROR 1114 (HY000): The table ‘yt’ is full For me table is…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… examine only those rows related to the ones which…”CREATE TABLE db.schema … AS SELECT” and/or “INSERT INTO db.schema … AS SELECT…tables used by the above view, as stored in the data dictionary: mysql> select * from flexviews.mview_table… and joins, is the value proposition for Flexviews. The …
Post: Reasons for run-away main Innodb Tablespace
June 10, 2010 By 1 Comment
…tables, also known as data dictionary – table and index definitions and related stuff, double write buffer, insert buffer which temporary stores records to be inserted…mysql> select * from innodb_rseg; …values though this functionality is a bit experimental. One related…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…MySQL thread id 9697561, query id 188161264 localhost root update insert into child values(2,2) Foreign key constraint fails for table…mysql tables in use 1, locked 0 MySQL thread id 8079, query id 728899 localhost root Sending data select…to separate tablespace related IO from log related IO so you …
Post: Drilling down to the source of the problem
November 8, 2008 By 7 Comments
… upgrading to MySQL 5.1 …inserts to table with auto increment were stalling but also inserts to the tables… is checkpoint related problem. Unfortunately…selects which were stuck waiting. The next symptom was AUTO-INC lock which again was a false one – because single insert… some low value to reduce …
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
… and used. > 4. Not using relations This one is right one … in my > Why MySQL Could be slow with Large Tables article. Deciding whether … references may add processing time to inserts, updates, and deletes but there are other… of values derived > by previous query. For example you can do: > SELECT id …
