… you do not have to use the union. So changing query to: mysql> SELECT sql_no_cache name FROM people WHERE age in(18… you can profile queries to see what exactly happens during query execution – very helpful for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> SELECT sql_no_cache name…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
…MySQL Query Cache is – I’ve seen number of people being confused, thinking MySQL Query Cache is the same as Oracle Query Cache – meaning cache where execution plans are cached. MySQL Query Cache…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… as information about the queries‘ execution plan (which includes things like whether Query cache was used or …51 0.0039 1.00 0.00 SELECT UNION wp_pp_daily_summary wp_pp_hourly_…queries that you can use to gather more data about the underlying tables involved and the query execution plan used by MySQL…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… increased to 8. Lets see how query times differ in these cases: mysql> SELECT sql_no_cache name FROM people WHERE age=19… can give a hand to MySQL Optimizer and change the query so it can handle it well…. use UNION: mysql> SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347) -> UNION…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
…MySQL configuration, we quite…because there’s no index. If the query joins several tables this way, …t use cache */ /* purecov: inspected */ 14274 cache->end=cache->buff+size; 14275 reset_cache_write(cache); 14276 DBUG_…select * from (select 1 union select 1) as x1 join (select 1 union select 1) as…
Post: Recall of Percona Server 5.1.47-11.0
June 29, 2010 By 10 Comments
…: MySQL Query Cache works by checking incoming queries if they may be found in query cache – this is done by performing simple check if query …queries starting with brackets and having space before select such as ( select * from t1 ) Queries starting with brackets are often used with UNION queries…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…query cache and the table cache and the size needed for each thread (thread stack, etc)? Those take memory too. The query cache… of those per query. Sorting and grouping and DISTINCT and UNION may/will also …mysql> set @a := repeat(‘a’, 1024 * 1024 * 100); Query OK, 0 rows affected (2.34 sec) mysql…
Post: MySQL: Data Storage or Data Processing
November 18, 2007 By 9 Comments
…does not take you that far. MySQL concept of Single Query=Single Thread works well when few rows…have to do careful indexing build summary/cache tables hunt queries which traverse a lot of data and…as sorting, unions/instersections have to be performed on the SQL node. May be MySQL Cluster will evolve…
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
… better to pre-create or cache it. There are many cache and pre-creation techniques which …are analyzed by the query. For example SELECT COUNT(*) FROM links WHERE domain = ‘mysql.com’; will return …’ve seen people trying to join all queries in single UNION (with padding to accommodate different types …
