Re: I need Optimizaton of a Query - DISTINCT

[Sumeet Shroff]

here is the show create table, and explain

For now, i am using a.created BETWEEN date-1-month to 1-month

It reducing the query to less than 1 minute. but let me know if there is an alternative

----

CREATE TABLE `jos_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
`username` varchar(150) NOT NULL DEFAULT '',
`email` varchar(100) NOT NULL DEFAULT '',
`password` varchar(100) NOT NULL DEFAULT '',
`usertype` varchar(25) NOT NULL DEFAULT '',
`block` tinyint(4) NOT NULL DEFAULT '0',
`sendEmail` tinyint(4) DEFAULT '0',
`gid` tinyint(3) unsigned NOT NULL DEFAULT '1',
`registerDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`lastvisitDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`activation` varchar(100) NOT NULL DEFAULT '',
`params` text NOT NULL,
PRIMARY KEY (`id`),
KEY `usertype` (`usertype`),
KEY `idx_name` (`name`),
KEY `gid_block` (`gid`,`block`),
KEY `username` (`username`),
KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=125 DEFAULT CHARSET=utf8


CREATE TABLE `jos_categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) NOT NULL DEFAULT '0',
`title` varchar(255) NOT NULL DEFAULT '',
`name` varchar(255) NOT NULL DEFAULT '',
`alias` varchar(255) NOT NULL DEFAULT '',
`image` varchar(255) NOT NULL DEFAULT '',
`section` varchar(50) NOT NULL DEFAULT '',
`image_position` varchar(30) NOT NULL DEFAULT '',
`description` text NOT NULL,
`published` tinyint(1) NOT NULL DEFAULT '0',
`checked_out` int(11) unsigned NOT NULL DEFAULT '0',
`checked_out_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`editor` varchar(50) DEFAULT NULL,
`ordering` int(11) NOT NULL DEFAULT '0',
`access` tinyint(3) unsigned NOT NULL DEFAULT '0',
`count` int(11) NOT NULL DEFAULT '0',
`params` text NOT NULL,
PRIMARY KEY (`id`),
KEY `cat_idx` (`section`,`published`,`access`),
KEY `idx_access` (`access`),
KEY `idx_checkout` (`checked_out`)
) ENGINE=MyISAM AUTO_INCREMENT=666 DEFAULT CHARSET=utf8


CREATE TABLE `jos_sections` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL DEFAULT '',
`name` varchar(255) NOT NULL DEFAULT '',
`alias` varchar(255) NOT NULL DEFAULT '',
`image` text NOT NULL,
`scope` varchar(50) NOT NULL DEFAULT '',
`image_position` varchar(30) NOT NULL DEFAULT '',
`description` text NOT NULL,
`published` tinyint(1) NOT NULL DEFAULT '0',
`checked_out` int(11) unsigned NOT NULL DEFAULT '0',
`checked_out_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`ordering` int(11) NOT NULL DEFAULT '0',
`access` tinyint(3) unsigned NOT NULL DEFAULT '0',
`count` int(11) NOT NULL DEFAULT '0',
`params` text NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_scope` (`scope`)
) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=utf8



id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a ref idx_section,idx_access,idx_state idx_state 1 const 30960 Using where; Using temporary; Using filesort
1 SIMPLE u eq_ref PRIMARY PRIMARY 4 arabstod_arabsdb.a.created_by 1
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 arabstod_arabsdb.a.catid 1 Using where
1 SIMPLE s eq_ref PRIMARY PRIMARY 4 arabstod_arabsdb.c.section 1 Using where