Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Loop, Count and Grouping Speed 1

Status
Not open for further replies.

waiting12345

Technical User
Dec 9, 2005
9
GB
Hi, I was wondering what's the best way to do a loop through a list of items and display the number of reviews for each item. I was wondering if it was practical to do a count and then select the rest of the records and group each one or if it would be easier to actually have a field in the items table which keeps count of the number of reviews (incremented when new review made). This is taking into account speed as I wish for it to be as fast as possible so if it had to do a count each time across thousands of records I was wondering if this would make it drastically slower.

I'd appreciate your help. Thanks
 
Do you mean you have a table of records, each of which is a review about a particular item, and you want to get the total number of records for each item? If so, then the following would be the natural solution:
[tt]
SELECT itemid,COUNT(*) numreviews
FROM itemstable
GROUP BY itemid
[/tt]
 
Yes, I was faced with this same dilema long time ago, and now use a field to store the count when it's something prone to grow a great deal, if it's something that will stay short, I do a count on the spot when no other way is available to obtain that same count.

Also note, when storing the count in a dedicated field, you have to add/subtract from it as stuff is added and deleted or moved, due to this required operation which increases the risk of producing a bad count if not done carefuly, I also make a script that will sync the count and bring it's value up to date, this script is run manually, and can take sometime depending on the number of records it has to count, so I run it on off-peak times and only once every 3 months or so.

This sync script can also tell you if what was counted by it, equals what is stored in your special field, so if this number differs, then you may have a bug somewhere in your code that is not updating the count when it should.

Hope all goes well.

------------------------------
------------------------------
 
Recording the count in a separate field is a bad idea, unless you've a really good excuse for it. It is redundant data, which could well be incorrect, and also involves extra work to keep it updated. If the identifying field is indexed, then there should be no performance problems using COUNT(*).
 
So many times I see stuff like this. I'm not sure where folks think the performance hit is going to come from. I agree with Tony. Realize that "small" is in the tens of thousands of records. A "large" database is in the tens or hundreds of millions of records. Most people don't even use tens of thousands of rows. Let the database do the work, calculations etc. Let your application language do any layout work, and again, don't store redundant data.
 
I always thought that asking the DB to COUNT thousands of records was hard comparing to just querying a field, so you are saying that it should be a walk in the park for the database server to COUNT everytime you need it?

Also, what if the field is not indexed? How much slower will it become?

Updating redundant data is extra work, but so it is having to do a seperate COUNT everytime, or can one do it all in one query?

ie. SELECT COUNT whatever FROM wherever + also give me all this from that table WHERE whatever -- is this kind of thing possible? Or one has to use 2 selects, one for the count and one to get whatever data?

Thanks, this may well be changing the way I do stuff from now on.
 
recording the count in a separate field is not necessarily a bad idea, but it should be your last resort, and only to solve a performance problem, in full awareness of the shenanigans you need to perform in order to keep the count accurate, as well as the risk of loss of integrity

yes, you can obtain a count at the same time as returning other data

r937.com | rudy.ca
 
I always thought that asking the DB to COUNT thousands of records was hard comparing to just querying a field, so you are saying that it should be a walk in the park for the database server to COUNT everytime you need it?

Also, what if the field is not indexed? How much slower will it become?

If the identifying field is indexed, then the database server would not need to do a physical count of the records; it can take advantage of binary-chop and other searching algorithms. It's like counting the number of entries for the surname "Murphy" in a phone-book. Since the surname is indexed, you wouldn't start at A and work your way to Z; you would quickly find the first Murphy, quickly find the last Murphy, and use those page/column/line numbers with the number of columns per page and entries per column to calculate the result.

Non-unique indexes like that can be added or removed to your heart's content; they don't affect your database structure. They can dramatically speed up read times, but can also slow down updates and insertions. If you can get away without an index, don't use one. If you find it slow without an index, add one.
 
Great explanation, I usually use COUNTs for this kind of thing;

December posts (263)
November posts (782)

Note the (...) that what I usually COUNT, the COUNT query looks like this;

SELECT COUNT(ID) AS cDecPosts ID, Month FROM tbl_Posts WHERE Month = 12

The ID column would be a Primary Key (auto-increment), is this going to need another Index? Or being a Primary Key that gives it all it needs for a quick COUNT?

Also, how do I COUNT something and at the same time retrieve a bunch of data from other tables in the same query?

Creating Indexes in MSSQL can be done with a simple CREATE INDEX, however in MySQL it requires more stuff, I just can't understand it, how do I go about to CREATE INDEXs the type you mentioned above (Non-unique) in MySQL, and do I really need them?

Thanks guys.
 
A primary key does everything a non-unique index does and more. You don't need a separate non-unique index.

Creating indexes in MySQL is easy. A simple example would be just "CREATE INDEX indexname ON tablename (fieldname)". The full syntax is given in the MySQL manual.

As already said, non-unique indexes are used for speed, nothing else. If performance is acceptable without an index, then you don't need one.

How you return a record count along with data from other tables depends on the nature of the query. If you can provide an example, somebody might be able to help.
 
Your explanations are the best I've seen in some time, thanks for that.

Here's an example of a couple o queries I do alot;

Code:
'// Count Main Menus
SQL = "SELECT COUNT(Menu_Main_ID) AS countMains FROM tbl_Menu_Main;"
Set rsMainMenu = Conn.Execute(SQL)
countMains = rsMainMenu("countMains")

'// Get Main Menus data
SQL = "SELECT Menu_Main_ID, Menu_Main_Position, Menu_Main_Active, Menu_Main_Txt FROM tbl_Menu_Main ORDER BY Menu_Main_Position;"
Set rsMainMenu = Conn.Execute(SQL)

The first query gets the count only, as for the second it gets the rest of the data from the same table as the count one, can this be combined in one query only?

And, say for instance, the second query would retrieve data from another table, could the two queries also be combined somehow?
 
your two queries are not the best example

if you run the second query, the number of rows returned is the same as the results of the first query

but you would combine them like this
Code:
SELECT Menu_Main_ID
     , Menu_Main_Position
     , Menu_Main_Active
     , Menu_Main_Txt 
     , ( select COUNT(Menu_Main_ID) 
           FROM tbl_Menu_Main ) AS countMains
  FROM tbl_Menu_Main 
ORDER 
    BY Menu_Main_Position


r937.com | rudy.ca
 
Is that what's called a sub-query? A select inside a select?

If so, I can't use it, still on 4.0.?? can't upgrade as there are connection and other problems coming from the new version.

Will find a better example soon.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top