… SELECT INTO OUFILE from this. Using the employees.employees table: mysql [localhost] {msandbox} (employees) > EXPLAIN SELECT * INTO OUTFILE ‘/home/viiin…You will notice the second query without the additional derived
Post: SELECT UNION Results INTO OUTFILE
September 14, 2011 By Leave a Comment
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 currently able to execute via query merge only…; Query OK, 0 rows affected (0.03 sec) mysql> explain select * from v2 where k=7 limit 5; +—-+————-+————+——-+—————+——+———+——+———+————-+ | id | select_type | table | type…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
… it will not be cached. Table level granularity in invalidation – If table gets modification all queries derived from this table are invalidated at once. Most of them quite likely would not have change their result set but MySQL…
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… are not, and so using IN() list of values derived by previous query. For example you can do: SELECT id FROM users…
Post: Data compression in InnoDB for text and blob fields
May 30, 2012 By 2 Comments
… indicated there would be a benefit derived from table compression. With the original InnoDB…MySQL 5.5) you can now leverage table compression by specifying ROW_FORMAT=COMPRESSED. In our case we only wanted MySQL…in queries against this table when the TEXT fields were not part of the query request …
Comment: A common problem when optimizing COUNT()
September 21, 2008 By 19 Comments
… … I would imagine that the syntax I showed ( using a derived table) would work on most databases. I’m not sure why… thinking and completely botched describing was that previous to MySQL having subqueries/derived tables being able to use columns that do not appear… is a ‘real database’. As many MySQL users do not want to write correct/sane queries that have GROUP BY means that…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
…statistics from which the ratio is derived (but not the ratio itself, which… between the counter events and the query‘s execution time. Alas, that … that’s dedicated to MyISAM tables. Consider your mixture of storage engines …to your IO system’s capabilities. MySQL doesn’t have good instrumentation …
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.com/doc…
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… are not, and so using IN() list of values derived > by previous query. For example you can do: > SELECT id FROM users…
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… to be invalidated – approach used by MySQL Query Cache is too coarse invalidating a lot of queries which really do not need to…
