…duplicate indexes so its the time to speak about these a bit. So what is duplicate index ? This is when table has multiple indexes defined on…query to run as index covered query (retrieve all columns from the index) – such indexes may become way too long to be efficiently used by other queries…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…queries a lot, right ? mysql> alter table idxitest drop key a,add key(a,b); Query OK, 0 rows affected (24.84 sec) Records: 0 Duplicates…indexes on (a) and (a,b) at the same time. This is something what suppose to work but it well often does not: CREATE TABLE…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… some INFORMATION_SCHEMA queries to help me find the biggest tables and so forth. If the server is heavily loaded or has a lot of….* tables, running mk-duplicate-key-checker to find redundant indexes, and so on. As with settings and status, these set the stage for bigger gains to come…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
…table and we need to somehow skip over it. To do it we would need to find…Query OK, 573140 rows affected (7.79 sec) Records: 573140 Duplicates: 0 Warnings: 0 So we tried to…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…query/run_worker to contain the following: #!/bin/bash while [ 1 ] do ./worker >> /dev/null 2>&1 < /dev/null done; Where to find…indexes (not even primary keys). Here is the complete InnoDB schema from one shard. The schema is duplicated…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
…to drop duplicate index with same name. === Error === alter table emp drop index fk_deptid;ERROR 1553 (HY000): Cannot drop index ‘fk_deptid’: needed in a…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…table tool a little bit of time to run (even for this very small table) this is because ANALYZE does index scans to find…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
…table may be scanned to find 10 rows. So index on (category_id, date_created) will be better idea. Lets take a look at a…index can be used to satisfy order by, index however will not be helpful to check c=5 (unless it is index covered query). Index on (c,a…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted to post little query I… of indexes. Large index size compared to data size often indicates there is a lot of indexes (so it is well possible there are some duplicates…
Post: Sphinx: Going Beyond full text search
July 23, 2007 By 18 Comments
…to mention general load on server such batch processing would put. The first alternative to this approach was to store duplicate data storing link to…to retrieve all posts from given web site or forum from the global sphinx index. So in general we find…
