Repository navigation
Expand file tree
/
Copy pathExperiment 10.sql
More file actions
108 lines (93 loc) · 2.8 KB
/
Copy pathExperiment 10.sql
File metadata and controls
108 lines (93 loc) · 2.8 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
CREATE TABLE QueryPerformance (
RecordID INT PRIMARY KEY,
CustomerCity VARCHAR(50),
OrderStatus VARCHAR(30),
ProductCategory VARCHAR(50),
OrderDate DATE,
Amount DECIMAL(10,2)
);
INSERT INTO QueryPerformance
(RecordID, CustomerCity, OrderStatus, ProductCategory, OrderDate, Amount)
SELECT
n,
CASE
WHEN n % 4 = 0 THEN 'Lucknow'
WHEN n % 4 = 1 THEN 'Kanpur'
WHEN n % 4 = 2 THEN 'Delhi'
ELSE 'Agra'
END,
CASE
WHEN n % 3 = 0 THEN 'Delivered'
WHEN n % 3 = 1 THEN 'Pending'
ELSE 'Cancelled'
END,
CASE
WHEN n % 3 = 0 THEN 'Electronics'
WHEN n % 3 = 1 THEN 'Clothing'
ELSE 'Furniture'
END,
DATE_ADD('2025-01-01', INTERVAL (n % 365) DAY),
500 + (n % 2000)
FROM (
SELECT
a.n + b.n * 10 + c.n * 100 + d.n * 1000 + 1 AS n
FROM
(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
CROSS JOIN
(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
CROSS JOIN
(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
CROSS JOIN
(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d
) AS numbers
WHERE n <= 10000;
SELECT COUNT(*) AS total_records
FROM QueryPerformance;
EXPLAIN
SELECT *
FROM QueryPerformance
WHERE CustomerCity = 'Lucknow';
EXPLAIN
SELECT *
FROM QueryPerformance
WHERE OrderStatus = 'Delivered';
EXPLAIN
SELECT *
FROM QueryPerformance
WHERE ProductCategory = 'Electronics'
AND OrderDate BETWEEN '2025-03-01' AND '2025-06-30';
EXPLAIN
SELECT CustomerCity, COUNT(*)
FROM QueryPerformance
GROUP BY CustomerCity;
EXPLAIN
SELECT *
FROM QueryPerformance
WHERE ProductCategory LIKE '%Electronics%';
CREATE INDEX idx_city
ON QueryPerformance(CustomerCity);
CREATE INDEX idx_status
ON QueryPerformance(OrderStatus);
CREATE INDEX idx_category_date
ON QueryPerformance(ProductCategory, OrderDate);
EXPLAIN
SELECT *
FROM QueryPerformance
WHERE CustomerCity = 'Lucknow';
EXPLAIN
SELECT *
FROM QueryPerformance
WHERE OrderStatus = 'Delivered';
EXPLAIN
SELECT *
FROM QueryPerformance
WHERE ProductCategory = 'Electronics'
AND OrderDate BETWEEN '2025-03-01' AND '2025-06-30';
EXPLAIN
SELECT CustomerCity, COUNT(*)
FROM QueryPerformance
GROUP BY CustomerCity;
EXPLAIN
SELECT *
FROM QueryPerformance
WHERE ProductCategory = 'Electronics';