← Back to team overview

maria-discuss team mailing list archive

Re: mariadb still uses indexes with high average value group size.

 

As a side note, the documentation for the engine independent table statistics isn't very complete :
- Is there a reason not to activate it and what are the possible downsides of its activation
- How much could it slow down an ANALYZE and possibly other operations?
- There are no practical examples except on the histogram-based stats page
- There are no other mention than a link to the KB page of the histogram-based stats on the KB page.

Some of these slides could be re-used for the documentation I think : https://www.slideshare.net/mobile/SergeyPetrunya/mariadb-engine-independent-table-statistics-including-histograms

---- dE wrote ----

> Humm.. thanks for clarifying that!
> 
> On 01/04/16 03:20, Sergei Golubchik wrote:
> > Hi, dE!
> >
> > On Jan 02, dE wrote:
> >> Hi!
> >>       I was playing around with indexes after we faced problems with
> >> mariadb choosing the wrong index because of high average value group size.
> >>
> >> So I created a table with 4 text columns and indexed the 4th column with
> >> a prefix length of 1.
> >>
> >> Following are the stats in brief --
> >>
> >> The index has 35 unique values
> >> There are around 1040000 rows in total.
> >> this will give an average value group size of 29714
> >> Cardinality is calculated as <no. of records in the index>/<average size
> >> of value groups>, thus it's value must be 35
> >>
> >> BUT from show indexes, it's value is shown as 72
> >> (innodb_stats_persistent_sample_pages = 9999999).
> > It's because InnoDB lies about the cardinality. It reports twice the
> > actual value to the server. If MyISAM would report the cardinality as
> > 36, InnoDB will say it's 72.
> >
> > You can enable the Engine Independent Table Statistics in MariaDB:
> > https://mariadb.com/kb/en/mariadb/engine-independent-table-statistics/
> > This will show the correct value.
> >
> > Regards,
> > Sergei
> > Chief Architect MariaDB
> > and security@xxxxxxxxxxx
> 
> 
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp

Follow ups

References