… information, we can generate an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT … ‘fk_lineitems’, TABLE_SCHEMA = ‘ecommerce’, TABLE_NAME = ‘LineItems’, COLUMN_NAME = ‘order_id’, ORDINAL_POSITION = 1, POSITION_IN_UNIQUE_CONSTRAINT = 1, …
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
…(JOIN*, enum_schema_table_state) (sql_show.cc:6238) ==9090== by 0x6A5E0C: JOIN::exec() (sql_select.cc:1863) ==9090== by 0x6A7D72: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List
Post: Be careful when joining on CONCAT
October 16, 2007 By 16 Comments
… I did – forced it to use key, forced a different join order, it did not want to use tb1.vid index for… number of rows analyzed was really huge: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND…, let’s check if that really helps: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND…
Post: Product to try: MySQL/MariaDB-Galera 0.8
June 13, 2011 By 34 Comments
… three node cluster is much more complex than an average MySQL master-slave setup. Potential performance penalty: Although to have performance… right now a node gets disconnected from the cluster, in order to join to the cluster again, it has to copy the… invite you to join me in testing MySQL/Galera 0.8, binaries are available from Launchpad. The downside of MySQL/Galera is that…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… did not affect the results. What was happening? Well, in order to understand it I must provide you with more background… the latest integrated tools that MySQL provides with its last GA release? Then I invite you to join me at the webinar I have prepared for this Friday, March 15: “Learn How MySQL 5.6 Makes…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…joining the tables in the other direction mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue from dim_date join…from p1 join p2 using(seq) where p1.state = p2.state order by p1.duration desc; …
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… is Hash Joins. Hash Join As I have told before MySQL has only supported one join algorithm and that is Nested Loop Join. MariaDB has introduced a new join algorithm Hash Join. This join algorithm… size 6M (Hash Join and Key-ordered Scan disabled) You can see that the lowest query time is for MySQL 5.6 which…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
….02 sec) Each year has tens of millions of flights: mysql> select count(*) from ontime_one.ontime_fact; +———–+ | count(*) | +———–+ | 135125787 | +———–+ 1… DayOfWeek ORDER BY c DESC; — Q3 SELECT CityName as Origin, count(*) AS c from ontime_fact JOIN dim_date using (date_id) JOIN…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
… those not matching the date range. A very inefficient approach! MySQL was unable to optimize those simple conditions to match both…__and__itm_order_timestamp key_len: 8 ref: NULL rows: 1306 Extra: Using where; Using index In this case MySQL does not…_order_date); UPDATE items_ordered SET itm_order_date = DATE(itm_order_timestamp); Now the rewritten query: EXPLAIN SELECT COUNT(1) FROM tags t JOIN…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
… the join in this case because there is really no join: mysql> EXPLAIN select SQL_NO_CACHE c.city from cities_join c JOIN states… performance, while join performance degraded dramatically. Here is why: mysql> explain select SQL_NO_CACHE c.city from cities_join c JOIN states s ON (s.id = c.state_id) ORDER BY…
