…query in a suboptimal form. Q: Doesn’t the primary key solution for random selection…the row with “=” instead of “>” and be guaranteed …and it would have to match the order of columns in the index. Example: WHERE (last_name, first_name… table to count the movies per… the tables and columns. It turned …
Post: A common problem when optimizing COUNT()
September 20, 2008 By 19 Comments
… to do with COUNT(). This is database-agnostic, not related to MySQL. The problem is when the COUNT() contains a column name, like this: select count(col1) from table; If you know your SQL well, you know COUNT() has two meanings. 1) count… up getting stalled waiting for them to reply and tell me what the query means so I can optimize it. A good…
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
… mysql> tee queries.log Logging to file ‘queries.log’ mysql> use sakila Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select count(*) from sakila…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…SELECT wp_options query, this is basically a unique way of identifying the query and simply implies that this is a SELECT query…Count of number of times this query was executed. Now let’s take a look at the values in the 95% column…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…query would take significantly longer (40+ minutes): mysql> SELECT -> customer_id as `customer_id`, -> SUM(price * quantity) as `total_price`, -> COUNT…column `mview$pk` which is an auto_increment BIGINT surrogate key for the table. For complete refresh views which are ordered with…
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
…_something=1 we should have index on column `has_something` (the column has two values 0 and 1). In reality the right answer…) mysql> select count(name) from testr ignore key (has_something) where has_something=0; 1 row in set (10.62 sec) query with index is still 2 times slower. and this time mysql is going to use index in execution plan: mysql> explain select count(name) from…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…name as TABLE_NAME, i.index_name as INDEX_NAME, i.NON_UNIQUE as NON_UNIQUE, count(*) as COLUMN_CNT, group_concat( i.column_name…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…SELECT non_key_column FROM tbl WHERE key_column=x This query will roughly be evaluated in following steps, without MRR: SELECT key_column, pk_column…and MariaDB 5.5. Handler_mrr_rowid_refills counts…queries with MRR are performing badly. The interesting thing is that though both MariaDB and…
Post: Debugging problems with row based replication
May 6, 2010 By 12 Comments
…SELECT a few times to get some dummy data: insert into repl.t1 select NULL from t1; Query…with: select count(*) from t1; +———-+ | count(*) | …and instead replaces the output with semi-valid SQL statements. I say semi-valid because the statements do not include column names. Column names…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…SELECT) queries with different typical data access patterns: primary key single row lookup, primary key range lookup, same access types for primary key and…Query: SELECT count(id) FROM $tableName WHERE id between %d and %d MyISAM scales good here, because of access only to key column and…
