The GetGalleryForDisplay procedure

The usp_galleries_GetGalleryForDisplay procedure is Deserted Chateau's most complex SQL procedure, owing to the amount of information that needs to be queried. There are a lot of performance optimisations in this procedure, which are explained both in the comments in the procedure file itself, and below in a bit more detail.

Limiting the user_artworks query result count before performing JOINS

As the query requires both INNER and LEFT JOINs on several tables, it is crucial to limit the number of rows we need to perform the joins for to prevent the query becoming astronomically slow.

Suppose we want to query the first 25 artworks, with a few search parameters. If we join the user_artworks table to other tables and then use LIMIT, we will have millions or more rows before limiting the final results, causing a very slow query. Instead, we can use a complex subquery on the user_artworks table with as few JOINS as necessary (we have to query anything which would affect which results we want to retrieve, since we are using LIMIT on this subquery), retrieve the results we want, and then join to the additional tables whose information we need. 

Subqueried GROUP_CONCAT select statements

Selected values that involve aggregating results from multiple rows, e.g. artwork tags and categories, are not selected in the main query's WHERE clause but via subqueries in the SELECT FROM part of the query, to avoid expensive joins that would create a huge number of additional rows.

Grouped tag, category IDs set before searching

To prevent having to perform additional subquery selects and redundant searches, if a user specified categories or tags to include or exclude, variables are set at the beginning of the procedure to get a list of the IDs for those entities which can be matched against later.

EXISTS clauses

For parts of the WHERE clause that require checking if a tag or category is present, EXISTS is used over IN as it only needs to find one matching element before terminating.