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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]