1

I have read many article about this one. I want to hear from you. My problem is: A table: ID(INT, Unique, Auto Increase) , Title(varchar), Content(text), Keywords(varchar)

My PHP Code will always do insert new record, but not accept duplicated record base on Title or Keywords. So, the title or keyword can't be Primary field. My PHP Code need to do check existing and insert like 10-20 records same time.

So, I check like this:

SELECT * FROM TABLE WHERE TITLE=XXX

And if return nothing, then I do INSERT. I read some other post. And some guy say:

INSERT IGNORE INTO Table values()

An other guy suggest:

SELECT COUNT(ID) FROM TABLE

IF it return 0, then do INSERT

I don't know which one faster between those queries.

And I have 1 more question, what is different and faster on those queries too:

SELECT COUNT(ID) FROM ..
SELECT COUNT(0) FROM ...
SELECT COUNT(1) FROM ...
SELECT COUNT(*) FROM ...

All of them show me total of records in table, but I don't know do mySQL think number 0 or 1 is my ID field? Even I do SELECT COUNT(1000) , I still get total records of my table, while my table only have 4 columns.

I'm using MySQL Workbench, have any option for test speed on this app?

Vasil Lukach
  • 3,658
  • 3
  • 31
  • 40
  • Dup of ["More Time Consuming/CPU Extensive `INSERT IGNORE` or `Select Count(*)`"](http://stackoverflow.com/questions/5443081/), ["`COUNT(*)` vs. `COUNT(1)` vs. `COUNT(pk)`: which is better?"](http://stackoverflow.com/questions/2710621/), ["How to skip duplicates using INSERT INTO in a better optimized way?"](http://stackoverflow.com/questions/4988278/). Possible dup of ["Which one is faster"](http://stackoverflow.com/questions/1328074/). See also ["What is the reason not to use select *?"](http://stackoverflow.com/questions/321299/). – outis Jan 24 '12 at 01:59
  • See also ["Performing an UPDATE or INSERT depending whether a row exists or not in MySQL"](http://stackoverflow.com/questions/1492761/) to update rather than ignore duplicates. – outis Jan 24 '12 at 02:13

2 Answers2

1

I would use insert on duplicate key update command. One important comment from the documents states that: "...if there is a single multiple-column unique index on the table, then the update uses (seems to use) all columns (of the unique index) in the update query."

So if there is a UNIQUE(Title,Keywords) constraint on the table in the example, then, you would use: 

INSERT INTO table (Title,Content,Keywords) VALUES ('blah_title','blah_content','blah_keywords')
  ON DUPLICATE KEY UPDATE Content='blah_content';

it should work and it is one query to the database.

Aleksandar Vucetic
  • 14,715
  • 9
  • 53
  • 56
0

SELECT COUNT(*) FROM .... is faster than SELECT COUNT(ID) FROM .. or build something like this:

INSERT INTO table (a,b,c) VALUES (1,2,3)
     ON DUPLICATE KEY UPDATE c=3;
John Woo
  • 258,903
  • 69
  • 498
  • 492