"gibex" <[EMAIL PROTECTED]> wrote:
>
> 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 .
>
Use EXPLAIN SELECT to see if MySQL uses indexes:
http://www.mysql.com/doc/en/EXPLAIN.html
BTW, ORDER BY .. DESC doesn't use index in 3.23.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
