By timlie on

Hi,

I made a portal for my school where people could post there cv. It's online since wednesday. At this moment there are already about 100 cv's online. With there cv people can post if they are available as well. They post it in this way:

MoAM
MoPM
TuAM
TuPM
WeAM
WePM
...
FrPM

(checkboxes)

I created a view so now people of my school can search for available people. When I choose "is one of" in views and select them all, there is no problem. When I select "is all of", and select all, the site crashes and doesn't display the available people.

It's served on a normal hosting so I have not the capability to upgrade the server.

Is there someone who has another option, to make this less database intensive.

Thanx a lot!!!

Comments

timlie’s picture

timlie commented

This is the query that gets executed:

SELECT  DISTINCT(node.nid) AS nid,
   users.name AS users_name,
   users.uid AS users_uid,
   node_data_field_naam.field_naam_value AS node_data_field_naam_field_naam_value,
   node_data_field_naam.nid AS node_data_field_naam_nid,
   node.type AS node_type,
   node_data_field_voornaam.field_voornaam_value AS node_data_field_voornaam_field_voornaam_value,
   node_data_field_voornaam.nid AS node_data_field_voornaam_nid,
   node_data_field_woonplaats.field_woonplaats_value AS node_data_field_woonplaats_field_woonplaats_value,
   node_data_field_woonplaats.nid AS node_data_field_woonplaats_nid,
   node.vid AS node_vid,
   node_data_field_woonplaats.field_gsm_value AS node_data_field_woonplaats_field_gsm_value,
   node_data_field_woonplaats.field_telefoonnummer_value AS node_data_field_woonplaats_field_telefoonnummer_value,
   users.mail AS users_mail
 FROM node node
 INNER JOIN users_roles users_roles ON node.uid = users_roles.uid
 INNER JOIN users users ON node.uid = users.uid
 INNER JOIN content_field_beschikbaarheidoneven node_data_field_beschikbaarheidoneven_value_0 ON node.vid = node_data_field_beschikbaarheidoneven_value_0.vid AND node_data_field_beschikbaarheidoneven_value_0.field_beschikbaarheidoneven_value = '1'
 INNER JOIN content_field_beschikbaarheidoneven node_data_field_beschikbaarheidoneven_value_1 ON node.vid = node_data_field_beschikbaarheidoneven_value_1.vid AND node_data_field_beschikbaarheidoneven_value_1.field_beschikbaarheidoneven_value = '2'
 INNER JOIN content_field_beschikbaarheidoneven node_data_field_beschikbaarheidoneven_value_2 ON node.vid = node_data_field_beschikbaarheidoneven_value_2.vid AND node_data_field_beschikbaarheidoneven_value_2.field_beschikbaarheidoneven_value = '3'
 INNER JOIN content_field_beschikbaarheidoneven node_data_field_beschikbaarheidoneven_value_3 ON node.vid = node_data_field_beschikbaarheidoneven_value_3.vid AND node_data_field_beschikbaarheidoneven_value_3.field_beschikbaarheidoneven_value = '4'
 INNER JOIN content_field_beschikbaarheidoneven node_data_field_beschikbaarheidoneven_value_4 ON node.vid = node_data_field_beschikbaarheidoneven_value_4.vid AND node_data_field_beschikbaarheidoneven_value_4.field_beschikbaarheidoneven_value = '5'
 INNER JOIN content_field_beschikbaarheideven node_data_field_beschikbaarheideven_value_0 ON node.vid = node_data_field_beschikbaarheideven_value_0.vid AND node_data_field_beschikbaarheideven_value_0.field_beschikbaarheideven_value = '1'
 INNER JOIN content_field_beschikbaarheideven node_data_field_beschikbaarheideven_value_1 ON node.vid = node_data_field_beschikbaarheideven_value_1.vid AND node_data_field_beschikbaarheideven_value_1.field_beschikbaarheideven_value = '2'
 INNER JOIN content_field_beschikbaarheideven node_data_field_beschikbaarheideven_value_2 ON node.vid = node_data_field_beschikbaarheideven_value_2.vid AND node_data_field_beschikbaarheideven_value_2.field_beschikbaarheideven_value = '3'
 INNER JOIN content_field_beschikbaarheideven node_data_field_beschikbaarheideven_value_3 ON node.vid = node_data_field_beschikbaarheideven_value_3.vid AND node_data_field_beschikbaarheideven_value_3.field_beschikbaarheideven_value = '4'
 INNER JOIN content_field_beschikbaarheideven node_data_field_beschikbaarheideven_value_4 ON node.vid = node_data_field_beschikbaarheideven_value_4.vid AND node_data_field_beschikbaarheideven_value_4.field_beschikbaarheideven_value = '5'
 LEFT JOIN content_field_naam node_data_field_naam ON node.vid = node_data_field_naam.vid
 LEFT JOIN content_field_voornaam node_data_field_voornaam ON node.vid = node_data_field_voornaam.vid
 LEFT JOIN content_type_profiel_sollicitant node_data_field_woonplaats ON node.vid = node_data_field_woonplaats.vid
 WHERE (node.status <> 0) AND (node.type in ('profiel_sollicitant')) AND (users_roles.rid = 3) AND (users.status <> 0) AND (node_data_field_beschikbaarheidoneven_value_0.field_beschikbaarheidoneven_value = '1' AND node_data_field_beschikbaarheidoneven_value_1.field_beschikbaarheidoneven_value = '2' AND node_data_field_beschikbaarheidoneven_value_2.field_beschikbaarheidoneven_value = '3' AND node_data_field_beschikbaarheidoneven_value_3.field_beschikbaarheidoneven_value = '4' AND node_data_field_beschikbaarheidoneven_value_4.field_beschikbaarheidoneven_value = '5') AND (node_data_field_beschikbaarheideven_value_0.field_beschikbaarheideven_value = '1' AND node_data_field_beschikbaarheideven_value_1.field_beschikbaarheideven_value = '2' AND node_data_field_beschikbaarheideven_value_2.field_beschikbaarheideven_value = '3' AND node_data_field_beschikbaarheideven_value_3.field_beschikbaarheideven_value = '4' AND node_data_field_beschikbaarheideven_value_4.field_beschikbaarheideven_value = '5')
   ORDER BY node_data_field_naam_field_naam_value ASC, node_data_field_woonplaats_field_woonplaats_value ASC
 LIMIT 0, 15;

Quit impossible I guess...

timlie’s picture

timlie commented

According to my hosting company it takes 1200 seconds to execute!!

Bobby1290’s picture

Bobby1290 commented

I don't know about the great database insight but....

What is normal hosting? is that shared hosting, then move to a VPS or go dedicated.

My gut feeling tells me you need more groupings, like a computed field that is "*all" and then base your search criteria on one field (within the constraints of the way you have set it up).

On a shared host I would do what you are doing with taxonomy terms
example:

Availability:
none
workweek
-mo
--moam
--mopm
-tu
--tuam
--tupm
. .
weekend
-sa
--saam
--sapm
-su
--suam
--supm
weekpm
-mopm
-tupm
nolife
-workweek
-weekend
etc etc

and then you could just select the term, and you would not even need a view for this but just use the taxonomy selectors ( http://drupal.org/node/299 under "Using categories in menus") or you could opt to go with the view just only selecting the term.

Hope this helps.

PS. test and see if it works with your other specs before applying.

timlie’s picture

timlie commented

Thanx for the quick reply!!

It's indeed shared hosting. I am going to try first with taxonomy like you explained. I also thought about the computed field with grouping methods.

I'll report my findings with taxonomy.

Thanx!

timlie’s picture

timlie commented

What happens with taxonomy if you select "moam" (monday am) for users who have selected "mo" (monday)?

Bobby1290’s picture

Bobby1290 commented

If mo = term 2 and moam =term4
And you want to select everyone who can work on monday(mo) or on monday morning(moam) your selection would be taxonomy/term/2+4 see http://drupal.org/node/299.
If you want to see ONLY people available for moam then taxonomy/term/4.

You don't have to have mo, it was just an example, I don't know your full specs.
You decide what you get to see, depending on how you allow user input (procedurally or in the theming layer).
PS. you can also use http://drupal.org/project/taxonomy_super_select to force end term selections, and avoid users selecting monday etc.

timlie’s picture

timlie commented

I created some extra fields like:

Complete week
Mo
Moam
Mapm
...

The queries are still too long for my shared hosting...