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