…sum(data_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1 row in set (0.98 sec) This however will not cover TEMPORARY tables some of…MySQL Support contract can be handy. Conclusion Understanding where MySQL can allocate memory can help us to find…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…of MariaDB 5.5. You can read more about these variables here. The query used is: select l_orderkey, sum…time for MySQL 5.6 becomes approximately equal to that of MySQL 5.5. MariaDB 5.5 is quite slow as compared to both MySQL…BKA performance. I did not find much of a performance improvement from using …
Post: Optimizing repeated subexpressions in MySQL
January 23, 2009 By 11 Comments
… MySQL optimizer? If it sees an expression repeated many times, does it realize they’re…SUM() 18 times in the first query. (A response on the internals mailing list agreed with this finding… to be taking a pretty significant amount of time; changing to ordinary multiplication made the query faster…
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… rule of thumb I sometimes use to decide which columns should come first in an index. This is not specific to MySQL, it’s generally applicable to any database server with b-tree indexes. And there are a bunch of subtleties, but I will also ignore those for the sake of simplicity. Let’s start with this query, which returns zero …
Post: Quick comparison of MyISAM, Infobright, and MonetDB
September 29, 2009 By 30 Comments
…sum(c19), sum(c89), sum(c129) from t where c11 > 5; select sum(c19), sum(c89), sum(c129) from t where c11 < 5; Graphs of query performance time… go to the mailing lists to find the correct COPY syntax — it wasn… mysql show query times in microseconds? I had to resort to Perl + Time::HiRes for timing …
Post: How to Identify Bad Queries in MySQL
January 10, 2011 By 16 Comments
… finds queries or groups of queries that add too much load to the system by grouping queries into classes and summing the execution time… of queries completed in a cluster. One-second granularity (what you get in standard MySQL) might be enough precision for finding some cases of queries blocking others, but it’s nowhere near precise enough to find and…
Post: The two even more fundamental performance metrics
May 5, 2011 By 6 Comments
… time, “weighted time” (the sum of all residence times in the observation period — the terminology is mine for lack of…time is under a millisecond — maybe it’s 50 microseconds, maybe a bit longer. This is common in MySQL…’ll start to find the place where we get some “static”, sort of like de-tuning…
Post: Color code your performance numbers
June 2, 2010 By 4 Comments
…time for search pages within 1 second, acceptable as response within 3 seconds and anything over that will be considered unacceptable: mysql> select sum(wtime=1.0 and wtime=3.)/…that bad or it could be majority of response times are withing 1 to 2 sec…
Post: Solving INFORMATION_SCHEMA slowness
December 23, 2011 By 16 Comments
Many of us find INFORMATION_SCHEMA painfully slow to work it when it comes … time it opens it. Here are some numbers from my test box: mysql> select count(*),sum(data_length) from information_schema.tables; +———-+——————+ | count(*) | sum…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… data to the client Do not try to find any good meaning for query or schema. For …`), KEY `j` (`j`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql> select sum(t1.i+t2.j+length(t2.c)+t1.j… degradation can range from few percent to couple of times for Innodb tables MyISAM Tables may suffer significantly …
