List:General Discussion« Previous MessageNext Message »
From:John Kopanas Date:November 26 2006 12:19am
Subject:Re: Performance Problems With Two Tables With Over 500K Rows
View as plain text  
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
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