…. One of the downsides to mysqldump is the need to scan the full tables and in turn, load that data into and pollute… CONCAT(‘ALTER TABLE `’, table_name, ‘` IMPORT TABLESPACE;’) AS _ddl FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=’orig’ AND ENGINE=’InnoDB’; EOF For full disclosure, here…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
… 2nd worst possible execution plan after a full table scan. If it is obvious that a full table scan is not good for performance, how much…: the employees table does not fit in memory With the full table scan, the query runs in about 4s. With the full index scan, it runs… case: the employees table fits in memory With the full table scan, the query runs in about 3.3s. With the full index scan, the query…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… this table has 10000 distinct value, so range 1..100 selects about 1% of the table. The times for full table scan vs range scan by… with in temporary table etc. Prefer full table scans to index accesses – For large data sets full table scans are often faster than range scans and other types…
Post: COUNT(*) for Innodb Tables
December 1, 2006 By 32 Comments
…) tables because they would simply read number of rows in the table from stored value. Innodb will however need to perform full table scan or full index scan because it does not have such counter, it also can’t be solved by simple singe counter for Innodb tables…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…’ve used for this benchmark. We have 4 tables: 1) Table with ENUM: CREATE TABLE cities_enum ( id int(10) unsigned NOT NULL… read. For Full Table Scan operation difference often would be larger. It is also interesting to note performance of Innodb tables in this case… note the times themselves – traversing about same amount of rows full table scan performs about 25 times better than accessing rows via index…
Post: Learning about MySQL Table Fragmentation
May 1, 2008 By 20 Comments
… it will do full table scan – running count(*) without where clause may pick to scan some small index instead. If your table is not fragmented… is worth to notice you can see poor sequential scan performance even if table is not logically fragmented and Innodb is reading… both fragmentation issues is the same – OPTIMIZE TABLE tbl – this command recreates the table by writing the new .ibd file (if you…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… did I use this solution compared to others: Innodb Tables – This table is getting much more reads than writes so transactional overhead…, saving IO dramatically. No recovery worries – checking/repairing large MyISAM tables in case of MySQL/System crash is painful and great… to happen. Full table scan for this table would also be slow as it is quite fragmented. Table however is not getting any table scans just single…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…. For example it may use indexes or do a full table scan, or a temporary table may be needed. These are the things that…: 56 Rows_examined: 1113 # QC_Hit: No Full_scan: No Full_join: No Tmp_table: Yes Disk_tmp_table: No # Filesort: Yes Disk_filesort: No…: 1 Rows_examined: 0 # QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Disk_tmp_table: No # Filesort: No Disk_filesort: No…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… of doing full table scan or doing the Union (instead of intersection) on values it gets from the single table. I have reverted table to… though in case of full table scan about 50 times more rows are scanned. This reflects very large performance difference between full table scan and access through…
Post: How number of columns affects performance ?
September 28, 2009 By 12 Comments
… speed so I looked at full table scan in case data fits in OS cache completely. I created 3 tables – First containing single tinyint… note the covering index scan speed is very similar to full table scan speed – this is rather expected as table data is stored in BTREE… much faster than Innodb when it comes to in memory full table scan. P.S Tests were done on MySQL 5.4.2…
