Performance improvement query tables - Filters

[georgmaisser]

Currently, the standard filter expects to find a column on which a filter can be applied.
This leads to the necessity to create a large table with all possible filter columns joined.

Actually, we just join all the custom fields, which makes our queries unnecessarily slow, even when we don't filter at all.

• A) Standardfilter should acquire a function to add filter sql. instead of joining the column in the table, we could just make a much faster subquery which gives us an array of ids. We can then use "id IN" instead of "customfielda = 'xxx'".
• B) Probably, we should make a standard filter clone, a "customfield_filter" class, which makes it easy and straightforward to actually use customfield filters in an efficient way.
• C) We need to have a few tests (MariaDB & Postgres) to find out what the effects are of different ways of building the sql. EG, is it useful to only add customfields when we actually filter (or worse, sort) with them? Or is the performance improvement by that negligible? Until now, it didn't seem to matter if we had one or ten customfields, BUT the unindexed "shortname" actually makes a difference.
• D) Keep a table to match customfield shortname to fieldid only once for everybody and put it in the cache. only use fields in all tables.
• Sorting will still need the joins and will thus be very slow.
• Fulltextsearch within customfields will also be slow and should be avoided

ad A) This will lead to problems in the way we add sql customfields now. It might be necessary to still add the columns with the shortnames of the customfields, but possibly just returning an empty string as value, instead of the joining on the real customfields.

[mm-poustini]

Concept:

1. Sample query with any filter

SELECT *
FROM
(
	SELECT  e.id,
                u.firstname,
                u.lastname,
                u.email,
                c.fullname
            FROM m_user u
            JOIN m_user_enrolments ue ON ue.userid = u.id
            JOIN m_enrol e ON e.id = ue.enrolid
            JOIN m_course c ON e.courseid = c.id
            
) s1
WHERE 1=1

2. Sample query with standard filter on category

SELECT *
FROM
(
	SELECT  e.id,
                u.firstname,
                u.lastname,
                u.email,
                c.fullname,
                cc.name as catoryname
            FROM m_user u
            JOIN m_user_enrolments ue ON ue.userid = u.id
            JOIN m_enrol e ON e.id = ue.enrolid
            JOIN m_course c ON e.courseid = c.id
            
            JOIN m_course_categories cc ON c.category = cc.id  #We have a JOIN here.
) s1
WHERE 1=1
 AND (  (  '' || ',' || category || ','  ILIKE 'Math' ESCAPE '\' OR  '' || ',' || category || ','  ILIKE 'Computer' ESCAPE '\' )  ) 

3. Sample query with custom field filter on category

SELECT *
FROM
(
	SELECT  e.id,
                u.firstname,
                u.lastname,
                u.email,
                c.fullname,
                c.category
            FROM m_user u
            JOIN m_user_enrolments ue ON ue.userid = u.id
            JOIN m_enrol e ON e.id = ue.enrolid
            JOIN m_course c ON e.courseid = c.id

            # We have no JOIN here.
) s1
WHERE 1=1
AND category IN ( SELECT cc.id FROM m_course_categories cc WHERE cc.name LIKE '%Math%' OR cc.name ILIKE '%Computer%' );