A simple way to find out duplicate indexes in 5.0.x SELECT * FROM information_schema.statistics WHERE … count(column_name) > 1; It would be better to include ‘index_type’ in GROUP BY clause to more accurate answer. In…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
… query I use for the purpose so I can easily find it later, plus it is quite handy in a way… index size compared to data size often indicates there is a lot of indexes (so it is well possible there are some duplicates, redundant or simply unused indexes among them) or may…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… get information about the physical drives installed; then I can find out exactly what they are, both in terms of size and… default users in the mysql.* tables, running mk-duplicate-key-checker to find redundant indexes, and so on. As with settings and status… desired information about the queries by any means necessary, I find out which are the most likely to give the greatest gains…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… InnoDB: Page may be an index page where index id is 0 25556 InnoDB: (index “PRIMARY” of table “test”.”…OK, 100 rows affected (1.47 sec) Records: 200 Duplicates: 100 Warnings: 0 mysql> insert ignore into test2 select… was OK. Again using binary search you can find out how many rows do you need to skip…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
… it is index covering query now – it does not need to access the data. It turns out it…(a,b) indexes and in the end decides to use neither rather doing full index scan until it finds a=… careful applying index changes from mk-duplicate-key-checker key checker when it comes to redundant indexes. If you…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 41 Comments
… it and so MySQL have to perform table scan to find out. This is also why result is different for the second…); Query OK, 7340032 rows affected (37.15 sec) Records: 7340032 Duplicates: 0 Warnings: 0 mysql> select count(val) from fact where…
Comment: Redundant index is not always bad
August 28, 2007 By 7 Comments
For those who want to find duplicate indexes, check out MySQL Toolkit: http://mysqltoolkit.sourceforge.net/
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
…at Users Conference but I’m just starting find bits of time from processing accumulated backlog. …out ALTERing Innodb tables may take a lot of time. Fast Index creation will allow Innodb to build indexes… probably similar problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+…
Post: FaceBook Search, Search for social networks
August 12, 2007 By 4 Comments
…data which needs to be indexed available for index process. With Sphinx which talks…all objects are public and so filtering out objects you can’t see …are required to avoid massive data duplication for some usages. We also already… Such as finding the goods from the given group or Finding all Males …
Post: Sphinx: Going Beyond full text search
July 23, 2007 By 18 Comments
…just 3 days worth of data. You might point out if we had link_date between X and … The first alternative to this approach was to store duplicate data storing link to “dev.mysql.com/download/…site or forum from the global sphinx index. So in general we find parallel processing using sphinx pretty good …
