Changes between Version 34 and Version 35 of QueryOptimization

Timestamp:

05/09/26 21:03:56 (2 weeks ago)

Author:

231027

Comment:

--

QueryOptimization

@@ -21 +21 @@
 }}}
 
+ 1. Без индекс
+
+ * '''SELECT'''
+
+{{{
+
+EXPLAIN ANALYZE
+    SELECT * FROM "Performer_Events" WHERE performer_id = 10;
+
+}}}
+
+ * '''INSERT'''
+
+{{{
+
+EXPLAIN ANALYZE
+    INSERT INTO "Event_Happening_Performer" (event_happening_id, performer_id)
+    VALUES (1, 10);
+
+}}}
+
+ * '''UPDATE'''
+
+{{{
+
+EXPLAIN ANALYZE
+    UPDATE "Event_Happening_Performer"
+    SET performer_id = 11
+    WHERE event_happening_id = 1 AND performer_id = 10;
+
+}}}
+
+Погледот е бавен поради комплексни врски помеѓи четири табели што резултира со време од 3.2s. Индексите на performer_id и event_id го елиминираат целосното скенирање на табелите и овозможуваат инстантно поврзување на изведувачите со нивните настани.
+
 ==== Оптимизација:
 
@@ -31 +65 @@
 -- index for optimizing event lookups within scheduled event happenings
 CREATE INDEX idx_event_happening_event_id ON "Event_Happening"(event_id);
+
+}}}
+
+ 2. Со индекс
+
+ * '''SELECT'''
+
+{{{
+
+EXPLAIN ANALYZE
+    SELECT * FROM "Performer_Events" WHERE performer_id = 10;
+
+}}}
+
+ * '''INSERT'''
+
+{{{
+
+EXPLAIN ANALYZE
+    INSERT INTO "Event_Happening_Performer" (event_happening_id, performer_id)
+    VALUES (2, 10);
+
+}}}
+
+ * '''UPDATE'''
+
+{{{
+
+EXPLAIN ANALYZE
+    UPDATE "Event_Happening_Performer"
+    SET performer_id = 12
+    WHERE event_happening_id = 2 AND performer_id = 10;
 
 }}}