Yes... with FORCE INDEX it still takes 7 seconds.
On 11/26/06, Dan Nelson <[EMAIL PROTECTED]> wrote:
In the last episode (Nov 26), John Kopanas said:
> On 11/26/06, Dan Nelson <[EMAIL PROTECTED]> wrote:
> >In the last episode (Nov 26), John Kopanas said:
> >> Thanks a lot for your help.
> >>
> >> The query should and only does return 1-6 rows depending on the id.
> >> Never more then that. Here are the comperative EXPLAINs:
> >>
> >> mysql> EXPLAIN SELECT * FROM purchased_services WHERE id = 1000;
> >>
>
>>+----+-------------+--------------------+-------+---------------+---------+---------+-------+------+-------+
> >>| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
>
>>+----+-------------+--------------------+-------+---------------+---------+---------+-------+------+-------+
> >>| 1 | SIMPLE | purchased_services | const | PRIMARY | PRIMARY |
4 | const | 1 | |
>
>>+----+-------------+--------------------+-------+---------------+---------+---------+-------+------+-------+
> >> 1 row in set (0.00 sec)
> >
> >This query definitly should run almost instantly, since it looks like a
> >direct lookup on the primary key.
> >
> >> mysql> EXPLAIN SELECT * FROM purchased_services WHERE company_id = 1000;
> >>
>
>>+----+-------------+--------------------+------+-------------------------------------+------+---------+------+--------+-------------+
> >>| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra |
>
>>+----+-------------+--------------------+------+-------------------------------------+------+---------+------+--------+-------------+
> >>| 1 | SIMPLE | purchased_services | ALL |
purchased_services_company_id_index | NULL | NULL | NULL | 626188 | Using where |
>
>>+----+-------------+--------------------+------+-------------------------------------+------+---------+------+--------+-------------+
> >> 1 row in set (0.00 sec)
> >
> >This EXPLAIN indicates that mysql thinks that the query would match
> >~600k rows and will do a full table scan. Mysql only keeps a single
> >"cardinality" value for each index that estimates how many records have
> >a unique value in the index. This can cause problems for the optimizer
> >if you have one value for say 60% of the table, and unique values for
> >the rest. You can try adding a FORCE INDEX clause to the query and see
> >if that helps.
>
> When I did a:
>
> SELECT * FROM purchased_services WHERE company_id = 1000;
>
> It took me 7 seconds. This is driving me crazy!
>
> I am going to have to try this on another computer and see if I am
> going to get the same results on another system. Argh...
Yes, a full table scan on 500k rows may very well take 7 seconds. Did
you try with a FORCE INDEX (purchased_services_company_id) clause?
--
Dan Nelson
[EMAIL PROTECTED]
--
John Kopanas
[EMAIL PROTECTED]
http://www.kopanas.com
http://www.cusec.net
http://www.soen.info
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]