… the shared lock on the latch protecting the AHI Drop Table in Oracle MySQL >= 5.5.23 The main function that is… mutex in Oracle MySQL, this mutex is not used by “Lazy Drop Table” implementation. While the MySQL’s version of drop table relies… certainly like the idea of not scanning the LRU list at all during the DROP TABLE, and I think this is something…
Post: DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL
June 22, 2012 By 33 Comments
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… is different in some respect and what works well for Oracle,MS SQL, PostgreSQL may not work well for MySQL and… 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… 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…
Post: Is your MySQL Application having Busy IO by Oracle Measures ?
January 8, 2012 By 1 Comment
… this post I wanted to mention what James calls “Busy” Oracle database application when it comes to IO consumption: It is… favor sequential scans even for analytic queries executions. Also lack of parallel query execution makes limits how much IO full table scan queries… is to continue to be significant difference between MySQL and Oracle deployments, yet as MySQL is getting better and better handling…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
…, I believe different applications work best on different database brands. Oracle‘s main database has a very competent team working on…: We have a fairly large Innodb table (150GB) that is showing poor performance for full table scans. using O_DIRECT, we are seeing… 64 pages == 1 MB. Then a full table scan would be fast. Maybe the table has become fragmented? Or maybe you have not…
Post: Indexes in MySQL
June 2, 2006 By 29 Comments
… a full table scan is preferable than an index access. As far as I know Oracle alway chooses a full table scan if selectivity over 25%. What with MySQL: mysql> EXPLAIN SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1; +—-+————-+——-+——+—————+——+———+——-+——-+————-+ | id | select_type | table…
Comment: Paul McCullagh answers your questions about PBXT
November 27, 2009 By 2 Comments
…, that table is going to be scattered throughout the sum total of all log segments. Which means that any full table scan is really going to be a full database scan, or alternately, a full index scan followed by nested… on something like INNODB or Oracle where the database engine tries to keep records from the same table in physically co-located…
Post: PBXT benchmarks
April 8, 2007 By 14 Comments
… able to run benchmarks. Description of benchmark: We used table “normal” table structure which corresponds to typical structure you would see in… range lookup, same access types for primary key and full table scans. To highlight different properties of storage engines we tested ranges… InnoDB team should solve this problem, I do not know Oracle plans about. It is also interesting to check PBXT performance…
Comment: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 12, 2013 By 7 Comments
…. In the OLTP case we expect occasional queries that do table scans but generally it’s best to prevent them from flushing… what works best. Views are my own, for an official Oracle view consult a PR person. James Day, MySQL Senior Principal…
Comment: Indexes in MySQL
June 3, 2006 By 29 Comments
According to the oreilly “oracle sql tuning” pocket guide oracle moves to a table scan if it expects to read more than 12% of the rows. supposedly mysql does so at 30%.
Comment: Indexes in MySQL
June 4, 2006 By 29 Comments
Well, maybe for Oracle it is 12%, I don’t know for sure, but as Peter said it is not always good to have the hard values, there can be a lot of cases. Regarding MySQL: MySQL does not use the selectivity calculation, so it’s impossible to say when MySQL prefers table scan.
