I have approximately 980 nodes in my database.
I have a page view for a content type that uses custom CCK fields. This view has five exposed filters, of which one is a text field and the other four are multiple select lists (each with 8+ options). Each of the exposed multiple select filters has "reduce duplicates" turned off, and the operator being used is set to "is all of". The view has "distinct" set to "no".
In addition to these filters, I'm filtering for a specific content type (not exposed), only published nodes and this view has a relationship with vote/five star rating results.
The "items per page" is set to 25, and I'm using the full pager. Also, this view is exposed as a block.
When filtering for a few parameters, the query runs relatively fast. However, it gets slower as you select more parameters. Interestingly, when nine options are selected, the query takes about 20 seconds. But when you add a tenth, the run time jumps to about 4 minutes!
I believe because I'm using the pager, two queries are actually run, each comprised of a mess of SQL join statements. According to the slow queries log, it appears each of the two queries take about the same time. Thus, when ten options are selected, each takes about 120 seconds. So, even if I remove the pager, the query still takes too long.
Any suggestions on improving the performance?
Comments
4 minutes is outrageously
4 minutes is outrageously slow.
I never encountered such extreme differences between 9/10 or any number of filter values.
Anyway. Look at your sorting options perhaps. I did improve performance by dropping some sorting on CCK field values in a similar scenario. Not totally to my satisfaction, but performance improved significantly.
Thanks
Thanks for the suggestion kees@qrios, but I have no sorting fields to begin with. I've minimized the issue by making forcing a single choice for most of the exposed fields, but I'd still like to solve the problem altogether.
Same issue here
Same issue here. (D6.19, Views 2.11)
I have a text field (widget:select list ; Number of values: unlimited )
An exposed filter with this field:
-Operator: Is all of
-checkboxes.
forcing single value is not an option, of course.
when i select 7 values of the filter the query runs OK. When i select 8 values, it takes minutes. If a select the total values (11), the query never ends and the DB gets blocked, website down, and I have to kill the process manually (phpmyadmin->proccess).
The problem is that Views make a JOIN in the SQL query for each value of the filter selected. 11 JOINs over the same table seems to crash.
any solution?
thanks
I didn't find a good fix
raguchi, sounds like the same thing. For all but one of the select lists, I limited to one choice. In the end this made logical sense for me since in most cases selecting multiple choices yielded no results. For example, if you selected category A and B, you would most likely get zero results since it is rare for a node to be in both categories. This probably doesn't apply to you.
However, the problem still occurred if someone chose almost every possible option, so I implemented validation (client and server side) to check the number and throw an error on submit if it exceeded the limit. Not very user friendly, but crashing the database is worse.
Haven't had the opportunity to go back and tackle the problem again. But please let me know if you come up with a better solution.
Good luck.
I have a similar issue on a
I have a similar issue on a view with a few exposed taxonomy filters and 'is all of'. Selecting a few options works fine, but any more than 10 selected items crashes the database. Did you ever find a solution?
No solution yet
No, I haven't come up with a good fix other than the workaround I mentioned above.