Slow query when order by is added, multiple inner joins..

[BWorld]

Hi All,

 

first of all I will introduce myself, my name is Chris Blokland and I am developing PHP for 4,5 years now.

I have knowledge of some other languages like C#, javascript, VB, ActionScript 3.0/Flex 3.5.

 

So to the point, I am having trouble with an mysql query wich I cant get solved some way..

 

The query is:

 

SELECT
    `s`.`id` AS s_id,
    `s`.`id_articles_categories` AS s_id_articles_categories,
    `s`.`id_security_boxes` AS s_id_security_boxes,
    `s`.`title` AS s_title,
    `s`.`author_name` AS s_author_name,
    `s`.`publish_date` AS s_publish_date,
    `s`.`tags` AS s_tags,
    `s`.`readCount` AS s_readCount,
    `s`.`id_articles_types` AS s_id_articles_types,
    `s`.`active` AS s_active,
    `s`.`declined` AS s_declined,
    `s`.`content` AS s_content,
    `s`.`date_creation` AS s_date_creation,
    `s`.`date_last_edit` AS s_date_last_edit,
    `u`.`id_security_boxes` AS u_id_security_boxes,
    `u`.`id_users_accounts` AS u_id_users_accounts,
    `u`.`id_security_groups` AS u_id_security_groups,
    `u`.`create` AS u_create,
    `u`.`read` AS u_read,
    `u`.`update` AS u_update,
    `u`.`delete` AS u_delete,
    `u`.`admin` AS u_admin,
    `t`.`id` AS t_id,
    `t`.`id_security_boxes` AS t_id_security_boxes,
    `t`.`id_security_control` AS t_id_security_control,
    `t`.`username` AS t_username,
    `t`.`password` AS t_password,
    `t`.`email` AS t_email,
    `t`.`activated` AS t_activated,
    `t`.`deleted` AS t_deleted,
    `t`.`signup_date` AS t_signup_date,
    `t`.`last_active` AS t_last_active,
    `t`.`last_location` AS t_last_location,
    `x`.`id` AS x_id,
    `x`.`id_security_boxes` AS x_id_security_boxes,
    `x`.`title` AS x_title,
    `x`.`article_class` AS x_article_class,
    `x`.`form_class` AS x_form_class,
    `y`.`id_security_boxes` AS y_id_security_boxes,
    `y`.`id_users_accounts` AS y_id_users_accounts,
    `y`.`id_security_groups` AS y_id_security_groups,
    `y`.`create` AS y_create,
    `y`.`read` AS y_read,
    `y`.`update` AS y_update,
    `y`.`delete` AS y_delete,
    `y`.`admin` AS y_admin
FROM
    articles_items s
            INNER JOIN
            (
              SELECT
                `acl`.`id_security_boxes` AS `id_security_boxes`,
                `acl`.`id_users_accounts` AS `id_users_accounts`,
                `acl`.`id_security_groups` AS `id_security_groups`,

                MAX(`acl`.`create`) AS `create`,
                  MAX(`acl`.`read`) AS `read`,
                MAX(`acl`.`update`) AS `update`,
                  MAX(`acl`.`delete`) AS `delete`,
                MAX(`acl`.`admin`) AS `admin`
              FROM
                  `security_acl` AS `acl`
              WHERE
                `acl`.`id_users_accounts` = 2 OR `acl`.`id_security_groups` IN (9,4,3,1)
              GROUP BY  `acl`.`id_security_boxes`
            ) AS `u`
INNER JOIN
    users_accounts t
ON
    t.id = s.id_users_accounts
INNER JOIN
    articles_types x
ON
    x.id = s.id_articles_types
            INNER JOIN
            (
              SELECT
                `acl`.`id_security_boxes` AS `id_security_boxes`,
                `acl`.`id_users_accounts` AS `id_users_accounts`,
                `acl`.`id_security_groups` AS `id_security_groups`,

                MAX(`acl`.`create`) AS `create`,
                  MAX(`acl`.`read`) AS `read`,
                MAX(`acl`.`update`) AS `update`,
                  MAX(`acl`.`delete`) AS `delete`,
                MAX(`acl`.`admin`) AS `admin`
              FROM
                  `security_acl` AS `acl`
              WHERE
                `acl`.`id_users_accounts` = 2 OR `acl`.`id_security_groups` IN (9,4,3,1)
              GROUP BY  `acl`.`id_security_boxes`
            ) AS `y`
WHERE
    s.active = 1
AND
    s.declined = 0
    AND
`u`.`id_security_boxes` = `s`.`id_security_boxes`
AND
    t.deleted = 0
AND
    t.activated = 1
    AND
`y`.`id_security_boxes` = `x`.`id_security_boxes`
ORDER BY
  s.publish_date DESC
LIMIT
    0,10

 

Well, this query is used for displaying a list of articles from a wiki-like system.

Ehm, the inner joins you see will get some infromation about the privileges the current user has on the fetched articles. (Create.Read.Update.Delete.Admin)

I have tested these apart from the whole query, these are very fast with 12.500 records (0.0004 seconds)

 

Now, the query taks 1.8229 seconds for execution, if I remove the ORDER BY then it takes 0.0384 seconds, pretty big difference..

If I prefix the query with EXPLAIN then you get the result wich can be seen in the attachment.

 

Below I will post the table structure, I am not really familliar with indexes but learning a lot about it last days.

 

Table structures:

 

-- 
-- Tabel structuur voor tabel `articles_items`
-- 

CREATE TABLE `articles_items` (
  `id` int(10) NOT NULL auto_increment,
  `id_comments_containers` int(10) NOT NULL default '0',
  `id_security_boxes` int(10) NOT NULL default '0',
  `id_articles_categories` int(10) NOT NULL default '0',
  `title` varchar(255) character set latin1 collate latin1_general_ci NOT NULL,
  `author_name` varchar(255) character set latin1 collate latin1_general_ci NOT NULL,
  `publish_date` date NOT NULL default '0000-00-00',
  `date_creation` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_last_edit` datetime NOT NULL default '0000-00-00 00:00:00',
  `id_users_accounts` int(10) NOT NULL default '0',
  `tags` varchar(255) character set latin1 collate latin1_general_ci NOT NULL,
  `readCount` int(10) NOT NULL default '0',
  `active` tinyint(1) NOT NULL default '0',
  `declined` tinyint(1) NOT NULL default '0',
  `id_articles_types` int(10) unsigned NOT NULL default '1',
  `content` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `security` (`id_security_boxes`),
  KEY `comments` (`id_comments_containers`),
  KEY `category` (`id_articles_categories`),
  KEY `article_type` (`id_articles_types`),
  KEY `active` (`active`),
  KEY `declined` (`declined`),
  KEY `sorting` (`publish_date`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=504 ;

-- --------------------------------------------------------

-- 
-- Tabel structuur voor tabel `articles_types`
-- 

CREATE TABLE `articles_types` (
  `id` int(11) NOT NULL auto_increment,
  `id_security_boxes` int(10) unsigned NOT NULL default '0',
  `title` varchar(45) NOT NULL default '',
  `article_class` varchar(255) NOT NULL default '',
  `form_class` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

-- --------------------------------------------------------

-- 
-- Tabel structuur voor tabel `security_acl`
-- 

CREATE TABLE `security_acl` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `id_security_groups` int(10) unsigned default NULL,
  `id_users_accounts` int(10) unsigned default NULL,
  `id_security_boxes` int(10) unsigned NOT NULL default '0',
  `create` int(10) unsigned NOT NULL default '0',
  `read` int(10) unsigned NOT NULL default '0',
  `update` int(10) unsigned NOT NULL default '0',
  `delete` int(10) unsigned NOT NULL default '0',
  `admin` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `Groups` (`id_security_groups`),
  KEY `Accounts` (`id_users_accounts`),
  KEY `SecurityBoxes` (`id_security_boxes`),
  KEY `crud` (`read`,`delete`,`update`,`create`,`admin`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12282 ;

-- --------------------------------------------------------

-- 
-- Tabel structuur voor tabel `users_accounts`
-- 

CREATE TABLE `users_accounts` (
  `id` int(11) NOT NULL auto_increment,
  `id_security_boxes` int(11) NOT NULL default '0',
  `id_security_control` int(11) NOT NULL default '0',
  `username` varchar(45) character set latin1 NOT NULL default '',
  `email` varchar(255) character set latin1 NOT NULL default '',
  `activated` tinyint(1) default '0',
  `deleted` tinyint(1) default '0',
  `creationCompleted` tinyint(1) unsigned NOT NULL default '0',
  `password` blob NOT NULL,
  `signup_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `system_account` tinyint(1) NOT NULL default '0',
  `last_active` datetime NOT NULL default '0000-00-00 00:00:00',
  `last_location` blob NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `Security` (`id_security_boxes`,`id_security_control`),
  KEY `activated_deleted` (`activated`,`deleted`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=77 ;

 

Greetz

 

Note; I am dutch, excuse me for the poor english

 

[attachment deleted by admin]

[kickstart]

Hi

 

Your English is infinitely better than my Dutch.

 

Looks like the biggest issue is that the 2 subselects are being joined inefficiently (ie, using tempory and filesort).

 

About to go out, but will have a play when I get back. Suspect you might be able to avoid using the subselects.

 

Also, on the subselects you are only grouping by 1 of the 3 non aggregate columns. MySQL seems to cope with this, but most flavours of SQL will not cope with it and it is probably best avoided (ie if id_users_accounts varies for the same id_security_boxes, which one should it pick up?).

 

All the best

 

Keith