…memory and so buffer pool. I tested very simple table, having with about 20.000 rows in it … than MyISAM in 2 cases out of 3. I guess the reasons are the following: Innodb primary key joins …by primary key join than for secondary key join. I guess because it knows for sure there is no more than one …
Comment: Is there a performance difference between JOIN and WHERE?
January 18, 2013 By 30 Comments
… trying to optimize my queries and when it comes to JOIN more than a 2 tables, i am not sure what to do. I thought…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…final table `ontime_fact` is created by joining the newly constructed dimension table tables to the staging tables, … all queries by spreading them over more than one physical server. This is called “scaling… 2 DESC; — Q8.2 SELECT dest.CityName, COUNT( DISTINCT origin.CityName) from ontime_fact JOIN …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…_table_name: orders mview_table_schema: demo mview_table_alias: o mview_join_condition: NULL mview_join_order: 999 *************************** 2. row *************************** mview_table_… sec) As an added benefit, if you build more than one complete refresh view from an incrementally refreshable …
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…14.2, based on kernel v1.32.2 LucidDB was 0.9.1 The table I loaded data is: CREATE TABLE `ontime` (…12.13s for InfoBright. -Q2: Count of flights delayed more than 10min per day of week for 2000-2008 …DepDelay>10 AND Year=2007 GROUP BY carrier) t JOIN (SELECT carrier, count(*) AS c2 FROM ontime WHERE …
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…2. row *************************** table: fact type: ref key_len: 4 rows: 606 Extra: Using where *************************** 3. row *************************** table: dim2 type: eq_ref key_len: 2… these had to go to disk, our STRAIGHT_JOIN plan would take more than a month to complete! Good thing we …
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… Innodb ? Innodb executed the same query in 2.9 seconds which was a bit disappointing… work any more. The next test I decided to do is to convert Innodb table to…tables MyISAM Tables may suffer significantly if key compression is not disabled Joining on Shorter CHAR keys is significantly faster than…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
…tables (and incidentally, tables with only one row) differently than tables which contain more than one row. More about that in a bit. mysql> create table…join `test`.`j2` join…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
…Even worse than that – looking at the short table which just gets single row from the table by …looking at the long running query which just joined 2 tables… I ran EXPLAIN for it and got … more then one Temporary Tables views which you join together you may have serious issues because such tables …
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… but non-accessed data for all configurations of more than one node. This would not be the … NULL, `CityName` varchar(100) DEFAULT NULL, `State` char(2) DEFAULT NULL, `StateFips` varchar(10) DEFAULT NULL, `StateName…delay=0 innodb-use-sys-stats-table innodb-write-io-threads=4 join-buffer-size=16M key-…
