Mechanism ::

Try to insert the new row into the table
While the insertion fails because a duplicate-key error occurs for a primary key or unique index:

Delete from the table the conflicting row that has the […]

Read Full Post »

ms_user table

user_id
username
email
area_id

1
sherlock
c_sherlock@email.com
1

2
poirot
poirot@email.com
2

3
watson
b_watson@email.com
1

4
hasting
hasting@email.com
2

5
mycroft
a_mycroft@email.com
1

SELECT * FROM `ms_user`
ORDER BY `area_id` , `email` DESC , `username`;

Read Full Post »

The row won’t actually be inserted if it results in a duplicate key.
Syntac ::
Insert ignore into T values(a, b, c)
Insert ignore into T(a, b, c) SELECT a, b, c FROM F
NB :: A Table must have a primary key.

Read Full Post »

A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs.
Use “Group by” when you use aggregate query, and “DISTINCT” when you want to distinct values.
Essentially it is just a matter of syntax in this case, the end result should be same.
Comment ::
Many […]

Read Full Post »

PostgreSQL 7.03

Normal
autocommit on
fsync off

insert update
insert update
insert update

5:16 6:46
n/a n/a
0:10 0:18

I thing that “insert” is faster than “update”.

Read Full Post »