Re: Tons of records - archive, or what?
> How many indexes do you have setup on the table? I had a table with > about 6 million records that I inherited and it only had an index It has all the pertinent fields included in indexes. I believe about 8-10 fields in the one main output table. All tables have all the indexes that are needed I believe. It just hit me this morning, I may be creating a bunch of data to query that'll never exist anyway. It's trying to count up 200,000 + records for one instructor. That'll never happen anyway! I think I need to load more real-world data into my tables and try it then. Maybe I'm asking something of it that nothing can handle, and we don't need that type of report anyway. make sense?? Thanks for your help! Will ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275146 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Tons of records - archive, or what?
ok now that I'm a bit more coherent. if you go to gaiaionline.com/forum you can see the msg board counts etc without needing to register. - Gaia has 998,523,683 articles posted with 6,899,737 registered users. - Most users ever online was 86,738 on Thu Jan 04, 2007 3:00 pm - There are currently 22,875 users online Topics (articles) have as many as 78,000 replies. If you're not familiar with phpbb a topic has its own table and its post information has its own table along with users in their own table. Theres some joins etc that happen similarly to what you were referring to. Its always been my opinion we as developers get pushed into being DBAs on smaller projects far too often and we're usually not fully competent to handle it on some level. Its not a knock on you or I etc, but imho most application slow downs relate to database bottlenecks and thats why prime time DBAs can get paid a lot more than I do :) ~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275137 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Tons of records - archive, or what?
How many indexes do you have setup on the table? I had a table with about 6 million records that I inherited and it only had an index setup on the primary key. It caused about 300-3000ms overhead on any page that accessed it. It took me a couple days but once I found it and added the indexes that were most commonly used (in this case only 1 that had 2 conditions), it reduced that 300-3000 to about 3ms. Just some food for thought. the post on the phpbb pages from the go gaia developer is here: http://www.phpbb.com/community/viewtopic.php?f=18&t=135383 Sorry, some of it is phpbb specific but there are a lot of good posts in the topic about indexing and some other types of performancing tweaking. I found it really interesting anyhow :) ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275132 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Tons of records - archive, or what?
Just joined up thanks for the info dana! I did find some performance info thru googling. I can't even query 200,000 records without my db givin out. Weird! Thanks,Will ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275109 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Tons of records - archive, or what?
sorry for the typos, I've been under the weather all week lol. anyhow the url is really http://www.gaiaonline.com/ now. sorry. ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275105 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Tons of records - archive, or what?
If you are properly configured and indexed on your SQL machine it won't be as a big a problem as you may think. For some good insight visit http://go-gaia.com and phpbb.com. The founder of that site posoted some mysql tuning tips but they can apply to other Dbs as well. Go-Gaia is a roleplaying like community with msg boards and games and stuff. At any given time they have something like 3k to 20k people online and sommething like 10 million database records etc. ranted now they are clustered but they weren't at one point. If you still need to go that route the archive table doesn't sound that bad. I've seen an application that only kept 30 days worth of archive in the live system and every night a scheduled task rolled off a day over to the archive mirror table. ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275104 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Tons of records - archive, or what?
On Wednesday 11 Apr 2007, Will Tomlinson wrote: > I have a mySQL db that has lotsa joins, lotsa filters, etc., and could > potentially accumulate millions of records over a year or two. The db > revolves around school semesters. Will queries always want to include all the data, or only the most recent ? -- Tom Chiverton Helping to globally drive leading-edge experiences on: http://thefalken.livejournal.com This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275059 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Tons of records - archive, or what?
Hey, I have a mySQL db that has lotsa joins, lotsa filters, etc., and could potentially accumulate millions of records over a year or two. The db revolves around school semesters. I'm worried about performance issues as time goes on. But I need to keep these records searchable. I've come up with a few ideas but thought you folks might have better solutions. Mine are: 1. Create a parallel set of tables prefixed with archive. Move records there every so often. 2. Allow users to create new tables with semester suffixes. Then search off those with a variable placed at the end of the table. 3. Save all the rollups/queries themselves in the db somehow. Anyone have ideas on this one? Thanks, Will ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275041 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
