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.
