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
In news:[EMAIL PROTECTED],
Shadow <[EMAIL PROTECTED]> wrote:
> I need to get number of items in a specific category, so I use
> SELECT COUNT(*) FROM items WHERE ctg=''
>
> But each query takes ~ 10seconds.
> Its really slow.
You may add an index on `items`.`ctg` if there is none.
Maciek
--
M
its doing a T/B scan on items because there isn't an
index define for ctg on the items TABLE.
Again, just one man's thought.
-Original Message-
From: Michael Gargiullo [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 29, 2007 6:10 PM
To: Shadow; mysql@lists.mysql.com
Subject:
Do you need a count for all and run one at a time? If so, try. Select ctg as
catagory,count(*) as count from items group by catagory. It will take a little
while to run, but return all of your counts.
Does the items table have an index in ctg?
Sent by Good Messaging (www.good.com)
-O
