Using SQL_CALC_FOUND_ROWS defiantly appears to be situational on the … Percona 5.5 server) I have a complex query w. joins on a table with 10+ million rows. Results are Data… 1) .90 for the data with SQL_CALC_FOUND_ROWS. No performance impact when using SQL_CALC_FOUND_ROWS 2) .013 for FOUND…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
… show close performance, while join performance degraded dramatically. Here is why: mysql> explain select SQL_NO_CACHE c.city from cities_join c JOIN states s…, but join query performance is 30% lower. Also note the times themselves – traversing about same amount of rows full table scan performs about…
Post: JOIN Performance & Charsets
October 21, 2008 By 2 Comments
… way, the character sets used on joined columns can have a significant impact on the performance of your queries. Take the following… in memory. mysql> EXPLAIN EXTENDED SELECT SQL_NO_CACHE COUNT(t1.char_id) > FROM t1 > JOIN t2 USING (char_id)\G *************************** 1…, from 4.33 to 3.12 seconds. This test was performed with MySQL 5.0.67, FreeBSD 7, on a box…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… support all of the SQL features available to SELECT statements. Refresh type Aggregation Outer join All SQL functions Built using SQL Requires FlexCDC COMPLETE… of SQL features not available with the incremental method, like ORDER BY or use of NOW(), it but still provides improved performance…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… you can build the functionality you need and get the performance you need. Because I’ve built two such systems to… intra-query parallelization, so ETL jobs written to rely on SQL tend to get really bogged down. In contrast, moving… tend to perform well on a data warehousing workload. The nested-loop joins are not all that fast on big joins; the…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… variables which may be adjusted to change behavior or for performance purposes. They are documented in the manual as well as…_key_write Delay updating indexes for MyISAM tables. Good for performance but tables will be badly corrupted on crash. delayed_insert… MyISAM related. join_buffer_size Buffer used for joins without indexes and few other cases. It is used by SQL layer so applies…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…conditions into the view query itself. This has significant performance advantages over TEMPTABLE views. A view which uses … (0.11 sec) If we then write a SQL query to aggregate over the rows of the …merge view v2 as select c1, count(*) from v1 join params p on p.connection_id = connection_id() …
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…than a second as they can also be a performance killer for a busy application. Recently I have started…slow log any queries executed by the slave’s SQL thread. This patch allows you to change that …all the rows from a table. Full_join means any of the joins didn’t use indexes. If a temporary…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
… not understand how to do that, so I generated big SQL file which contained load statements. Load of each chunk was… WHERE DepDelay>10 AND Year=2007 GROUP BY carrier) t JOIN (SELECT carrier, count(*) AS c2 FROM ontime WHERE Year=2007… if you want to compare or get info about air performance.
Comment: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
For what set of SQL problems will my method not work on? Assuming you are … and shard by customer, then by date, where does the join performance break down? With my system I can still aggregate across…, and I can eliminate vast swaths of data and only join tiny amounts when I look at data for a range…
