Best Way to Test If a Row Exists in a MySQL Table

Best way to test if a row exists in a MySQL table

You could also try EXISTS:

SELECT EXISTS(SELECT * FROM table1 WHERE ...)

and per the documentation, you can SELECT anything.

Traditionally, an EXISTS subquery starts with SELECT *, but it could
begin with SELECT 5 or SELECT column1 or anything at all. MySQL
ignores the SELECT list in such a subquery, so it makes no difference.

an efficient way to test if a table row exists

I would do

SELECT COUNT(1) FROM table 1 WHERE some_condition.

But I don't think it makes a significant difference unless you call it a lot (in which case, I'd probably use a different strategy).

Check if row exists, The most efficient way?

By best I guess you mean consuming the least resources on both MySQL server and client.

That is this:

      SELECT COUNT(*) count FROM table WHERE id=1

You get a one-row, one-column result set. If that column is zero, the row was not found. If the column is one, a row was found. If the column is greater that one, multiple rows were found.

This is a good solution for a few reasons.

1. COUNT(*) is decently efficient, especially if id is indexed.
2. It has a simple code path in your client software, because it always returns just one row. You don't have to sweat edge cases like no rows or multiple rows.
3. The SQL is as clear as it can be about what you're trying to do. That's helpful to the next person to work on your code.

Adding LIMIT 1 will do nothing if added to this query. It is already a one-row result set, inherently. You can add it, but then you'll make the next person looking at your code wonder what you were trying to do, and wonder whether you made some kind of mistake.

COUNT(*) counts all rows that match the WHERE statement. COUNT(id) is slightly slower because it counts all rows unless their id values are null. It has to make that check. For that reason, people usually use COUNT(*) unless there's some chance they want to ignore null values. If you put COUNT(id) in your code, the next person to work on it will have to spend some time figuring out whether you meant anything special by counting id rather than *.

You can use either; they give the same result.

Check if a row exists using MySQL 8 version

SELECT .. INTO does not return the rowset. EXISTS needs in rowset. So SELECT .. INTO cannot be used in EXISTS.

Remove it:

SELECT EXISTS ( SELECT tTbl 
                FROM t_table 
                WHERE tTbl = "t_contents_1_2021" );

If you need both check the row existence and save the value to the variable then use inline assigning:

SELECT EXISTS ( SELECT NULL
                FROM t_table 
                WHERE (@tTbl := tTbl) = "t_contents_1_2021" );

SQL: How to properly check if a record exists

It's better to use either of the following:

-- Method 1.
SELECT 1
FROM table_name
WHERE unique_key = value;

-- Method 2.
SELECT COUNT(1)
FROM table_name
WHERE unique_key = value;

The first alternative should give you no result or one result, the second count should be zero or one.

How old is the documentation you're using? Although you've read good advice, most query optimizers in recent RDBMS's optimize SELECT COUNT(*) anyway, so while there is a difference in theory (and older databases), you shouldn't notice any difference in practice.

Most efficient way to determine if a row EXISTS and INSERT into MySQL using java JDBC

Let the database do the work.

You should do the second method. If you don't want to get a failure, you can use on duplicate key update:

insert into t(pk1, pk2, . . . )
    values ( . . . )
    on duplicate key update set pk1 = values(pk1);

The only purpose of on duplicate key update is to do nothing useful but not return an error.

Why is this the best solution? In a database, a primary key (or columns declared unique) have an index structure. This is efficient for the database to use.

Second, this requires only one round-trip to the database.

Third, there are no race conditions, if you have multiple threads or applications that might be attempting to insert the same record(s).

Fourth, the method with on duplicate key update will work for inserting multiple rows at once. (Without on duplicate key insert, then a multi-value statement would fail if a single row is duplicated.) Combining multiple inserts into a single statement can be another big efficiency.

Your second option is really the right way to go.

Fastest way to determine if record exists

SELECT TOP 1 products.id FROM products WHERE products.id = ?; will outperform all of your suggestions as it will terminate execution after it finds the first record.

Simple mysql Query to check if row exist

There are a lot of ways of doing this really but if you arnt going to use any more information then weither or not the user has liked it doing select * is a bad idea. The reason why is that you are asking the database to return the value of every column in that table.

Assuming its a small database its probably not a problem no but as your database gets bigger you are puting more load on it then you need you should try and only select the columns you need and intend to use. Ok in this case the userid is probably indexed and its only one row, but if you get in the habit of doing it here you may do it else where as well.

try this instead.

$userid=$_COOKIE['userid'];
$sql = "SELECT count(user_id) as total FROM likes WHERE `user_id`='{$userid}'"; 
$query = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($query);

if( $row ['total'] > 0){
    echo "unlike";
}
else{
    echo "like";
}

This way we are just getting the total. simple and elegant

MySQL check row exists but do not return it

try this:

SELECT EXISTS(SELECT * FROM Sometable WHERE id = {Some_id})

see documentation

---

Related Topics