Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
50
votes
4 answers

Can I add a unique constraint that ignores existing violations?

I have a table which currently has duplicate values in a column. I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added. Can I create a UNIQUE that doesn't check for existing…
Matthew
  • 1,693
  • 2
  • 17
  • 27
50
votes
4 answers

Are individual queries faster than joins?

Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient? TL;DR: If my joined query takes longer than running…
Martin
  • 2,420
  • 4
  • 26
  • 35
50
votes
1 answer

Dump only the Stored Procedures in MySQL

I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?
nakhli
  • 743
  • 2
  • 7
  • 10
50
votes
5 answers

ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT

Most of the forum and example online always suggest to have both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT set to ON whenever someone is asking snapshot, row versioning or similar question. I guess the word SNAPSHOT in both setting get a…
Travis
  • 2,345
  • 2
  • 20
  • 25
50
votes
3 answers

How to speed up queries on a large 220 million rows table (9 gig data)?

The issue: We have a social site where members can rate each other for compatibility or matching. This user_match_ratings table contains over 220 million rows (9 gig data or almost 20 gig in indexes). Queries against this table routinely show up in…
Ranknoodle
  • 661
  • 1
  • 6
  • 8
50
votes
5 answers

Why would I NOT use the SQL Server option "optimize for ad hoc workloads"?

I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one: http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/ Why is there even an option to "optimize for ad hoc…
SomeGuy
  • 2,053
  • 8
  • 35
  • 48
50
votes
20 answers

Trying to use MySQL Workbench with TCP/IP over SSH - failed to connect

I can't connect using TCP/IP over SSH connection in MySQL Workbench from a PC. What's going on? I created a MySQL 5.1 database on an Ubuntu server mysql.myhost.com. I can access it locally. MySQL Workbench (PC) offers to make a connection via TCP…
Dizzley
  • 601
  • 1
  • 5
  • 3
50
votes
4 answers

How to update one table based on another table's values on the fly?

I have a table in the name of ips as below: CREATE TABLE `ips` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `begin_ip_num` int(11) unsigned DEFAULT NULL, `end_ip_num` int(11) unsigned DEFAULT NULL, `iso` varchar(3) DEFAULT NULL, `country`…
Alireza
  • 3,676
  • 10
  • 38
  • 44
50
votes
6 answers

MySQL Set UTC time as default timestamp

How do I set a timestamp column whose default value is the current UTC time? MySQL uses UTC_TIMESTAMP() function for UTC timestamp: mysql> SELECT UTC_TIMESTAMP(); +---------------------+ | UTC_TIMESTAMP() | +---------------------+ | 2012-07-01…
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
50
votes
5 answers

When is it OK to shrink a Database?

I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming. The list goes on... That being said, say I have a 100 GB database and I delete 50 GB of data -- not on one table, but a…
bumble_bee_tuna
  • 977
  • 2
  • 11
  • 20
50
votes
3 answers

MongoDB using too much memory

We've been using MongoDB for several weeks now, the overall trend that we've seen has been that mongodb is using way too much memory (much more than the whole size of its dataset + indexes). I've already read through this question and this question,…
Alireza
  • 1,011
  • 1
  • 10
  • 13
49
votes
4 answers

Is there a tool like Microsoft's "SQL Server Profiler" for MySQL?

While developing on MySQL I really miss being able to fire up a profiler. I find SQLyog is a good enough replacement for Query Analyzer but have not found a tool that works like SQL profiler. For the MySQL folk who have not seen Microsoft's SQL…
Sam Saffron
  • 1,114
  • 1
  • 10
  • 12
49
votes
2 answers

Difference between Hash, Merge and Loop join?

In SQL Server you can specify the join hints: HASH JOIN MERGE JOIN LOOP JOIN What is the definition of those three join hints, and when should each be used?
Andrew Bickerton
  • 3,254
  • 5
  • 30
  • 38
49
votes
2 answers

Is there any difference at all between NUMERIC and DECIMAL?

I know that the NUMERIC and DECIMAL data types in SQL Server work the same: the syntax for creating them is the same, the ranges of values you can store in them is the same, etc. However, the MSDN documentation describes the relationship between the…
KutuluMike
  • 1,599
  • 2
  • 14
  • 13
49
votes
15 answers

Eliminate duplicates in ListAgg (Oracle)

Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row. 11.2 Added the LISTAGG function, so I am trying to use that instead. My problem is that I need to eliminate duplicates in the results and don't seem…
Leigh Riffel
  • 23,884
  • 17
  • 80
  • 155
1 2 3
…
99 100