The optimization process and recommendations:

1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
3. Prefer Direct Join Over Joined Subquery (query line: 49): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, we recommend to replace subqueries with JOIN clauses.
4. Prefer Direct Join Over Joined Subquery (query line: 61): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, we recommend to replace subqueries with JOIN clauses.
5. Prefer Direct Join Over Joined Subquery (query line: 76): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, we recommend to replace subqueries with JOIN clauses.
6. Prefer Direct Join Over Joined Subquery (query line: 92): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, we recommend to replace subqueries with JOIN clauses.
7. Use Numeric Column Types For Numeric Values (query line: 78): Referencing a numeric value (e.g. 3200) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
8. Use Numeric Column Types For Numeric Values (query line: 83): Referencing a numeric value (e.g. 3200) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
9. Use Numeric Column Types For Numeric Values (query line: 89): Referencing a numeric value (e.g. 3200) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
10. Use Numeric Column Types For Numeric Values (query line: 95): Referencing a numeric value (e.g. 3200) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
11. Use Numeric Column Types For Numeric Values (query line: 101): Referencing a numeric value (e.g. 3200) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.

Optimal indexes for this query:

ALTER TABLE `t_internet` ADD INDEX `t_internet_idx_res_id` (`res_id`);
ALTER TABLE `t_kepemilikan_tik` ADD INDEX `t_tik_idx_res_id` (`res_id`);
ALTER TABLE `t_media_cetak` ADD INDEX `t_cetak_idx_res_id` (`res_id`);
ALTER TABLE `t_mst_aksesibilitas` ADD INDEX `t_aksesibilitas_idx_kd_akses` (`kd_akses`);
ALTER TABLE `t_mst_desa` ADD INDEX `t_desa_idx_kode_kode_ka_kode_pr_kode_ke` (`kode`,`kode_kabkota`,`kode_prop`,`kode_kec`);
ALTER TABLE `t_mst_kabkota` ADD INDEX `t_kabkota_idx_kode_kode_prop` (`kode`,`kode_prop`);
ALTER TABLE `t_mst_kec` ADD INDEX `t_kec_idx_kode_kode_kabko_kode_prop` (`kode`,`kode_kabkota`,`kode_prop`);
ALTER TABLE `t_mst_mobilitas` ADD INDEX `t_mobilitas_idx_kd_mobilitas` (`kd_mobilitas`);
ALTER TABLE `t_mst_pekerjaan` ADD INDEX `t_pekerjaan_idx_kode` (`kode`);
ALTER TABLE `t_mst_pendidikan` ADD INDEX `t_pendidikan_idx_kd_pendidikan` (`kd_pendidikan`);
ALTER TABLE `t_mst_pengeluaran` ADD INDEX `t_pengeluaran_idx_kode` (`kode`);
ALTER TABLE `t_mst_penghasilan` ADD INDEX `t_penghasilan_idx_kd_penghasilan` (`kd_penghasilan`);
ALTER TABLE `t_mst_prop` ADD INDEX `t_prop_idx_kode` (`kode`);
ALTER TABLE `t_pernyataan` ADD INDEX `t_pernyataan_idx_res_id` (`res_id`);
ALTER TABLE `t_radio` ADD INDEX `t_radio_idx_res_id` (`res_id`);
ALTER TABLE `t_responden` ADD INDEX `t_responden_idx_res_tahunsurvei_res_propinsi` (`res_tahunsurvei`,`res_propinsi`);
ALTER TABLE `t_telepon_hp` ADD INDEX `t_hp_idx_res_id` (`res_id`);
ALTER TABLE `t_televisi` ADD INDEX `t_televisi_idx_res_id` (`res_id`);

The optimized query:

SELECT
        * 
    FROM
        t_responden a 
    INNER JOIN
        t_kepemilikan_tik b 
            ON a.res_id = b.res_id 
    INNER JOIN
        t_televisi c 
            ON a.res_id = c.res_id 
    INNER JOIN
        t_telepon_hp d 
            ON a.res_id = d.res_id 
    INNER JOIN
        t_radio e 
            ON a.res_id = e.res_id 
    INNER JOIN
        t_media_cetak f 
            ON a.res_id = f.res_id 
    INNER JOIN
        t_internet g 
            ON a.res_id = g.res_id 
    LEFT JOIN
        t_mst_pendidikan n 
            ON a.res_pendidikan_kk = n.kd_pendidikan 
    LEFT JOIN
        t_mst_pendidikan o 
            ON a.res_pendidikan = o.kd_pendidikan 
    LEFT JOIN
        t_mst_penghasilan p 
            ON a.res_penghasilan = p.kd_penghasilan 
    LEFT JOIN
        t_mst_aksesibilitas q 
            ON a.res_aksesibilitas = q.kd_akses 
    LEFT JOIN
        t_mst_mobilitas r 
            ON a.res_mobilitas = r.kd_mobilitas 
    LEFT JOIN
        t_mst_pekerjaan s 
            ON a.res_pekerjaan = s.kode 
    LEFT JOIN
        t_mst_pengeluaran t 
            ON a.res_pengeluaran = t.kode 
    INNER JOIN
        t_pernyataan h 
            ON a.res_id = h.res_id 
    INNER JOIN
        t_mst_prop i 
            ON a.res_propinsi = i.kode 
    INNER JOIN
        t_mst_kabkota j 
            ON (
                a.res_kabkota = j.kode 
                AND a.res_propinsi = j.kode_prop
            ) 
    INNER JOIN
        t_mst_kec k 
            ON (
                a.res_kecamatan = k.kode 
                AND a.res_kabkota = k.kode_kabkota 
                AND a.res_propinsi = k.kode_prop
            ) 
    INNER JOIN
        t_mst_desa l 
            ON (
                a.res_keldesa = l.kode 
                AND a.res_kabkota = l.kode_kabkota 
                AND a.res_propinsi = l.kode_prop 
                AND l.kode_kec = a.res_kecamatan
            ) 
    WHERE
        (
            (
                (
                    (
                        a.res_tahunsurvei = 2013 
                        AND a.res_propinsi IN (
                            '3200', '1600', '1800', '3600'
                        )
                    ) 
                    AND (
                        i.kode IN (
                            '3200', '1600', '1800', '3600'
                        )
                    )
                ) 
                AND (
                    j.kode_prop IN (
                        '3200', '1600', '1800', '3600'
                    )
                )
            ) 
            AND (
                k.kode_prop IN (
                    '3200', '1600', '1800', '3600'
                )
            )
        ) 
        AND (
            l.kode_prop IN (
                '3200', '1600', '1800', '3600'
            )
        ) 
    ORDER BY
        1