SELECT d.id,
d.ip,d.p,d.uid,d.cat,d.region,
(SELECT count(1) FROM site_comments WHERE message_id = d.id) comments_count
FROM site_db as d
WHERE status = 1 AND cat in (218,220,219,649,217,619,226,544,543,542,216,818,215,230,616,227,221,620,654,668,225,224,231,228,214)
AND (region IN (126,127,661,662,663,128,380,381,382,383,384,385,386,387,388,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,129,157,158,159,160,161,162,163,164,165,166,167,130,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,131,196,197,198,199,200,201,202,203,204,205,206,207,208,209,132,210,211,212,213,214,215,216,217,218,219,220,133,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,134,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,135,156,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,136,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,137,155,664,665,666,667,669,670,671,672,681,682,683,684,685,694,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,28,329,330,331,332,333,334,335,336,337,6627,6639,138,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,139,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,6683211,6683212,6683213,6683214,6683215,6683216,6683217,6683218,6683219,6683220,6683221,6683222,6683223,6683224,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,140,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,141,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,142,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,
45,546,547,143,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,697,6683210,144,567,568,569,570,571,572,573,574,575,576,577,145,578,579,580,581,582,583,584,585,586,587,588,589,6626,6633,6649,146,590,591,592,593,594,595,596,597,598,599,600,147,601,602,603,604,605,606,607,608,609,610,611,6628,6637,6650,6651,148,612,613,614,615,616,617,618,695,6632,149,619,620,621,622,623,624,625,842,150,626,627,628,629,630,631,632,633,634,843,151,635,636,637,638,639,674,675,676,677,678,679,680,6630,152,640,641,642,643,644,645,646,844,6634,6635,153,647,648,649,650,651,652,653,654,655,656,657,658,659,660,6629,6631,6640,6641,6642,6643,6644,6645,6646,6647,6648,696,6638,389,6652)) ORDER by raised desc, date_add desc;
SELECT d.id,
d.ip,
d.p,
d.uid,
d.cat,
d.region,
(SELECT count(1) FROM site_comments WHERE message_id = d.id) comments_count
FROM site_db as d
WHERE status = 1 AND cat in (218,220,219,649,217,619,226,544,543,542,216,818,215,230,616,227,221,620,654,668,225,224,231,228,214)
AND urifull like (SELECT urifull from site_regions where id = 127) ORDER by raised desc, date_add desc;
urifull содержит полное наименование региона древовидной структуры без пробела, к примеру для записей в регионе Россия->Москва->Центр urifull будет равен РоссияМоскваЦентр.
Первый запрос исходный, второй переделал под like, работает быстрее, оно и логично. Проблема в том что фрагмент второго запроса
(SELECT urifull from site_regions where id = 127)
Берет urifull из другой таблицы и like ищет точное соответствие строк, к примерк РоссияМосква.
Соответственно запросы возвращают разное количество строк.
Если втрой запрос написать так
SELECT d.id,
d.ip,
d.p,
d.uid,
d.cat,
d.region,
(SELECT count(1) FROM site_comments WHERE message_id = d.id) comments_count
FROM site_db as d
WHERE status = 1 AND cat in (218,220,219,649,217,619,226,544,543,542,216,818,215,230,616,227,221,620,654,668,225,224,231,228,214)
AND urifull like 'РоссияМосква%' ORDER by raised desc, date_add desc;
то всё выбирается правильно, отсюда вопрос: Как дописать % к конструкции
(SELECT urifull from site_regions where id = 127)