… most common questions we hear is how to build high-performance systems to do this work. Let’s see some ways you can build the functionality you need and get the performance you need. Because I’ve built two such systems to… tend to perform well on a data warehousing workload. The nested-loop joins are not all that fast on big joins; the…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 8 Comments
… I ran into interesting problem – query joining about 20 tables (thank you ORM by joining all tables connected with foreign keys just… taking same 5 seconds, which points to problem with optimizer performance. Note though if you have subqueries these might need to… EXPLAIN phase yet making it unusable to check the optimizer performance. Solution for this problem was to use set optimizer_search…
Post: Is there a performance difference between JOIN and WHERE?
April 14, 2010 By 30 Comments
… to blog about the answer. “Is there a performance difference between putting the JOIN conditions in the ON clause or the WHERE…); Personally, I prefer to stay away from “comma joins” because a) the ANSI join syntax is more expressive and you’re going to use it anyway for LEFT JOIN, and mixing styles is asking for trouble, so you might…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… on date_created separately would still work. The good from performance standpoint (even though a bit ugly) will be UNION workaround… wrote about. So what if you have application which can perform search on many different columns, with worse then perfect selectivity… “const” or “system” access type it is effectively removed from join execution (replaced with constants) and so ORDER BY can be…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
…` and indexes on other columns used in joins. Let’s verify how the query performed in greater detail: SHOW STATUS LIKE ‘Handler… uses 3 bytes). Also ref shows two columns used in join. SHOW STATUS LIKE ‘Handler_read%’; +———————–+——-+ | Variable_name | Value | +———————–+——-+ | Handler_read…
Comment: FusionIO 320GB MLC benchmarks
January 14, 2010 By 19 Comments
… random I/O too. And while an SSD can improve join performance remember that all queries on MySQL are essentially single threaded… are plenty of other bottlenecks in joins such that denormalization will still almost always buy more performance. Also don’t forget that the optimizer may be forced to pick non-optimal join orders because of outer joins or…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… less than a second as they can also be a performance killer for a busy application. Recently I have started the… read all the rows from a table. Full_join means any of the joins didn’t use indexes. If a temporary table… Rows_examined: 1113 # QC_Hit: No Full_scan: No Full_join: No Tmp_table: Yes Disk_tmp_table: No # Filesort: Yes…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
… WHERE DepDelay>10 AND Year=2007 GROUP BY carrier) t JOIN (SELECT carrier, count(*) AS c2 FROM ontime WHERE Year=2007… c1 from ontime WHERE DepDelay>10 GROUP BY YearD) t JOIN (select YEARD,count(*) as c2 from ontime GROUP BY YEARD… if you want to compare or get info about air performance.
Post: Be careful when joining on CONCAT
October 16, 2007 By 16 Comments
… I had a case with an awful performance of a rather simple join. It was a join on tb1.vid = CONCAT(‘prefix-’, tb2… was really huge: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND -> tb1.vid = CONCAT… that really helps: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND -> tb1.vid = CONCAT…
Post: Thank you for joining us at Percona Live, NYC 2012
October 7, 2012 By 1 Comment
… technology allowing to dramatically increase performance of complicated join queries. MySQL users are always hungry for better performance as you might guess. Tokutek… and performance with real time analytics. FusionIO have been showing high performance PCI-E Flash storage cards, one of the highest performing and…
