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]
