GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Slow Query

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

  • Slow Query

    SELECT lf_keyword AS lf_title, lf_rank, lf_ondate
    FROM (SELECT lf_keyword, lf_rank, lf_ondate FROM lfdb_report
    WHERE lf_rank>10
    ORDER BY lf_ondate DESC
    )keywords
    GROUP BY lf_keyword
    ORDER BY lf_rank ASC
    limit 90,10;

    Take too much time.

    +-----------------------+---------+---------------------+
    | lf_title | lf_rank | lf_ondate |
    +-----------------------+---------+---------------------+
    | blue denim | 11 | 2007-03-22 03:45:10 |
    | blank shirts | 11 | 2007-03-22 03:41:33 |
    | air max | 11 | 2007-03-22 03:39:34 |
    | nike basketball shoes | 11 | 2007-03-22 03:13:41 |
    | wedding necklace | 11 | 2007-03-22 03:04:30 |
    | custom go karts | 11 | 2007-03-22 03:02:14 |
    | stainless steel nut | 11 | 2007-03-22 02:59:26 |
    | mens armani suits | 11 | 2007-03-22 02:55:47 |
    | discount sportswear | 11 | 2007-03-22 02:51:10 |
    | tight clothing | 11 | 2007-03-22 02:50:09 |
    +-----------------------+---------+---------------------+

    EXPLAIN SELECT lf_keyword AS lf_title, lf_rank, lf_ondate
    FROM (SELECT lf_keyword, lf_rank, lf_ondate FROM lfdb_report
    WHERE lf_rank>10
    ORDER BY lf_ondate DESC
    )keywords
    GROUP BY lf_keyword
    ORDER BY lf_rank ASC
    limit 90,10;

    +----+-------------+-----------------------------+------+--- ------------+------+---------+------+--------+-------------- -------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-----------------------------+------+--- ------------+------+---------+------+--------+-------------- -------------------+
    | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 252762 | Using temporary; Using filesort |
    | 2 | DERIVED | lfdb_report | ALL | NULL | NULL | NULL | NULL | 430522 | Using filesort |
    +----+-------------+-----------------------------+------+--- ------------+------+---------+------+--------+-------------- -------------------+
    2 rows in set (2.88 sec)


    Tried to optimize this query its works fine but difference in result.

    SELECT lf_keyword AS lf_title, lf_rank, lf_ondate
    FROM lfdb_report WHERE lf_rank>10 ORDER BY lf_ondate DESC, lf_rank ASC limit 90,10;

    +-----------------------------+---------+------------------- --+
    | lf_title | lf_rank | lf_ondate |
    +-----------------------------+---------+------------------- --+
    | resveratrol | 28 | 2007-03-28 02:39:30 |
    | fog lamps | 18 | 2007-03-28 02:39:07 |
    | hid foglight kits | 39 | 2007-03-28 02:36:24 |
    | stainless steel money clips | 54 | 2007-03-28 02:36:09 |
    | silver money clip | 49 | 2007-03-28 02:36:07 |
    | sterling silver | 23 | 2007-03-28 02:36:04 |
    | money clip wallets | 48 | 2007-03-28 02:36:03 |
    | best hid kits | 13 | 2007-03-28 02:35:52 |
    | discount new rock boots | 20 | 2007-03-28 02:30:40 |
    | new rock boot | 58 | 2007-03-28 02:29:07 |
    +-----------------------------+---------+------------------- --+

    EXPLAIN SELECT lf_keyword AS lf_title, lf_rank, lf_ondate
    FROM lfdb_report WHERE lf_rank>10 ORDER BY lf_ondate DESC, lf_rank ASC limit 90,10;

    +----+-------------+-----------------------------+------+--- ------------+------+---------+------+--------+-------------- ---------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-----------------------------+------+--- ------------+------+---------+------+--------+-------------- ---------------+
    | 1 | SIMPLE | lfdb_report | ALL | NULL | NULL | NULL | NULL | 430525 | Using where; Using filesort |
    +----+-------------+-----------------------------+------+--- ------------+------+---------+------+--------+-------------- ---------------+
    1 row in set (0.00 sec)

  • #2
    You have a couple of problems in your original query.

    You don't need to order the result in a sub query since the order doesn't mean anything.

    You also have a group by but you have columns in the select part that aren't part of the group by expression or uses a group function.

    My suggestions is something like this:

    SELECT lf_keyword AS lf_title, MAX(lf_rank), MAX(lf_ondate)FROM lfdb_reportWHERE lf_rank > 10GROUP BY lf_keywordORDER BY MAX(lf_rank) ASCLIMIT 90,10

    But since I don't know what you are after you will have to figure out that yourself.

    But if you have the query above you should create a combined index on (lf_rank, lf_keyword) that should speed this query up.
    The first lf_rank since it is part of the WHERE clause to find the correct rows. And the lf_keyword since there is an optimization that can use the index if the group by column is part of the index.

    Comment


    • #3
      Dear sterin,
      Hello,

      Thanks for answering. I tried your suggestion but its not working.

      FYI,

      mysql> SELECT lf_keyword AS lf_title, MAX(lf_rank), MAX(lf_ondate)
      -> FROM lfdb_report
      -> WHERE lf_rank > 10
      -> GROUP BY lf_keyword
      -> ORDER BY MAX(lf_rank) ASC
      -> LIMIT 90,10;
      ERROR 1111 (HY000): Invalid use of group function
      mysql>

      I do have index on lf_keyword.

      mysql> SHOW INDEX FROM lfdb_report;
      +-----------------------------+------------+------------+--- -----------+-------------+-----------+-------------+-------- --+--------+------+------------+---------+
      | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
      +-----------------------------+------------+------------+--- -----------+-------------+-----------+-------------+-------- --+--------+------+------------+---------+
      | lfdb_report | 0 | PRIMARY | 1 | lf_id | A | 431846 | NULL | NULL | | BTREE | |
      | lfdb_report | 1 | lf_keyword | 1 | lf_keyword | A | 143948 | NULL | NULL | | BTREE | |
      +-----------------------------+------------+------------+--- -----------+-------------+-----------+-------------+-------- --+--------+------+------------+---------+
      2 rows in set (0.00 sec)

      Best Regards.

      Comment


      • #4
        Which version of MySQL are you running?

        Because that query works on both 4.1 and 5.1 that I have on my laptop.

        Otherwise you can try this instead:

        SELECT lf_keyword AS lf_title , MAX(lf_rank) AS rank , MAX(lf_ondate)FROM lfdb_reportWHERE lf_rank > 10GROUP BY lf_keywordORDER BY rank ASCLIMIT 90,10

        Where I put an alias on the MAX(lf_rank) which is used in the order by.

        As for the index that is not what I meant.
        You need at least an index on lf_rank because that is the WHERE part of your query.

        But even better is if you create a _combined_ index on the two columns lf_rank and lf_keyword then it can use that combined index for this query. But only if the combined index is created with lf_rank as the first column in the index.

        Comment


        • #5
          Dear sterin,
          Hello,

          I am concerned about the results coming from below query.

          mysql> SELECT lf_keyword AS lf_title, lf_rank, lf_ondate
          -> FROM (SELECT * FROM lfdb_report
          -> WHERE (lf_rank /10) >1
          -> ORDER BY lf_ondate DESC
          -> )keywords
          -> GROUP BY lf_keyword
          -> ORDER BY lf_rank ASC
          -> limit 90,10;
          +-----------------------+---------+---------------------+
          | lf_title | lf_rank | lf_ondate |
          +-----------------------+---------+---------------------+
          | blue denim | 11 | 2007-03-22 03:45:10 |
          | blank shirts | 11 | 2007-03-22 03:41:33 |
          | air max | 11 | 2007-03-22 03:39:34 |
          | nike basketball shoes | 11 | 2007-03-22 03:13:41 |
          | wedding necklace | 11 | 2007-03-22 03:04:30 |
          | custom go karts | 11 | 2007-03-22 03:02:14 |
          | stainless steel nut | 11 | 2007-03-22 02:59:26 |
          | mens armani suits | 11 | 2007-03-22 02:55:47 |
          | discount sportswear | 11 | 2007-03-22 02:51:10 |
          | tight clothing | 11 | 2007-03-22 02:50:09 |
          +-----------------------+---------+---------------------+
          10 rows in set (8.39 sec)

          and When i run your query results are different.

          mysql> SELECT lf_keyword AS lf_title, MAX(lf_rank) AS rank, MAX(lf_ondate)
          -> FROM lfdb_report WHERE lf_rank > 10
          -> GROUP BY lf_keyword
          -> ORDER BY rank ASC
          -> LIMIT 90,10
          -> ;
          +---------------------------+------+---------------------+
          | lf_title | rank | MAX(lf_ondate) |
          +---------------------------+------+---------------------+
          | abercrombie sexy | 11 | 2007-03-15 03:02:15 |
          | abercrombie t shirt | 11 | 2006-12-20 21:44:24 |
          | abrasive paper supplier | 11 | 2006-12-29 05:50:26 |
          | ac motors universal motor | 11 | 2007-01-31 12:37:13 |
          | acryl display stand | 11 | 2007-01-23 11:57:27 |
          | acrylic filler | 11 | 2006-12-11 23:50:53 |
          | acrylic jars | 11 | 2007-01-07 14:12:33 |
          | acrylic nails glitter | 11 | 0000-00-00 00:00:00 |
          | acrylic yarns | 11 | 2007-01-22 08:02:01 |
          | addidas | 11 | 0000-00-00 00:00:00 |
          +---------------------------+------+---------------------+
          10 rows in set (2.26 sec)

          Best Regards.

          Comment


          • #6
            Are you aware of that you are selecting from a different table?

            And besides it can't be the exact same result since your original query is not a valid query and MySQL is the only DB in the world that accepts a query like that.
            But the result that MySQL is returning is not reliable when using queries like that.

            Comment


            • #7
              Dear sterin,
              Hello,

              Opps, Sorry that was typo mistake.

              mysql> SELECT lf_keyword AS lf_title, lf_rank, lf_ondate
              -> FROM (SELECT * FROM lfdb_report
              -> WHERE (lf_rank /10) >1
              -> ORDER BY lf_ondate DESC
              -> )keywords
              -> GROUP BY lf_keyword
              -> ORDER BY lf_rank ASC
              -> limit 90,10;

              +-----------------------+---------+---------------------+
              | lf_title | lf_rank | lf_ondate |
              +-----------------------+---------+---------------------+
              | blue denim | 11 | 2007-03-22 03:45:10 |
              | blank shirts | 11 | 2007-03-22 03:41:33 |
              | air max | 11 | 2007-03-22 03:39:34 |
              | nike basketball shoes | 11 | 2007-03-22 03:13:41 |
              | wedding necklace | 11 | 2007-03-22 03:04:30 |
              | custom go karts | 11 | 2007-03-22 03:02:14 |
              | stainless steel nut | 11 | 2007-03-22 02:59:26 |
              | mens armani suits | 11 | 2007-03-22 02:55:47 |
              | discount sportswear | 11 | 2007-03-22 02:51:10 |
              | tight clothing | 11 | 2007-03-22 02:50:09 |
              +-----------------------+---------+---------------------+
              10 rows in set (8.39 sec)

              Best Regards.

              Comment

              Working...
              X