Mysql – Speed-up lookup of a row with prefix pattern

This is exactly the kind of query that can benefit from a composite, or multiple-column index: when a single statement filters by multiple columns in the same table. The order of columns in the index will depend on your use cases, but in general the order in which the columns appear in the DML statement is fine, in your case (assuming MySQL but the concept is the same for most RDBMSs):

ALTER TABLE 'venue'
ADD INDEX ('startdate','enddate')

See the documentation for other alternatives, such as adding a column containing the hash of multiple columns.

Have you tried this?

SELECT Id, StatusDate, [ some way to select columns from appropiate table depending on result.tbl ]
FROM (
        SELECT Id, StatusDate, 'Results_201505' as tbl
        FROM Results_201505
        WHERE A = 0, B = 1, C = 3
    UNION ALL 
        SELECT Id, StatusDate, 'Results_201504' as tbl
        FROM Results_201504
        WHERE A = 0, B = 1, C = 3
    UNION ALL 
        SELECT Id, StatusDate, 'Results_201503' as tbl
        FROM Results_201503
        WHERE A = 0, B = 1, C = 3
    ORDER BY StatusDate DESC
    OFFSET xxx ROWS
    FETCH NEXT 25 ROWS ONLY
) AS result ;