На самом деле сейчас 700000 записей тока на локальном компе, а на серваке ещё старая версия, но я её скоро залью.
Ещё, кстати, таблицы не оптимизированный с помощью PROCEDURE ANALYSE
Вот как это выглядит:
Запрос: SHOW CREATE TABLE acat_details
Записей: 1Table Create Table
acat_details CREATE TABLE `acat_details` (
`detail_id` int(11) NOT NULL default \'0\',
`detail_name` varchar(150) NOT NULL default \'\',
`detail_no` varchar(30) default NULL,
`detail_okp` varchar(12) default NULL,
`detail_picture` smallint(6) NOT NULL default \'0\',
`detail_pos` smallint(6) default NULL,
`detail_quantity` text NOT NULL,
`map` smallint(6) NOT NULL default \'0\',
`detail_dopinfo` text NOT NULL,
`detail_alt1` varchar(25) default NULL,
`detail_alt2` varchar(25) default NULL,
PRIMARY KEY (`detail_id`),
KEY `map` (`map`,`detail_picture`),
KEY `map_only` (`map`)
) TYPE=MyISAM
Запрос: SHOW CREATE TABLE acat_mark
Записей: 1Table Create Table
acat_mark CREATE TABLE `acat_mark` (
`mark_id` int(11) NOT NULL default \'0\',
`mark_name` varchar(20) NOT NULL default \'\',
`mark_name_full` varchar(255) default NULL,
PRIMARY KEY (`mark_id`)
) TYPE=MyISAM
Запрос: SHOW CREATE TABLE acat_model
Записей: 1Table Create Table
acat_model CREATE TABLE `acat_model` (
`model_id` int(11) NOT NULL default \'0\',
`mark_id` int(11) NOT NULL default \'0\',
`model_name` varchar(30) NOT NULL default \'\',
`type_id` int(11) NOT NULL default \'0\',
`map` smallint(6) NOT NULL default \'0\',
`modification` varchar(255) default NULL,
`actual` varchar(10) default NULL,
`favorites` int(11) NOT NULL default \'0\',
`hidden` int(11) NOT NULL default \'0\',
`info` text,
`diskno` smallint(6) NOT NULL default \'0\',
`model_order` smallint(6) NOT NULL default \'0\',
PRIMARY KEY (`model_id`),
UNIQUE KEY `model_order` (`model_order`),
KEY `map` (`map`)
) TYPE=MyISAM
Запрос: SHOW CREATE TABLE acat_trees;
Записей: 1Table Create Table
acat_trees CREATE TABLE `acat_trees` (
`map` smallint(6) NOT NULL default \'0\',
`id` int(11) NOT NULL default \'0\',
`parent_id` int(11) NOT NULL default \'0\',
`detail_picture` smallint(6) unsigned NOT NULL default \'0\',
`tree_level` smallint(6) NOT NULL default \'0\',
`tree_pos` smallint(6) NOT NULL default \'0\',
`tree_name` text NOT NULL,
PRIMARY KEY (`id`),
KEY `map` (`map`,`detail_picture`)
) TYPE=MyISAM
Общее время выполнения 0.028872
Далее запросы:
кстати заметь что всё это можно выбрать 1 запросом но работать будет на порядок дольше (я очень долго схему эту разрабатывал)
CREATE TEMPORARY TABLE acat_details_count (KEY (map)) TYPE=HEAP
SELECT detail_id, map FROM acat_details USE INDEX (map_only) WHERE 1=1 AND ((detail_name LIKE "%Болт%" AND detail_name LIKE "%корпуса%") OR (detail_no LIKE "%Болт%" AND detail_no LIKE "%корпуса%")) AND map IN (57, 56, 55, 54, 181, 182, 118, 196, 18, 19, 205, 190, 189, 191, 163, 115, 81, 149, 80, 161, 175, 176, 177, 58, 1, 2, 64, 3, 116, 4, 5, 6, 7, 66, 8, 37, 188, 9, 28, 120, 114, 62, 72, 192, 134, 117, 15, 10, 11, 31, 29, 167, 12, 13, 14, 110, 16, 42, 45, 43, 129, 49, 86, 17, 44, 85, 160, 48, 171, 111, 172, 183, 30, 102, 130, 202, 23, 82, 52, 84, 159, 35, 24, 25, 127, 126, 125, 128, 143, 145, 144, 146, 20, 21, 22, 204, 119, 173, 195, 194, 162, 68, 106, 107, 108, 169, 33, 109, 103, 104, 168, 83, 50, 51, 151, 53, 152, 47, 164, 121, 122, 123, 124, 198, 131, 132, 38, 87, 34, 26, 96, 137, 105, 88, 95, 98, 99, 60, 97, 100, 61, 133, 101, 63, 147, 148, 170, 73, 139, 140, 141, 142, 77, 78, 74, 186, 187, 71, 69, 154, 174, 46, 155, 79, 156, 150, 36, 136, 135, 157, 158, 113, 165, 166, 193, 59, 39, 179, 32, 178, 40, 180, 185, 41, 184, 197, 201, 70, 138, 153, 203, 65, 67, 76, 27, 89, 90, 75, 91, 92, 93, 199, 94, 200, 112);
CREATE TEMPORARY TABLE acat_details_tmp TYPE=HEAP
SELECT STRAIGHT_JOIN model_order, detail_id FROM acat_model ml INNER JOIN acat_details_count d FORCE INDEX(map) ON d.map=ml.map
WHERE 1=1 AND ml.model_id IN (128,127,126,125,248,249,173,265,36,46,37,130,48,274,275,276,277,259,258,260,225,170,139,207,138,222,238,239,240,140,1,2,3,4,5,171,6,7,8,9,10,11,12,38,13,14,15,257,16,39,175,18,40,41,17,176,159,142,261,191,172,27,19,20,25,43,21,229,22,23,26,24,42,28,29,30,31,185,32,44,33,186,34,45,221,35,233,129,234,250,54,55,187,271,74,75,76,77,220,78,84,79,80,81,82,83,183,182,181,184,201,203,202,204,49,53,50,51,52,273,174,235,264,263,223,224,118,119,120,121,231,108,109,114,110,111,115,113,112,116,117,122,123,230,65,69,66,209,67,210,68,211,131,212,71,213,70,226,177,178,179,180,267,188,189,63,89,87,132,47,96,169,195,107,90,92,166,93,94,97,85,167,98,168,88,99,91,95,165,86,103,100,105,104,101,190,106,102,205,206,232,160,197,161,198,199,200,162,163,164,255,256,143,253,144,254,145,146,215,237,236,64,216,136,137,217,208,62,193,194,192,218,219,158,227,228,262,141,58,245,60,241,61,247,56,246,59,242,244,57,243,252,73,251,72,266,270,124,196,214,272,133,134,135,147,149,148,150,151,152,153,154,155,268,156,269,157)
LIMIT 0, 1001;
SELECT STRAIGHT_JOIN ml.model_id, m.mark_id, t1.id tree_id1, t2.id tree_id2, t3.id tree_id3
, d.detail_id, m.mark_name, CONCAT(m.mark_name, \' \', model_name) auto,
t1.tree_name t1, t2.tree_name t2, t3.tree_name t3, d.detail_name, d.detail_no, d.detail_okp
FROM acat_details_tmp d_tmp
INNER JOIN acat_details d ON d_tmp.detail_id=d.detail_id
INNER JOIN acat_model ml ON ml.model_order=d_tmp.model_order
INNER JOIN acat_trees t3 ON (d.map=t3.map AND d.detail_picture=t3.detail_picture)
INNER JOIN acat_trees t2 ON t2.id=t3.parent_id
INNER JOIN acat_trees t1 ON t1.id=t2.parent_id
INNER JOIN acat_mark m ON m.mark_id=ml.mark_id
LIMIT 100
