Tag Info

New answers tagged

0

I think there is a case for changing the indexes here because a) you have a task to do (these multiple queries) b) data warehouse volumes (25+ million rows) and c) a performance problem. This would also be a good use case for non-clustered columnstore indexes introduced in SQL Server 2012, ie summarise / aggregate a few columns on a large table with many ...


4

The question is mainly about how to optimize the select statement: SELECT [TABLE], [FIELD], [AFTER], [DATE] FROM mytbl WITH (NOLOCK) WHERE [TABLE] = 'OTB' AND [FIELD] = 'STATUS' Removing the redundant projections and adding the presumed dbo schema: SELECT [AFTER], [DATE] FROM dbo.mytbl WITH (NOLOCK) WHERE [TABLE] = 'OTB' AND FIELD = 'STATUS'; Without ...


2

Your query: SELECT scratch_tickets.id AS ticket_id, -- more columns FROM (scratch_tickets) WHERE scratch_tickets.created_at >='2015-02-12 00:00:00' AND created_at <= '2015-02-18 23:59:59' AND scratch_tickets.status = 4 LIMIT 50 ; needs a simple index on (status, created_at): ALTER TABLE scratch_tickets ADD INDEX ...


0

It's probably fastest to use COPY (or \copy) to copy the data into unencrypted tables. Disable triggers, foreign keys, indexes. Then encrypt, and add your indexes and keys again. Use one SQL operation to encrypt, like update my_table set bytea1 = pgp_sym_encrypt_bytea(bytea1,var_pwd), bytea2 : = pgp_sym_encrypt_bytea(bytea2,var_pwd) ....; ...


1

Is it common to spread data of a single user operation across multiple rows? Think of the "entities": User, Review, Rating. A "user" may go to the restaurant many times, and give a "review" each time. The "review" may give ratings for multiple things, such as 'food' and 'location'. These are "Many-to-one" relationships, so Ratings has a review_id ...


1

First question: You can't if you are not running on a replica set. On a replica set you may follow that guide http://docs.mongodb.org/manual/tutorial/build-indexes-on-replica-sets/. Indexes are persistent, upon an index is created it doesn't rebuild on start-up unless something trigger it (maybe your application?) Second question: I don't see any index ...


1

The real problem is all the I/O that is needed in creating indexes on huge tables. When the index is being created, does SHOW PROCESSLIST say "Repair by key_buffer" or "by sorting"? Sorting is better for large tables. Are you running only MyISAM? If so, make these changes: innodb_buffer_pool_size = 0 key_buffer_size = 3G However, if you switched to ...


0

If you are running Developer or Enterprise edition, consider restoring from a database snapshot rather than a full backup (https://msdn.microsoft.com/en-s/library/ms189281.aspx). This can also be less resource-intensive than a full restore. The time needed to revert from a snapshot is largely proportional to the amount of changes made since the snapshot ...


-1

Instant file initialization doesn't apply to transaction log files. So, you may also help yourself by precreating and/or adjusting the destination database to at least the sizes from the database you are restoring from. Thus, SQL Server won't have to zero out any files during the restore.


2

OPTIMIZE TABLE is almost never needed on InnoDB. Are you DELETEing records based on age? If so, you can make the "bulk delete" essentially free by using PARTITIONing and DROP PARTITION. More details here: http://mysql.rjweb.org/doc.php/partitionmaint



Top 50 recent answers are included