…query patterns the optimizer recognizes, and which are left to the developer to refactor manually. There … Couldn’t you have used a SUBQUERY to fetch and compare MAX(episode_id) as an option …Training classes in North America and Europe. Join Percona and the MySQL community at our events. The next…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
…mysql); function display_menu($parent, $level) { $result = mysql_query(“SELECT a.id, a.label, a.link, Deriv1.Count FROM `menu` a LEFT OUTER JOIN…id = Deriv1.parent WHERE a.parent=” . $parent); echo “”; while ($row = mysql_fetch_assoc($result)) { if ($row['Count'] > 0) { echo “” . $row['label'] . “”; …
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… in KEY_COLUMN_USAGE, we can write a query to fetch every distinct KEY_COLUMN_USAGE.CONSTRAINT_NAME where the REFERENCED… that information, we can generate an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT… AN ORPHAN mysql> SELECT Bar_ibfk_1.ID AS `test.Bar.ID` FROM test.Bar AS Bar_ibfk_1 LEFT OUTER JOIN test…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… query or schema. For joins which fetch just few rows difference is likely to be less as the join code itself is likely… DEFAULT CHARSET=utf8 mysql> select sum(t1.i+t2.j+length(t2.c)+t1.j) from intjoin t1 left join intjoin t2 on… key lengths: mysql> explain select sum(t1.i+t2.j+length(t2.c)+t1.j) from intjoin t1 left join intjoin t2 on…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…Join of MySQL 5.5.24, Batched Key Access (BKA) Join of MySQL 5.6.5 and Block Nested Loop Hash (BNLH) Join…fetch the columns that are not part of the secondary key. Even though MySQL…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… would you get number of comments left by the given user ? mysql> select count(*) from comments where user_… such query and time for the query which fetches everything from the VIEW – it is almost the… case which are fully populated and “full join” used to to join between them. In this particular case…
Comment: GROUP_CONCAT useful GROUP BY extension
January 17, 2013 By 49 Comments
…Andrey: GROUP_CONCAT is rather slower than a regular while( mysql_fetch_array() ) PHP loop. The example here is just for simplicity…_concat( concat( `pf`.`description`, ‘: ‘, `pf`.`filename` ) SEPARATOR ‘;’ ) FROM `pages` `p` LEFT JOIN `pages_files` `pf` USING( `pages_id` ) to get same as…
Comment: Why MySQL could be slow with large tables ?
January 29, 2009 By 141 Comments
…All i am using mysql I had a problem with joining table where all table had a records more than… with multiple table joing of both type like inner join and left join etc but it was not working and give … 10 minituts so please anybody help me for the fetch row from multiple rows having a more than two …
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…important for most diagnostic cases – It is left by Innodb developers so SHOW INNODB STATUS… 1150142816 fetching rows, thread declared inside InnoDB 166 mysql tables in use 1, locked 0 MySQL thread …”waiting in InnoDB queue” or “sleeping before joining InnoDB queue”. Latest one is quite interesting -…
