List:General Discussion« Previous MessageNext Message »
From:John Kopanas Date:November 27 2006 2:46am
Subject:Re: Re: Performance Problems With Two Tables With Over 500K Rows
View as plain text  
Yes... with FORCE INDEX it still takes 7 seconds.

On 11/26/06, Dan Nelson <dnelson@stripped> wrote:
> In the last episode (Nov 26), John Kopanas said:
> > On 11/26/06, Dan Nelson <dnelson@stripped> 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
>         dnelson@stripped
>


-- 
John Kopanas
john@stripped

http://www.kopanas.com
http://www.cusec.net
http://www.soen.info
Thread
Performance Problems With Two Tables With Over 500K RowsJohn Kopanas25 Nov
  • Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas26 Nov
    • Re: Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas26 Nov
      • Re: Re: Re: Performance Problems With Two Tables With Over 500K RowsDan Buettner26 Nov
        • Re: Re: Re: Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas27 Nov
  • Re: Performance Problems With Two Tables With Over 500K RowsDan Nelson26 Nov
    • Re: Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas27 Nov
      • Re: Performance Problems With Two Tables With Over 500K RowsDan Nelson27 Nov
        • Re: Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas27 Nov
          • Re: Performance Problems With Two Tables With Over 500K RowsDan Nelson27 Nov
            • Re: Re: Performance Problems With Two Tables With Over 500K RowsJohn Kopanas27 Nov
          • Re: Re: Performance Problems With Two Tables With Over 500KRowsmos27 Nov
        • Re: Performance Problems With Two Tables With Over 500K RowsDominik Klein27 Nov
  • Re: Performance Problems With Two Tables With Over 500K RowsDuncan Hill27 Nov