Re: I need Optimizaton of a Query - DISTINCT
Hi
CREATE TABLE `jos_content` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL DEFAULT '',
`alias` varchar(255) NOT NULL DEFAULT '',
`title_alias` varchar(255) NOT NULL DEFAULT '',
`introtext` mediumtext NOT NULL,
`fulltext` mediumtext NOT NULL,
`state` tinyint(3) NOT NULL DEFAULT '0',
`sectionid` int(11) unsigned NOT NULL DEFAULT '0',
`mask` int(11) unsigned NOT NULL DEFAULT '0',
`catid` int(11) unsigned NOT NULL DEFAULT '0',
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`created_by` int(11) unsigned NOT NULL DEFAULT '0',
`created_by_alias` varchar(255) NOT NULL DEFAULT '',
`modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified_by` int(11) unsigned 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',
`publish_up` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`publish_down` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`images` text NOT NULL,
`urls` text NOT NULL,
`attribs` text NOT NULL,
`version` int(11) unsigned NOT NULL DEFAULT '1',
`parentid` int(11) unsigned NOT NULL DEFAULT '0',
`ordering` int(11) NOT NULL DEFAULT '0',
`metakey` text NOT NULL,
`metadesc` text NOT NULL,
`access` int(11) unsigned NOT NULL DEFAULT '0',
`hits` int(11) unsigned NOT NULL DEFAULT '0',
`metadata` text NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_section` (`sectionid`),
KEY `idx_access` (`access`),
KEY `idx_checkout` (`checked_out`),
KEY `idx_state` (`state`),
KEY `idx_catid` (`catid`),
KEY `idx_createdby` (`created_by`)
) ENGINE=MyISAM AUTO_INCREMENT=39251 DEFAULT CHARSET=utf8
------------
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
jos_content MyISAM 10 Dynamic 35970 4379 157513432 281474976710655 2874368 44 39251 2010-01-05 19:38:09 2010-10-17 09:31:11 2010-10-12 07:49:37 utf8_general_ci NULL
-------------
>> DISTINCT makes sure you don't have two identical output rows. Is that even possible in the data? Is u.id the PRIMARY KEY for u? If there are never dups, why have DISTINCT?
u.id is the primary key, but there is a join.... If i remove distinct, the php script does not work.... there maybe more than one identical row received...
>> DISTINCT needs to read all the rows and de-dup them. This may involve a sort, or it may be done by some other means.
I am looking for alternatives...