Database statistics / cardinality - NULL problem

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Ihug
    #1

    Database statistics / cardinality - NULL problem

    I have a database table with approx 3 million rows.
    There are 9 indexes on this table.

    The command:
    SHOW INDEX FROM TRAN;
    Returns results as expected with all indexes some valid "Cardinalit y"
    values.

    After a period of time or a series of events (unknown to me) the statistics
    for one or more indexes shows a NULL value in the cardinality.
    This causes queries that rely on these indexes to become incredibly slow,
    sometimes doing a full table read.

    I have been trying to find out what causes the statistics to become NULL?
    Any help here would be appreciated.

    I currently have a nightly process that scans for NULL table statistics then
    updates (analyze table) any table effected.

    Thank you for any help!


    Tags: cardinality database
    • Bill Karwin
      #2
      Re: Database statistics / cardinality - NULL problem

      Ihug wrote:[color=blue]
      > After a period of time or a series of events (unknown to me) the statistics
      > for one or more indexes shows a NULL value in the cardinality.
      > This causes queries that rely on these indexes to become incredibly slow,
      > sometimes doing a full table read.[/color]

      What version of MySQL are you using?

      I see a bug (http://bugs.mysql.com/bug.php?id=2019) that matches this
      issue, but it's reported against MySQL 3.23, and the response is "Won't
      Fix" because that release it so far out of date.

      If you can reproduce the behavior on the latest MySQL 4.1 or 5.0
      releases, I'd recommend filing a bug at bugs.mysql.com.

      Regards,
      Bill K.

        Comment

        • Ihug
          #3
          Re: Database statistics / cardinality - NULL problem

          The site is currently running 4.0.18.
          Maybe I will look at the mysql version and upgrade notes then perhaps
          install the latest stable release.
          Thank you.


          "Bill Karwin" <bill@karwin.co m> wrote in message
          news:dhhb250sip @enews3.newsguy .com...[color=blue]
          > Ihug wrote:[color=green]
          >> After a period of time or a series of events (unknown to me) the
          >> statistics for one or more indexes shows a NULL value in the cardinality.
          >> This causes queries that rely on these indexes to become incredibly slow,
          >> sometimes doing a full table read.[/color]
          >
          > What version of MySQL are you using?
          >
          > I see a bug (http://bugs.mysql.com/bug.php?id=2019) that matches this
          > issue, but it's reported against MySQL 3.23, and the response is "Won't
          > Fix" because that release it so far out of date.
          >
          > If you can reproduce the behavior on the latest MySQL 4.1 or 5.0 releases,
          > I'd recommend filing a bug at bugs.mysql.com.
          >
          > Regards,
          > Bill K.[/color]


            Comment

            Previous template Next
            Working...