Just for heck of it, I did a Big-O-notation for its efficiency -- as you predicted, it will take longer. I also factored-in the SORT required by the 'GROUP BY' -- say there are 64,000 categories therefore mysql may have to do 64,000 SORTs -- that's not cheap.
My gut feelings are 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: RE: Querying large table 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) -----Original Message----- From: Shadow [mailto:[EMAIL PROTECTED] Sent: Thursday, March 29, 2007 07:00 PM Eastern Standard Time To: mysql@lists.mysql.com 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. ******************************************** This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. ******************************************** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
