On Sat, 11 Jul 2015 19:02:59 -0600 Scott Robison <scott at casaderobison.com> wrote:
> > I don't follow you. A complex query is an assemblage of clauses. > > Whether or not broken down "to individual sub queries", the search > > arguments are the same. They are few in kind, even though they may > > be large in number. The analysis for what indexes to create will > > be the same either way. In fact, Keith already proposed a > > plausible design. > > I'm just saying that knowing which index to create in advance is > impossible if the user can specify arbitrarily complex where clauses. In that case, I can make your job easier, starting today. You don't need to consider query complexity to design your indexes. :-) The complexity of the query per se is immaterial. If we had a database with one table with only one column of integers, the *complexity* of the queries against it could still be arbitrarily complex, because that's what SQL allows. But the choice of what to index would be trivial. The database in question is nearly that simple. It consists of just a few tables, and AIUI the search terms are limited to keywords and rating values. Maybe there are a few others. The combinations in which those could be used in a query are limitless. The number of indexes appears to be about 3. Determination of indexes is always driven by search arguments. What will be in the WHERE clause? What will be in the JOIN? The number of ANDs and ORs doesn't matter, nor does the number of tables joined. No matter how complex the queries, the number of indexes is always limited by the searchable columns. --jkl
