CODEWITHSUNDEEP
mysqlsqlperformancejoin
Tarek
Tarek

Reputation: 3798

Searching on multi (1-n) relation tables

I learned the hard way that i shouldn't store serialized data in a table when i need to make it searchable .

So i made 3 tables the base & two 1-n relation tables .

enter image description here

So here is the query i get if i want to select a specific activity . 

SELECT
jdc_organizations_activities.id
FROM
jdc_activity_sector ,
jdc_activity_type
INNER JOIN jdc_organizations_activities ON jdc_activity_type.activityId = jdc_organizations_activities.id 
AND 
jdc_activity_sector.activityId = jdc_organizations_activities.id
WHERE
jdc_activity_sector.activitySector = 5 AND
jdc_activity_type.activityType = 3

Questions :

1- What kind of indexes can i add on a 1-n relation table , i already have a unique combination of (activityId - activitySector) & (activityId - activityType)

2- Is there a better way to write the query to have a better performance ?

Thank you !

Upvotes: 1

Views: 261

Answers (2)

MatBailie
MatBailie

Reputation: 86716

I would re-organise the query to avoid the cross product caused by using , notation.

Also, you are effectively only using the sector and type tables as filters. So put activity table first, and then join on your other tables.

Some may suggest that; the first join should ideally be the join which is most likely to restrict your results the most, leaving the minimal amount of work to do in the second join. In reality, the sql engine can actually re-arrange your query when generateing a plan, but it does help to think this way to help you think about the efforts the sql engine are having to go to.

Finally, there are the indexes on each table. I would actually suggest reversing the Indexes...
- ActivitySector THEN ActivityId
- ActivityType THEN ActivityId

This is specifically because the sql engine is manipulating your query. It can take the WHERE clause and say "only include records from the Sector table where ActivitySector = 5", and similarly for the Type table. By having the Sector and Type identifies FIRST in the index, this filtering of the tables can be done much faster, and then the joins will have much less work to do.

SELECT
  [activity].id
FROM
  jdc_organizations_activities    AS [activity]
INNER JOIN
  jdc_activity_sector             AS [sector]
    ON [activity].id = [sector].activityId
INNER JOIN
  jdc_activity_type               AS [type]
    ON [activity].id = [type].activityId
WHERE
  [sector].activitySector = 5
  AND [type].activityType = 3

Or, because you don't actually use the content of the Activity table...

SELECT
  [sector].activityId
FROM
  jdc_activity_sector             AS [sector]
INNER JOIN
  jdc_activity_type               AS [type]
    ON [sector].activityId = [type].activityId
WHERE
  [sector].activitySector = 5
  AND [type].activityType = 3

Or...

SELECT
  [activity].id
FROM
  jdc_organizations_activities    AS [activity]
WHERE
  EXISTS (SELECT * FROM jdc_activity_sector WHERE activityId = [activity].id AND activitySector = 5)
  AND EXISTS (SELECT * FROM jdc_activity_type WHERE activityId = [activity].id AND activityType = 3)

Upvotes: 3

Andomar
Andomar

Reputation: 238086

I would advise against mixing old style from table1, table2 and new style from table1 inner join table2 ... in a single query. And you can alias tables using table1 as t1, shortening long table names to an easy to remember mnenomic:

select    a.id
from      jdc_organizations_activities a
join      jdc_activity_sector as
on        as.activityId = a.Id
join      jdc_activity_type as at
on        at.activityId = a.Id
where     as.activitySector = 5
          and at.activityType = 3

Or even more readable using IN:

select    a.id
from      jdc_organizations_activities a
where     a.id in 
          (
          select  activityId 
          from    jdc_activity_sector 
          where   activitySector = 5
          )
          and a.id in 
          (
          select  activityId 
          from    jdc_activity_type 
          where   activityType = 3
          )

Upvotes: 1

Related Questions