Ребят сложный запрос к базе(сильно грузит сервер)... Сам по себе запрос не очень(не я его делал), оптимизировал как смог:
| MySQL |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
| SELECT
STR_ID,
TEX_TEXT AS STR_DES_TEXT,
IF(
EXISTS(
SELECT
*
FROM
SEARCH_TREE AS SEARCH_TREE2
WHERE
SEARCH_TREE2.STR_ID_PARENT <=> SEARCH_TREE.STR_ID
LIMIT
1
), 1, 0) AS DESCENDANTS
FROM
SEARCH_TREE
INNER JOIN DESIGNATIONS ON DES_ID = STR_DES_ID
INNER JOIN DES_TEXTS ON TEX_ID = DES_TEX_ID
WHERE
STR_ID_PARENT = 'переменная' AND
DES_LNG_ID = 16 AND
EXISTS (
SELECT
*
FROM
LINK_GA_STR
INNER JOIN LINK_LA_TYP ON LAT_TYP_ID = 'переменная' AND
LAT_GA_ID = LGS_GA_ID
INNER JOIN LINK_ART ON LA_ID = LAT_LA_ID
WHERE
LGS_STR_ID = STR_ID
ORDER BY TEX_TEXT desc
LIMIT
1
) |
|
сам запрос на серваке выполняется секунду. Но проблема, этот запрос к базе изначально выводит список + к этому списку нужно подкрепить дерево его значений(т.е. это список <ul>)+к этому списку еще вывод списка. И всего их до 5ти.
Выглядит это так:
mysql запрос который показал +
| PHP |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
| $tree_all_array = array();
$lookup = mysqli_query($linktcLi, "$mysql1'$group'$mysql2'$tree_all_id'$mysql3"); // поиск артикула
if(mysqli_num_rows($lookup) > 0){
while($cross_result = mysqli_fetch_assoc($lookup)){
$tree_all_array[$cross_result['STR_ID']] = $cross_result; // массив поиска
}}
foreach($tree_all_array as $key => $value){
if($value['DESCENDANTS'] == 1){
$lookup = mysqli_query($linktcLi, "$mysql1'{$value['STR_ID']}'$mysql2'$tree_all_id'$mysql3"); // поиск артикула
if(mysqli_num_rows($lookup) > 0){
while($cross_result = mysqli_fetch_assoc($lookup)){
$tree_all_array[$value['STR_ID']]['one'][$cross_result['STR_ID']] = $cross_result; // массив поиска
}}}}
foreach($tree_all_array as $key1 => $value){
if(isset($value['one'])){
foreach($value['one'] as $key => $value){
if($value['DESCENDANTS'] == 1){
$lookup = mysqli_query($linktcLi, "$mysql1'{$value['STR_ID']}'$mysql2'$tree_all_id'$mysql3"); // поиск артикула
if(mysqli_num_rows($lookup) > 0){
while($cross_result = mysqli_fetch_assoc($lookup)){
$tree_all_array[$key1]['one'][$key]['two'][$cross_result['STR_ID']] = $cross_result; // массив поиска
}}}}}}
foreach($tree_all_array as $key1 => $value){
if(isset($value['one'])){
foreach($value['one'] as $key2 => $value){
if(isset($value['two'])){
foreach($value['two'] as $key => $value){
if($value['DESCENDANTS'] == 1){
$lookup = mysqli_query($linktcLi, "$mysql1'{$value['STR_ID']}'$mysql2'$tree_all_id'$mysql3"); // поиск артикула
if(mysqli_num_rows($lookup) > 0){
while($cross_result = mysqli_fetch_assoc($lookup)){
$tree_all_array[$key1]['one'][$key2]['two'][$key]['tree'][$cross_result['STR_ID']] = $cross_result; // массив поиска
}}}}}}}} |
|
и плюс к этому вывод на странице
| PHP |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
| [HTML]<ul id="techMenuId">
<? foreach($tree_all_array as $key => $value ){
if($value['DESCENDANTS'] == 0)$text_arr[] = $value['STR_DES_TEXT'].' (group'.$value['STR_ID'].')';
if($value['DESCENDANTS'] == 1){
?>
<li><a id="cookIdt<?=$group?>" class="techStyleLi"> <?=$value['STR_DES_TEXT']?></a>
<ul id="techMenuId2" class='techStyleSection'>
<? $i = 100;
if(isset($value['one'])){
foreach($value['one'] as $key => $value ){
if($value['DESCENDANTS'] == 0)$text_arr[] = $value['STR_DES_TEXT'].' (group'.$value['STR_ID'].')';
$i = $i +1;
if($value['DESCENDANTS'] == 1){
?>
<li> <a id="cookIde<?=$i?>" class="techStyleLi"> <?=$value['STR_DES_TEXT']?></a>
<ul id="techMenuId3" class='techStyleSection'>
<?
if(isset($value['two'])){
foreach($value['two'] as $key => $value ){
if($value['DESCENDANTS'] == 0)$text_arr[] = $value['STR_DES_TEXT'].' (group'.$value['STR_ID'].')';
$i = $i +1;
if($value['DESCENDANTS'] == 1){
?>
<li> <a id="cookIdl<?=$i?>" class="techStyleLi"> <?=$value['STR_DES_TEXT']?></a>
<ul id="techMenuId3" class='techStyleSection'>
<?
if(isset($value['tree'])){
foreach($value['tree'] as $key => $value ){
if($value['DESCENDANTS'] == 0)$text_arr[] = $value['STR_DES_TEXT'].' (group'.$value['STR_ID'].')';
$i = $i +1;
if($value['DESCENDANTS'] == 1){
?>
<li> <a class="techNoFollow" href="?id=tree_sort&<?=$value['STR_DES_TEXT']?>-на-<?=$_GET['model']?>&tree_group_id=<?=$value['STR_ID']?>&tree_all_id=<?=$_GET['tree_all_id']?>&tree_id=<?=$_GET['tree_id']?>&model=<?=$_GET['model']?>&typ=<?=$_GET['typ']?>&cod=<?=$_GET['cod']?>&start=<?=$_GET['start']?>&end=<?=$_GET['end']?>&text=<?=$value['STR_DES_TEXT']?>&car=<?=$_GET['car']?>"> <?=$value['STR_DES_TEXT']?></a></li>
<?}else{?>
<li> <a class="techNoFollow" href="?id=tree_sort&<?=$value['STR_DES_TEXT']?>-на-<?=$_GET['model']?>&tree_group_id=<?=$value['STR_ID']?>&&tree_all_id=<?=$_GET['tree_all_id']?>&tree_id=<?=$_GET['tree_id']?>&model=<?=$_GET['model']?>&typ=<?=$_GET['typ']?>&cod=<?=$_GET['cod']?>&start=<?=$_GET['start']?>&end=<?=$_GET['end']?>&text=<?=$value['STR_DES_TEXT']?>&car=<?=$_GET['car']?>"> <?=$value['STR_DES_TEXT']?></a></li>
<?}}}?>
</ul>
</li>
<?}else{?>
<li> <a class="techNoFollow" href="?id=tree_sort&<?=$value['STR_DES_TEXT']?>-на-<?=$_GET['model']?>&tree_group_id=<?=$value['STR_ID']?>&tree_all_id=<?=$_GET['tree_all_id']?>&tree_id=<?=$_GET['tree_id']?>&model=<?=$_GET['model']?>&typ=<?=$_GET['typ']?>&cod=<?=$_GET['cod']?>&start=<?=$_GET['start']?>&end=<?=$_GET['end']?>&text=<?=$value['STR_DES_TEXT']?>&car=<?=$_GET['car']?>"> <?=$value['STR_DES_TEXT']?></a></li>
<?}}}?>
</ul>
<?}else{?>
<li> <a class="techNoFollow" href="?id=tree_sort&<?=$value['STR_DES_TEXT']?>-на-<?=$_GET['model']?>&tree_group_id=<?=$value['STR_ID']?>&tree_all_id=<?=$_GET['tree_all_id']?>&tree_id=<?=$_GET['tree_id']?>&model=<?=$_GET['model']?>&typ=<?=$_GET['typ']?>&cod=<?=$_GET['cod']?>&start=<?=$_GET['start']?>&end=<?=$_GET['end']?>&text=<?=$value['STR_DES_TEXT']?>&car=<?=$_GET['car']?>"> <?=$value['STR_DES_TEXT']?></a></li>
</li>
<?}}?>
</ul>
</li>
<?}}else{?>
<li><a class="techNoFollow" href="?id=tree_sort&<?=$value['STR_DES_TEXT']?>-на-<?=$_GET['model']?>&tree_group_id=<?=$value['STR_ID']?>&tree_all_id=<?=$_GET['tree_all_id']?>&tree_id=<?=$_GET['tree_id']?>&model=<?=$_GET['model']?>&typ=<?=$_GET['typ']?>&cod=<?=$_GET['cod']?>&start=<?=$_GET['start']?>&end=<?=$_GET['end']?>&text=<?=$value['STR_DES_TEXT']?>&car=<?=$_GET['car']?>"> <?=$value['STR_DES_TEXT']?></a></li>
<?}};?>
</ul>[/HTML] |
|
Если нет чего сказать, лучше не сказать, советы типа оооо ну ты грузишь мускул ппц... в другой теме.
Очень буду благодарен за любой совет!
(
