… goal of optimizing TPC-H queries or queries from any other benchmarks, rather they look at the queries which MySQL Users have problem with and… is “Derived Tables” AKA “Subselects in FROM Clause” are not optimized yet even though some optimizations may make it into final MySQL 6.0…
Post: MySQL Wish for 2013 - Better Memory Accounting
December 31, 2012 By 6 Comments
… which can be relatively easily derived from wait and stage information). Where we’re still walking blind with MySQL is resource usage – specifically… allocated for Innodb Buffer Pool or Query Cache but amount of memory used for example for Table Cache can be only guessed… addition to monitoring it which I’m sure hosting providers, MySQL as a Service vendors will especially appreciate as currently they…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…query to include the restriction: mysql> explain extended select c1, count(*) from v1 where c1 = 10 group by c1; +—-+————-+——-+——+—————+——+———+——-+——+———-+————————–+ | id | select_type | table…
Post: SELECT UNION Results INTO OUTFILE
September 14, 2011 By Leave a Comment
…and then doing the SELECT INTO OUFILE from this. Using the employees.employees table: mysql [localhost] {msandbox} (employees) > EXPLAIN SELECT * INTO… will notice the second query without the additional derived
Comment: Derived Tables and Views Performance
September 1, 2006 By 16 Comments
… for derived tables to be materialized. It is design deficiency in MySQL 5.0. Speaking about Views with group by – good question. MySQL is… count(*) cnt, k from test group by k; Query OK, 0 rows affected (0.03 sec) mysql> explain select * from v2 where k=7 limit 5; +—-+————-+————+——-+—————+——+———+——+———+————-+ | id | select_type | table | type…
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… loosing data etc. Read more in my Why MySQL Could be slow with Large Tables article. 5. The n+1 pattern This… is typical error. On other hand in MySQL you might be better of using several queries than doing complicated ones. Of course… so using IN() list of values derived by previous query. For example you can do: SELECT id FROM users WHERE featured=1; Now…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
…use for the statistics from which the ratio is derived (but not the ratio…from SHOW GLOBAL STATUS. I’ll start out by copying and pasting from the MySQL… between the counter events and the query‘s execution time. Alas, that … that’s dedicated to MyISAM tables. Consider your mixture of storage engines …
Post: Caching techinques
August 8, 2006 By 11 Comments
… information is updated. This is how MySQL Query Cache works by removing all queries derived from the table if that table is updated. This technique requires you… to be invalidated – approach used by MySQL Query Cache is too coarse invalidating a lot of queries which really do not need to…
Comment: Derived Tables and Views Performance
September 1, 2006 By 16 Comments
…query rewriting, so effectively query becomes same as on base table. Inline views/derived tables however can’t do it they always have to materialize table. Creating view you can also force it to use TEMPTABLE for query execution. See http://dev.mysql… to subselects in FROM clause only. Other …
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… data etc. Read more in my > Why MySQL Could be slow with Large Tables article. Deciding whether or not to let the… is typical error. On other hand in MySQL you might be > better of using several queries than doing complicated ones. Of > course… so using IN() list of values derived > by previous query. For example you can do: > SELECT id FROM users WHERE featured=1; > Now…
