mysql - 如何避免Mysql中的内部查询

Question

我有下表:

table_categories - id, name, imagepath
table_subcategories - sub_categories_id, sub_category_name
table_subcategory_categories - subcategory_id, category_id (Mapping table for category and sub category)

我想以这种格式获取所有类别及其各自的子类别:

[
    {
        "subcategories": [
            {
                "scname": "Sugar",
                "scid": "5"
            },
            {
                "scname": "Salt",
                "scid": "6"
            },
            {
                "scname": "Jaggery",
                "scid": "7"
            }
        ],
        "name": "Salt and Sugar",
        "id": "1",
        "image": "/images/salt_sugar.png"
    },
    {
        "subcategories": [
            {
                "scname": "Tea",
                "scid": "8"
            },
            {
                "scname": "Coffee",
                "scid": "9"
            },
            {
                "scname": "Tea Bags",
                "scid": "162"
            }
        ],
        "name": "Tea and Coffee",
        "id": "2",
        "image": "/images/tea_and_coffee.png"
    }
]   

这是我获取类别和子类别的逻辑。

my $sql_query = "SELECT * from table_categories";
my $statement = $db_handle->prepare ($sql_query) or die "Couldn't prepare query '$sql_query': $DBI::errstr\n";  
$statement->execute() or die "SQL Error: $DBI::errstr\n";  

my @loop_data = ();
while (my @data = $statement->fetchrow_array()) {
    my $sql = "SELECT SCC.subcategory_id, SC.sub_category_name
            FROM
            table_subcategory_categories as SCC
            INNER JOIN 
            table_subcategories as SC
            ON
            SCC.subcategory_id = SC.sub_categories_id
            where SCC.category_id = '$data[0]' ";
    my $stmt = $db_handle->prepare ($sql) or die "Couldn't prepare query '$sql_query': $DBI::errstr\n";  
    $stmt->execute() or die "SQL Error: $DBI::errstr\n";  

    my @InnerLoopData = ();
    while (my @InnerData = $stmt->fetchrow_array()) {
        my %InnerData = ('scid', $InnerData[0], 'scname', $InnerData[1]);
        push(@InnerLoopData, \%InnerData);
    }

    my %data = ('id', $data[0], 'name', $data[1], 'image', $data[2], 'subcategories', \@InnerLoopData);
    push(@loop_data, \%data);
}

my $json_text = to_json(\@loop_data);
print $json_text;
$db_handle->disconnect;

一切都按预期工作，但问题是因为内部查询响应太慢。由于我有 1000 多个类别，因此内部查询执行了 1000 多次。

谁能帮我优化这个查询？？

Answer 1

您可以在 perl 脚本中进行处理，减少数据库调用的次数。数据库调用很昂贵。

1. 获取排序后的表格类别列表。

SELECT * FROM table_categories ORDER BY id ASC将值存储在多维数组中。说类别

1. 获取数组中每个类别的计数。再次排序。保存在数组中说 category_count

select count(*) FROM table_subcategory_categories group by category_id order by category_id ASC

1. 获取按类别排序的完整子类别列表保存在一个多维数组中说 sub_categories

SELECT sc.*,scc.category_id 来自 table_subcategories sc, table_subcategory_categories scc WHERE sc.sub_categories_id=scc.sub_categories_id ORDER BY scc.category_id ASC

然后

my @loop_data = ();
my $currentPos = 0;
my $noOfElementsInThisCategory = 0;
foreach my $i (0 .. $#categories){
     $noOfElementsInThisCategory = $category_count[$i];
     my @InnerLoopData = ();
   for (my $k = $currentPos; $noOfElementsInThisCategory > 0;      $noOfElementsInThisCategory++) {
       my %InnerData = ('scid', $sub_categories [$k][0], 'scname',  $sub_categories [$k][1]);
       push(@InnerLoopData, \%InnerData);
       $currentPos = $currentPos + 1;
}
my %data = ('id', $categories[$i][0], 'name', $categories[$i][1], 'image', $categories[$i][2], 'subcategories', \@InnerLoopData);
    push(@loop_data, \%data);

}
my $json_text = to_json(\@loop_data);
print $json_text;