…table has 10000 distinct value, so range 1..100 selects about 1% of the table. The times for full table scan vs… sets. In MySQL single query runs as single thread (with …joins to large tables Joining of large data sets using nested loops is very expensive. Try to avoid it. Joins to smaller tables…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
… when you use 3 different approaches: ENUM, VARCHAR and tinyint (+joined table) columns. In practice you can also often use 4th variant…), KEY state_id (state_id) ) ENGINE=MyISAM; 4) Dictionary table for cities_join: CREATE TABLE IF NOT EXISTS `states` ( `id` tinyint(3) NOT… unique,it is pre-read and query executed basically on single table querying state by ID. Next test was a result of…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
… to the question is: for small tables, everything can reside within a single extent. For big tables, a secondary index reserves different extents… a 3x performance boost and it would write at 100MBps vs 33MBps. Any plans to enable tuning of the checkpointing rate… denormalized database (for example, if ORDER and ORDERLINE tables are joined to form a single table), it might make sense for a foreign key…
Post: Just do the math!
July 6, 2009 By 4 Comments
…scan the table, when for each row insert (or update) row in the temporary heap table. After temporary table …vs data on disk, the access patterns which have more “random” access patterns – some form of index accesses, joins…. Having this data we can see the single day report with 10000 events per day …
Post: The Doom of Multiple Storage Engines
May 8, 2010 By 22 Comments
…you look at In Memory storage engines vs Distributed ones. Trying to handle all …combination such as running multi-table update joining PBXT and Innodb tables. Operations Things like backup and…”FLUSH TABLE WITH READLOCK” and hoping nobody is touching “mysql” database any more. Single Storage Engine…
Post: Air traffic queries in LucidDB
October 26, 2009 By 20 Comments
…have for LucidDB vs previous systems. Load time To load data into LucidDB in single thread, it …immediately. It seems LucidDB has to scan whole table to get result. – Q0: select avg(c1) from (…DepDelay”>10 AND “Year”=2007 GROUP BY “Carrier”) t JOIN (SELECT “Carrier”, count(*) AS c2 FROM OTP.”ontime”…
Post: Estimating Replication Capacity
July 20, 2010 By 4 Comments
…the difference becomes, as long as replication remains single thread process. At the same time it …time the replication thread was busy replicating events vs staying idle. Note you can speak about … Filesort # 0% (5k) Full_join # 0% (7k) Full_scan # 0% (10k) Tmp_table # 0% (4k) Tmp_table_on_disk There…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…for MonetDB, but it’s almost single case where MonetDB was significantly slower…0.9.1 The table I loaded data is: CREATE TABLE `ontime` ( `Year` year(4…Year=2007 GROUP BY carrier) t JOIN (SELECT carrier, count(*) AS c2 …This experiment was not really about InfoBright vs MonetDB comparison. My goal was …
