Enhence performance when querying root model of subclasses

[darron1217]

I found that if model has subclasses, trait adds whereIn condition to query.

But the root model (which queries every type) don't need whereIn condition to select specific types. It will just make query slow.

I think it would be nice to remove whereIn condition when querying root model.

[jonspalmer]

From by brief research I believe its common for STI libraries to behave like this.

a) I'd be curious to see numbers of the relative performance of the queries
b) we'd need this behavior to be configurable so we can support situations where there are unmapped types in the type column that shouldn't be returned by a root query.

I don't have time to dig in to the implementation details of this but a PR would always be considered.

[darron1217]

@jonspalmer Thanks for the reply.

a) When it comes to about 10000 records, it matters. I brought evidence from laravel repo.

laravel/framework#26051 (comment)

b) yes, i agree to your opinion.

I will create PR for this issue soon :)

[jonspalmer]

@darron1217 thanks for the details but those are not good examples of the problem.

a) The first query is only selecting one column from the table which will typically be faster than selecting "*" as there is less data to for the DB to collect and send over the wire
b) A very large "IN" query with 1000s of values in the "in' condition is potentially slow (to send as a query and to execute). However, we're talking about a max # of conditions as the max of the number of classes you have mapped.
c) The indexing performance will be very different. A type column for STI will have low cardinality (the # of mapped classes) vs a Primary Key index in those examples which will necessarily be unique. The query performance of queries against those two types of indexes will be very different. It also might be that certain SQL engines optimize the "where in" query to a no-op if you pass every value of the index 😄.

We think we need real examples of the problem at hand with sql "EXPLAIN" details of the queries.

[darron1217]

@jonspalmer
Oh, I didn't realize that I brought wrong example... haha

I'll test with index as you mentioned :)

[darron1217]

@jonspalmer
I just tested on my database, and only 1 milisecond delayed....

Let's close this issue :)