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