No indexes on tables causes join to crash server
-
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).
- The topic ‘No indexes on tables causes join to crash server’ is closed to new replies.