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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]