2

SQL Queries /P1/

  1. SELECT EXISTS(SELECT /p2/ FROM table WHERE id = 1)
  2. SELECT /p2/ FROM table WHERE id = 1 LIMIT 1

SQL SELECT /P2/

  1. COUNT(id)
  2. id

PHP PDO Function /P3/

  1. fetchColumn()
  2. rowCount()

From the following 3 Parts, What is the best method to check if a row exists or not with and without the ability to retrieve data like.

Retrievable:

/Query/ SELECT id FROM table WHERE id = 1 LIMIT 1

/Function/ rowCount()

Irretrievable

/Query/ SELECT EXISTS(SELECT COUNT(id) FROM table WHERE id = 1)

/Function/ fetchColumn()

In your opinion, What is the best way to do that?

Axon
  • 439
  • 1
  • 4
  • 13
  • 1
    The most efficient way - in fact, the only efficient way - is not to check. – Strawberry Aug 05 '17 at 21:34
  • @Strawberry Well, If i wanted to check if it exists or not, To show the default value or the existed value, Which way to use? – Axon Aug 05 '17 at 21:35
  • Have you tried writing this out and using the query analyser? – twoleggedhorse Aug 05 '17 at 21:35
  • @twoleggedhorse nope, Just knew about it from you now, The query analyser. – Axon Aug 05 '17 at 21:36
  • Free alternatives to the enterprise query analyser https://stackoverflow.com/questions/2056840/mysql-query-analyzer-free-solutions – twoleggedhorse Aug 05 '17 at 21:43
  • There shouldn't be noticeable difference between those queries. Creating a connection to database usually takes more time than executing basic queries. – Ram Aug 05 '17 at 21:45

1 Answers1

6

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Would it be bad if we added `LIMIT 1` at the end of this query? And why `COUNT(*)`instead of `COUNT(id)` – Axon Aug 05 '17 at 21:38
  • 1
    Limit 1 is pointless as count only ever returns 1 value if you do not aggregate (group by) – twoleggedhorse Aug 05 '17 at 21:45
  • 1
    Again, unless you are aggregating, count(*) will only count rows and will use your clustered index if you have one, which is in most cases your primary key – twoleggedhorse Aug 05 '17 at 21:47
  • The other thing to mention is that exists is a function so requires additional processing after the select – twoleggedhorse Aug 05 '17 at 21:51