GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

faster not using Index????

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • faster not using Index????

    One table VAL_FAKTA_VH containing 5.000.000 rows and one table VAL_DIM_AVTAL containing 34 rows. The query takes 15 s using Index and 7 s not using index, how is this possible!!! It is not a question about IO, no IOWAIT but alot of CPU 99% (on machines with single CPU and 49.9 om machines with 2 CPU, MySQL does not seems to utilize both CPU:s)

    HW
    2*2Ghz CPU
    8G RAM

    I have used huge-conf with the following add:
    join_buffer_size 131072
    key_buffer_size 3221225472
    tmp_table_size 67108864
    read-only
    and some more

    DB:
    VAL_FAKTA_VH.MYD ~ 600M
    VAL_FAKTA_VH.MYI ~ 400M
    VAL_DIM_AVTAL.MYI ~ 2M
    VAL_DIM_AVTAL.MYD ~ 1M

    mysql> explain SELECT avtal.avtal, sum(utfallkronor) FROM VAL_FAKTA_VH V force index(Index__avtalid) , VAL_DIM_AVTAL avtal where V.avtalid = avtal.avtalid and avtal.avtal = 'Huvud' group by avtal.avtal;
    +----+-------------+-------+------+----------------+-------- --------+---------+---------------------+--------+---------- ---+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+------+----------------+-------- --------+---------+---------------------+--------+---------- ---+
    | 1 | SIMPLE | avtal | ALL | PRIMARY | NULL | NULL | NULL | 34 | Using where |
    | 1 | SIMPLE | V | ref | Index__avtalid | Index__avtalid | 5 | carro.avtal.avtalid | 138929 | Using where |
    +----+-------------+-------+------+----------------+-------- --------+---------+---------------------+--------+---------- ---+
    2 rows in set (0.00 sec)
    This query takes 15 s


    If I ignore index:
    mysql> explain SELECT avtal.avtal, sum(utfallkronor) FROM VAL_FAKTA_VH V ignore index(Index_2, Index__avtalid) , VAL_DIM_AVTAL avtal where V.avtalid = avtal.avtalid and avtal.avtal = 'Huvud' group by avtal.avtal;
    +----+-------------+-------+--------+---------------+------- --+---------+-----------------+---------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+--------+---------------+------- --+---------+-----------------+---------+-------------+
    | 1 | SIMPLE | V | ALL | NULL | NULL | NULL | NULL | 4723596 | |
    | 1 | SIMPLE | avtal | eq_ref | PRIMARY | PRIMARY | 4 | carro.V.avtalid | 1 | Using where |
    +----+-------------+-------+--------+---------------+------- --+---------+-----------------+---------+-------------+
    2 rows in set (0.00 sec)

    mysql> SELECT avtal.avtal, sum(utfallkronor) FROM VAL_FAKTA_VH V ignore index(Index_2, Index__avtalid) , VAL_DIM_AVTAL avtal where V.avtalid = avtal.avtalid and avtal.avtal = 'Huvud' group by avtal.avtal;
    +-------+-------------------+
    | avtal | sum(utfallkronor) |
    +-------+-------------------+
    | Huvud | 20396597.210337 |
    +-------+-------------------+
    1 row in set (7.98 sec)

    I have tried this on several machines with different architectures and different configurations the result is the same .... please help me with this problem?

    /Ted

  • #2
    The index is secondary in this case what is important is join order, which you can force with STRAIGHT_JOIN hint by the way.

    Note the order of tables becomes different and this is why.

    Scanning large table and doing single row lookups in tiny tables is faster than other way around. Quite expected.

    Comment


    • #3
      Thanks for your answer!

      With straight_join the question takes 7 s, but if you always use straight_join other questions suffer.
      Should not the optimizer se this and do the appropriate thing in this case? Can the optimizer learn from earlier queries?

      Background to problem:
      We will be creating this kind of questions from our Cognos-platform and are kean not to build these kind of exceptions in to our model. The Cognos environment are to be exposed to 1000 end users and these questions are generated by Cognos. In our model now with SQL Server this is not a problem.

      Is there a way to get this to work with MySQL? HW, changing the structure of data etc. We are aiming for 2-3 seconds for this query.

      Best regards,
      Ted

      Comment


      • #4
        Optimizer does not learn from previous queries it creates plan based on stats available from the tables.

        If you do not want to use hints it may be hard to make MySQL to use the plan you're looking for - playing with indexing may help sometimes, running ANALYZE on tables or changing optimizer related MySQL settings. There is no general solution though.

        You may report it as a bug to MySQL but Optimizer bugs usually are not fixed quickly because these are limitations rather than true bugs

        Comment


        • #5
          Hi Peter!

          how it is possible for the optimizer to get the rows wrong in explain, there are over 4 million rows where avtal='Huvud' but the explain shows 138 929??? This is the total number of rows in table VAL_FAKTA_VH divided by rows in VAL_DIM_AVTAL, is MySLQ a litttle to smart for itself? Is there a workaround for this problem that allways work?

          mysql> SELECT avtal.avtal, count(*),sum(utfallkronor) FROM VAL_FAKTA_VH V , VAL_DIM_AVTAL avtal where V.avtalid = avtal.avtalid group by avtal.avtal;
          +-----------+----------+-------------------+
          | avtal | count(*) | sum(utfallkronor) |
          +-----------+----------+-------------------+
          | Geriatrik | 28262 | 473027.543976605 |
          | Huvud | 4427905 | 20396597.2103366 |
          | Övrig | 267429 | 1495763.62524932 |
          +-----------+----------+-------------------+
          3 rows in set (24.25 sec)

          mysql> explain SELECT avtal.avtal, count(*),sum(utfallkronor) FROM VAL_FAKTA_VH V , VAL_DIM_AVTAL avtal where V.avtalid = avtal.avtalid and avtal.avtal = 'Huvud' group by avtal.avtal;
          +----+-------------+-------+------+------------------------+ ---------+---------+---------------------+--------+--------- ----+
          | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
          +----+-------------+-------+------+------------------------+ ---------+---------+---------------------+--------+--------- ----+
          | 1 | SIMPLE | avtal | ALL | PRIMARY | NULL | NULL | NULL | 34 | Using where |
          | 1 | SIMPLE | V | ref | Index_2,Index__avtalid | Index_2 | 5 | carro.avtal.avtalid | 138929 | Using where |
          +----+-------------+-------+------+------------------------+ ---------+---------+---------------------+--------+--------- ----+
          2 rows in set (0.00 sec)

          Best regards,
          Ted

          Comment


          • #6
            The number of rows you see in explain in second table is number of rows which is estimated to be examined for each row in the first table.

            This estimation is done using cardinality, so in average there are 130K matching records for each row in the first table. There are no stats to help MySQL to know distribution is skewed.

            Workarounds are cases specific as I mentioned. One need to take a closer look at your schema/data and basically play with it trying various workarounds to find one.

            For example covering indexes often able to help you to use the index you're looking for.

            Comment

            Working...
            X