title: MySQL search for user and their roles
body: <p>I am re-writing the SQL which lets a user search for any other user on our site and also shows their roles.</p>
  
  <p>An an example, roles can be "Writer", "Editor", "Publisher".</p>
  
  <p>Each role links a User to a Publication.</p>
  
  <p>Users can take multiple roles within multiple publications.</p>
  
  <p>Example table setup:</p>
  
  <pre><code>"users" : user_id, firstname, lastname
  "publications" : publication_id, name  
  "link_writers" : user_id, publication_id  
  "link_editors" : user_id, publication_id  
  </code></pre>
  
  <p>Current psuedo SQL:</p>
  
  <pre><code>SELECT * FROM (
    (SELECT user_id FROM users WHERE firstname LIKE '%Jenkz%') 
    UNION 
    (SELECT user_id FROM users WHERE lastname LIKE '%Jenkz%')
  ) AS dt
  JOIN (ROLES STATEMENT) AS roles ON roles.user_id = dt.user_id
  </code></pre>
  
  <p>At the moment my roles statement is:</p>
  
  <pre><code>SELECT  dt2.user_id, dt2.publication_id, dt.role FROM (
    (SELECT 'writer' AS role, link_writers.user_id, link_writers.publication_id
    FROM link_writers)
    UNION
    (SELECT 'editor' AS role, link_editors.user_id, link_editors.publication_id
    FROM link_editors)
  ) AS dt2
  </code></pre>
  
  <p>The reason for wrapping the roles statement in UNION clauses is that some roles are more complex and require a table join to find the publication_id and user_id.</p>
  
  <p>As an example "publishers" might be linked accross two tables</p>
  
  <pre><code>"link_publishers": user_id, publisher_group_id
  "link_publisher_groups": publisher_group_id, publication_id
  </code></pre>
  
  <p>So in that instance, the query forming part of my UNION would be:</p>
  
  <pre><code>SELECT 'publisher' AS role, link_publishers.user_id, link_publisher_groups.publication_id
  FROM link_publishers
  JOIN link_publisher_groups ON lpg.group_id = lp.group_id
  </code></pre>
  
  <p>I'm pretty confident that my table setup is good (I was warned off the one-table-for-all system when researching the layout). My problem is that there are now 100,000 rows in the users table and upto 70,000 rows in each of the link tables.</p>
  
  <p>Initial lookup in the users table is fast, but the joining really slows things down.</p>
  
  <p>How can I only join on the relevant roles?</p>
  
  <p>-------------------------- EDIT ----------------------------------
  <a href="https://i.stack.imgur.com/ODhY0.gif" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ODhY0.gif" alt="explain"></a>
  </p>
  
  <p>Explain above (open in a new window to see full resolution).</p>
  
  <p>The bottom bit in red, is the "WHERE firstname LIKE '%Jenkz%'" the third row searches WHERE CONCAT(firstname, ' ', lastname) LIKE '%Jenkz%'. Hence the large row count, but I think this is unavoidable, unless there is a way to put an index accross concatenated fields?</p>
  
  <p>The green bit at the top just shows the total rows scanned from the ROLES STATEMENT.</p>
  
  <p>You can then see each individual UNION clause (#6 - #12) which all show a large number of rows. Some of the indexes are normal, some are unique.</p>
  
  <p>It seems that MySQL isn't optimizing to use the dt.user_id as a comparison for the internal of the UNION statements. Is there any way to force this behaviour?</p>
  
  <p>Please note that my real setup is not publications and writers but "webmasters", "players", "teams" etc.</p>