Здравствуйте.
Подмогите. Запрос выполняется очень долго(10 сек.), при том, что записей не так много.
| Код |
SELECT
cat.id AS cat_id,
cat.name,
cat.description,
cat.pict,
COUNT(podcat_firms.firms_id) AS cnt_fb
FROM
cat
LEFT OUTER JOIN cat_podcat ON (cat_podcat.cat_id = cat.id)
LEFT OUTER JOIN podcat_firms ON (podcat_firms.podcat_id = cat_podcat.podcat_id)
LEFT OUTER JOIN firmbr ON (firmbr.id = podcat_firms.firms_id)
LEFT OUTER JOIN city ON (city.id = firmbr.city_id)
LEFT OUTER JOIN cat_glava ON (cat_glava.cat_id = cat.id)
LEFT OUTER JOIN podcat_glava ON (podcat_glava.podcat_id = cat_podcat.podcat_id)
WHERE
cat_glava.glava_id = '1' AND
city.id = '1' AND
podcat_glava.glava_id = '1' AND
firmbr.visible = '1' AND
firmbr.moder = '1' AND
firmbr.prenadl = 'f'
GROUP BY
cat.id,
cat.name,
cat.description,
cat.pict
ORDER BY
cat.name
|
| Код |
CREATE TABLE `cat` (
`id` int(255) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`name_short` varchar(200) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
`pict` varchar(255) NOT NULL default '',
`prior` int(5) NOT NULL default '0',
`views` int(255) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=54 ;
-- --------------------------------------------------------
CREATE TABLE `cat_glava` (
`id` int(25) NOT NULL auto_increment,
`cat_id` int(25) NOT NULL default '0',
`glava_id` int(25) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=169 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=169 ;
-- --------------------------------------------------------
CREATE TABLE `cat_podcat` (
`id` int(11) NOT NULL auto_increment,
`cat_id` int(11) NOT NULL default '0',
`podcat_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=218 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=218 ;
-- --------------------------------------------------------
CREATE TABLE `city` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`sort` int(3) NOT NULL,
`visible` int(2) NOT NULL,
`symbol_name` varchar(10) NOT NULL,
`city_code` varchar(10) NOT NULL,
`area` int(2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=7 ;
-- --------------------------------------------------------
CREATE TABLE `firmbr` (
`id` int(25) NOT NULL auto_increment,
`prenadl` varchar(2) default NULL,
`private` varchar(100) NOT NULL,
`city_id` int(5) NOT NULL default '1',
`name` varchar(255) default NULL,
`otch` varchar(255) NOT NULL,
`fam` varchar(255) NOT NULL,
`alt` varchar(100) default NULL,
`description` text,
`url` varchar(100) default NULL,
`ind` text,
`street_1` varchar(150) default NULL,
`dom_1` varchar(150) default NULL,
`tel_1` varchar(150) default NULL,
`rr_1` varchar(100) default NULL,
`email_1` varchar(100) default NULL,
`min_zak` varchar(255) NOT NULL,
`kol_ch` varchar(255) NOT NULL,
`login` varchar(20) default NULL,
`pass` varchar(20) default NULL,
`contact` varchar(50) NOT NULL default '',
`tel` varchar(50) NOT NULL default '',
`email` varchar(100) default NULL,
`str` varchar(4) default NULL,
`stlb` varchar(4) default NULL,
`map` varchar(30) default NULL,
`ip_adr_add` varchar(20) default NULL,
`auth_user_ins` varchar(100) NOT NULL default '',
`date_add` datetime default NULL,
`ip_adr_upd` varchar(20) NOT NULL default '',
`date_upd` datetime NOT NULL default '0000-00-00 00:00:00',
`auth_user_upd` varchar(100) NOT NULL default '',
`search` text NOT NULL,
`mailsend_1` int(2) NOT NULL default '0',
`first_vizit` int(2) NOT NULL default '0',
`message` text NOT NULL,
`activation_code` varchar(255) NOT NULL,
`moder` varchar(2) NOT NULL default '1',
`visible` varchar(2) NOT NULL default '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6127 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=6127 ;
-- --------------------------------------------------------
CREATE TABLE `podcat_firms` (
`id` int(255) NOT NULL auto_increment,
`podcat_id` int(255) NOT NULL default '0',
`firms_id` int(255) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=26901 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=26901 ;
-- --------------------------------------------------------
CREATE TABLE `podcat_glava` (
`id` int(255) NOT NULL auto_increment,
`podcat_id` int(10) NOT NULL default '0',
`glava_id` int(10) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=366 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=366 ;
|
|
