…take a look into solving this problem. You will be thanked by a lot of people running MySQL…count to see how many prepared statements are allocated on server and Com_stmt_send_long_data to see whenever sending long… mysql> select * from information…TIME: NULL UPDATE_TIME: NULL …
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 17 Comments
… let’s see how long a table scan with no WHERE clause takes: mysql> select sql_no_cache count(*) from t; +———-+ | count(*) | +———-+ | 8388608 | +———-+ 1 row… WHERE clause, mysql> select count(*) from t where a = current_date and left(a, 10) = ’2008-10-29′; The average time is 9.39…
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
…a lot. Warming up a system so it’s actually responsive can take hours. As a…a long time. To measure how much this really matters, I did some tests for a… following query: select table_schema, table_name, page_type, count(*) from information… used — it could be MySQL Proxy or a TCP sniffer or anything …
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…in MySQL. This is very helpful for measurement as otherwise we might not catch some long … # Query_time: 2.365434 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 655360 use test; SET timestamp=1325145746; select count…shows the Count of number of times this query was executed. Now let’s take a look at…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… | +————————+———+ 7 rows in set (0.00 sec) So how long does it take: mysql> select count(*) cnt,c from gt group by c order by… repeated the run a few times with very similar results. OK so what if we set key buffer to 128M ? mysql> show global… (0.00 sec) mysql> set tmp_table_size=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) cnt,c from gt…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…NAME, i.NON_UNIQUE as NON_UNIQUE, count(*) as COLUMN_CNT, group_concat( i….of our SELECT. I can now select my droppable (unused) indexes from this view: mysql> select * from droppable_indexes; …a large table can take quite some time. All the normal tricks and tips about doing a long blocking…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…time for MariaDB is still a little higher by a couple of seconds, when compared to MySQL 5.6. Now let’s take a…MySQL 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts how many times…take a look at what are the good and bad query execution plans. a. Bad Plan id select…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…Time | State | Info | Rows_sent | Rows_examined | Rows_read | +——-+——+———–+———-+———+——+————————-+—————————–+———–+—————+———–+ | 10219 | root | localhost | dumptest | Query | 324 | Sending data | select count…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…which take less than 1s. Now long_query_time…time: 0.503016 Lock_time: 0.000048 Rows_sent: 56 Rows_examined: 1113 LOGGING OF THE REPLICATED STATEMENTS Normally MySQL…a small hash array representing the entire buffer pool, because it could take a… available for this query SELECT COUNT(1) FROM t2; …
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…count the data dictionary. This can take many gigabytes on servers with a…mysql> set @a := repeat(‘a‘, 1024 * 1024 * 100); Query OK, 0 rows affected (2.34 sec) mysql> select get_lock(@a, 1); +—————–+ | get_lock(@a…a 100M user variable and a 100M lock key. Note how long…
