… workload is cached or not different selectivity might show benefit from using indexes. In fact even MySQL optimizer currently does not take it into… types of index lookups. Even if you look at 1% or rows or less full table scan may be faster. Avoid joins to large tables Joining of…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…not much difference if 20 or 30 bytes are being read. For Full Table Scan…join query performance is 30% lower. Also note the times themselves – traversing about same amount of rows full table scan performs about 25 times better than accessing rows via index…
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
…not all row accessed are created equal (such as full table scan row accesses may be much faster than random index…If you’re joining multiple tables or if you …full table scan is performed only rows with COUNTRY=’USA’ are used in results set which clearly puts query as optimization candidate. It is not…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…not used by default, the file system cache will give better read performance after first run (but not…full table scans. In the classic LRU, a full table scan pushes out important hot pages from the buffer pool often for an infrequent scan…table_name, index…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… random scans of the PK to fetch the columns that are not part of the secondary key. Even though MySQL 5.6 uses… non-indexed column. They also provide big improvement in query response time when you are joining tables with no indexes on the join condition (Full Join). The best performance with Hash Join can be achieved when the left table can fit…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… optimizer to use the indexes to join rows together, potentially preventing a table scan. The trick is to know what kind of JOIN to use so that… to fix have number of indexing missing which > requires queries to do full table scans. Funny enough this is often > not the problem in the…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…not use indexes, even if it is quite fast: mysql> explain select * from test order by k limit 5; +—-+————-+——-+——-+—————+——+———+——+———+——-+ | id | select_type | table…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
…scanned is shown but it does not tell much about cost of the operation – full table scan of table…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… it may use indexes or do a full table scan, or a temporary table may be …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…c; or if InnoDB was not used: # Time: 071031 20:03:16 # User@Host…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
…full table scans and index scans more efficient. I can’t wait for ability to create physically sorted indexes with Innodb (with index…index. Can you describe a real-world use case where it would be useful to have a foreign key not… and ORDERLINE tables are joined to form a single table), it might …
