June 18, 2013

Post: Multiple column index vs multiple indexes

August 22, 2008 By 13 Comments

… couple of other tables with same data just with really low cardinality with i and j having just 3 values each. The… 2980 – - Note1: Q1 will not use Index Merge technique for low cardinality table but instead pick to do single index scan. I… 2 column index is faster for Q3 in case of low cardinality even though MySQL can’t use index well. You’re…

Filed Under: Benchmarks, Insight for DBAs

Post: How (not) to find unused indexes

October 16, 2009 By 2 Comments

… query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be… | Using where | +—-+————-+——-+——+—————+——+———+——+——-+————-+ 1 row in set (0.01 sec) The cardinality of status index is woeful, but provided that the application… cardinality but should not be used since too many countries have a population greater than 1000.  An automated search for low cardinality…

Filed Under: Insight for DBAs, MySQL Tagged With: , , ,

Comment: Database problems in MySQL/PHP Applications

August 11, 2006 By 13 Comments

… that have very low cardinality will often be a waste of resources because the optimizer just won’t use them. Cardinality can be… for in this table?” An index that has very high cardinality (as high as the number of rows in the table… information. On the other hand, an index that has very low cardinality (1/2 a million rows with an index on a…

Filed Under: Insight for Developers Tagged With: , ,

Comment: How to find wrong indexing with glance view

March 8, 2010 By 27 Comments

… performance by using one of these foreign key index with low cardinality and somewhat even distribution first instead of a primary or… the same indexes regardless. The foreign key indexes are all low cardinality with roughly even distributions. Nearly always a poor choice to…

Filed Under: Insight for DBAs Tagged With:

Comment: Analyzing air traffic performance with InfoBright and MonetDB

February 2, 2010 By 50 Comments

… a full column scan query. Group BY column has very low cardinality (7) => do grouping and aggregation in memory. DayOfWeek requires only… table from this post (120 columns – most of them with low cardinality – no strings only numbers)

Filed Under: Benchmarks, MySQL Tagged With: , , ,

Comment: Long PRIMARY KEY for Innodb tables

April 10, 2009 By 8 Comments

… be interesting to see your thoughts on low cardinality indexes and short-prefix indexes resulting in low cardinality. What happens if I have a…

Filed Under: Insight for DBAs Tagged With: ,

Comment: How to find wrong indexing with glance view

August 22, 2008 By 27 Comments

Peter, You said:”in case there is good cardinality on both columns and full index can be used”. I’…”. In my line of work I’m searching trough many low cardinality columns in the same query. The query is very specific…

Filed Under: Insight for DBAs Tagged With:

Comment: Sphinx search performance optimization: attribute-based filters

January 15, 2013 By 11 Comments

Edmar, Consider looking at fastbit if you have low cardinality columns that you need to intersect with high ones. Fastbit uses WAH compressed bitmaps which can be compared without decompressing the bitmaps. This makes multidimensional lookups very efficient. https://sdm.lbl.gov/fastbit/

Filed Under: Insight for Developers, MySQL Tagged With: , ,

Post: Intro to OLAP

July 12, 2010 By 10 Comments

…. Any yes/no field and/or fields with a very low cardinality (such as gender or order status) will probably be stored…

Filed Under: Insight for Developers, MySQL Tagged With: , , , ,

Comment: Analyzing air traffic performance with InfoBright and MonetDB

February 2, 2010 By 50 Comments

Forgot to mention YearD column in my example. Of course we need to scan through 2 columns, but both are with very low cardinality and must be very compressible ones. The second column will add possibly 60Mb more data to load from disk.

Filed Under: Benchmarks, MySQL Tagged With: , , ,
« Older Posts