Not the answer you need?
Register and ask your own question!

Slow Query

LinuxFreakLinuxFreak ContributorInactive User Role Advisor
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 | <derived2> | 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)

Comments

  • sterinsterin Mentor Inactive User Role Contributor
    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</pre>

    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.
  • LinuxFreakLinuxFreak Contributor Inactive User Role Advisor
    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.
  • sterinsterin Mentor Inactive User Role Contributor
    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</pre>

    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.
  • LinuxFreakLinuxFreak Contributor Inactive User Role Advisor
    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.
  • sterinsterin Mentor Inactive User Role Contributor
    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.
  • LinuxFreakLinuxFreak Contributor Inactive User Role Advisor
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.