… unusual setup. Q: Table1 has a primary key. Table2 joins to table1 using Table1′s primary key. Should table2 have an index on the… cases should auto-increment be used as primary key? A: Auto-increment is a good default primary key. You should pick something else if… and index for “order by desc” indicates a double linked list. Innodb has double linked list – each leaf page contains pointers to…
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
… support Unlike Innodb Falcon does not cluster data by Primary Key or any other key. This is good for some applications bad for… its internal hidden primary key) but this functionality is not exported well as it requires you to get rid of primary keys etc. Some… remember having problem not the once and twice before Innodb Double Write Buffer was implemented and seeing some pages recovered from…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… innodb_flush_method=O_DIRECT in my.cnf, to prevent double buffering. Configure as much memory as possible to InnoDB’s…! If you have inserted in an ascending order of the PRIMARY KEY, then InnoDB should have placed the rows in contiguous blocks… searches/s, 15979.01 non-hash searches/s HT: Strange, primary key equality searches should work through the adaptive hash index! What…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… tests by Peter Zaitsev, O_DIRECT works well to remove double buffering. In distant future, I would like to have the… foreign key not reference an entire primary key or unique constraint (something that is mandatory in every other RDBMS that suppors foreign key constraints… is sound design practice to make the foreign key to reference an entire primary key, so that you can easily port your applications…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
… 8 is decent start innodb_flush_method=O_DIRECT Avoid double buffering and reduce swap pressure, in most cases this setting… of Innodb properties – clustering by primary key, having primary key in all indexes (so keep primary key short), fast lookups by primary keys (try to use it in…
Comment: Joining on range? Wrong!
May 19, 2010 By 10 Comments
… problem happen often, that why on big projects, you better “double” any timestamp / datetime column with additional date column. then if… int, something chat(40), created datetime, created_date date, primary key(id), key(created), key(created_date) ); then: select * from x where created_date…
Post: MySQL Performance on Memory Appliance
March 31, 2008 By 23 Comments
… I use Sysbench to get 300Mil rows table and ran primary key lookups with uniform distribution some 90% of which would need… percents. I also tried running with key buffer on and off. Interesting enough disabling key buffer actually worked better for this workload… conditions I got about 13.5K queries/sec from MyISAM (double of Innodb already) which scaled to 70.000 queries/sec…
Post: Cache Performance Comparison
August 9, 2006 By 74 Comments
… selecting from MySQL table. Query vas very simple – lookup by primary cache, so this is kind of peak performance MySQL can…’re getting over 7000 queries/sec and it is almost doubled if result sets are cached from query cache. Pretty impressive…` varchar(60) NOT NULL default ”, `val` varchar(255) default NULL, PRIMARY KEY (`k`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `test` VALUES…
Comment: Using UNION to implement loose index scan in MySQL
January 21, 2010 By 15 Comments
… with multiple key: table: Create table tabData ( channel_id Int NOT NULL, date_time Datetime NOT NULL, value Double NOT NULL, Primary Key (channel…
Post: Test Drive of Solid
September 22, 2006 By 5 Comments
…` ( `id` int(11) NOT NULL, `names` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=solidDB DEFAULT CHARSET=latin1 insert into test2 values…
