… looks at stats from different indexes independently not trying to reconcile them some way. Because index is longer query execution takes a bit… multiple column indexes. This is in case you’re using OR between the columns. In this case the combined index is useless and… columns – single column indexes are required for index merge to work and combined indexes can’t be used for such queries. P.S The…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
… +—-+————-+———+——-+—————+————+———+——+——+————-+ 1 row in set (0.00 sec) The index on query 3 had high cardinality but should not be…An automated search for low cardinality indexes wouldn’t have revealed it’s uselessness. For range scans, it…
Post: Gathering queries from a server with Maatkit and tcpdump
July 1, 2009 By 23 Comments
…, rather than watching the network traffic directly. This might sound useless, but it’s not. It means you can go tcpdump… queries that say No_index_used? If we increase –limit a bit, we can see # 0% No_good_index_used # 100% No_index_used # Query_time distribution … snip … show databases\G I did not know that SHOW DATABASES sets the “no index…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…::DBI, which does $dbh->ping before each and every query. Why is Ping useless? Well, it is intended to tell you whether… big gains. Schema, query, and index optimization Analyzing and optimizing a server’s table and index structures, and the queries that run against them… is off the table. Queries can be rewritten. Indexes and data types and table structures can be changed. Queries can be broken into…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
… the indexes on the referencing columns are quite useless. Actually they’re more than useless they’re detrimental. Not only do these indexes slow… rather overwhelmed by the number of indexes and tables to choose from for moderately complex queries and selects an extraordinarily inefficient plan…
Comment: Using UNION to implement loose index scan in MySQL
August 12, 2006 By 15 Comments
…, the condition on zip is useless in this step; 2. For each rows returned by the index scan above, retreive that row… executing the “age BETWEEN 18 AND 22 AND” query is: 1. Perform an index range scan of KEY(age, zip), start with…, the condition on zip is useless in this step; 2. For each rows returned by the index scan above, retreive that row…
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
…Page may be an index page where index id is 0 25556 InnoDB: (index “PRIMARY” of table… CHECK TABLE in INNODB is pretty useless. For my manually corrupted table I am…HY000): Lost connection to MySQL server during query mysql> check table test; +———–+——-+———-+———-+ | Table | Op | …
Comment: The Optimization That (Often) Isn't: Index Merge Intersection
December 15, 2012 By 6 Comments
… original query and the conditions on those columns, we can easily see that the indexes on status and parent_id are effectively useless, particularly when compared to the third index on user_type…-based) than my index hint query which has an estimated search space of about 32K. AFAIK, index hints (or multi-column indexes, when it…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
…standard web traffic is drastically impacted when some heavy queries are ran in background or backup is done …buffer_pool_pages_index as p using(index_id) where t.schema=’test’ group by i.index_id \G INDEX_NAME …it is configured in a way it is quite useless. The blocks are indeed first placed in the …
Comment: MySQL Partitioning - can save you or kill you
June 29, 2012 By 45 Comments
… the index; the only clue of this is Key_len in the EXPLAIN. EXPLAIN _usually_ reflects the query plan for the query, but… knowledgeable. USE/FORCE INDEX — Do these only as a last resort. As the data changes, and the query plan needs to change… allow swapping; that will kill MySQL performance. “50 users” — a useless metric without knowing what they are doing. 1000 light users…
