He leido y leido acerca de como mostrar resultados de forma más rápida y es utilizando indices porque evita que tenga que hacer un full scan en toda la tabla pero veo que la velocidad en traer los resultados no es la que se espera, así que en una consulta SQL donde todos los campos usan indice para brindar un resultado mas rapido, les voy a compartir una estructura:
Código PHP:
CREATE TABLE IF NOT EXISTS `index_site` (
`id_building` char(32) NOT NULL COMMENT 'Id MD5 de oferta',
`id_client` char(32) NOT NULL COMMENT 'Id MD5 de publicador',
`id_broker` smallint(5) unsigned NOT NULL,
`kind_client` char(1) NOT NULL,
`city` smallint(6) unsigned NOT NULL,
`lat` float(10,6) NOT NULL,
`lng` float(10,6) NOT NULL,
`zone` smallint(2) unsigned NOT NULL,
`sector` smallint(4) unsigned NOT NULL,
`subregion` smallint(6) unsigned NOT NULL,
`country` char(2) NOT NULL,
`habs` smallint(5) unsigned NOT NULL,
`bath` smallint(5) unsigned NOT NULL,
`persons` smallint(5) unsigned NOT NULL,
`include_elevator` enum('1','0') NOT NULL,
`build_level` varchar(20) NOT NULL,
`area` mediumint(8) unsigned NOT NULL,
`area_um` enum('1','2','3','4','5') NOT NULL,
`area_str` varchar(10) NOT NULL,
`code` char(10) NOT NULL,
`title` tinytext NOT NULL,
`type_offer` varchar(50) NOT NULL,
`offer_name` varchar(20) NOT NULL,
`comments` text NOT NULL,
`type_building` varchar(50) NOT NULL,
`address` tinytext NOT NULL,
`sector_name` tinytext NOT NULL,
`city_name` varchar(50) NOT NULL,
`subregion_name` varchar(50) NOT NULL,
`area_terrain` varchar(10) NOT NULL,
`area_um_terrain` tinyint(4) NOT NULL,
`image` varchar(70) NOT NULL,
`image_total` tinyint(2) unsigned NOT NULL,
`build_status` tinyint(3) unsigned NOT NULL,
`tags` text NOT NULL COMMENT 'Etiquetas de oferta',
`url` varchar(200) NOT NULL,
`include_offer_value` enum('1','0') NOT NULL,
`offer_value` varchar(15) NOT NULL,
`offer_value_format` varchar(20) NOT NULL,
`prc_comission` varchar(5) NOT NULL,
`date_added` datetime NOT NULL,
`date_updated` datetime NOT NULL,
`date_expire` datetime NOT NULL,
`date_suspended` date NOT NULL,
`visits` int(11) NOT NULL,
`kind_offer` tinyint(4) NOT NULL,
`kind_building` tinyint(5) unsigned NOT NULL,
`kind_building_type` tinyint(5) unsigned NOT NULL,
`mark_bld` tinyint(3) unsigned NOT NULL,
`mark_bld_color` char(7) NOT NULL,
`status` tinyint(1) unsigned NOT NULL,
`is_made` enum('0','1') NOT NULL,
`is_project` enum('0','1') NOT NULL,
`is_bm` enum('0','1') NOT NULL COMMENT 'Incluida en Broker Market',
`is_demo` enum('0','1') NOT NULL,
`is_leading` enum('0','1') NOT NULL COMMENT 'Es destacado',
`visible_in_metasearch` mediumtext NOT NULL,
`visible_in_web` mediumtext NOT NULL,
`seller_image` varchar(150) NOT NULL,
`seller_name` varchar(50) NOT NULL,
KEY `id_broker` (`id_broker`),
KEY `id_client` (`id_client`),
KEY `kind_building` (`kind_building`),
KEY `city` (`city`),
KEY `offer_value` (`offer_value`),
KEY `is_bm` (`is_bm`),
KEY `status` (`status`),
KEY `sector` (`sector`),
KEY `zone` (`zone`),
KEY `area` (`area`),
KEY `prc_comission` (`prc_comission`),
KEY `is_made` (`is_made`),
KEY `is_leading` (`is_leading`),
KEY `id_building` (`id_building`),
KEY `date_added` (`date_added`),
KEY `code` (`code`),
KEY `country` (`country`),
KEY `habs` (`habs`),
KEY `kind_offer` (`kind_offer`),
FULLTEXT KEY `tags` (`tags`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Indizador de sitio';
Bueno el tema es como se pueden dar cuenta estoy usando varios indices de los cuales cuando hago una búsqueda usando varios indices, hasta ahi normal; el paso siguiente es cuando hago la siguiente consulta:
Código PHP:
SELECT * FROM `index_site` WHERE kind_building='1' AND kind_offer='1' AND city='1'
Código PHP:
EXPLAIN SELECT * FROM `index_site` WHERE kind_building='1' AND kind_offer='1' AND city='1'
Código PHP:
+----+-------------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+-------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+-------------------------------------------------------------+
| 1 | SIMPLE | index_site | index_merge | kind_building,city,kind_offer | kind_offer,city,kind_building | 1,2,1 | NULL | 184 | Using intersect(kind_offer,city,kind_building); Using where |
+----+-------------+------------+-------------+-------------------------------+-------------------------------+---------+------+------+-------------------------------------------------------------+
¿Qué tengo mal?
Espero alguien pueda entender mi pregunta. Gracias!
