June 19, 2013

Post: Identifying the load with the help of pt-query-digest and Percona Server

December 29, 2011 By 3 Comments

…with all the data points you need to attack the right query in the right way. But vanilla MySQL … # Tmp table 4% yes, 95% no # Tmp table on 2% yes, 97% no It tells you that a total of 20….the longest sum of run time, query ranked #1. The first row in the table above shows the Count of number of times …

Filed Under: Insight for DBAs, MySQL

Post: Troubleshooting MySQL Memory Usage

March 21, 2012 By 5 Comments

…tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1 row…

Filed Under: Insight for DBAs, MySQL

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

… the total sales for all customers from the dashboard_customer_sales table. If …of magnitude more quickly than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select sum(total…

Filed Under: Insight for DBAs, MySQL

Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By 7 Comments

…all of the queries in the benchmark. I got some strange results, however, which showed MySQL…mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select table_name, index_name, count…TOTAL: 186940 LRU_IO_CURRENT: 0 UNCOMPRESS_TOTAL: 0 UNCOMPRESS_CURRENT: 0 1 row…

Filed Under: MySQL Tagged With: , , , , ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… you drop all of the FT indexes for a given table, you’re still left with all of the other …total_word_count | | | optimize_start_time | | | optimize_end_time | | | stopword_table_name | | | use_stopword | 1 | | table_state | 0 | +—————————+———+ 10 rows in set (0.02 sec) mysql…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Quickly finding unused indexes (and estimating their size)

December 5, 2012 By 2 Comments

…tables.  Then I simply created a view of a UNION DISTINCT of those two tables: mysql> create view used_indexes as (select TABLE_SCHEMA, TABLE…all_indexes.table_schema as table_schema, all_indexes.table_name as table_name, all_indexes.index_name as index_name, ROUND(all_indexes.index_total…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?

August 28, 2007 By 57 Comments

…- to get a result set, another – to count total number of rows. In this post I’ll try to check…% 1000; mysql_query(“INSERT INTO count_test SET b=$b, c=ROUND(RAND()*10), d=MD5($i)”); } First of all, let’s try to perform some query on this table using indexed column b in where clause: mysql…

Filed Under: Benchmarks, Insight for Developers Tagged With:

Post: When EXPLAIN estimates can go wrong!

October 13, 2011 By 7 Comments

…count of rows: mysql [localhost] {msandbox} (foo2) > select count(*) from test_estimate where type=6 \G *************************** 1. row *************************** count…

Filed Under: MySQL

Post: Shard-Query turbo charges Infobright community edition (ICE)

May 6, 2011 By 23 Comments

…table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct UniqueCarrier): 29 1 row… in parallel over all of these machines. With …of data (23GB raw data, compressed to about 2GB total…

Filed Under: MySQL

Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1

October 15, 2009 By 16 Comments

…row in another table based on a text field ( called email address ). Seems simple enough right?  My two tables each contain 5M rows of…or roughly with 9% of the total data in memory. let…All to often people force themselves into using a database like MySQL…, or even the rare count. But for the most…

Filed Under: Benchmarks, Cloud and NoSQL, MySQL Tagged With: , ,
« Older Posts