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.

In news:907873.28994.qm@web52811.mail.re2.yahoo.com,
Shadow <shadow_developer@yahoo.com> wrote:

> 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.

You may add an index on `items`.`ctg` if there is none.

Maciek

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" <shadow_developer@yahoo.com>
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.