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
