… you compare the use of subqueries/multiple joins vs. multiple queries (e.g. temp tables)? For … Doesn’t the primary key solution for random selection only work when the IDs for movies … match the order of columns in the index. Example: WHERE (last_name, first_name) = (‘Karwin’, ‘Bill’); Q: …
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…TABLE for MyISAM vs Innodb. I used …select count(distinct c) from antest; +——————-+ | count(distinct c) | +——————-+ | 101 | +——————-+ 1 row in set (0.36 sec) mysql> select… | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 45 Comments
… needed because column can be NULL. Now lets try few more queries: mysql> select count(*) from fact where i explain select count(*) from fact where i select count(val) from fact where i explain select count…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…3 different approaches: ENUM, VARCHAR and tinyint (+joined table) columns. In practice you can also often use 4th …name=’Minnesota’ limit 10000,5 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: s type: const possible_keys: PRIMARY,name key: name…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…-9%. Falcon shows very bad scalabilty. READ_KEY_POINT Query: SELECT name FROM $tableName WHERE country_id = %d In this case Falcon…: SELECT count(id) FROM $tableName WHERE id between %d and %d MyISAM scales good here, because of access only to key column and ‘pread’ syscall is not used. READ_KEY_RANGE Query: SELECT name FROM $tableName WHERE country_id…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…number of tmp tables created in memory vs on-disk, percentage of queries that …`.`wp_options`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT option_name, option_value FROM wp_options WHERE autoload =…selecting every column from all the tables involved in the query, probably selecting only the needed columns…
Post: PBXT benchmarks
April 8, 2007 By 14 Comments
… to ones in benchmark InnoDB vs MyISAM vs Falcon (http://www….SELECT name FROM $tableName WHERE id = %d READ_KEY_POINT Query: SELECT name FROM $tableName WHERE country_id = %d READ_KEY_POINT_LIMIT Query: SELECT name…different the values of accessed column is stored in key. Interesting…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… being used then, after the selected rows are read from table1, the values of indexed columns that will be used to perform…let’s take a look at the status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w…the amount of data is read from disk 2.2G vs 5G. However, there is one number in MariaDB 5.5…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
