Just as a neutral comment, making a reference to the "MySQL Performance"
book at O'Reilly's, sometimes it might be much more efficient to make a
query for a set of IDs, return it to PHP, filter it in PHP and then ask
for the details to the database system.
The explanation for that is quite long (and not always logical) and
requires intimate understanding on how MySQL works in half-duplex (which
is not the case for all RDMS), but the point is that the only way to
know is to try it.
I think Pieterjan made it clear that the query is useful and that it
won't affect you if you don't need it, although as Hans says its
definition seems a bit too long, but we will never know the efficiency
difference until we try it anyway, so...
Yannick
El mar, 19-04-2011 a las 14:23 +0200, Pieterjan Broekaert escribió:
> Hi Hans,
>
> In the weblcms people need an overview of all the subscribed users,
> with their status and some subscribe/unsubscribe actions in one list.
> Users can be subscribed directly or through platform groups (and their
> subgroups). In the current user tool in the weblcms, this information
> was only available through different lists (Direct subscribed users
> and platform group users).
>
> To accomplish this we have to retrieve all the direct subscribed
> groups, then retrieve all their subgroups, then retrieve the users in
> those groups and add the direct subscribed users. Determine the
> correct status (teacher/student) and the available actions (e.g. users
> subscribed through platform groups can't be directly unsubscribed).
> This information is spread out over different tables.
>
> The resulting list can contain doubles (users can be part of multiple
> groups) that can have different status values (direct subscribed
> groups can also have status) and to top it off, the list should be
> sorted.
> For big installations with an extensive platformgroup structure and
> userlist performance problems arise.
>
> We need a query that provides our table with a limited (depending on
> how many rows are to be shown) resultset with the definitive, sorted
> records. This way we avoid having to send all possible users to the
> php server, remove the doubles and sort the list there, as this would
> kill performance.
>
> This is a complex problem, so the resulting query turned out to be
> complex. However we designed it with performance in mind.
>
> The mathematical functions were added because otherwise we would have
> to determine the way a user is subscribed and the resulting available
> actions for every row (retrieve all the groups a user is a part of,
> and check if one of their parents is directly subscribed in the
> course). As mysql already has to remove the doubles and sort the list,
> we think that adding the mathematical functions don't require much
> extra overhead.
>
> We tested the query on our installation and for normal courses the
> performance is good. For a course with alot of
> platformgroups/subgroups, the performance is acceptable. However if we
> would split out the query and move the processing to the php
> serverside we think it would be worse, especialy in big
> installations.
>
> Ofcourse feedback is always welcome.
>
> Best regards,
> Pieterjan
>
> Op 19/04/11 09:04, Hans De Bisschop schreef:
> > Hi all,
> >
> > Just to be clear: I really would like feedback from as many
> > developers as possible on this.
> >
> > I had kind of hoped this would have already been posted to the dev
> > list, explaining a few things, but since it's been there for more
> > then a few days now, I'm going to simply ask.
> >
> > What in the name of all things divine is this?
> > (from the WebLCMS MDB2 datamanager implementation)
> >
> > > /**
> > > * Retrieves all users subscribed to a course, including the
> > > users in all subscribed (sub-)groups.
> > > * The course_id is not given in the condition statement
> > > because it is used to select the subgroups
> > > * BEFORE running the actual select. Including the course_id
> > > in the condition statement could result
> > > * in invalid queries.
> > > * @param int $course_id The course for which we want to
> > > retrieve the users
> > > * @param Condition $condition
> > > * @param int $offset
> > > * @param int $count
> > > * @param order $order_property
> > > */
> > > function retrieve_all_course_users($course_id, $condition =
> > > null, $offset = null, $count = null, $order_property = null)
> > > {
> > > // datamanagers
> > > $user_dm = UserDataManager :: get_instance();
> > > $group_dm = GroupDataManager :: get_instance();
> > >
> > >
> > > //////////////////////////////////////////////////////////////////////
> > > // GET RELATED GROUPS
> > >
> > > //--------------------------------------------------------------------
> > >
> > > // SELECT
> > > $tree_values_query = 'SELECT ' .
> > > $group_dm->get_alias(Group :: get_table_name()) . '.' .
> > > $group_dm->escape_column_name(Group :: PROPERTY_LEFT_VALUE);
> > > $tree_values_query .= ', ' . $group_dm->get_alias(Group ::
> > > get_table_name()) . '.' . $group_dm->escape_column_name(Group ::
> > > PROPERTY_RIGHT_VALUE);
> > >
> > > // FROM course_group_relation
> > > $tree_values_query .= ' FROM ' .
> > > $this->escape_table_name(CourseGroupRelation ::
> > > get_table_name()) . ' AS ' .
> > > $this->get_alias(CourseGroupRelation :: get_table_name());
> > >
> > > // JOIN group
> > > $tree_values_query .= ' JOIN ' .
> > > $group_dm->escape_table_name(Group :: get_table_name()) . ' AS ' .
> > > $group_dm->get_alias(Group :: get_table_name());
> > > $tree_values_query .= ' ON ' .
> > > $group_dm->get_alias(Group :: get_table_name()) . '.' .
> > > $group_dm->escape_column_name(Group :: PROPERTY_ID);
> > > $tree_values_query .= ' = ' .
> > > $this->get_alias(CourseGroupRelation :: get_table_name()) . '.' .
> > > $this->escape_column_name(CourseGroupRelation ::
> > > PROPERTY_GROUP_ID);
> > >
> > > // WHERE
> > > $tree_values_condition = new
> > > EqualityCondition(CourseGroupRelation :: PROPERTY_COURSE_ID,
> > > $course_id, $this->get_alias(CourseGroupRelation ::
> > > get_table_name()), true);
> > >
> > > // RETRIEVE
> > > $direct_groups_with_tree_values =
> > > $group_dm->retrieve_object_set($tree_values_query, Group ::
> > > get_table_name(), $tree_values_condition, null, null, null,
> > > Group :: CLASS_NAME);
> > >
> > >
> > > //////////////////////////////////////////////////////////////////////
> > > // SELECT RESULT + STATUS
> > > // 1 = individual, 2 = group --> sum(status)>=3? both user
> > > and group
> > >
> > > //--------------------------------------------------------------------
> > > $query = 'SELECT ';
> > >
> > > // -- id
> > > $query .= 'result.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_ID);
> > > $query .= ', ';
> > >
> > > // -- official code
> > > $query .= 'result.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_OFFICIAL_CODE);
> > > $query .= ', ';
> > >
> > > // -- lastname
> > > $query .= 'result.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_LASTNAME);
> > > $query .= ', ';
> > >
> > > // -- firstname
> > > $query .= 'result.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_FIRSTNAME);
> > > $query .= ', ';
> > >
> > > // -- username
> > > $query .= 'result.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_USERNAME);
> > > $query .= ', ';
> > >
> > > // -- email
> > > $query .= 'result.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_EMAIL);
> > > $query .= ', ';
> > >
> > > // -- status
> > > $query .= 'result.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_STATUS);
> > >
> > > // -->-- subscription status
> > > $query .= ', MIN(result.record_subscription_status) AS
> > > "subscription_status"';
> > >
> > > // -->-- subscription type
> > > $query .= ', SUM(result.record_subscription_type) AS
> > > "subscription_type" FROM (';
> > >
> > >
> > > //////////////////////////////////////////////////////////////////////
> > > // CONSTRUCT RESULT
> > > // 1. individual users
> > >
> > > //--------------------------------------------------------------------
> > >
> > > // - SELECT
> > > $query .= ' SELECT ';
> > >
> > > // -- id
> > > $query .= $user_dm->get_alias(User :: get_table_name()) .
> > > '.' . $user_dm->escape_column_name(User :: PROPERTY_ID);
> > > $query .= ', ';
> > >
> > > // -- official code
> > > $query .= $user_dm->get_alias(User :: get_table_name()) .
> > > '.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_OFFICIAL_CODE);
> > > $query .= ', ';
> > >
> > > // -- lastname
> > > $query .= $user_dm->get_alias(User :: get_table_name()) .
> > > '.' . $user_dm->escape_column_name(User :: PROPERTY_LASTNAME);
> > > $query .= ', ';
> > >
> > > // -- firstname
> > > $query .= $user_dm->get_alias(User :: get_table_name()) .
> > > '.' . $user_dm->escape_column_name(User :: PROPERTY_FIRSTNAME);
> > > $query .= ', ';
> > >
> > > // -- username
> > > $query .= $user_dm->get_alias(User :: get_table_name()) .
> > > '.' . $user_dm->escape_column_name(User :: PROPERTY_USERNAME);
> > > $query .= ', ';
> > >
> > > // -- email
> > > $query .= $user_dm->get_alias(User :: get_table_name()) .
> > > '.' . $user_dm->escape_column_name(User :: PROPERTY_EMAIL);
> > > $query .= ', ';
> > >
> > > // -- status
> > > $query .= $user_dm->get_alias(User :: get_table_name()) .
> > > '.' . $user_dm->escape_column_name(User :: PROPERTY_STATUS);
> > > $query .= ', ';
> > >
> > > // - record_subscription_status
> > > $query .= $this->get_alias(CourseUserRelation ::
> > > get_table_name()) . '.' .
> > > $this->escape_column_name(CourseUserRelation :: PROPERTY_STATUS);
> > > $query .= ' AS "record_subscription_status"';
> > > $query .= ', ';
> > >
> > > // -- record_subscription_type
> > > $query .= '1 AS "record_subscription_type"';
> > >
> > > // - FROM course_user_relation
> > > $query .= ' FROM ' .
> > > $this->escape_table_name(CourseUserRelation :: get_table_name()) .
> > > ' AS ' . $this->get_alias(CourseUserRelation ::
> > > get_table_name());
> > >
> > > // - JOIN user
> > > $query .= ' JOIN ' . $user_dm->escape_table_name(User ::
> > > get_table_name()) . ' AS ' . $user_dm->get_alias(User ::
> > > get_table_name());
> > > $query .= ' ON ' . $this->get_alias(CourseUserRelation ::
> > > get_table_name()) . '.' .
> > > $this->escape_column_name(CourseUserRelation :: PROPERTY_USER);
> > > $query .= ' = ' . $user_dm->get_alias(User ::
> > > get_table_name()) . '.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_ID);
> > >
> > > // WHERE
> > > $translator = ConditionTranslator :: factory($this);
> > > $conditions = array();
> > > $conditions[] = new
> > > EqualityCondition(CourseUserRelation :: PROPERTY_COURSE,
> > > $course_id, CourseUserRelation :: get_table_name());
> > > if (isset($condition))
> > > {
> > > $conditions[] = $condition;
> > > }
> > > $direct_user_condition = new AndCondition($conditions);
> > > $query .=
> > > $translator->render_query($direct_user_condition);
> > >
> > >
> > > //////////////////////////////////////////////////////////////////////
> > > // CONSTRUCT RESULT
> > > // 2a. union with groups
> > >
> > > //--------------------------------------------------------------------
> > > if($direct_groups_with_tree_values->size() > 0)
> > > {
> > > $query .= ' UNION';
> > >
> > > // - SELECT
> > > $query .= ' SELECT ';
> > >
> > > // -- id
> > > $query .= $user_dm->get_alias(User ::
> > > get_table_name()) . '.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_ID);
> > > $query .= ', ';
> > >
> > > // -- official code
> > > $query .= $user_dm->get_alias(User ::
> > > get_table_name()) . '.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_OFFICIAL_CODE);
> > > $query .= ', ';
> > >
> > > // -- lastname
> > > $query .= $user_dm->get_alias(User ::
> > > get_table_name()) . '.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_LASTNAME);
> > > $query .= ', ';
> > >
> > > // -- firstname
> > > $query .= $user_dm->get_alias(User ::
> > > get_table_name()) . '.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_FIRSTNAME);
> > > $query .= ', ';
> > >
> > > // -- username
> > > $query .= $user_dm->get_alias(User ::
> > > get_table_name()) . '.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_USERNAME);
> > > $query .= ', ';
> > >
> > > // -- email
> > > $query .= $user_dm->get_alias(User ::
> > > get_table_name()) . '.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_EMAIL);
> > > $query .= ', ';
> > >
> > > // -- status
> > > $query .= $user_dm->get_alias(User ::
> > > get_table_name()) . '.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_STATUS);
> > > $query .= ', ';
> > >
> > > // -- record_subscription_status
> > > $query .= $this->get_alias(CourseGroupRelation ::
> > > get_table_name()) . '.' .
> > > $this->escape_column_name(CourseGroupRelation ::
> > > PROPERTY_STATUS);
> > > $query .= ' AS "record_subscription_status"';
> > > $query .= ', ';
> > >
> > > // -- subscription_type
> > > $query .= '2 AS "record_subscription_type"';
> > >
> > > // - FROM group
> > > $query .= ' FROM ' .
> > > $group_dm->escape_table_name(Group :: get_table_name()) . ' AS ' .
> > > $group_dm->get_alias(Group :: get_table_name());
> > >
> > > // - JOIN group_rel_user
> > > $query .= ' JOIN ' .
> > > $group_dm->escape_table_name(GroupRelUser :: get_table_name()) . '
> > > AS ' . $group_dm->get_alias(GroupRelUser :: get_table_name());
> > > $query .= ' ON ' . $group_dm->get_alias(Group ::
> > > get_table_name()) . '.' . $group_dm->escape_column_name(Group ::
> > > PROPERTY_ID);
> > > $query .= ' = ' . $group_dm->get_alias(GroupRelUser ::
> > > get_table_name()) . '.' .
> > > $this->escape_column_name(GroupRelUser :: PROPERTY_GROUP_ID);
> > >
> > > // - JOIN user
> > > $query .= ' JOIN ' .
> > > $user_dm->escape_table_name(User :: get_table_name()) . ' AS ' .
> > > $user_dm->get_alias(User :: get_table_name());
> > > $query .= ' ON ' .
> > > $group_dm->get_alias(GroupRelUser :: get_table_name()) . '.' .
> > > $this->escape_column_name(GroupRelUser :: PROPERTY_USER_ID);
> > > $query .= ' = ' . $user_dm->get_alias(User ::
> > > get_table_name()) . '.' . $user_dm->escape_column_name(User ::
> > > PROPERTY_ID);
> > >
> > > // - JOIN course_group_relation
> > > $query .= ' LEFT JOIN ' .
> > > $this->escape_table_name(CourseGroupRelation ::
> > > get_table_name()) . ' AS ' .
> > > $this->get_alias(CourseGroupRelation :: get_table_name());
> > > $query .= ' ON ' .
> > > $this->get_alias(CourseGroupRelation :: get_table_name()) . '.' .
> > > $this->escape_column_name(CourseGroupRelation ::
> > > PROPERTY_GROUP_ID);
> > > $query .= ' = ' . $group_dm->get_alias(Group ::
> > > get_table_name()) . '.' . $group_dm->escape_column_name(Group ::
> > > PROPERTY_ID);
> > >
> > > // 2b. construct WHERE
> > > $direct_group_conditions = array();
> > > while ($group =
> > > $direct_groups_with_tree_values->next_result())
> > > {
> > > $and_conditions = array();
> > > $and_conditions[]= new
> > > InequalityCondition(Group :: PROPERTY_LEFT_VALUE,
> > > InequalityCondition :: GREATER_THAN_OR_EQUAL,
> > > $group->get_left_value());
> > > $and_conditions[]= new
> > > InequalityCondition(Group :: PROPERTY_RIGHT_VALUE,
> > > InequalityCondition :: LESS_THAN_OR_EQUAL,
> > > $group->get_right_value());
> > > $direct_group_conditions[] = new
> > > AndCondition($and_conditions);
> > > }
> > > $or_condition = new
> > > OrCondition($direct_group_conditions);
> > > $user_conditions = array();
> > > $user_conditions[] = $or_condition;
> > > if (isset($condition))
> > > {
> > > $user_conditions[] = $condition;
> > > }
> > > $group_user_condition = new
> > > AndCondition($user_conditions);
> > > $translator = ConditionTranslator :: factory($this);
> > > $query .=
> > > $translator->render_query($group_user_condition);
> > > }
> > >
> > >
> > > //////////////////////////////////////////////////////////////////////
> > > // 3. end subquery
> > >
> > > //--------------------------------------------------------------------
> > > $query .= ') AS result ';
> > >
> > >
> > > //////////////////////////////////////////////////////////////////////
> > > // COMPLETE QUERY
> > >
> > > //--------------------------------------------------------------------
> > > // ORDER BY
> > > $orders = array();
> > > if (is_null($order_by))
> > > {
> > > $order_by = array();
> > > }
> > > elseif (! is_array($order_by))
> > > {
> > > $order_by = array($order_by);
> > > }
> > > foreach ($order_by as $order)
> > > {
> > > if ($order)
> > > {
> > > $orders[] =
> > > $user_dm->escape_column_name($order->get_property(),
> > > ($order->alias_is_set() ? $order->get_alias() :
> > > $user_dm->get_alias(User :: get_table_name()))) . ' ' .
> > > ($order->get_direction() == SORT_DESC ? 'DESC' : 'ASC');
> > > }
> > > }
> > > if (count($orders))
> > > {
> > > $query .= ' ORDER BY ' . implode(', ', $orders);
> > > }
> > >
> > > // GROUP BY
> > > $query .= 'GROUP BY result.' .
> > > $user_dm->escape_column_name(User :: PROPERTY_USERNAME);
> > >
> > > // LIMIT
> > > if (isset($count))
> > > {
> > > $query .= ' LIMIT ';
> > > $query .= isset($offset) ? $offset : 0;
> > > $query .= ',';
> > > $query .= $count;
> > > }
> > >
> > > return new Mdb2ResultSet($this, $user_dm->query($query),
> > > User :: CLASS_NAME);
> > > }
> >
> > So ... I tend to remember discussions about performance, about how
> > our multitude of queries was very, VERY bad ... and I absolutely
> > agreed that in some cases a select number of well chosen joins was a
> > good idea. Combined with well thought out caching that would be a
> > big advantage. (although with an installation time of about 5
> > seconds on an old server, I'm not really complaining anyway)
> >
> > But then there's this: a combination of inner and outer joins,
> > unions, subselects, mathematical functions (and I hope we all know
> > how much MySQL *loves* to do maths) and to finish it all off
> > everyone's favourite memory / execution-time hog: group by. Can
> > anyone PLEASE explain the reasoning and or logic behind this kind of
> > construction? I know of only one other piece of software that has
> > equally exotic constructs (BaMaFlex) ... and I wouldn't consider
> > that a good thing. I expect to see these kinds of things in
> > Microsoft products or maybe even in high-end database environments
> > that need to keep track of very complex relational data, but in
> > Chamilo? I guess it really is rocket science if this is the kind of
> > thing we need.
> >
> > Analyzing queries is good, even essential. Trying to improve them is
> > equally important. But we will not achieve proverbial world peace
> > with one super-query to end all queries ... it is without a doubt
> > not the final solution. It's a bit late for April Fool's Day, no?
> >
> > Looking forward to reading what will no doubt be a very creative and
> > informative reply. I am equally interested in seeing comparative
> > performance reports between the classic approach and "this".
> >
> > --
> >
> >
> > Hans De Bisschop
> > Hoofddeskundige ICTO | Lead Developer Chamilo 2.0
> > Software Coordinator Chamilo Association
> > Erasmushogeschool Brussel
> > Nijverheidskaai 170 | B-1070 Brussel
> > T 02 559 02 54 | i 254
> > [email protected] | www.erasmushogeschool.be
> >
> > Kom eens langs: www.erasmushogeschool.be/infodagen
> > of lees onze elektronische nieuwsbrief: ehbrief.ehb.be
> > P Before printing, think about the environment
> >
> >
> >
> >
> > _______________________________________________
> > Dev mailing list
> > [email protected]
> > http://lists.chamilo.org/listinfo/dev
>
> _______________________________________________
> Dev mailing list
> [email protected]
> http://lists.chamilo.org/listinfo/dev
_______________________________________________
Dev mailing list
[email protected]
http://lists.chamilo.org/listinfo/dev
