MySQL: Check entire column if any row has value

Question

I want to switch conditionally if all rows are empty for audio column. I have tried with GROUP BY type but doesn't works.

  SELECT postid, title, has_audio, audio, type 
  FROM qa_posts 
  WHERE parentid=1

So if audio exist for entire column for that parent id that execute code A if not than code B

Answer 1

You could just count all rows for which audio is not NULL. But if your table is bigger and contains tons of rows etc. then the real problem with this approach is that such query this may be expensive for DB and at least be slower than it could be. Of course premature optimization is bad thing, but this is not the case - we are going to optimize the approach, not the code :)

want to switch conditionally if all rows are empty for audio column

First, it would be good to think the question are all rows' audio column empty? it really the right one. And, no. It's not :) In fact you neither need to know how many of rows are not null nor are all rows' audio column empty? because it's completely irrelevant for your condition if there's 5, 10 or 100000's. What you should do is to "flip" the question and look for the answer to question like if there's any row that is NOT null?. And even both questions look similar at first glance, we are using database to get the answer, so this actually is the whole game changer.

By logic, to answer the if there's any row that is NOT null? question it is just sufficient to find out if there at least one row with audio being NOT NULL. The "at least" thing allows us apply some constraints to the query, because 1 fulfills "at least one" condition nicely, we will use SQL LIMIT 1, which would make DB stop and return the result as soon as it finds first record matching the query, w/o need of going thru whole table. This simply should be faster, and less resource hungry.

Once executed, our query can return two results. Either we get nothing (zero rows returned), which means that all audio's are NULL or, as opposite, we get something (single row, does not matter which one) which would mean the answer to if there's any row that is NOT null? question is 'yes, it is' (because at least the one returned is).

Answer 2

Do a select statement with a select count(*) for WHERE audio != null. Then do a conditional statement based off the number you receive. Make sure audio is indexed.