…does not require you to specify name of the index if you’re running ALTER TABLE statement – it is optional. Though what…Duplicate key name ‘idx_i’ Adding indexes without name specified is very common reason why systems tend to get duplicate indexes…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… notes and generally keeping in touch with what the lead consultant does. We want two sets of eyes …in reply. If the client doesn’t understand what I’m doing, I’ve done something wrong. Clients need… in the mysql.* tables, running mk-duplicate-key-checker to find redundant indexes, and so on. As with …
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…0 Duplicates: 0 Warnings: 0 Let’s trigger a table rebuild by adding a new column and see what…method, because it does not have to do random disk seeks to fetch secondary index pages to the buffer ….08 sec) Records: 4194304 Duplicates: 0 Warnings: 0 mysql> SET expand_fast_index_creation=ON; Query OK, 0…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…indexes on (a) and (a,b) at the same time. This is something what suppose to work but it well often does… and (a,b) indexes and in the end decides to use neither rather doing full index scan until… applying index changes from mk-duplicate-key-checker key checker when it comes to redundant indexes. If …
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
…what if I structure my data differently? We can “compress” the table in the database by removing the duplicates…decompression, because a mathematical transformation does not have to be decompressed….This is a way to do it without using the “search set…to delete. There is no index on this table. mysql> …
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
…index “PRIMARY” of table “test”.”test”) InnoDB: Database page corruption on disk or a failed and crash with assertion failure. So what…Duplicates: 0 Warnings: 0 Now you got all your data in MyISAM table so all you have to do…taking place and as MySQL crashes it does not store all data it could recover…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…index stats are a bit off, so what ? What…Duplicates: 0 Warnings: 0 mysql> show index from antest_innodb; +—————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
…of MySQL Performance problems. Here is what you need to know about ORDER BY…order index can’t be used. Sometimes it means breaking normalization and duplicating column(…it is enough. If you do not do anything else make sure you …query log as query which does not use indexes, even if it is quite fast…
Post: UNION vs UNION ALL Performance
October 5, 2007 By 13 Comments
…!=5″ clause – this is what allows us to ensure we do not have duplicate rows in result set …The first informed guess would be – because UNION ALL does not need to use temporary table to …temporary table with index (to skip duplicate rows) while UNION ALL will create table without such index. This …
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 … what is required is simple boolean full text search. Also FaceBook search does… even worse with permission check – what if you do not have access to …are required to avoid massive data duplication for some usages. We also …
