We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Unable to test query but need best indexing strategy for WHERE OR.. OR.. OR clause

In application runs a routine once a week. The last run was extremely slow and we know that more data will be processed during the next run. The slowest statement in the routine, by far, joins a temp table to a products table. Profiler shows a very high number of reads, suggesting it is not indexed well. During the last run, the Products table had 200,000 rows and the temp table had 1200.

update tmp
set tmp.col1 = pd.col1, tmp.col2 = pd.col2, tmp.col3 = pd.col3 from #temptable tmp , Products pd with (nolock)
where tmp.col2 = pd.col2 or tmp.col2 = pd.col3 or tmp.col2 = pd.col4
or tmp.col2 = pd.col5

I only have one chance to apply an indexing strategy. The temp table is generated from data that only exists for a short period of time and no copy exists, Sql server so cannot be recreated before the next run. The plan cache does not have an execution plan.

The query should be updated to ANSI-92 but I'm dealing with it as found.

The products table has indexes on each of the columns col2, col3, col4, col5, but no covering composite or INCLUDES for the update values.

The temp table has no indexing.

I have not tried anything because there is no way to test before the next live run.

Can anyone advise whether I should apply a composite index covering the 4 Product columns, or use four indexes - one for each column and INCLUDEs for col1, col2 and col3?

Kind of hard to determine what your mysql(?) tables have going there, if this is a temp table, don't think Phalcon can help you much on the indexing. I would log the SQLs that are run, take that to MYSQL and describe it there, fix issues within.

As for Phalcon, and you did not mention, if you are using paging or anything else. PHQL does not run as fast as QueryBuilder does with paging large data sets.

Use a stored procedure with an exists check in your where clause to perform a semi join, that should be better.
But I don't understand the use of this ... think you could use another method avoiding this.