mysql - 如何避免在MySQL内部查询

我有以下表格：

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多次。

能否请任何人帮助我如何优化此查询？

最佳答案

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


以排序的方式获取表类别的列表。


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


获取数组中每个类别的计数。再次排序。保存在数组中，例如category_count


按category_id从table_subcategory_categories组中选择count（*），按category_id ASC选择顺序


获取按类别排序的整个子类别列表
保存在多维数组中，请说sub_categories


从table_subcategories sc，table_subcategory_categories scc WHERE sc.sub_categories_id = scc.sub_categories_id中选择sc。*，scc.category_id排序依据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;