… further index parts. If you have A BETWEEN 5 AND 10 AND B=5 for the same index MySQL will use the index… but it will only use A prefix for row lookups and scan whole… page boundary) so doing index range scan you typically get many index entries from the same page but the rows itself can be scattered…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… you may have serious issues because such tables do not get any indexes. Let me now show couple of examples. Assume we…: index possible_keys: NULL key: PRIMARY key_len: 8 ref: NULL rows: 1792695 Extra: Using index 2 rows in set (0.96 sec) mysql> select…: 8 ref: NULL rows: 1792695 Extra: Using index 4 rows in set (1.91 sec) As you can see we get 2 derived tables…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… disk (both data and index) you would need 2+ IOs to retrieve the row which means you get about 100 rows/sec. Note multiple… scan by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select count…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… not look too bad. MySQL is using three different indexes to search approximately 8100 out of 4.5M rows to return the data… the context of index merge, it means that MySQL is really looking at a search space closer to 8.5M rows, not 8100… sub-selects. In cases where the optimizer just can’t get things right, and you, as the developer or DBA, know…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
… cur_open_with_no_init_func,row_sel_get_clust_rec_for_mysql,row_search_for_mysql,ha_innobase::index_read,handler::read_ra nge_first,handler::read_multi_range_next,QUICK_RANGE_SELECT::get_next… cur_open_with_no_init_func,row_sel_get_clust_rec_for_mysql,row_search_for_mysql,ha_innobase::index_read,handler::read_ra nge_first…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
…indexes – you can’t easily see what is the index MySQL trying to use from explain, such as if you have some smart index…indexes indexes can get different names which makes scripted upgrade and downgrade processes complicated. If you use auto generated index…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
… get if we force the optimizer to use the index? mysql> EXPLAIN SELECT * FROM employees FORCE INDEX(idx_first) ORDER BY first_name\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees type: index…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… ? If you can’t get fancy graph quickly you can…mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1 row…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
…-record.html You can also get the table row format from the Information Schema: mysql (information_schema) > SELECT ROW_FORMAT from TABLES WHERE TABLE… to find the different indexes and types. mysql (information_schema) > select i.INDEX_ID, i.NAME FROM INNODB_SYS_INDEXES as i INNER JOIN…
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
… we are able to get from performance schema tables. 1. table_io_waits_summary_by_table mysql> select * from table… statistic per index: mysql> select * from table_io_waits_summary_by_index_usage where OBJECT_NAME=’sbtest55′G *************************** 1. row *************************** OBJECT_TYPE: …
