Jon Diamond Jon Diamond - 1 month ago 9
MySQL Question

How can I improve this mysql pivot query?

I'm working with a Drupal site and I need to pull a report of webform submissions.

The Table structure is such:

webform_submissions
- sid
- nid
- serial
- uid
- is_draft
- submitted
- remote_addr

webform_submitted_data
- nid
- sid
- cid
- no
- data


So when a submission is made something like this goes into each table:
webform_submissions

sid: 1, nid:100, serial: 1, uid: 0, is_draft: 0, submitted: [timestamp], remote_addr: 0.0.0.0


webform_submitted_data

nid: 100, sid: 1, cid: 1, no: 0, data: John
nid: 100, sid: 1, cid: 2, no: 0, data: Doe


There is also another table that maps the cid to the form field name but I'm not worried about that.

for every row in the webform_submissions table that matches a nid I need a row with all of the corresponding data in the webform_submitted_data.

result_set
nid: 100, cid_1: John, cid_2: Doe

the actual form data I need has about 30 fields.

This is the best query I have been able to come up with:

SELECT ws.nid, wd_c2.data, wd_c3.data
FROM webform_submissions ws,
webform_submitted_data wd_c2,
webform_submitted_data wd_c3
WHERE ws.nid = 168
AND ws.sid = wd_c3.sid
AND wd_c3.cid = 3
AND ws.sid = wd_c2.sid
AND wd_c2.cid = 2;


That query takes nearly 20 seconds on my local mysql to pull 8 records. I can only assume my approach is not a good one. What is a better way to get all of the rows related to a submission from the webform_submitted_data table into a single row of columns in my result set.

FULL QUERY updated with Joins (returns empty set).

SELECT ws.nid,
wd_c2.data as 'First Name',
wd_c3.data as 'Last Name',
wd_c4.data as 'Email Address',
wd_c5.data as 'Primary Phone',
wd_c6.data as 'Secondary Phone',
wd_c7.data as 'Address',
wd_c8.data as 'City',
wd_c9.data as 'State',
wd_c10.data as 'Zip Code',
wd_c11.data as 'Cf',
wd_c12.data as 'AR',
wd_c13.data as 'G',
wd_c14.data as 'PE',
wd_c15.data as 'PD',
wd_c16.data as 'AN',
wd_c17.data as 'V',
wd_c18.data as 'S',
wd_c19.data as 'V2',
wd_c20.data as 'S',
wd_c21.data as 'Si',
wd_c22.data as 'D',
wd_c23.data as 'M',
wd_c24.data as 'L',
wd_c25.data as 'V',
wd_c26.data as 'C',
wd_c27.data as 'O',
wd_c28.data as 'M',
wd_c29.data as 'P',
wd_c30.data as 'C',
wd_c31.data as 'C',
wd_c32.data as 'C',
wd_c33.data as 'E',
wd_c34.data as 'Ey',
wd_c41.data as 'TD',
wd_c42.data as 'RN',
wd_c43.data as 'TP',
wd_c45.data as 'LM',
wd_c47.data as 'PIp',
wd_c48.data as 'wd',
wd_c49.data as 'Cr',
wd_c50.data as 'N',
wd_c51.data as 'A'

FROM webform_submissions ws
INNER JOIN webform_submitted_data wd_c2 on ws.sid = wd_c2.sid
INNER JOIN webform_submitted_data wd_c3 on ws.sid = wd_c3.sid
INNER JOIN webform_submitted_data wd_c4 on ws.sid = wd_c4.sid
INNER JOIN webform_submitted_data wd_c5 on ws.sid = wd_c5.sid
INNER JOIN webform_submitted_data wd_c6 on ws.sid = wd_c6.sid
INNER JOIN webform_submitted_data wd_c7 on ws.sid = wd_c7.sid
INNER JOIN webform_submitted_data wd_c8 on ws.sid = wd_c8.sid
INNER JOIN webform_submitted_data wd_c9 on ws.sid = wd_c9.sid
INNER JOIN webform_submitted_data wd_c10 on ws.sid = wd_c10.sid
INNER JOIN webform_submitted_data wd_c11 on ws.sid = wd_c11.sid
INNER JOIN webform_submitted_data wd_c12 on ws.sid = wd_c12.sid
INNER JOIN webform_submitted_data wd_c13 on ws.sid = wd_c13.sid
INNER JOIN webform_submitted_data wd_c14 on ws.sid = wd_c14.sid
INNER JOIN webform_submitted_data wd_c15 on ws.sid = wd_c15.sid
INNER JOIN webform_submitted_data wd_c16 on ws.sid = wd_c16.sid
INNER JOIN webform_submitted_data wd_c17 on ws.sid = wd_c17.sid
INNER JOIN webform_submitted_data wd_c18 on ws.sid = wd_c18.sid
INNER JOIN webform_submitted_data wd_c19 on ws.sid = wd_c19.sid
INNER JOIN webform_submitted_data wd_c20 on ws.sid = wd_c20.sid
INNER JOIN webform_submitted_data wd_c21 on ws.sid = wd_c21.sid
INNER JOIN webform_submitted_data wd_c22 on ws.sid = wd_c22.sid
INNER JOIN webform_submitted_data wd_c23 on ws.sid = wd_c23.sid
INNER JOIN webform_submitted_data wd_c24 on ws.sid = wd_c24.sid
INNER JOIN webform_submitted_data wd_c25 on ws.sid = wd_c25.sid
INNER JOIN webform_submitted_data wd_c26 on ws.sid = wd_c26.sid
INNER JOIN webform_submitted_data wd_c27 on ws.sid = wd_c27.sid
INNER JOIN webform_submitted_data wd_c28 on ws.sid = wd_c28.sid
INNER JOIN webform_submitted_data wd_c29 on ws.sid = wd_c29.sid
INNER JOIN webform_submitted_data wd_c30 on ws.sid = wd_c30.sid
INNER JOIN webform_submitted_data wd_c31 on ws.sid = wd_c31.sid
INNER JOIN webform_submitted_data wd_c32 on ws.sid = wd_c32.sid
INNER JOIN webform_submitted_data wd_c33 on ws.sid = wd_c33.sid
INNER JOIN webform_submitted_data wd_c34 on ws.sid = wd_c34.sid
INNER JOIN webform_submitted_data wd_c41 on ws.sid = wd_c41.sid
INNER JOIN webform_submitted_data wd_c42 on ws.sid = wd_c42.sid
INNER JOIN webform_submitted_data wd_c43 on ws.sid = wd_c43.sid
INNER JOIN webform_submitted_data wd_c45 on ws.sid = wd_c45.sid
INNER JOIN webform_submitted_data wd_c47 on ws.sid = wd_c47.sid
INNER JOIN webform_submitted_data wd_c48 on ws.sid = wd_c48.sid
INNER JOIN webform_submitted_data wd_c49 on ws.sid = wd_c49.sid
INNER JOIN webform_submitted_data wd_c50 on ws.sid = wd_c50.sid
INNER JOIN webform_submitted_data wd_c51 on ws.sid = wd_c51.sid
INNER JOIN webform_submitted_data wd_c52 on ws.sid = wd_c52.sid

WHERE ws.nid = 16818

AND wd_c2.cid = 2
AND wd_c3.cid = 3
AND wd_c4.cid = 4
AND wd_c5.cid = 5
AND wd_c6.cid = 6
AND wd_c7.cid = 7
AND wd_c8.cid = 8
AND wd_c9.cid = 9
AND wd_c10.cid = 10
AND wd_c11.cid = 11
AND wd_c12.cid = 12
AND wd_c13.cid = 13
AND wd_c14.cid = 14
AND wd_c15.cid = 15
AND wd_c16.cid = 16
AND wd_c17.cid = 17
AND wd_c18.cid = 18
AND wd_c19.cid = 19
AND wd_c20.cid = 20
AND wd_c21.cid = 21
AND wd_c22.cid = 22
AND wd_c23.cid = 23
AND wd_c24.cid = 24
AND wd_c25.cid = 25
AND wd_c26.cid = 26
AND wd_c27.cid = 27
AND wd_c28.cid = 28
AND wd_c29.cid = 29
AND wd_c30.cid = 30
AND wd_c31.cid = 31
AND wd_c32.cid = 32
AND wd_c33.cid = 33
AND wd_c34.cid = 34
AND wd_c41.cid = 41
AND wd_c42.cid = 42
AND wd_c43.cid = 43
AND wd_c45.cid = 45
AND wd_c47.cid = 47
AND wd_c48.cid = 48
AND wd_c49.cid = 49
AND wd_c50.cid = 50
AND wd_c51.cid = 51
AND wd_c52.cid = 52
LIMIT 20

Answer

Be sure you have proper index on webform_submissions(nid, sid), webform_submitted_data(sid, cid) (and for a better readiing use join notation instead of where/and clause )

    SELECT ws.nid, wd_c2.data, wd_c3.data
    FROM        webform_submissions ws   
    INNER JOIN  webform_submitted_data wd_c2  on ws.sid = wd_c2.sid
    INNER JOIN  webform_submitted_data wd_c3  on ws.sid = wd_c3.sid
    WHERE   ws.nid = 168
    AND     wd_c3.cid = 3
    AND     wd_c2.cid = 2;

eg for the index

 CREATE INDEX ws_id on  webform_submissions(nid, sid)

try using left join (so if a value don't match the query work however)

SELECT ws.nid, 
wd_c2.data  as 'First Name', 
wd_c3.data  as 'Last Name', 
wd_c4.data  as 'Email Address', 
wd_c5.data  as 'Primary Phone', 
wd_c6.data  as 'Secondary Phone', 
wd_c7.data  as 'Address', 
wd_c8.data  as 'City', 
wd_c9.data  as 'State', 
wd_c10.data as 'Zip Code', 
wd_c11.data as 'Cf', 
wd_c12.data as 'AR', 
wd_c13.data as 'G', 
wd_c14.data as 'PE', 
wd_c15.data as 'PD', 
wd_c16.data as 'AN', 
wd_c17.data as 'V', 
wd_c18.data as 'S', 
wd_c19.data as 'V2', 
wd_c20.data as 'S', 
wd_c21.data as 'Si', 
wd_c22.data as 'D', 
wd_c23.data as 'M', 
wd_c24.data as 'L', 
wd_c25.data as 'V', 
wd_c26.data as 'C', 
wd_c27.data as 'O', 
wd_c28.data as 'M', 
wd_c29.data as 'P', 
wd_c30.data as 'C', 
wd_c31.data as 'C', 
wd_c32.data as 'C', 
wd_c33.data as 'E', 
wd_c34.data as 'Ey', 
wd_c41.data as 'TD', 
wd_c42.data as 'RN', 
wd_c43.data as 'TP', 
wd_c45.data as 'LM', 
wd_c47.data as 'PIp', 
wd_c48.data as 'wd', 
wd_c49.data as 'Cr', 
wd_c50.data as 'N', 
wd_c51.data as 'A'

FROM webform_submissions ws 
LEFT JOIN  webform_submitted_data wd_c2    on ws.sid = wd_c2.sid
LEFT JOIN  webform_submitted_data wd_c3    on ws.sid = wd_c3.sid
LEFT JOIN  webform_submitted_data wd_c4    on ws.sid = wd_c4.sid
LEFT JOIN  webform_submitted_data wd_c5    on ws.sid = wd_c5.sid
LEFT JOIN  webform_submitted_data wd_c6    on ws.sid = wd_c6.sid
LEFT JOIN  webform_submitted_data wd_c7    on ws.sid = wd_c7.sid
LEFT JOIN  webform_submitted_data wd_c8    on ws.sid = wd_c8.sid
LEFT JOIN  webform_submitted_data wd_c9    on ws.sid = wd_c9.sid
LEFT JOIN  webform_submitted_data wd_c10   on ws.sid = wd_c10.sid
LEFT JOIN  webform_submitted_data wd_c11   on ws.sid = wd_c11.sid
LEFT JOIN  webform_submitted_data wd_c12   on ws.sid = wd_c12.sid
LEFT JOIN  webform_submitted_data wd_c13   on ws.sid = wd_c13.sid
LEFT JOIN  webform_submitted_data wd_c14   on ws.sid = wd_c14.sid
LEFT JOIN  webform_submitted_data wd_c15   on ws.sid = wd_c15.sid
LEFT JOIN  webform_submitted_data wd_c16   on ws.sid = wd_c16.sid
LEFT JOIN  webform_submitted_data wd_c17   on ws.sid = wd_c17.sid
LEFT JOIN  webform_submitted_data wd_c18   on ws.sid = wd_c18.sid
LEFT JOIN  webform_submitted_data wd_c19   on ws.sid = wd_c19.sid
LEFT JOIN  webform_submitted_data wd_c20   on ws.sid = wd_c20.sid
LEFT JOIN  webform_submitted_data wd_c21   on ws.sid = wd_c21.sid
LEFT JOIN  webform_submitted_data wd_c22   on ws.sid = wd_c22.sid
LEFT JOIN  webform_submitted_data wd_c23   on ws.sid = wd_c23.sid
LEFT JOIN  webform_submitted_data wd_c24   on ws.sid = wd_c24.sid
LEFT JOIN  webform_submitted_data wd_c25   on ws.sid = wd_c25.sid
LEFT JOIN  webform_submitted_data wd_c26   on ws.sid = wd_c26.sid
LEFT JOIN  webform_submitted_data wd_c27   on ws.sid = wd_c27.sid
LEFT JOIN  webform_submitted_data wd_c28   on ws.sid = wd_c28.sid
LEFT JOIN  webform_submitted_data wd_c29   on ws.sid = wd_c29.sid
LEFT JOIN  webform_submitted_data wd_c30   on ws.sid = wd_c30.sid
LEFT JOIN  webform_submitted_data wd_c31   on ws.sid = wd_c31.sid
LEFT JOIN  webform_submitted_data wd_c32   on ws.sid = wd_c32.sid
LEFT JOIN  webform_submitted_data wd_c33   on ws.sid = wd_c33.sid
LEFT JOIN  webform_submitted_data wd_c34   on ws.sid = wd_c34.sid
LEFT JOIN  webform_submitted_data wd_c41   on ws.sid = wd_c41.sid
LEFT JOIN  webform_submitted_data wd_c42   on ws.sid = wd_c42.sid
LEFT JOIN  webform_submitted_data wd_c43   on ws.sid = wd_c43.sid
LEFT JOIN  webform_submitted_data wd_c45   on ws.sid = wd_c45.sid
LEFT JOIN  webform_submitted_data wd_c47   on ws.sid = wd_c47.sid
LEFT JOIN  webform_submitted_data wd_c48   on ws.sid = wd_c48.sid
LEFT JOIN  webform_submitted_data wd_c49   on ws.sid = wd_c49.sid
LEFT JOIN  webform_submitted_data wd_c50   on ws.sid = wd_c50.sid
LEFT JOIN  webform_submitted_data wd_c51   on ws.sid = wd_c51.sid
LEFT JOIN  webform_submitted_data wd_c52   on ws.sid = wd_c52.sid

WHERE   ws.nid = 16818

AND     wd_c2.cid = 2
AND     wd_c3.cid = 3
AND     wd_c4.cid = 4
AND     wd_c5.cid = 5
AND     wd_c6.cid = 6
AND     wd_c7.cid = 7
AND     wd_c8.cid = 8
AND     wd_c9.cid = 9
AND     wd_c10.cid =    10
AND     wd_c11.cid =    11
AND     wd_c12.cid =    12
AND     wd_c13.cid =    13
AND     wd_c14.cid =    14
AND     wd_c15.cid =    15
AND     wd_c16.cid =    16
AND     wd_c17.cid =    17
AND     wd_c18.cid =    18
AND     wd_c19.cid =    19
AND     wd_c20.cid =    20
AND     wd_c21.cid =    21
AND     wd_c22.cid =    22
AND     wd_c23.cid =    23
AND     wd_c24.cid =    24
AND     wd_c25.cid =    25
AND     wd_c26.cid =    26
AND     wd_c27.cid =    27
AND     wd_c28.cid =    28
AND     wd_c29.cid =    29
AND     wd_c30.cid =    30
AND     wd_c31.cid =    31
AND     wd_c32.cid =    32
AND     wd_c33.cid =    33
AND     wd_c34.cid =    34
AND     wd_c41.cid =    41
AND     wd_c42.cid =    42
AND     wd_c43.cid =    43
AND     wd_c45.cid =    45
AND     wd_c47.cid =    47
AND     wd_c48.cid =    48
AND     wd_c49.cid =    49
AND     wd_c50.cid =    50
AND     wd_c51.cid =    51
AND     wd_c52.cid =    52
LIMIT 20