Select only rows with at least 2 non-missing values I'm pretty new to MySQL (used to programming in R/Stata), and getting stuck on this - I'm probably going about it the wrong way. Any pointers would be appreciated!  

I have a large database (a couple million rows) and want to select records where at least two fields from a selection of variables are non-missing (not NULL). I can find lots on counting missing values by column, but not by row. 

As an example, I would like to write a select statement for the table below, based only on var1-var5, that would select ID 1 and 3 as they have at least 2 non-NULL values. 

ID | var1 | var2 | var3 | var4  | var5 | var6 | var7
1  | NULL | 4    | X    | NULL  | NULL | NULL | R69
2  | NULL | NULL | NULL | NULL  | 1    | X    | J10
3  | 4    | 5    | 23   | 1jan03| 2    | 22   | Z38


I was thinking creating a user-defined variable to count the number of non-missing values and delete rows where this is &lt;2, but it doesn't seem to work. 

SET @include = 0;
SELECT var1, var2, var3, var4, var5 FROM db1;
@include := @include + 1 if var1 is not null;
DELETE FROM db1 WHERE @include&lt;2;


I'm guessing this is something I can do with a WHERE statement in the SELECT line, but I can only think of writing it so it only selects rows where everything is non-missing, not at least 2 values.
I have the following way to get the IDs of the rows that have at least two not null values; however, I am afraid this is not efficient when it comes to few million rows per table. I'd like to share it anyway:

mysql&gt; select * from l;
+------+------+------+------+------+------+------+------+
| id   | var1 | var2 | var3 | var4 | var5 | var6 | var7 |
+------+------+------+------+------+------+------+------+
|    1 | NULL |    4 |    9 | NULL | NULL | NULL |    7 |
|    2 | NULL | NULL | NULL | NULL | NULL |    1 |    2 |
|    3 |    4 |    5 |   23 |    1 |    2 |    4 |   22 |
+------+------+------+------+------+------+------+------+
3 rows in set (0.00 sec)

mysql&gt; select id, 5-(isnull(var1)+isnull(var2)+isnull(var3)+isnull(var4)+isnull(var5)) AS non_null_count from l;
+------+----------------+
| id   | non_null_count |
+------+----------------+
|    1 |              2 |
|    2 |              0 |
|    3 |              5 |
+------+----------------+
3 rows in set (0.00 sec)

mysql&gt; SELECT id FROM (select id, 5-(isnull(var1)+isnull(var2)+isnull(var3)+isnull(var4)+isnull(var5)) AS non_null_count from l)x WHERE non_null_count&gt;=2;
+------+
| id   |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.01 sec)


Basically, I am "counting" how many non-null values exist in each row.