Mysql view are very slow

Question

My mysql view is really slow in thousands of data how can we improve this functionality?

While fetch this view in 10000s data then it takes more than 30 sec. how could we revise this view table?

SELECT 
    i.jo_in_id,
    j.*,
    i.jo_in_week_number,
    i.jo_in_client_ref_no,
    i.cl_id AS jo_in_cl_id,
    c.cl_short_name,
    c.cl_business_name,
    m.me_first_name,
    m.me_last_name, 
    m.me_mobile,
    sk.sk_name,
    sk.sk_ticketed,
    ti.ti_id,
    ta.ta_name,
    u.un_id,
    u.un_from,
    u.un_to,
    v.ve_name,
    mp.vmp_name,
    r.vr_name 
FROM 
    jo_2_no j2n,
    jo_in_numbers i,
    jobs j
LEFT JOIN venues_new v ON j.ve_id = v.ve_id
LEFT JOIN venues_meeting_place mp ON j.vmp_id = mp.vmp_id
LEFT JOIN venues_rooms r ON j.vr_id = r.vr_id
LEFT JOIN clients c ON j.cl_id = c.cl_id
LEFT JOIN members m ON j.me_id = m.me_id
LEFT JOIN skills sk ON j.sk_id = sk.sk_id
LEFT JOIN tasks ta ON j.ta_id = ta.ta_id
LEFT JOIN crew_tickets ti ON j.sk_id = ti.sk_id AND j.me_id = ti.me_id AND j.jo_time_off < ti.ti_expire
LEFT JOIN unavailability u ON j.me_id = u.me_id AND ((j.jo_time_on BETWEEN u.un_from AND u.un_to) OR  (j.jo_time_on BETWEEN u.un_from AND u.un_to))
WHERE 
    j.jo_id = j2n.jo_id
    AND j2n.jo_in_numbers_id = i.jo_in_id

AFTER user EXPLAIN SELECT following is the output

Do you have any indices on the join columns? Other than this, you can stop using views, which tend to be slow. — Tim Biegeleisen, Dec 11 '16 at 09:51
@TimBiegeleisen Views are not slower or faster than other queries. — arkascha, Dec 11 '16 at 09:52
The primary cause most likely is the simple fact that you combine many many tables. That _is_ slow. You can try speeding things up with proper indexes, but only so far. At least try to get around using the "comma join" used for the first three tables, which _really_ bloats the temporary table created for the result set. The bigger that set, the slower the query. But ultimately you should redesign your database so that you can simplify the queries (views) — arkascha, Dec 11 '16 at 09:53
@arkascha thanks is there any temporary way that i can solve this issue ? — , Dec 11 '16 at 10:06
What do you mean by that? Temporary? Either you solve it and speed things up or you don't. I don't see how you can "temporarily speed up" a query. — arkascha, Dec 11 '16 at 10:08
Using the `mysql` command line client, run this query with `EXPLAIN SELECT`, instead of just `SELECT`. Edit the question and paste the output. The nature of the problem is likely to become apparent. — Michael - sqlbot, Dec 11 '16 at 18:02

score 0 · Accepted Answer · edited May 23 '17 at 11:45

In your EXPLAIN, I see that your joined tables ti and u are read with table-scans (type: ALL). This is probably the biggest problem for your performance.

You should make sure you have the following indexes created:

ALTER TABLE crew_tickets ADD KEY (sk_id, me_id, ti_expire);

ALTER TABLE unavailability ADD KEY (me_id, un_from, un_to);

That should help the joins to those tables work with index lookups instead of table-scans. I think they'll be accessed as covering indexes, too.

Also, please don't use the outdated "comma-joins." Especially do not mix both styles. It will bite you when you get surprised by the order of precedence between comma-joins and JOIN operators. See examples in Can someone help explain why not using a SQL JOIN is bad practice and wrong? or Error on JOIN mysql.

Write your joins this way:

FROM jo_2_no j2n 
INNER JOIN jo_in_numbers i ON j2n.jo_in_numbers_id = i.jo_in_id
INNER JOIN jobs j ON j.jo_id = j2n.jo_id
LEFT JOIN venues_new v ON j.ve_id = v.ve_id
LEFT JOIN venues_meeting_place mp ON j.vmp_id = mp.vmp_id
LEFT JOIN venues_rooms r ON j.vr_id = r.vr_id
LEFT JOIN clients c ON j.cl_id = c.cl_id
LEFT JOIN members m ON j.me_id = m.me_id
LEFT JOIN skills sk ON j.sk_id = sk.sk_id
LEFT JOIN tasks ta ON j.ta_id = ta.ta_id
LEFT JOIN crew_tickets ti ON j.sk_id = ti.sk_id 
  AND j.me_id = ti.me_id AND j.jo_time_off < ti.ti_expire
LEFT JOIN unavailability u ON j.me_id = u.me_id 
  AND j.jo_time_on BETWEEN u.un_from AND u.un_to

I removed the redundant term in the join condition for u. The optimizer might eliminate that logic, but why make it work so hard?

Mysql view are very slow

1 Answers1