mysql - optimize expensive php query/subquery -

i have table 3 columns: submission_id, column_id, data. each submission_id has 25 columns , data values.

i have page displays submissions column_id = 16 , data = ('' or 0). this, use subquery distinct submission_id's need, , columns in main query. query is:

select sid,cid,data webform_submitted_data sid in(select distinct sid webform_submitted_data cid=16 , data in (' ',0)) order sid asc

the table getting large, , query takes 30-40 seconds when run php, (though 1.0e-6 seconds mysql) not php overhead, checked using mysqld-slow.log file, following: <-- # query_time: 32.975552 lock_time: 0.000138 rows_sent: 108 rows_examined: 177396 -->

i tried running explain in php ![explain]:(http://i.imgur.com/692eyhf.png)

one more thing, page updates current submission , puts id value in column_id 16, takes off of page when reloads. reloads without update take less second, when need update 100 records, rebuilds cache every time.

any thoughts appreciated.

it's dependent subquery reported explain costly. means executes subquery once each distinct sid in outer query. how overhead costs relative number of distinct sid values on 180000+ rows.

you can try query ensure subquery executed once. may need store results in temp table, temp table have ~7 rows, according explain.

select d1.sid, d1.cid, d1.data  webform_submitted_data d1  inner join (   select distinct sid webform_submitted_data    cid=16 , data in (' ',0)) d2 using (sid) order d1.sid asc