…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 100 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 27 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…
Comment: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
…on the “Duplicate keys on clustered index” section: Consider a chat room app, we have the messages table… performance of the following query. (Confirmed on MySQL 5.1 and …a room, you could stop scanning index just reading 100 entries, otherwise you’d have to read the entire index of (room_id) to find…
Comment: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
October 26, 2011 By 15 Comments
…a trigger on an innodb table that “dumps” any updates to the table to a myisam table holding a log of updates. The update table…ON DUPLICATE KEY UPDATE to achieve that. The problem is that I find many locked queries in the db as soon as there’s a…
