CODEWITHSUNDEEP
mysqlsqldatabase
bodacious
bodacious

Reputation: 6695

Which of these SQL approaches is optimal?

I have a fairly large table (several thousand records) and wan't to make sure duplicates can't be created on the same day.

Any SQL gurus out there know which of these approaches is optimal and, if so, could you explain why?

{ created at is a DATETIME column }
SELECT `records`.* 
 FROM `records` 
 WHERE DATE(records.created_at) = '2012-02-02';

SELECT `records`.* 
FROM `records` 
WHERE records.created_at BETWEEN '2012-02-02 00:00:00' AND '2012-02-02 23:59:59';

Upvotes: 1

Views: 80

Answers (3)

Odobenus Rosmarus
Odobenus Rosmarus

Reputation: 5998

if compare that approaches, second one is certainly better. in the first variant DATE(somefield) prevents optimizer to use any index. So in the first variant - mysql will scan the whole table.

BTW: they are not equal queries. First one selects only '02-02-2012', second one selects 2 and 3 feb.

But. both variants are not appropriate, if you need just avoid of duplicates. Use unique index for that.

Upvotes: 1

cHao
cHao

Reputation: 86506

The second is less likely to trigger a table scan (assuming, of course, that you've indexed created_at).

Whenever you do some stuff to a column before you check it (unless the server is smart enough to realize it doesn't have to, which it quite often isn't), that stuff has to be done to each row's value before the test. Meaning every row in the table needs to be checked. If you have an index on created_at, the server may well ignore it.

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

This looks like MySQL(not my forte) so I might be talking totally nonsense but I would look into

  • Creating a calculated column create_at_date as DATE
  • Add a Unique Index on the calculated column.

Something like

CREATE TABLE records (
  created_at DATETIME
  , created_at_date AS DATE(created_at)
  , ...
)

CREATE UNIQUE INDEX UIX_RECORDS_CREATED_AT_DATE ON Records (created_at_date)

Upvotes: 2

Related Questions