… of all, we used a smaller dataset for this benchmarks round, since reportedly Dimitri was using 8 tables with 1M rows each (about…-distinct-ranges=0 –oltp-skip-trx=on –oltp-test-mode=nontrx –oltp-read-only=off –oltp-index-updates=0 –oltp-non-index-updates…-distinct-ranges=0 –oltp-skip-trx=on –oltp-test-mode=nontrx –oltp-read-only=off –oltp-index-updates=1 –oltp-non-index-updates…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… of distinct values, independent on each other. I also created couple of other tables with same data just with really low cardinality with i… would allow to force index merge as you can do with index accesses in general. Note2 Q2/Q3 can’t use Index Merge however as it is currently implemented so they would use single index…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…with perfect stats why bother updating them ? Also note many simple “queries” (using constants for index accesses) will not use index… it with data with following true cardinality: mysql> select count(distinct c) from antest; +——————-+ | count(distinct c) | +——————-+ | 101 …
Post: UNION vs UNION ALL Performance
October 5, 2007 By 15 Comments
… limit using index merge is faster than UNION as it indeed should be. So why UNION ALL is faster than UNION DISTINCT ? The… need to use temporary table to store result set, however this is not correct – both UNION ALL and UNION distinct use temporary table… requires internal temporary table with index (to skip duplicate rows) while UNION ALL will create table without such index. This also explains why…
Post: Sphinx: Going Beyond full text search
July 23, 2007 By 18 Comments
… now these 6 rows become simply one row in sphinx index with 6 “keywords” – specially crafter strings which corresponded to the URLs… Profile we’ve got to use even more of those features such as counting number of distinct sites pointing to the given… and distinct number is bit approximate for large numbers but it still works really well for our needs. Sure we could use…
Comment: COUNT(*) vs COUNT(col)
April 25, 2007 By 45 Comments
… that innodb uses clustered indexes would play into the equation. I find that, with a … from this index: mysql> select count( distinct (comment_approved) ) from wp_comments; +————————————–+ | count( distinct (comment_approved) ) | +————————————–+ | 3 …
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…into our schema with one command: mysql> create table index_analysis.used_indexes select * from information_schema.index_statistics; If …used_indexes as (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats ) UNION DISTINCT (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from slave_index…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
…index! It’s not always like this, but there are a lot of cases where applications have good selectivity with… SELECT count(distinct(population)) FROM Country; +—————————–+ | count(distinct(population)) | +—————————–+ …for an index to be useful (that is, …
Post: Unused indexes by single query
September 12, 2008 By 14 Comments
…. But now ( with userstatsV2.patch) you can find all unused indexes (since last restart of mysqld) by single query SELECT DISTINCT s.TABLE… the INDEX_NAME and the ROWS_READ columns, you can use this query: SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics `s` LEFT JOIN information_schema.index…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… index will still be superior. Index condition pushdown. Q: Is it always the case that no second-star index exists for a query with…, after the GROUP BY reduces it to one row per distinct value in the grouping column. Then sorting it may be… while using UNIQUE KEY constraints the way that makes most sense for your table. Logically, your index design will be the same with…
