… not -for example, if one of the two indexes was unique or a foreign key. This is the output of our….57 sec) Records: 0 Duplicates: 0 Warnings: 0 $ mysqlindexcheck –server=user:pass@localhost test.test # Source on localhost: … connected. $ pt-duplicate…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… queries for a star schema. A star schema presents some unique challenge to the database optimizer. The SSB benchmark consists of… basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size=64G innodb_log_file_size…
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
… or SET columns. The tricky bit is that it’s unique intervals, not actual intervals, so two ENUM columns both having…’,'N’) columns, you can only have 255 ENUM columns with unique values. If you were looking for a very basic formula… REC_MAX_N_FIELDS (1024 – 1) #define REC_MAX_N_USER_FIELDS (REC_MAX_N_FIELDS – DATA_N_SYS_COLS * 2…
Post: Announcing Percona XtraBackup 2.1.1 GA
May 15, 2013 By Leave a Comment
… and Percona Software Repositories. Percona XtraBackup enables backups without blocking user queries, making it ideal for companies with large data sets… open source solution, XtraBackup drives down backup costs while providing unique features for MySQL backup. The new 2.1.1 GA…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… few partitions are one week each. So, even if the user‘s ad hoc query leads to scanning the entire last…”: * If the DELETE is a single row based on a UNIQUE/PRIMARY key and it can prune, the delete involves (1…
Post: Two Types of MySQL Users
May 10, 2010 By 5 Comments
… come across. There are also people here who indeed have unique problems which can’t be solved by conventional problems (and a lot more people think their problems is unique than really have them) who have to be on the… features which are enough for majority of users. Note it was note about cool unique features which MySQL architecture offers but about…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… of time from processing accumulated backlog. The Theme of this Users Conference was surely Storage Engines both looking at number of… asking Heikki about probably since my first days as Innodb user when I found out ALTERing Innodb tables may take a… MyISAM tables by separate phase of building Indexes ? Will be UNIQUE indexes built by sorting in case of ALTER TABLE (the…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… order) the primary key you specify (an auto_increment), a unique key, or one it creates for you. It’s been… implications. Remember that if you don’t create either a unique key or a column with an auto_increment primary key… scanned and returned. SELECT * FROM articles INNER JOIN users ON users.id = articles.user_id AND users.featured = 1 ; > Use Indexes This item was…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
… the two most common here. User Statistics from Percona Server and pt-index-usage. User Statistics User Statistics is an improvement on Percona… TABLE `tpcc`.`stock` DROP KEY `fkey_stock_2`; — type:non-unique If you compare these results with the previous example, there… conversion so there can be some differences. Be cautious with Unique indexes, because maybe they are not used for index lookups…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… 10 or so characters usually are the same). Of course UNIQUE index optimizations can no longer be applied either. A: This… case. Some people would prefer to define second key as UNIQUE this has benefits and drawbacks. Benefit being you can get… more. Would doing a SELECT competition_id, COUNT(user_id) AS user_count FROM user_competition_entry GROUP BY competition_id; be slower…
