When I include distinct in my query below a full table scan is occurring on GROUP_WIDGET table and my query below is very slow. When I remove distinct it is fast. Any ideas why the performance suffers when using distinct?
Tables:
COMPONENT
ID (primary key)
GROUP
ID (primary key)
WIDGET
ID (primary key)
COMPONENT_ID (foreign key to COMPONENT TABLE)
GROUP_WIDGET (join table between GROUP AND WIDGET)
ID (primary key)
GROUP_ID (foreign key to GROUP table)
WIDGET_ID (foreign key to WIDGET table)
***all foreign keys are indexed
Very Slow (30 seconds):
SELECT DISTINCT GROUP_WIDGET.ID FROM GROUP_WIDGET GW, WIDGET W WHERE W.COMPONENT_ID=12345 AND W.ID=GW.WIDGET_ID
Very Fast (1 second):
SELECT GROUP_WIDGET.ID FROM GROUP_WIDGET GW, WIDGET W WHERE W.COMPONENT_ID=12345 AND W.ID=GW.WIDGET_ID
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
CREATE INDEX group_widget_idx_widget_id ON GROUP_WIDGET (WIDGET_ID);
CREATE INDEX widget_idx_component_id_id ON WIDGET (COMPONENT_ID,ID);
SELECT
DISTINCT GROUP_WIDGET.ID
FROM
GROUP_WIDGET GW,
WIDGET W
WHERE
W.COMPONENT_ID = 12345
AND W.ID = GW.WIDGET_ID