Доброе время суток.
СУБД: MySQL 5.1.48. Сервер: Intel Xeon 2 Ghz, RAM 2 Gb, Centos.
Есть такой запрос:
| Код |
select o.*, t.name as name_type, s_t.name as name_supertype, u_a.login as login_user_add,
u_e.login as login_user_edit, r.name as name_region, str.name as name_street,
st.name as name_state, m.name as name_metro, rz.name as name_razdel, srv.name as name_service,
mt.name as name_material, i.name as name_infosource, i_ex.name as name_infosource_exclusive,
obn.name as name_mediator, group_concat(p.num_phone) as phones, cast(group_concat(ons.id_site) as CHAR) as id_site,
group_concat(distinct u_i.login) as login_user_image
from object_n o
left join types t on o.id_type=t.id
left join types s_t on o.id_supertype=s_t.id
left join users u_a on o.id_user_add=u_a.id
left join users u_e on o.id_user_edit=u_e.id
left join regions r on r.id=o.id_region
left join streets str on o.id_street=str.id
left join state st on o.id_state=st.id
left join metro m on o.id_metro=m.id
left join razdel rz on rz.id=o.id_razdel
left join services srv on srv.id=o.id_service
left join materials mt on mt.id=o.id_material
left join infosource i on i.id=o.id_infosource
left join infosource i_ex on i_ex.id=o.id_infosource_exclusive
left join object_n obn on obn.id=o.id_mediator
left join phones p on p.id_object_n=o.id and p.is_request=0
left join object_n_site ons on ons.id_object_n=o.id
left join images im on im.id_object_n=o.id and im.type_img='images'
left join users u_i on u_i.id=im.id_user_add
where o.id_supertype='39' and o.is_deleted='0' and o.id_type<>0
group by o.id
order by o.id desc
LIMIT 0, 10
|
Он выполняется в течении 14 сек на localhost и где-то 17 сек на хосте.
Скорость не зависит от того - есть LIMIT или нет.
Скрипты таблиц (только поля, используемые в запросе, без PK и FK):
| Код |
CREATE TABLE IF NOT EXISTS `object_n` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_supertype` int(11) NOT NULL,
`id_type` int(11) NOT NULL,
`id_user_add` int(11) NOT NULL,
`id_region` int(11) NOT NULL,
`id_street` int(11) NOT NULL,
`id_state` int(11) NOT NULL,
`id_metro` int(11) NOT NULL,
`id_razdel` int(11) NOT NULL,
`id_service` int(11) NOT NULL,
`id_material` int(11) NOT NULL,
`id_infosource` int(11) NOT NULL,
`id_mediator` int(11) NOT NULL,
`id_user_edit` int(11) NOT NULL,
`count_room` int(11) NOT NULL,
`num_floor` int(11) NOT NULL,
`count_floor` int(11) NOT NULL,
`num_house` varchar(50) NOT NULL,
`num_korpus` int(11) NOT NULL,
`num_apart` varchar(50) NOT NULL,
`ref_point` varchar(255) NOT NULL,
`price` float NOT NULL,
`is_phone` int(1) NOT NULL,
`is_photo` int(1) NOT NULL,
`remark1` varchar(255) NOT NULL,
`remark2` varchar(255) NOT NULL,
`is_on_site` int(1) NOT NULL,
`is_deleted` int(1) NOT NULL,
`date_add` datetime NOT NULL,
`dle` datetime NOT NULL,
`square_all` float NOT NULL,
`square_live` float NOT NULL,
`square_kitchen` float NOT NULL,
`count_balcone` int(11) NOT NULL,
`count_balcone_glass` int(11) NOT NULL,
`is_exchange` int(1) NOT NULL,
`formula_exchange` varchar(255) NOT NULL,
`id_infosource_exclusive` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`flag_admin` int(1) NOT NULL COMMENT '0-new_add, 1-checked, 2-not checked',
`sum_seller` float NOT NULL);
CREATE TABLE IF NOT EXISTS `types` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL);
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`login` varchar(255) NOT NULL);
CREATE TABLE IF NOT EXISTS `regions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL);
CREATE TABLE IF NOT EXISTS `streets` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL);
CREATE TABLE IF NOT EXISTS `state` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL);
CREATE TABLE IF NOT EXISTS `metro` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL);
CREATE TABLE IF NOT EXISTS `razdel` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL);
CREATE TABLE IF NOT EXISTS `materials` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL);
CREATE TABLE IF NOT EXISTS `infosource` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL);
CREATE TABLE IF NOT EXISTS `phones` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_object_n` int(11) NOT NULL,
`num_phone` varchar(30) NOT NULL);
CREATE TABLE IF NOT EXISTS `object_n_site` (
`id` int(11) NOT NULL,
`id_object_n` int(11) NOT NULL,
`id_site` int(11) NOT NULL,
`id_user_add` int(11) NOT NULL);
CREATE TABLE IF NOT EXISTS `images` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_object_n` int(11) NOT NULL,
`type_img` varchar(10) NOT NULL,
`id_user_add` int(11) NOT NULL);
|
Индексы:
object_n:
| Код |
KEY `id_type` (`id_type`),
KEY `id_region` (`id_region`),
KEY `id_street` (`id_street`),
KEY `id_state` (`id_state`),
KEY `id_metro` (`id_metro`),
KEY `id_usefull` (`id_usefull`),
KEY `id_gaz` (`id_gaz`),
KEY `id_water` (`id_water`),
KEY `id_razdel` (`id_razdel`),
KEY `id_service` (`id_service`),
KEY `id_material` (`id_material`),
KEY `id_infosource` (`id_infosource`),
KEY `id_mediator` (`id_mediator`),
KEY `id_part_house` (`id_part_house`),
KEY `id_firm_build` (`id_firm_build`),
KEY `id_sewerage` (`id_sewerage`),
KEY `id_kommun` (`id_kommun`),
KEY `id_pattern_own` (`id_pattern_own`),
KEY `id_part_square` (`id_part_square`),
KEY `id_infosource_exclusive` (`id_infosource_exclusive`),
KEY `is_deleted` (`is_deleted`),
KEY `id_supertype` (`id_supertype`),
KEY `id_user_add` (`id_user_add`),
KEY `id_user_edit` (`id_user_edit`)
|
images:
| Код |
KEY `id_object_n` (`id_object_n`),
KEY `id_user_add` (`id_user_add`)
|
phones:
| Код |
KEY `num_phone` (`num_phone`),
KEY `id_object_n` (`id_object_n`)
|
Что выдает explain:
| Код |
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE o ref id_type,is_deleted,id_supertype id_supertype 4 const 30539 Using where; Using temporary; Using filesort
1 SIMPLE t eq_ref PRIMARY PRIMARY 4 ekr.o.id_type 1
1 SIMPLE s_t eq_ref PRIMARY PRIMARY 4 ekr.o.id_supertype 1
1 SIMPLE u_a eq_ref PRIMARY PRIMARY 4 ekr.o.id_user_add 1
1 SIMPLE u_e eq_ref PRIMARY PRIMARY 4 ekr.o.id_user_edit 1
1 SIMPLE r eq_ref PRIMARY PRIMARY 4 ekr.o.id_region 1
1 SIMPLE str eq_ref PRIMARY PRIMARY 4 ekr.o.id_street 1
1 SIMPLE st eq_ref PRIMARY PRIMARY 4 ekr.o.id_state 1
1 SIMPLE m eq_ref PRIMARY PRIMARY 4 ekr.o.id_metro 1
1 SIMPLE rz eq_ref PRIMARY PRIMARY 4 ekr.o.id_razdel 1
1 SIMPLE srv eq_ref PRIMARY PRIMARY 4 ekr.o.id_service 1
1 SIMPLE mt eq_ref PRIMARY PRIMARY 4 ekr.o.id_material 1
1 SIMPLE i eq_ref PRIMARY PRIMARY 4 ekr.o.id_infosource 1
1 SIMPLE i_ex eq_ref PRIMARY PRIMARY 4 ekr.o.id_infosource_exclusive 1
1 SIMPLE obn eq_ref PRIMARY PRIMARY 4 ekr.o.id_mediator 1
1 SIMPLE p ref id_object_n id_object_n 4 ekr.o.id 2
1 SIMPLE ons ALL id_object_n NULL NULL NULL 1
1 SIMPLE im ref id_object_n id_object_n 4 ekr.o.id 2
1 SIMPLE u_i eq_ref PRIMARY PRIMARY 4 ekr.im.id_user_add 1
|
В таблице object_n 71748 записей, в phones - 182086, в images и object_n_site пока мало, но кол-во будет расти. Все таблицы кроме object_n, phones, images и object_n_site - справочники, и их объем не будет расти.
Я пыталась записать запрос с использованием подзапроса:
| Код |
select o.*, t.name as name_type, s_t.name as name_supertype, u_a.login as login_user_add,
u_e.login as login_user_edit, r.name as name_region, str.name as name_street, st.name as name_state,
m.name as name_metro, rz.name as name_razdel, srv.name as name_service, mt.name as name_material,
i.name as name_infosource, i_ex.name as name_infosource_exclusive, obn.name as name_mediator,
group_concat(p.num_phone) as phones, cast(group_concat(ons.id_site) as CHAR) as id_site,
group_concat(distinct u_i.login) as login_user_image
from (select * from object_n where id_supertype='39' and is_deleted='0' and id_type<>0) o
left join types t on o.id_type=t.id left
join types s_t on o.id_supertype=s_t.id
left join users u_a on o.id_user_add=u_a.id
left join users u_e on o.id_user_edit=u_e.id
left join regions r on r.id=o.id_region
left join streets str on o.id_street=str.id
left join state st on o.id_state=st.id
left join metro m on o.id_metro=m.id
left join razdel rz on rz.id=o.id_razdel
left join services srv on srv.id=o.id_service
left join materials mt on mt.id=o.id_material
left join infosource i on i.id=o.id_infosource
left join infosource i_ex on i_ex.id=o.id_infosource_exclusive
left join object_n obn on obn.id=o.id_mediator
left join phones p on p.id_object_n=o.id and p.is_request=0
left join object_n_site ons on ons.id_object_n=o.id
left join images im on im.id_object_n=o.id and im.type_img='images'
left join users u_i on u_i.id=im.id_user_add
group by o.id
order by o.id desc
LIMIT 0, 10
|
Но в результате вообще ничего не выводится, и ошибки, ни пустого результата, вообще ничего, как-будто я не нажимала на кнопку "выполнить запрос".
Заранее благодарна. |
)
