… locking databases also acquire gap locks. What is a Phantom Read A Phantom Read happens when in a…19 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 72C… for foreign-key constraint checking or duplicate-key checking. The most important difference between these two options is…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…indexes by insertion. Let’s see if having more secondary indexes in the table makes any difference: mysql> SET expand_fast_index…Records: 4194304 Duplicates: 0 Warnings: 0 mysql> SET expand_fast_index_creation=ON; …keys (the test database contained only the table I used in my previous examples): $ …
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… implement the database in a way that matched with natural set theory. You see in, in set theory there is no possibility of duplicates in sets… in set (5.49 sec) Now, what if I structure my data differently? We can “compress” the table in the database by removing the duplicates… it takes a long time to delete. There is no index on this table. mysql> delete from data where val=16…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… throw at databases Many people assume their application is difficult or somehow different, and…default users in the mysql.* tables, running mk-duplicate-key-checker to find redundant indexes, and …any one of these factors (schema, queries, indexing) in isolation, because they are tightly bound …
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
…- Storage Engine partnership with IBM, “Clash of Database Egos” – Storage Engine developers showcase as one… be able to build indexes in parallel by running sorts in different threads – using multiple cores …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
…indexed available for index process. With Sphinx which talks directly to the MySQL database we can get some 10.000 of documents indexed…different for each users. Two basic approaches one can take is either embedding it in …are required to avoid massive data duplication for some usages. We also already…
Post: MySQL caching methods and tips
April 4, 2011 By 4 Comments
…rate of change in the database tables. This results in unpredictable and therefore, undesirable…Asking the cache for many different pieces of data to … access, since it can be indexed appropriately for your queries. Using … duplicate items are deleted. When this happens, decreasing the counts in …
Post: Missleading Innodb message on recovery
February 15, 2010 By 2 Comments
…be duplicate for describe different issues). Anyway. The customer came to me today having the following message in the…”Could not find first log file name in binary log index file” error message Looking at the …it is stored in the tablespace the code is still there to print it. This database was …
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… examine the differences in size. This will give us some interesting information about how Shard-Query will perform on each database. To do the size comparison, I used the du utility: InnoDB file size on disk: 42GB (with indexes) # du… have any indexes (not even primary keys). Here is the complete InnoDB schema from one shard. The schema is duplicated 20 times…
Post: Converting Character Sets
March 17, 2009 By 16 Comments
…databases to utf8. Googling for “mysql convert charset to utf8″ results in a plethora of sites, each with a slightly different…rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE …and target column, while leaving in place any FULLTEXT indexes. It also requires …
