Hi,
---Can you provide the table structure of the usr_tbl table along with the output of EXPLAIN?
This is the table structure
+--------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------+------+-----+---------+-------+
| username | varchar(25) | | PRI | | |
| id | varchar(50) | | PRI | | |
| date | timestamp(14) | YES | PRI | NULL | |
| backup_date | varchar(14) | YES | | NULL | |
| batch_id | int(6) | YES | | NULL | |
| legacy_flag | char(1) | YES | | NULL | |
| total_items | int(9) | YES | | NULL | |
| total_results | int(9) | YES | | NULL | |
| total_income | int(9) | YES | | NULL | |
| gross_income | decimal(10,3) | YES | | NULL | |
| good_items | decimal(10,3) | YES | | NULL | |
| rev_share | decimal(5,2) | YES | | NULL | |
| turnover | decimal(10,3) | YES | | NULL | |
| us_income | int(9) | YES | | NULL | |
| nonus_inc | int(9) | YES | | NULL | |
| unique_item | int(9) | YES | | NULL | |
| unique_item_income | int(9) | YES | | NULL | |
| salefilter_options | varchar(255) | YES | | NULL | |
| sale_err_ab | int(9) | YES | | NULL | |
| sale_err_timeout | int(9) | YES | | NULL | |
| sale_err_rb | int(9) | YES | | NULL | |
| sale_err_nc | int(9) | YES | | NULL | |
| sale_err_db | int(9) | YES | | NULL | |
| sale_err_vl | int(9) | YES | | NULL | |
| sale_err_mip | int(9) | YES | | NULL | |
| sale_err_ipm | int(9) | YES | | NULL | |
| sale_err_apr | int(9) | YES | | NULL | |
| sale_err_other | int(9) | YES | | NULL | |
| sale_err_bur | int(9) | YES | | NULL | |
| err_count | float(4,2) | YES | | 0.00 | |
| total_time | float(6,2) | YES | | 0.00 | |
| sale_count | int(9) | YES | | 0 | |
+--------------------------+---------------+------+-----+---------+-------+
The EXPLAIN TABLE result is
+------------------------------+------+---------------+------+---------+------+----------+-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+------------------------------+------+---------------+------+---------+------+----------+-----------------------------+
| usr_tbl | ALL | NULL | NULL | NULL | NULL | 16605846 | where used; Using temporary |
+------------------------------+------+---------------+------+---------+------+----------+-----------------------------+
---Have you tried running OPTIMIZE and ANALYZE on the table before running the query?
I dont think optimize and analyze are useful in this case.
---Since you are doing a GROUP BY what is the size of your sort_buffer_size variable?
2097144
Please help me how can I optimize the query.
Thanks in Advance,
--
Trinath somanchi.
--
Trinath Somanchi,
(
http://www.myospages.com ).