Question 316589 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL complex query in 5 million rows

I have table with 5 million rows.
Example

+----+-----------+---------+-------
|  product    | date       |  count    | 
+-------------+------------+-----------+
|  платье     | 04-01-2024 | 186574    | 
|  штаны      | 04-01-2024 | 20564     |
|  кепка      | 07-01-2024 | 104443    | 
|  штаны      | 06-01-2024 | 10574     | 
|  платье     | 06-01-2024 | 223001    |
+-------------+------------+-----------+

I need select rows with 2 custom date,then if product name1 = name2 get the difference between count and then get top 100 on this diffence.

now i create 2 select and then I’m looping through the array to get it. but i have error about memory. How can i create 1 query in mysql ?

my query

    $get_ex = $connection->prepare("SELECT * FROM product WHERE date = :date");
    $get_ex->execute(array(':date' => $first_date));
    $ex_list_2 = $get_ex->fetchAll(PDO::FETCH_ASSOC);

    
    $get_ex_3 = $connection->prepare("SELECT * FROM product WHERE date = :date");
    $get_ex_3->execute(array(':date' => $second_date));
    $ex_list_3 = $get_ex_3->fetchAll(PDO::FETCH_ASSOC);

    
    $result = Array();
    $i=0;
    foreach($ex_list_2 as $key => $value)
    {
        foreach($ex_list_3 as $key2 => $value2)
        {
            if($value['product_name'] == $value2['product_name'])
            {
                $result[$i]['product_name'] = $value['product_name'];
                $result[$i]['count_1'] = $value['count'];
                $result[$i]['count_2'] = $value2['count'];
                $result[$i]['res'] = $value['count']- $value2['count'];
                $i++;
            }
        }
        
    }

and result –

Fatal error: Allowed memory size of 1073741824 bytes exhausted

memory_limit = 2048mb

for result i need array with next info

+-------------+-------------+-------------+-----------+
|  product    | 04-01-2024  |  06-01-2024 |  diff     | 
+-------------+-------------+-------------+-----------+
|  платье     | 186574      | 223001      | 36427     |
|  штаны      | 20564       | 10574       | 99000     |
+-------------+-------------+-------------+-----------+
Tags:

2

Answers


  1. 0

    Don’t do two queries, use a single JOIN query.

    SELECT t1.product_name, t1.count AS count1, t2.count AS count2, t1.count - t2.count AS res
FROM product AS t1
JOIN product AS t2 ON t1.product_name = t2.product_name
WHERE t1.date = :first_date AND t2.date = :second_date
ORDER BY res DESC
LIMIT 100
    Login or Signup to reply.
  2. 0

    To optimize your query and avoid memory issues, you can use a single SQL query to perform all the necessary operations. The aim is to join the table with itself on the product name, filter the rows by the two specific dates, calculate the difference in counts, and then order the results to get the top 100 differences. This approach is much more efficient as it leverages the database engine for computation, reducing the memory load on your application.

    Here is an example SQL query that should achieve what you are looking for:

    SELECT 
    t1.product,
    t1.count AS count_date1,
    t2.count AS count_date2,
    ABS(t1.count - t2.count) AS diff
FROM 
    product AS t1
JOIN 
    product AS t2 ON t1.product = t2.product
WHERE 
    t1.date = '2024-01-04' AND 
    t2.date = '2024-01-06'
ORDER BY 
    diff DESC
LIMIT 100;

    Here’s how you can adapt your PHP code to use the new SQL query:

    try {
    // Assuming $connection is your PDO connection
    $first_date = '2024-01-04';  // Set your first date
    $second_date = '2024-01-06'; // Set your second date

    // Prepare the optimized SQL query
    $sql = "
        SELECT 
            t1.product,
            t1.count AS count_date1,
            t2.count AS count_date2,
            ABS(t1.count - t2.count) AS diff
        FROM 
            product AS t1
        JOIN 
            product AS t2 ON t1.product = t2.product
        WHERE 
            t1.date = :first_date AND 
            t2.date = :second_date
        ORDER BY 
            diff DESC
        LIMIT 100";

    $stmt = $connection->prepare($sql);

    // Bind the parameters
    $stmt->bindParam(':first_date', $first_date);
    $stmt->bindParam(':second_date', $second_date);

    // Execute the query
    $stmt->execute();

    // Fetch the results
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Output or process the results as needed
    foreach ($result as $row) {
        echo "Product: " . $row['product'] . ", Count on " . $first_date . ": " . $row['count_date1'] . ", Count on " . $second_date . ": " . $row['count_date2'] . ", Difference: " . $row['diff'] . "<br/>";
    }
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search