Thanks a lot for your help.
The query should and only does return 1-6 rows depending on the id.
Never more then that. Here are the comperative EXPLAINs:
mysql> EXPLAIN SELECT * FROM purchased_services WHERE id = 1000;
+----+-------------+--------------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+----+-------------+--------------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | purchased_services | const | PRIMARY |
PRIMARY | 4 | const | 1 | |
+----+-------------+--------------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM purchased_services WHERE company_id = 1000;
+----+-------------+--------------------+------+-------------------------------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------+-------------------------------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | purchased_services | ALL |
purchased_services_company_id_index | NULL | NULL | NULL | 626188 |
Using where |
+----+-------------+--------------------+------+-------------------------------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
Here is the explain for the SELECT COUNT(id)
mysql> EXPLAIN SELECT count(id) FROM companies;
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | companies | index | NULL | PRIMARY |
4 | NULL | 533821 | Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.10 sec)
The explain takes a fraction of a second and returns the amound of row
plus some. But when I just as for the count it took me 5 seconds.
Something is broken.
My innodb_buffer_pool_size is:
innodb_buffer_pool_size | 8388608
That looks like 8MB... that sounds small if I have a DB with over 1M
rows to process. No?
Thanks again for your help.
Your Friend,
John
On 11/25/06, Dan Nelson <dnelson@stripped> wrote:
> In the last episode (Nov 25), John Kopanas said:
> > 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)
>
> How many rows does it return, and what's its explain plan?
>
> > purchased_services.company_id has an index on it.
> >
> > The following query takes over 3 seconds:
> > SELECT count(id) FROM companies;
>
> An explain plan here would be useful too.
>
> > 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'.
>
> That doesn't make much sense, since both queries should simply be doing
> index scans (or full table scans depending on how many rows are
> expected to match in the first query, but the explain plans will show
> that).
>
> > My DB engine is InnoDB. I am running this on my laptop that is a
> > PowerBook 867 with 756 MB of Ram.
>
> What's your innodb_buffer_pool_size set to?
>
> --
> Dan Nelson
> dnelson@stripped
>
--
John Kopanas
john@stripped
http://www.kopanas.com
http://www.cusec.net
http://www.soen.info
