At 03:16 AM 8/25/2009, you wrote:
Hi everyone,
Is there any forseeable issue with having an extremely large data set, say 1
TB in size for a single database and doing a SELECT * FROM tbl WHERE
<constraints> where <constraints> are super restrictive (in that they return
only a few rows since only a few match) and the proper indexes are in place?
This is on the InnoDB engine.
I was curious if there are any problems where doing a read on a large
dataset has huge problems down the road. Let's assume the server is a quad
core with 4 GB of RAM. Surely it shouldn't have a *huge* effect?
Sincerely,
Suhail Doshi
Suhail,
I think your biggest problem is getting the 1TB of data inserted into
an InnoDb table. It is going to take an extremely long time. Why does it
have to be InnoDb? Are you running transactions on the table the same time
you're reading from it? If you don't need transactions or RI, may I
suggest creating several MyISAM tables and then use a Merge table on that.
It has worked really well for me. I have each table holding 1 year's worth
of data. This allows me to rebuild any one table quite quickly and I can
have separate processes loading data for each year simultaneously without
locking problems. Once the data is loaded, I can then run a query on the
merge table to pull data from all of the tables.
Also there are other 3rd party engines that may be better depending on
what type of data you are storing.
Take a look at InfoBright at
http://www.mysql.com/news-and-events/generate-article.php?id=1180
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
