List:General Discussion« Previous MessageNext Message »
From:John Kopanas Date:November 27 2006 1:05am
Subject:Re: Re: Re: Re: Performance Problems With Two Tables With Over 500K Rows
View as plain text  
The application is not in production yet but when it will go in
production the server will be considerably faster and have much more
RAM.  But before I put the app in production I want to make sure it is
working properly.  500K rows does not sounds like that much in this
day in age.  If I understand what is going on on my laptop I will be
confident it will work well in production.

:-)

On 11/25/06, Dan Buettner <drbuettner@stripped> wrote:
> This kind of timeframe (2 - 2.5 secs) could just be the result of
> running on a laptop.  You've got a small amount of RAM compared to
> many servers, a bit slower processor, and *much* slower hard disk
> system than most servers.  If your query has to access multiple
> records spread out throughout the table off a slow laptop disk, this
> makes sense to me.
>
> Do you normally run this database on a "real" server in production?
> Is response time better?
>
> Still, it does seem a bit slow, even for an 867 MHz laptop, and seeing
> the output of an EXPLAIN might be helpful.
>
> Also, to answer your question about the speed of selecting the count
> of rows in a table - MyISAM always knows exactly how many rows are in
> a table, so it answers quickly without checking the data.  InnoDB does
> not keep track of how many rows are in a table, so it has to go count
> them when you do a SELECT COUNT(*) or in your case SELECT COUNT(id).
> That takes a little bit of time.
>
> Dan
>
>
> On 11/25/06, John Kopanas <kopanas@stripped> wrote:
> > 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
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> >
> >
>


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