… interesting performance optimization, a full index scan (type: index) is according to the documentation the 2nd worst possible execution plan after a full table scan. …, which will translate to a full index scan. Let’s see how the optimizer will execute it: mysql> EXPLAIN SELECT * FROM employees …
Post: MySQL: what read_buffer_size value is optimal ?
September 17, 2007 By 25 Comments
The more I work with MySQL Performance Optimization and Optimization for other applications the better I understand I have to … following query to perform full table scans, with 3 runs and averaged results. MySQL 5.1.21-beta was used for tests. mysql> select count(*) from…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
…TABLE on this table which essentially performs data and indexes scan which will be reflective to large full table scan and index scan…MySQL has about data which will be accessed next – in case of full table scan, index scans…
Post: Guidance for MySQL Optimizer Developers
September 20, 2009 By 4 Comments
… Methods Performance problems can be due to optimizer picking the wrong plan, such as doing full table scan when Index access is better or because MySQL simply does not have execution method to resolve query in optimal way – loose index scan…
Post: Is your MySQL Application having Busy IO by Oracle Measures ?
January 8, 2012 By 1 Comment
…traditional MySQL as MySQL optimizer with its nested loops joins does not favor sequential scans even for analytic queries executions. Also lack of parallel query execution makes limits how much IO full table scan… get a lot better performance with multiple MySQL instances on powerful storage. …
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… 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 index: mysql…disk bound workloads. There are certain optimizations in works which would improve performance of index accesses/index scans. For example retrieving index values…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
… typical way sorting is used in practice. I tested full table scan of the table completes in 0.22 seconds giving us about 4… though MySQL does not optimize “get top N sorted rows” very well it takes just 2.5 times longer than full table scan to get… gives quite expected performance benefit, now we’re just 2 times slower than table scan of the query and considering table size was about…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
Running EXPLAIN for problematic queries is very powerful tool for MySQL Performance optimization. If you’ve been using this tool a lot you… which stage MySQL actually perform the sort or creates temporary table and so how much rows will be stored to temporary table. Sometimes it… rows to be scanned is shown but it does not tell much about cost of the operation – full table scan of table in memory may…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…MySQL 5.6 server, rebooted and verified again. Same issue. This was very different from MySQL 5.5.30, which performs…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… 0 | 3 | 3 | | 8 | optimizing | 0.000048 | 0….
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…table_definitionsblogpercona.commanual Opened_tablesblogpercona.commanual optimizer_prune_levelblogpercona.commanual optimizer_search_depthblogpercona.commanual optimizer_switchblogpercona.commanual partitionblogpercona.commanual performance_schemablogpercona.commanual Performance…
