Re: Data in different tables or is one big table just as fast?
We have a table that grow by 200MB each day. Should we put data in different tables or is one big table just as fast? The table contains data from RSS and Atom feeds. Most users only need to see the newest items. A select could look like this: SELECT title, desc FROM items WHERE feedid = 25 ORDER BY id DESC LIMIT 10 I would, however, be seriously concerned about diskspace if a table is adding 200 MB a day with no archiving/compression/purges. What if we use COMPRESS() for the text in old rows? Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data in different tables or is one big table just as fast?
how about purging rows older than a month? Do you need to keep them? Archive them them to another database? Actually, I got a better idea. Have your master db which is huge and holds everything. Then on a seperate DB run a table for each feedid with the last 100 feeds for that id. Have a cron job that runs continually updating those tables with current data. get it? On Tue, 15 Feb 2005 23:02:38 +0100, Jacob Friis Larsen [EMAIL PROTECTED] wrote: We have a table that grow by 200MB each day. Should we put data in different tables or is one big table just as fast? The table contains data from RSS and Atom feeds. Most users only need to see the newest items. A select could look like this: SELECT title, desc FROM items WHERE feedid = 25 ORDER BY id DESC LIMIT 10 I would, however, be seriously concerned about diskspace if a table is adding 200 MB a day with no archiving/compression/purges. What if we use COMPRESS() for the text in old rows? Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Ryan McCullough mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data in different tables or is one big table just as fast?
how about purging rows older than a month? Do you need to keep them? Yes. Archive them them to another database? We are currently archiving them to another table, where we compress the text. Actually, I got a better idea. Have your master db which is huge and holds everything. Then on a seperate DB run a table for each feedid with the last 100 feeds for that id. That would be some 40 tables. What about the Drawbacks to Creating Many Tables in the Same Database http://dev.mysql.com/doc/mysql/en/creating-many-tables.html Have a cron job that runs continually updating those tables with current data. get it? I think so :) Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data in different tables or is one big table just as fast?
We have a table that grow by 200MB each day. Should we put data in different tables or is one big table just as fast? We will for new data do select, update and insert and for old data only select. Any help would be great. Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data in different tables or is one big table just as fast?
Jacob Friis Larsen [EMAIL PROTECTED] wrote on 28/01/2005 11:23:46: We have a table that grow by 200MB each day. Should we put data in different tables or is one big table just as fast? We will for new data do select, update and insert and for old data only select. It is not possible to answer in the general case - you would need to explain more about your table and usage patterns. However, if you can use MyISAM tables, you should at least consider using MERGE tables: http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html If your database is well indexed and your searches can generate relatively small amounts of data on the first key used, the overhead of having enormous files is small. At the other end, if the MySQL is reduced to a linear search, the impact of a huge file is enormous. If most of your SELECTs are on recent data, it is work considering splitting the table into daily, weekly, or monthly sub-tables. This means that you can archive very old months, and pack recent months, while keeping only the current month active. But if you need to search the entire archive frequently, this will reduce performance. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data in different tables or is one big table just as fast?
On Fri, 28 Jan 2005 11:47:47 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Jacob Friis Larsen [EMAIL PROTECTED] wrote on 28/01/2005 11:23:46: We have a table that grow by 200MB each day. Should we put data in different tables or is one big table just as fast? We will for new data do select, update and insert and for old data only select. It is not possible to answer in the general case - you would need to explain more about your table and usage patterns. The table contains data from RSS and Atom feeds. Most users only need to see the newest items. A select could look like this: SELECT title, desc FROM items WHERE feedid = 25 LIMIT 10 However, if you can use MyISAM tables, you should at least consider using MERGE tables: http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html I will. If your database is well indexed and your searches can generate relatively small amounts of data on the first key used, the overhead of having enormous files is small. At the other end, if the MySQL is reduced to a linear search, the impact of a huge file is enormous. I'm not sure I understand this. If most of your SELECTs are on recent data, it is work considering splitting the table into daily, weekly, or monthly sub-tables. This means that you can archive very old months, and pack recent months, while keeping only the current month active. But if you need to search the entire archive frequently, this will reduce performance. I only need to go through the entire archive when indexing via Swish or when Google stops by. Thanks for your help. Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data in different tables or is one big table just as fast?
Jacob Friis Larsen wrote: On Fri, 28 Jan 2005 11:47:47 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Jacob Friis Larsen [EMAIL PROTECTED] wrote on 28/01/2005 11:23:46: We have a table that grow by 200MB each day. Should we put data in different tables or is one big table just as fast? We will for new data do select, update and insert and for old data only select. It is not possible to answer in the general case - you would need to explain more about your table and usage patterns. The table contains data from RSS and Atom feeds. Most users only need to see the newest items. A select could look like this: SELECT title, desc FROM items WHERE feedid = 25 LIMIT 10 Jacob: The above query should be very fast as long as you have a key on feedid even if the table is very large. I see one problem with it, though - in this form it is not guaranteed to give you the most recent items. You need to add an ORDER BY some timestamp DESC for it to work right. I would, however, be seriously concerned about diskspace if a table is adding 200 MB a day with no archiving/compression/purges. -- Sasha Pachev AskSasha Linux Consulting http://www.asksasha.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
