Slow Queries

[Farrukh Ahmed]

Dear Sir,
Hello,

I am trying to Optimize Query of huge Database.

mysql> describe lfdb_report;
+------------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------------------+----------------+
| lf_id | int(11) | | PRI | NULL | auto_increment |
| lf_keyword | varchar(255) | | MUL | | |
| lf_search_engine | varchar(255) | | | | |
| lf_rank | int(11) | YES | | NULL | |
| lf_url | tinytext | | | | |
| lf_competetion | int(10) unsigned | | | 0 | |
| lf_ondate | timestamp | YES | | 0000-00-00 00:00:00 | |
+------------------+------------------+------+-----+---------------------+----------------+
7 rows in set (0.00 sec)

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)

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)

Best Regards.

Farrukh Ahmed