… range 1..100 selects about 1% of the table. The times for full table scan vs range scan by index: mysql> select… (the work is on a way) – MySQL can’t do hash join or sort merge join – it only can do nested… to work with in temporary table etc. Prefer full table scans to index accesses – For large data sets full table scans are often faster…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… in the hash table by using values of the columns participating in the equijoin condition. The hash table is accessed by using a hash function…_orderdate | PRIMARY | 4 | NULL | 2993459 | 50.00 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | l | ref | PRIMARY,i_l_orderkey…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… reasonable performance optimization. (Keep in mind that the prefix adaptive hashing would fail here, because the first 10 or so characters… for such table. It also will help the query you’re mentioning allowing group by to be performed without temporary table or external…: If you have Index on the column MySQL can avoid temporary table or filesort for group by by this column. This works…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… does not only contain data pages – it also contain adaptive hash indexes, insert buffer, locks which also take some time. Though… – these include MySQL buffers – query cache, key_buffer, mysql threads, temporary tables, per thread sort buffer which can be allocated. There are… consume significantly less memory than connections doing work with huge temporary tables and otherwise running complex queries. It is usually much better…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… a table. Full_join means any of the joins didn’t use indexes. If a temporary table is required for query execution Tmp_table will be set. Sometimes that table must be created on disk… query. This is actually an approximation based on a small hash array representing the entire buffer pool, because it could take…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
…table_schema = innodb_sys_tables.SCHEMA ORDER BY cnt DESC LIMIT 20; +————–+————–+————–+——+——-+——–+———+ | table_schema | table_name | index_name | cnt | dirty | hashed…pool rather than using some form of temporary measures. I often check these stats during…
Post: UNION vs UNION ALL Performance
October 5, 2007 By 15 Comments
… to use temporary table to store result set, however this is not correct – both UNION ALL and UNION distinct use temporary table for result… the fact UNION requires internal temporary table with index (to skip duplicate rows) while UNION ALL will create table without such index. This… becomes larger when on disk table is required (as in this case) – Hash indexes used by MEMORY table are very efficient and do…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… huge temporary table was required (there were about 5 million of distinct pages visited during that day) which resulted in on disk temporary table which as we know quite slow. Of course it would be possible to allocate more memory to the temporary table or… so I could trick MySQL to do group by a hash of the page instead of page itself: mysql> select sum…
Post: InnoDB in self-compiled MySQL 5.1
September 13, 2007 By 9 Comments
… < dump.sql I bet you will not notice all your tables now is MyISAM. Why? Welcome to the MySQL monitor. Commands… | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MyISAM… to see is BIG Warning or even Error that InnoDB table can’t be created instead of calm converting to MyISAM…
