• I manage a server for a WP site using the nex-forms plugin. Every few days,
    the server would run out of disk space. Restarting mysql would recover 15GB
    and things would work again. Turns out the space was being used by mysql
    temp files to process a join query with no indexes on the join columns.

    I was going to just add indexes to fix the problem but several of the nex_forms_Id fields are defined as “text” rather than int (although I think
    they only ever store ints).

    Here is the join query that triggers the problem:

    SELECT form.Id,
                    form.title,
                    COUNT(DISTINCT v.Id) AS views,
                    COUNT(DISTINCT i.Id) AS interactions,
                    COUNT(DISTINCT s.Id) AS submissions
                FROM wp_wap_nex_forms AS form
                LEFT JOIN wp_wap_nex_forms_views AS v ON form.Id = v.nex_forms_Id
                LEFT JOIN wp_wap_nex_forms_stats_interactions AS i ON form.Id = i.nex_forms_Id
                LEFT JOIN wp_wap_nex_forms_entries AS s ON form.Id = s.nex_forms_Id
                WHERE form.is_form = 1
                AND form.Id IN (10,16,11,18,87,98,96,94) GROUP BY form.Id

    Would it be possible to change those nex_forms_Id columns to all be ints? (It is an int in one table, text in the others). Joining between fields of different type is not recommended (someone told me it would invalidate the
    indexes (if they were indexed) but I haven’t tried it yet).

Viewing 1 replies (of 1 total)
  • Plugin Author Basix

    (@webaways)

    Hi, if you are running your own SQL queries then I am not sure what you are asking here? Can you please use https://basix.ticksy.com for support? We can not help you from as we cant share any sensitive info. Thanks

Viewing 1 replies (of 1 total)
  • The topic ‘No indexes on tables causes join to crash server’ is closed to new replies.