Tag Info

Hot answers tagged

4

It sounds like this is more of a DataWarehouse workload than an OLTP. In that case, heavily indexed tables are normal. Keep in mind a couple of things, when you do your nightly loads, drop the indexes first then reindex after. Also, there will be a higher storage cost but generally this wont be a problem (but be aware of it). I know your using 2008R2 but ...


3

Forget about mysqltuner and check for human advice. This tool tells you general recommendations that may be useless and even hurtful in some cases. Optimize table is probably going to be useless, but it locks your tables for writes. A consultant may save you time and money in the long run. Swapping should be a no-go for MySQL. Make sure to tune your ...


2

Assuming you have 300 Bytes per row, that makes a whopping 95GB per three weeks - that's not very much in today's terms - a 1TB disk would last 30 weeks - that's almost 1/2 of a year. If you compressed this data, I'm fairly sure that you could store at least a couple of years (possibly a lot more) on a single 1TB disk. I would keep the "live" data on one ...


2

Back in the day we would bulk load our data in this way: Drop indexes Load data in the order for which the clustered index would be built (i.e., you export the data in a precise way) After the load is completed, create the clustered index Next, create any additional non-clustered indexes Miller time (this was before I could afford decent beer) That ...


2

bytea will be optimal for storing the hash. It'll be transferred in/out of the database as a hex string anyway, unless you use PostgreSQL's binary wire protocol (supported by libpq and partly by PgJDBC) to transfer them. For best results, store as bytea and have the client application use a PQexecParams call that requests binary results. Though on ...


1

See this article about the performance problems and possible solutions using ORDER BY ... LIMIT. I would create an index on ord.ID DESC and remove the subquery. This is assuming that ID is not a primary key and indexed already. SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, ...


1

If you are doing a large, load operation it is faster to utilize the TokuMX bulk loader, as it only requires one pass over the data to create both the primary key index and any secondary indexes. More information is available in the documentation at http://docs.tokutek.com/tokumx/tokumx-commands.html#tokumx-new-commands-loader



Only top voted, non community-wiki answers of a minimum length are eligible