How could I optimize the database queries or the program?
<?php include 'db.php'; // Five Most Frequently Ordered Products $query_highesttotalquantity = " SELECT id_product, SUM(anzahl) total_quantity FROM bestellungen_products GROUP BY id_product ORDER BY total_quantity DESC LIMIT 5 "; $highesttotalquantity = $conn->query($query_highesttotalquantity); if (!$highesttotalquantity) { die("Query Error (highesttotalquantity): " . $conn->error); } // Five Products with the Highest Number of Orders $query_highesnumb = " SELECT id_product, COUNT(DISTINCT id_bestellung) order_count FROM bestellungen_products GROUP BY id_product ORDER BY order_count DESC LIMIT 5 "; $highesnumb = $conn->query($query_highesnumb); if (!$highesnumb) { die("Query Error (highesnumb): " . $conn->error); } // Five Least Frequently Ordered Products $query_lowest5 = " SELECT id_product, SUM(anzahl) total_quantity FROM bestellungen_products GROUP BY id_product ORDER BY total_quantity ASC LIMIT 5 "; $lowest5 = $conn->query($query_lowest5); if (!$lowest5) { die("Query Error (lowest5): " . $conn->error); } // Order History Over the Last Four Weeks $query_last4weeks = " SELECT YEARWEEK(bestelldatum, 1) week, SUM(bp.anzahl) total_quantity FROM bestellungen b JOIN bestellungen_products bp ON b.id_bestellung = bp.id_bestellung WHERE bestelldatum >= CURDATE() - INTERVAL 4 WEEK GROUP BY week ORDER BY week DESC "; $last4weeks = $conn->query($query_last4weeks); if (!$last4weeks) { die("Query Error (last4weeks): " . $conn->error); } ?> <!DOCTYPE html> <html> <head> <title>Admin Statistics</title> <style> body { font-family: Arial, sans-serif; padding: 20px; } h1, h2 { color: #333; } .stat-section { margin-bottom: 20px; } </style> </head> <body> <h1>Admin Statistics</h1> <div class="stat-section"> <h2>Five Most Frequently Ordered Products</h2> <?php while ($product = $highesttotalquantity->fetch_assoc()): ?> <p>Product ID: <?= htmlspecialchars($product['id_product']) ?> - Total Quantity: <?= htmlspecialchars($product['total_quantity']) ?></p> <?php endwhile; ?> </div> <div class="stat-section"> <h2>Five Products with the Highest Number of Orders</h2> <?php while ($product = $highesnumb->fetch_assoc()): ?> <p>Product ID: <?= htmlspecialchars($product['id_product']) ?> - Order Count: <?= htmlspecialchars($product['order_count']) ?></p> <?php endwhile; ?> </div> <div class="stat-section"> <h2>Five Least Frequently Ordered Products</h2> <?php while ($product = $lowest5->fetch_assoc()): ?> <p>Product ID: <?= htmlspecialchars($product['id_product']) ?> - Total Quantity: <?= htmlspecialchars($product['total_quantity']) ?></p> <?php endwhile; ?> </div> <div class="stat-section"> <h2>Order History Over the Last Four Weeks</h2> <?php while ($week = $last4weeks->fetch_assoc()): ?> <p>Week: <?= htmlspecialchars($week['week']) ?> - Total Quantity Ordered: <?= htmlspecialchars($week['total_quantity']) ?></p> <?php endwhile; ?> </div> </body> </html>
(1 votes)
Loading...
You group and sort a lot in your MySQL query, which naturally means that the performance depends heavily on the set indices and partitions. I hope you have set indexes or partitions to optimize queries.
In the “Five Least Frequently Ordered Products” section, I do not understand the meaning of the current approach. The focus here is on the number of products, not orders. The problem is that this does not have a real significance about the popularity of a product. For example, if a person orders a product 10 times, the result is the same as if 10 different persons ordered the product once.
The background is that shopkeepers are often reduced in price and then bought several times by the same person, which can distort statistics. It would therefore be more sensible to count the number of orders in which a product was purchased. This gives you a more precise idea of how popular a product really is. The same goes for the top products.
Otherwise, there is a risk that your statistics – and possibly even your storage – will be severely falsified by single mass orders.
In addition, it would be helpful to ask the question
to establish which products have never been ordered at all. This allows you to take specific measures to promote or remove these products from the range.
Where exactly did it happen to your script? Is the execution time the problem?
real salate. I recommend PDO