… case. MySQL indexes are (with few exceptions) BTREE indexes – this index type is very good to be able to quickly lookup the data on… use that value to lookup the full row data corresponding to the given index entry. So what choices does MySQL have if you… columns in where clause. Index merge does helps performance but it is far from performance of combined index in this case. In…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…the right operand and for each row scanned performing a lookup in the hash table by using values of the…method=O_DIRECT query_cache_size=0 query_cache_type=0 MySQL 5.6.5 Configuration innodb_file_per_…that reduces the size of the joining data set. And hash join performs even badly and takes 7x more …
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
… differently. For uncompressed index blocks MySQL can do binary search inside the…character column type but not for numerical and other column types. Also… practice here. With DEFAULT configuration lookups for integer keys are fast … does not always improve performance even if data fits in memory. Take…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…queries with different typical data access patterns: primary key single row lookup, primary key range lookup, same access types for primary key and … performance parameters may vary a lot in next releases) Compilation parameters: For MyISAM / InnoDB ./configure –prefix=/usr/local/mysqltest/mysql- –…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…performance counters, statistics, information about transaction processing and all kinds of other things. In MySQL 5 number of Innodb performance…mysql tables in use 1, locked 0 MySQL thread id 8079, query id 728899 localhost root Sending data…lookups and number of non-hash index lookups… types of…
Post: PBXT benchmarks
April 8, 2007 By 14 Comments
… different typical data access patterns: primary key single row lookup, primary key range lookup, same access types for primary … : 1024 KB 4GB of RAM MySQL version We used MySQL 5.1.16-beta sources for MyISAM …, but I hope PBXT team will improve performance to make the difference a bit better. …
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
…performance implications. On the other hand, if we determine that 99% of the traffic are these single row PK lookups…type=uniform –max-time=300 –mysql-user=root –mysql-socket=/var/lib/mysql/mysql…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
…performance. Joins are expensive and > you can often do much better with denormalized data. You may wish > to use denormalized data as cached lookup table however so you do > not have all these problems with loosing data etc. Read more in my > Why MySQL…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
… id: 1 select_type: SIMPLE table: t1 type: eq_ref…data distribution but good enough for performance gotcha illustration purposes. The benchmark shows following results for MyISAM using MySQL…performs much better in this case (not surprisingly as there is a lot of primary key lookups…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… query to read data for both tables….table and join query performed on INT fields: CREATE TABLE `intjoin`…row *************************** id: 1 select_type: SIMPLE table: t2 type: ref possible_keys:… so random key lookups become significantly slower. …shorten some internal buffers MySQL has to allocate …
