Hi

I have 2 tables and I want a faster report like this.

| counter | user_company_name          |
+---------+----------------------------+
|   65537 | company1      |
|   65535 | company2                   |
|   64563 | company3 |
|   52676 | company4     |

User_table description

| Field                  | Type            | Null | Key | Default | Extra          |
+------------------------+-----------------+------+-----+---------+----------------+
| user_id                | int(8) unsigned |      | PRI | NULL    | auto_increment |
| user_company_name      | text            | YES  |     | NULL    |                |

user inventory table description

+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| pr_id          | bigint(20) unsigned |      | PRI | NULL    | auto_increment |
| user_id        | int(8)              | YES  | MUL | NULL    |                |
| pr_part_no     | varchar(50)         |      | MUL |         |                |
| pr_part_desc   | varchar(200)        | YES  |     | NULL    |                |
| pr_part_mfg    | varchar(200)        | YES  |     | NULL    |                |
| pr_part_cond   | varchar(200)        | YES  |     | NULL    |                |
| pr_qty         | varchar(50)         | YES  |     | NULL    |                |
| pr_price       | varchar(50)         | YES  |     | NULL    |                |
| pr_submit_date | timestamp(8)        | YES  |     | NULL    |                |
| pr_item_type   | varchar(200)        | YES  |     | NULL    |                |
+----------------+---------------------+------+-----+---------+----------------+

Indexes from user_inventory

+----------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table          | Non_unique | Key_name         | Seq_in_index | Column_name | 
Collation | Cardinality | Sub_part | Packed | Comment |
+----------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+---------+
| user_inventory |          0 | PRIMARY          |            1 | pr_id       | A      
   |      323424 |     NULL | NULL   |         |
| user_inventory |          1 | user_id_index    |            1 | user_id     | A      
   |          73 |     NULL | NULL   |         |
| user_inventory |          1 | pr_part_no_index |            1 | pr_part_no  | A      
   |      107808 |       25 | NULL   |         |
+----------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+---------+

the query is:

select count(*) as counter,ut.user_company_name from user_inventory st left join 
user_table ut 
on st.user_id = ut.user_id  where st.pr_submit_date between '20030815' and '20030915' 
group by ut.user_id,
ut.user_company_name order by counter desc limit 0,10;

the problem is when I run this query my mysql server is slowing down the server . 

Can somebody help with this PROBLEM.
THANKS in advance.

Reply via email to