…_wait: 0.382176 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.067538 # InnoDB_pages_distinct: 20 InnoDB_IO_r_ops… queue or inside that queue waiting for execution. InnoDB_pages_distinct tells the number of unique pages accessed by the query….382176 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.067538 # InnoDB_pages_distinct: 20 SET timestamp=1193841780; SELECT DISTINCT c from sbtest…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
… ( select * from ( select 1 as a ) as b ) as c; You can have many of those per query. Sorting and grouping and DISTINCT… OK, 0 rows affected (2.34 sec) mysql> select get_lock(@a, 1); +—————–+ | get_lock(@a, 1) | +—————–+ | 1 | +—————–+ 1 row in set (3…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
… ? Let me show. The query in question is (Q1): SELECT count(distinct username) FROM tracker where TIME_ID >= ’2009-07-20 00… (0.00 sec) and extended stats: # Query_time: 17.765369 Lock_time: 0.000137 Rows_sent: 1 Rows_examined: 11569733 Rows…_wait: 0.204893 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 35584 You see that time…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…_time: 2.365434 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 655360 use test; SET timestamp=1325145746; select count(*) from auto….001414 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 973 SET timestamp=1325146286; select count(*) from auto… shown here that every query is reading approximately 38.53 distinct InnoDB pages (meaning 616.48K of data), however, 95% of…
Comment: Why MySQL could be slow with large tables ?
December 4, 2008 By 141 Comments
…: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 150000 SELECT COUNT(DISTINCT(u.unit_id)) FROM unit u RIGHT JOIN (SELECT up1.unit… is fine. No problem. Now, I have to make a selection from the unit_param table which, for which I need…: 1138 Lock_time: 0 Rows_sent: 0 Rows_examined: 2271979789 SELECT COUNT(DISTINCT(u.unit_id)) FROM unit u RIGHT JOIN (SELECT up1.unit…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… 0 # Query_time: 9.031233 Lock_time: 0.000086 Rows_…id=a.id; +—-+————-+——-+——–+—————+———+———+————-+——+——-+ | id | select_type | table | type | possible_keys | key |…rows query sends after group by, distinct and aggregate functions are removed (…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, ‘.’, P.COLUMN_NAME, ‘ AS… and detect orphans early so you can correct them. 1Innodb locking and Foreign Keys 2Instrumentation and the cost of Foreign Keys
