…access. This applies to anything else – if you can avoid dynamic page generation at all…count amount of rows in the table (no joking). Sometimes you would see 100 stories selected so one random of…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…in memory, because there is no extra cost for memory access at random locations versus memory access at sequential locations. In…query execution plans. a. Bad Plan id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE nation ALL…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…all the pages. The inaccuracy grows with the number of pages accessed by a query as there is more chance for hash collisions. In…table: No Disk_tmp_table: No # Filesort: No Disk_filesort: No Merge_passes: 0 # No InnoDB statistics available for this query SELECT COUNT…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
…tables in use 2, locked 0 MySQL thread id 53038, OS thread handle 0x7ff759b22700, query id 3918786 localhost root Sending data select count…tables on MySQL level will prevent tables from being accessed and will not show up in…
Post: Innodb row size limitation
April 7, 2011 By 12 Comments
…least 8448 bytes for local storage, not counting other fixed length columns. This exceeds …queries that access all columns in the original table. It also requires you to change the way you insert/modify data, as more tables…as part of a row-read, they don’t have to be touched at all. Of course, these…
Post: Air traffic queries in LucidDB
October 26, 2009 By 20 Comments
…table the actual size of data is 4.5GB, the 9.3GB is size of…Queries time Let me put list of queries and times for all systems. – Lame query “count start” LucidDB: SELECT count(*) FROM otp.”ontime”; 1 row…access to MySQL via JDBC driver) just in SQL queries (compare with single LOAD DATA statement in …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…of lines in the order_lines table, three orders of magnitude more quickly than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…in which a hash table is created over the join attributes and rows of…row scanned performing a lookup in the hash table by using values of the columns participating in the equijoin condition. The hash table is accessed…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…rows, thread declared inside InnoDB 166 mysql tables in use 1, locked 0 MySQL thread id 8078, query…in microseconds. mysql tables in use 1, locked 0 is number of tables used by transaction in question (meaning it was accessed) and number of tables…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…accesses) will not use index cardinality data at all but will estimate number of rows during query execution. I typically look at ANALYZE TABLE…
