List:General Discussion« Previous MessageNext Message »
From:John Kopanas Date:November 26 2006 1:34am
Subject:Re: Re: Performance Problems With Two Tables With Over 500K Rows
View as plain text  
If I just SELECT id:
SELECT id FROM purchased_services WHERE (company_id = 1000)

It takes approx 2-2.5s.  When I look at the process list it looks like
that it's state seems to always be in sending data...

This is after killing the db and repopulating it again.  So what is going on?

On 11/25/06, John Kopanas <kopanas@stripped> wrote:
> I tried the same tests with the database replicated in a MyISAM
> engine.  The count was instantaneous but the following still took
> 3-6seconds:
>
> SELECT * FROM purchased_services WHERE (purchased_services.company_id = 535263)
>
> The following though was instantaneous:
>
> SELECT * FROM purchased_services WHERE (id = 1000)
>
> This is the result from my SHOW INDEX FROM purchased_services:
>
>
> +--------------------+------------+-------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | Table              | Non_unique | Key_name
>  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
> Packed | Null | Index_type | Comment |
>
> +--------------------+------------+-------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | purchased_services |          0 | PRIMARY
>  |            1 | id          | A         |      627546 |     NULL |
> NULL   |      | BTREE      |         |
> | purchased_services |          1 |
> purchased_services_company_id_index |            1 | company_id  | A
>       |      627546 |     NULL | NULL   | YES  | BTREE      |
> |
>
> +--------------------+------------+-------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>
> So I do have the necessary index.  I am so confused!!!!  Argh...
>
> Your Friend,
>
> John
>
>
> On 11/25/06, John Kopanas <kopanas@stripped> wrote:
> > Sorry about these questions.  I am used to working with DBs with less
> > then 10K rows and now I am working with tables with over 500K rows
> > which seems to be changing a lot for me.  I was hoping I can get some
> > people's advice.
> >
> > I have a 'companies' table with over 500K rows and a
> > 'purchased_services' table with over 650K rows.
> >
> > The following query takes over 6 seconds:
> > SELECT * FROM purchased_services WHERE (purchased_services.company_id = 535263)
> >
> > purchased_services.company_id has an index on it.
> >
> > The following query takes over 3 seconds:
> > SELECT count(id) FROM companies;
> >
> > To me the time it takes to run these queries makes no sense.  I would
> > imagine both of these queries would take a fraction of a second.
> >
> > When running some of these queries and looking at 'SHOW processlist' I
> > was getting a lot of 'Writing to tmp'.
> >
> > My DB engine is InnoDB.  I am running this on my laptop that is a
> > PowerBook 867 with 756 MB of Ram.
> >
> > Feedback and insight would be greatly appreciated.
> >
> > Thanks my friends! :-)
> >
> > --
> > John Kopanas
> > john@stripped
> >
> > http://www.kopanas.com
> > http://www.cusec.net
> > http://www.soen.info
> >
>
>
> --
> John Kopanas
> john@stripped
>
> http://www.kopanas.com
> http://www.cusec.net
> http://www.soen.info
>


-- 
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