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 <[EMAIL PROTECTED]> 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
[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]
