…) In previous versions of MySQL, you can create two indexes with the same columns (in the same order) and the server will…, as the indexes are different. But as you may know, we can use the second index not only for filtering on both columns, but… with a prefix of the clustered index # Key definitions: # KEY `redundant` (`col2`,`id`) # PRIMARY KEY (`id`), # Column types: # `col2` int(11) default…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
…basis which has given me time to explore new tools using their environments. One tool that I am finding very …relationships between columns/tables based on names, tables without indexes, columns flagged ‘nullable’ and ‘must be unique’ (woops!), single column tables, incrementing column names in…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
…. However, if I were using the tuple syntax to look up values against multiple columns in a compound index, then the order would matter, and it would have to match the order of columns in the index… I did not use the STRAIGHT_JOIN, the query optimizer reordered the tables. It seemed to prefer an index-scan of 7…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…. We will need four of them: SYS_TABLES SYS_INDEXES SYS_COLUMNS SYS_FIELDS SYS_TABLES Here InnoDB keeps correspondence between human…/lib/mysql/ibdata1 3. Recover SYS_TABLES, SYS_INDEXES, SYS_COLUMNS and SYS_FIELDS from indexes 0-1, 0-3, 0-2 and… pages-ibdata1/FIL_PAGE_INDEX/<index_id> 4. Load dumps of the dictionary tables into some MySQL server. Use LOAD DATA INFILE constraints…
Comment: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… is indexes. If you add multiple multi-column indexes the difference with InnoDB will be even more notable. Also it allows multiple clustering indexes that give additional huge win on selects. We used TokuDB in the past (versions 3.x and 4.x). It really shines on large tables and index scan like…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… is a single row (well, the PRIMARY KEY index entry for that row). This means typically on …In Example 2, above, how above moving the ‘joined’ column to the users_groups table so we don’… distributed system requires tradeoffs. Most of us are used to the tradeoffs we take when deploying conventional …
Comment: MySQL Partitioning - can save you or kill you
April 25, 2013 By 45 Comments
… well, as you will be adding into a smaller dataset/index. Partitions are effectively a ‘chunked’ table, which requires the DB… you are issuing queries against this table, but NOT using the id column. This could cause a longer running query, as the…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… see how many index parts are actually used for row lookup. Very common problem I see is multi column indexes which are used but only… MySQL uses single index – there are more complex rules of how indexes will be used if you look at multiple indexes usage with “index merge” Using Index to…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… poor decision when it comes to choosing between using index for ORDER BY or using index for restriction. Consider we’re running web site… is possible to use index for further restriction and than using file sort, rather than using index for sorting and doing non-index based filtering for… this issue MySQL would need to better take into account column selectivity together with LIMIT range. If there are only few…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…Using intersect(i2,i1); Using where | +—-+————-+———+————-+—————-+——-+———+——+——+————————————-+ 1 row in set (0.00 sec) Hm… Optimizer decides to use index…
