How to Implement Temporary Table ?

In Mysql Temporary Table Can provide Additional Performance in a Large Set of Data in Select.

Ive Tried to Dig in Laravel's Api

And Found Out Laravel Indeed Has a Temporary Table Function

But I Havent Seen Anyone Used it at All in Any Example.

I Really Want to Know How Can i Used it like a Stored Procedure in mysql

If Anyone Know How to Use it Please Share thanks

Schema::create('flights', function (Blueprint $table) { $table->increments('id'); $table->string('name'); $table->string('airline'); $table->timestamps(); $table->temporary(); });

greenwood360

9 years ago

Level 3

I have found to build temporary tables built off a query - it is best to use the DB::unprepared(DB::raw("query here")); method. This also allows you to execute multiple table creations and drops without having to write out extensive schemas.

To give a use case scenario- let's assume you have hundreds of thousands of parcels. Each with costs associated in different categories. Let's say you want to know the median cost of the aggregate total per parcel for category id 9.

To do this speedily we can create a temporary table of all the total costs per parcel for category 9 using a select statement.

However, a MySQL limitation will not let you reference a temp table within itself, which is the common way to get a median value.

To solve this we create two temporary tables with identical values.

NOTE: Make sure you have enough memory for your MySQL to do this in scale.

Because we want fresh values everytime, we run a laravel unprepared method to drop our tables.

$dropTempTables = DB::unprepared(
    DB::raw("
        DROP TABLE IF EXISTS table_temp_a ;
        DROP TABLE IF EXISTS table_temp_b ;
    ")
);

The unprepared method returns true or false. You can use the returned value to gate whether or not you continue. In this case we don't need to check if we dropped the tables unless we are debugging.

We then create the identical temp tables based on a select statement - MySQL then creates the schema based on the source data type.

$createTempTables = DB::unprepared(
    DB::raw("
        CREATE TEMPORARY TABLE table_temp_a 
                        AS (
                            SELECT 
                                parcel_id, 
                                sum(amount) as amount 
                            FROM cost_items 
                            WHERE expense_category_id = 9 
                            GROUP BY parcel_id 
                            );
                        CREATE TEMPORARY TABLE table_temp_b 
                        AS (
                            SELECT 
                                parcel_id, 
                                sum(amount) as amount 
                            FROM cost_items 
                            WHERE expense_category_id = 9 
                            GROUP BY parcel_id 
                            );

    ")
);

We then gate entry into our next query based on the $createTempTables being true. The query below assumes you would want to do more than one median in a single query so I am showing it using left join to a programs table. This way if the median is empty - you still get a result.

if($createTempTables){
    $medianData = DB::
                select(
                    DB::raw("
                        SELECT 
                        p.id
                        nc.median_category_9_cost

                FROM programs p
                LEFT JOIN(
                    SELECT
                        avg(t1.amount) AS median_category_9_cost
                    FROM
                        ( 
                            SELECT
                                @rownum :=@rownum + 1 AS `row_number` ,
                            d.amount
                            FROM
                                table_temp_a d,
                            (SELECT @rownum := 0) r
                            WHERE
                                1
                            ORDER BY
                                d.amount
                        ) AS t1 ,

                        (
                            SELECT
                                count(*) AS total_rows
                            FROM
                                table_temp_b d
                            WHERE
                                1

                        ) AS t2

                        WHERE
                            1
                        AND t1.row_number 
                            IN(
                                floor((t2.total_rows + 1) / 2) ,
                                floor((t2.total_rows + 2) / 2)
                            )
                                
                        )nc on 1 = 1
    );
} else {
    $error = "ERROR MESSAGE";
    dd($error);
}

Running that set of queries on MySQL with the appropriate indexes on the cost_items table with over 500,000 rows took .01 seconds.

Now since we are dealing with Temporary Tables - and knowing they are session based - I would recommend running the drop table if exists again after you are done with them to free up memory.

1 like

//dd($query1); $query1 = DB::select(DB::raw(" SELECT TOP 5 B.GroupItemID, B.Code, C.Name AS Branch, B.Name, SUM(A.Qty) AS Qty, SellPrice AS price, A.BranchID, A.LinkID FROM #TEMP1 A INNER JOIN GroupItem B ON A.GroupItemID = B.GroupItemID inner join Branch C ON A.BranchID = C.BranchID WHERE B.ItemType = 'I' AND B.IsDeleted = 0 AND B.Code NOT LIKE 'OTH%' AND (B.Name like '%BT%' OR REPLACE(B.Code, '-', '') like REPLACE('%BT%', '-', '')) GROUP BY B.GroupItemID, B.Code, B.Name, B.IsDeleted, C.Name, SellPrice, A.BranchID, A.LinkID HAVING SUM(A.Qty) <> 0 order by C.Name ")); //dd($query1); $query1 = DB::statement(DB::raw(" DROP TABLE #TEMP1 "));