It's taking a long time because your filter is external to the table, so you can't use an index. You want to focus your search on
the category table, where you can quickly narrow down the number of records to search.
SELECT ctg, count(itemid) FROM categories JOIN items ON ctgID=itemCtgID WHERE
ctg='<ctg>' GROUP BY ctg;
----- Original Message -----
From: "Shadow" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Thursday, March 29, 2007 6:59 PM
Subject: Querying large table
Hey, guys.
I have 2 tables: categories and items.
COUNT(*) categories = 63 833
COUNT(*) items = 742 993
I need to get number of items in a specific category, so I use
SELECT COUNT(*) FROM items WHERE ctg='<ctg>'
But each query takes ~ 10seconds.
Its really slow.
Can anybody propose some optimization?
Thanks.
---------------------------------
Finding fabulous fares is fun.
Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
