… which restricts how many rows will be examined MySQL will still print full number. Here is example: mysql> explain select * from lt limit… index | +—-+————-+——-+——-+—————+———+———+——+——+————-+ 1 row in set (0.00 sec) This statement obviously will not scan more than 10 rows but EXPLAIN shows it will scan over 1000. This is not the problem if you examine your queries manually…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…] @ localhost [] # Query_time: 2.365434 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 655360 use test; SET timestamp=1325145746; select count(*) from… # Query_time: 0.117904 Lock_time: 0.002886 Rows_sent: 1 Rows_examined: 655360 Rows_affected: 0 Rows_read: 655361 # Bytes_sent: 68 Tmp_tables…’\G # SHOW CREATE TABLE `wp_blog_one `.`wp_options`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT option_name, option_value FROM wp_options…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…row affected (0.00 sec) When MySQL executes the new view it will be able to restrict the rows examined…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… # Query_time: 9.031233 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 0 # Bytes_sent: 213 Tmp_tables… where a.id=5 and b.id=a.k; mysql> explain select * from sbtest a,sbtest b where a.id=5… # Query_time: 5.391612 Lock_time: 0.000121 Rows_sent: 2 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 2 # Bytes_sent: 75 Tmp_tables…
Post: Slow Query Log analyzes tools
September 6, 2006 By 84 Comments
…] @ localhost [] # Query_time: 0.000652 Lock_time: 0.000109 Rows_sent: 1 Rows_examined: 1 SELECT id FROM users WHERE name=’baouong’; # User…] @ localhost [] # Query_time: 0.001943 Lock_time: 0.000145 Rows_sent: 0 Rows_examined: 0 INSERT IGNORE INTO articles (id, body,)VALUES(3558268… have created full log – parse it and check queries using EXPLAIN starting from most impacted onces. After you’ve implemented changes…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
… rows in set (0.00 sec) and extended stats: # Query_time: 17.765369 Lock_time: 0.000137 Rows_sent: 1 Rows_examined: 11569733 Rows_affected: 0 Rows_read: 11569733 # InnoDB_IO_r_ops: 38530…_id” inserted not in order. +—————————+ | count(distinct username) | +—————————+ | 5903053 | +—————————+ 1 row in set (2 min 8.92 sec) mysql> SHOW STATUS…
Post: When EXPLAIN can be misleading
November 12, 2006 By 1 Comment
… see with people using EXPLAIN is trusting it too much, ie assuming if number of rows is reported by EXPLAIN is large query… rows: 49280 Extra: Using index 1 row in set (0.00 sec) This EXPLAIN estimates there would be almost 50.000 of rows scanned… badly. Too bad –min-examined-row-limit is not yet implemented 
EXPLAIN would also return misleading number of rows for queries of the type…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… here had approximately 4.5M rows, and an EXPLAIN produced the following execution plan: *************************** 1. row *************************** id: 1 select_type: SIMPLE …day and we looked at the Cacti reports, the “InnoDB rows examined” graph had dropped several orders of magnitude and CPU …
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
….3s! # Query_time: 0.503016 Lock_time: 0.000048 Rows_sent: 56 Rows_examined: 1113 LOGGING OF THE REPLICATED STATEMENTS Normally MySQL will… are the things that you can usually see by running EXPLAIN on the query. The patch will now allow you to…: 4 # Query_time: 0.503016 Lock_time: 0.000048 Rows_sent: 56 Rows_examined: 1113 # QC_Hit: No Full_scan: No Full_join…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
… correct index and here I’m going to explain three of them: A) INNODB_SYS_INDEXES Percona …one will connect to the database in order to examine the table and create the table_defs.h content…..recovery you can find lot of deleted rows, not only rows deleted accidentally. You should manually find the …
