Most Popular
1500 questions
64
votes
4 answers
Which is faster, InnoDB or MyISAM?
How can MyISAM be "faster" than InnoDB if
MyISAM needs to do disk reads for the data?
InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?
jcho360
- 1,999
- 8
- 23
- 31
64
votes
3 answers
How to install the additional module pg_trgm
I just want to know how to install the module pg_tgrm as used in the trigram indexing scheme that allows you to do un-anchored search patterns on an index.
WHERE foo LIKE '%bar%';
Lizardie
- 773
- 1
- 5
- 8
64
votes
4 answers
Does running pg_dump on live db produce consistent backups?
I have a 3GB database that is constantly modified and I need to make backups without stopping the server (Postgres 8.3).
My pg_dump runs for 5 minutes. What if the data is modified during the process? Do I get consistent backups? I don't want to…
Roman
- 789
- 1
- 6
- 7
63
votes
2 answers
Reference column alias in same SELECT list
I'm converting an old MS-Access-based system to PostgreSQL. In Access, fields that were made up in SELECTs could be used as parts of equations for later fields, like this:
SELECT
samples.id,
samples.wet_weight / samples.dry_weight - 1 AS…
wizpig64
- 733
- 1
- 5
- 5
63
votes
2 answers
Cast to date is sargable but is it a good idea?
In SQL Server 2008 the date datatype was added.
Casting a datetime column to date is sargable and can use an index on the datetime column.
select *
from T
where cast(DateTimeCol as date) = '20130101';
The other option you have is to use a range…
Mikael Eriksson
- 22,175
- 5
- 59
- 103
63
votes
6 answers
About single threaded versus multithreaded databases performance
H2 is a single threaded database with a good reputation regarding performance. Other databases are multi-threaded.
My question is: when does a multi-thread database become more interesting than an single thread database? How many users? How many…
Jérôme Verstrynge
- 1,461
- 5
- 22
- 27
63
votes
6 answers
Why not use a table instead of a materialized view?
I'm new to Oracle databases. If I have understood correctly, materialized view is a view which result set is saved as a physical table in the database and this view/table is refreshed bases on some parameter. If view is saved as a physical table,…
jrara
- 5,333
- 20
- 56
- 65
63
votes
6 answers
Date range rolling sum using window functions
I need to calculate a rolling sum over a date range. To illustrate, using the AdventureWorks sample database, the following hypothetical syntax would do exactly what I need:
SELECT
TH.ProductID,
TH.TransactionDate,
TH.ActualCost,
…
Paul White
- 83,961
- 28
- 402
- 634
63
votes
2 answers
How does ORDER BY FIELD() in MySQL work internally
I understand how ORDER BY clause works and how the FIELD() function works.
What i want to understand is how the both of them work together to sort.
How are the rows retrieved and how is the sort order derived
+----+---------+
| id | name …
itz_nsn
- 796
- 1
- 6
- 7
62
votes
8 answers
Restore mysql database with different name
How can we restore mysql database with different name from mysqldump file.
I dont't want to open dump file and edit it. Any other better methods?
Praveen Prasannan
- 1,526
- 6
- 24
- 38
62
votes
7 answers
.bak file not visible in any directory in SSMS
I have a .bak file created today by someone else, manually created through SSMS 2008 R2. I'm trying to manually restore the database, unfortunately the file isn't appearing when I go to browse it.
I can script the restore process, but I've seen this…
Sean Long
- 2,236
- 5
- 22
- 30
62
votes
4 answers
What is the difference between select count(*) and select count(any_non_null_column)?
I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table.
What are the differences between these two statements, if any?
Martin
- 2,420
- 4
- 25
- 35
62
votes
6 answers
What are different ways to replace ISNULL() in a WHERE clause that uses only literal values?
What this isn't about:
This is not a question about catch-all queries that accept user input or use variables.
This is strictly about queries where ISNULL() is used in the WHERE clause to replace NULL values with a canary value for comparison to a…
Erik Darling
- 40,781
- 14
- 130
- 456
62
votes
4 answers
Find IDs from a list that don't exist in a table
Say I have the following schema and data:
create table images(
id int not null
);
insert into images values(1), (2), (3), (4), (6), (8);
I want to perform a query like:
select id from images where id not exists in(4, 5, 6);
But this doesn't…
Patrick D'appollonio
- 725
- 1
- 5
- 7
62
votes
1 answer
Is there any benefit to SCHEMABINDING a function beyond Halloween Protection?
It is well-known that SCHEMABINDING a function can avoid an unnecessary spool in update plans:
If you are using simple T-SQL UDFs that do not touch any tables (i.e. do not access data), make sure you specify the SCHEMABINDING option during creation…
Paul White
- 83,961
- 28
- 402
- 634