How to avoid inner queries in Mysql

Question

I have following tables:

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)

I want to fetch all the categories and their respective subcategories in this format:

[
    {
        "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"
    }
]

This is my logic for fetch the categories and subcategories.

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;

Every thing is working as expected, but the problem is because of inner query response is too slow. As i have more than 1000 categories, inner query executes more than 1000 times.

Can please anyone help me how to optimize this query??

Anirban Roy · Accepted Answer · 2015-07-25 04:51:36Z

You can do the processing within the perl script reducing the number of DB calls. DB calls are expensive.

Get the list of table categories in a sorted manner.

SELECT * FROM table_categories ORDER BY id ASC Store the values in a multi dimensional array. say categories

Get the count for each category in an array. Again sorted. Save in an array say category_count

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

Get the entire list of subcategories sorted by the Save in a multi-dimensional array say sub_categories

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

And then

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;

M0rtiis · Accepted Answer · 2015-07-25 02:41:07Z

2

+50

SCC.subcategory_id
SC.sub_categories_id
SCC.category_id

this fields should be indexes, then it will be fast. Joins should be on indexed fields

also you dont need (for fetching subcategories data) a loop here (queries in a loop - bad idea), use single query:

SELECT SCC.subcategory_id, SC.sub_category_name, C.*
            FROM
            table_subcategory_categories as SCC
            INNER JOIN 
            table_subcategories as SC
            ON
            SCC.subcategory_id = SC.sub_categories_id
INNER JOIN table_categories as C ON SCC.category_id = C.id

edited Jul 25, 2015 at 2:41

answered Jul 25, 2015 at 2:31

M0rtiis

3,7611 gold badge17 silver badges22 bronze badges

2 Comments

Devesh Agrawal Over a year ago

Thanks for response. Can you help me on How to create above mentioned JSON from this SQL query result?

M0rtiis Over a year ago

i am not familiar with perl, sorry :)

Channel Islander · Accepted Answer · 2015-07-25 03:22:33Z

You should prepare the query outside the loop using placeholders and bind variables.

You should also create your database handle with { RaiseError => 1} so you don't have to explicitly check for errors.

You should also fetch the results as a hashref and avoid creating your own hash each time through the loop.

my $dbh  = DBI->connect($dsn, { RaiseError => 1 });

my $sql1 = "SELECT * from table_categories";
my $sth1  = $dbh->prepare ($sql1);  
$sth1->execute();  

my @loop_data = ();

my $sql2 = qq/
    SELECT SCC.subcategory_id as scid, SC.sub_category_name as scname
    FROM table_subcategory_categories SCC
    INNER JOIN table_subcategories SC
      ON SCC.subcategory_id = SC.sub_categories_id
    WHERE SCC.category_id = ?
/;
my $sth2 = $dbh->prepare( $sql2 );

while (my @data = $sth1->fetchrow_array()) {
    $sth2->execute( $data[0] );

    my @InnerLoopData = ();

    while (my $InnerData = $sth2->fetchrow_hashref()) {
        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;
$dbh->disconnect;

Collectives™ on Stack Overflow

How to avoid inner queries in Mysql

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related