CODEWITHSUNDEEP
mysqlsqlperformancepostgresqljoin
Dan Dart
Dan Dart

Reputation: 362

SQL query to find counts of joined table rows as summary for each row

I have a table of widgets that looks like:

id (integer)
referrer (varchar(255))
width (integer)
height (integer)

... and some others.

I also have a table of events that look like:

id (integer)
widgetid (integer)
eventtype (string)
created_at (datetime)

... and some others.

I'm looking to get a sample table of data that finds, for each widget, the details about itself and related events for certain event types (once for event types A, B and C, and once for event type A only).

I need to be using non-vendor-specific (i.e. ANSI SQL) for this, it needs to work both on PostgreSQL as well as MySQL.

I'm trying something akin to this, but it's very slow:

SELECT w.id, w.referrer, w.width, w.height, COUNT(e.widgetid), COUNT(f.widgetid)
FROM widgets w
JOIN events e on (e.widgetid = w.id AND e.eventtype = 'A')
JOIN events f on (f.widgetid = w.id AND f.eventtype IN ('A','B','C'))
GROUP BY w.id;

but it's incredibly slow (naturally). There are indexes on e.widgetid, e.eventtype and w.id.

Am I structuring this right, and how may I make this faster (indexing on the widgetid of course nonwithstanding)?

I thought of doing subqueries, but without knowing the widget ID for each row (is there a function for that?) I haven't got very far.

I'm also not entirely sure which JOIN I should be using either. I think (but correct me if I'm wrong) that a LEFT or INNER JOIN would be appropriate for this.

Cheers

Upvotes: 1

Views: 219

Answers (1)

Noam Rathaus
Noam Rathaus

Reputation: 5598

Your JOIN is slow because you don't have indexes, or you have indexes but not for the values you are JOINing with.

Add an index for id, widgetid and eventtype and I assure you it will show a substantial speed increase.

Upvotes: 1

Related Questions