Slow Query

MySQL & MariaDB Other MySQL® Questions
1

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.

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.

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.

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.

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.

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.