$this->db->having() makes my query a bit slow

[eluser]johnwbaxter[/eluser]
I've got a little app that queries a database. Through a series of forms it allows the user to build up the query which is simply a select query with "where" for each section.

The next stage is it allows you to choose extra fields where there are no null values.
So i have done $this->db->having('Field Is Not Null') (could be up to 3 of these.

Anyway, the normal query (+ loading of page which is not much load) takes 0.0819 seconds then when i add the "having" part the query shoots up to 0.4237 seconds.

Is there a better/quicker way of adding a Not Null specification to the query?

Any advice would be very much appreciated.

[eluser]Developer13[/eluser]
Can we see all of the query statements you've built? I assume the query must be grouped?

[eluser]kgill[/eluser]
The reason it's slowing way down is that "having" is normally used for filtering aggregate functions eg. you sum'd a column and only want the records where the sum is greater than X, that can't be done in a where clause so it has to be done after the completed record set is built. Using is not null in a having clause means the DB first selects the entire record set based on your where clause then it has to go back over it and search for nulls. Instead you should be sticking that "is not null" in the where clause to limit the data while it's being selected: $this->db->where('field is not null')

- K

[eluser]jtotheb[/eluser]
The reason i used the having type is because putting 'field is not null' in the where part did not actually work for some reason. Lord knows why!


Anyway, i have now found that it is not the having part that was causing it to slow down, it was me accidentally calling a function i didn't want.

Case closed!

Thanks for your replies guys!