…explain select * from article where article_id=”10″; +—-+————-+———+——+—————+———+———+——-+——+————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows…be done as it would result in wrong result for some queries. The …
Post: Beware of MySQL Data Truncation
February 7, 2009 By 4 Comments
… the wrong article (…explain select count(*) from article_comment where article_id=4300000000; +—-+————-+—————–+——+—————+————+———+——-+——+————————–+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows…
Post: Neat tricks for the MySQL command-line pager
June 23, 2008 By 13 Comments
…’s not really a limitation.(Edit: I’m wrong! You can. See Giuseppe’s comment below.) For…here? mysql> pager mk-visual-explain PAGER set to ‘mk-visual-explain‘ mysql> explain select * from sakila.film …key_len 2 | ref sakila.film.film_id | rows 2 +- Table scan rows 1022 +- Table table film possible_…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…pages_read 120548 123868 100551 123592 100566 Innodb_rows_read 799239 914146 912318 914146 912318 Select_…incremented when index lookup is performed. As I explained at the start of the post that traditional…thing when benchmarking ICP, the optimizer made a wrong choice. It looks like that there is …
Comment: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 29, 2007 By 57 Comments
IMHO comparison is not true, in “EXPLAIN SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = … on index without access physical data. But in “EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 999 ORDER BY… one with SQL_CALC_FOUND_ROWS just stores number of records before restricting rows. If I am wrong, please clarify it for me…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…_time: 9.031233 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 0 # Bytes_sent: 213 Tmp_tables… at all – you would be wrong. The index lookup is being perform but as only actual rows which are found and returned… where a.id=5 and b.id=a.k; mysql> explain select * from sbtest a,sbtest b where a.id=5…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
…the index MySQL trying to use from explain, such as if you have some smart…: 0 mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `…generated index names you may drop the wrong indexes as part of upgrade process just…
Comment: ORDER BY ... LIMIT Performance Optimization
December 29, 2008 By 100 Comments
…A | 20971520 | NULL | NULL | | BTREE | | +——-+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ 1 row in set (0.00 sec) And finally: > explain select id from c limit …id on SSD hard drive What I am doing wrong?…
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
…_date`) ) ENGINE=InnoDB and then inserted a few bogus rows with the 0024-06-21 date. I then proceeded… using date windowing for two-digit dates (this would explain 2024-06-21), but on the other hand, it… DATETIME values, and now it’s definitely returning the wrong answer. Always range check your input…
Post: Measuring the amount of writes in InnoDB redo logs
October 8, 2012 By 4 Comments
…GB/hour (almost a 3x difference!) Something was obviously wrong in our understanding of how to measure the …if you measure variations over 60s, this will not explain a 3x difference with the LSN. It also means…write workload (a mono-threaded script that inserts one row at a time in a table, as fast …
