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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 > > > [EMAIL PROTECTED] > > > > > > http://www.kopanas.com > > > http://www.cusec.net > > > http://www.soen.info > > > > > > > > > -- > > John Kopanas > > [EMAIL PROTECTED] > > > > http://www.kopanas.com > > http://www.cusec.net > > http://www.soen.info > > > > > -- > John Kopanas > [EMAIL PROTECTED] > > 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/[EMAIL PROTECTED] > >
-- John Kopanas [EMAIL PROTECTED] 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/[EMAIL PROTECTED]
