Geek Answers Handbook

Effective way to check for table row presence

I am trying to find the most efficient way to determine if a table exists.

I mean 3 options:

  • SELECT EXISTS (SELECT 1 FROM table1 WHERE some_condition);

  • SELECT 1 FROM table1 WHERE some_condition LIMIT 0,1;

  • SELECT COUNT (1) FROM table1 WHERE some_condition;

For MySQL, the first approach seems to be more efficient: The best way to check if a row exists in the MySQL table

Is this overall for any database?

UPDATE:

I added a third option.

UPDATE2:

Suppose the database products are mysql, oracle, and sql-server.

+2
source share
5 answers

I would do

SELECT COUNT(1) FROM table 1 WHERE some_condition.

But I don’t think this is significant unless you name it a lot (in which case I would probably use a different strategy).

+4
source

If you want to use as a test, if there is an AT LEAST ONE string with some condition (1 or 0, true or false), then:

 select count(1) from my_table where ... and rownum < 2;

Oracle may stop counting after it gets a hit.

+4
source

Exists are faster because it will return the number of results that match the subquery, not the entire result.

+3
source

Different methods have different pros and cons:

 SELECT EXISTS(SELECT 1 FROM table1 WHERE some_condition);

may be the fastest in MySQL but

 SELECT COUNT(1) FROM table 1 WHERE some_condition

as in @Luis's answer, gives you the score.

In addition, I recommend that you take a look at your business logic. Very rarely you just need to see if a line exists, most often you want

  • either use these lines, so just select and handle the case with 0 lines
  • or you want to change these lines, in which case just do your update and check mysql_affected_rows ()
  • If you want to INSERT .. ON DUPLICATE KEY row if it does not already exist, take a look at INSERT .. ON DUPLICATE KEY or REPLACE INTO
+3
source

The exists function is mainly defined in SQL, it is not only a MySQL function: http://www.techonthenet.com/sql/exists.php and I usually use this function to check if a particular row exists.

However, in Oracle, I have seen many times a different approach proposed earlier:

 SELECT COUNT(1) FROM table 1 WHERE some_condition.
+1
source

More articles:


All Articles