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.
