MySQL Forums
Forum List  »  Optimizer & Parser

Slow Queries
Posted by: Farrukh Ahmed
Date: March 29, 2007 02:32AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow Queries
2586
March 29, 2007 02:32AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.