…tables at onces, which typically make system extremely slow and also can use up all allowed connections or run out of memory…created or not completely written. Partially written .frm files or .frm being unsync with internal Innodb dictionary may cause MySQL… might want to populate caches. For MyISAM …
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
… cache. Additionally, memory pressure may cause unexpected invalidation of items, once again resulting in unpredictable, and therefore undesirable performance… since they were last populated. MySQL includes two statements that make this easier: CREATE TABLE .. SELECT and INSERT .. SELECT. These SQL …
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
…tables: CREATE TABLE `…populated this table with random data for i and…and j having just 3 values each. The table contained about 18M rows though was small enough to fit in the systems memory…MySQL can’t use index well. You’re right MySQL can’t and MySQL does not – Full table scan is performed and…
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
…memory and so buffer pool. I tested very simple table, having with about 20.000 rows in it on 32bit Linux. The columns “id” “i” and “c” were populated…and it does not return many rows. I varied the join clause to be id, i and C columns appropriately. CREATE TABLE…
Post: Cache Performance Comparison
August 9, 2006 By 74 Comments
…requests from cache, which was previously populated to contain value and measuring how long does it …cached data set fits in memory well and so served from OS cache, and when it does not….”MyTestString” 2) You need to create table test.test for MySQL Cache to work CREATE TABLE `test` ( `k` varchar(60)…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
…tables ever accessed since table start – there is no variable to control its size and it can take significant amount of memory…tables first time after MySQL time takes about 500 seconds (so it is some 200 tables/sec) – first time Innodb actually populates…
Post: Just do the math!
July 6, 2009 By 4 Comments
…table, when for each row insert (or update) row in the temporary heap table. After temporary table is populated to the sort and…table – the number of rows MySQL can insert/update depends on whenever temporary table can be kept in memory…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
…table can have very serious and negative performance implications. Remember that if you don’t create…my > Why MySQL Could be slow with Large Tables article. Deciding …FROM users WHERE featured=1; > Now populate List for IN on your PHP … up tremendous amounts of memory and disk. Because indexes are …
Post: JOIN Performance & Charsets
October 21, 2008 By 2 Comments
…KEY (`char_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `t2` ( `id` int unsigned NOT NULL auto_increment… populated with 100,000 records, while t2 has 400,000. The data set fit easily in memory. mysql… is no CONVERT() required and the key_len on both tables match. Just this simple change…
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
…BTREE does not fit in memory any more and so more data reads is…created MyISAM tables containing just integer auto_increment primary key and containing char(36) value and used for UUID primary key and when I populated…and is still going. From MySQL status I can see it is loading about 200 rows/sec and…
