[Solved] Multiple column subselect in mysql 5 (5.1.42)
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Multiple column subselect in mysql 5 (5.1.42)

Database type:

This one seems to be a simple problem, but I can't make it work in a single select or nested select. Retrieve the authors and (if any) advisers of a paper (article) into one row.

I order to explain the problem, here are the two data tables (pseudo)

papers (id, title, c_year)
persons (id, firstname, lastname)

plus a link table w/one extra attribute (pseudo):

paper_person_roles(
  paper_id
  person_id
  act_role ENUM ('AUTHOR', 'ADVISER')
)

This is basically a list of written papers (table: papers) and a list of staff and/or students (table: persons)

An article my have (1,N) authors.
An article may have (0,N) advisers.
A person can be in 'AUTHOR' or 'ADVISER' role (but not at the same time).

The application eventually puts out table rows containing the following entries:

TH: || Paper_ID  |  Author(s)          |   Title                 |   Adviser(s)  |
TD: ||   21334   |John Doe, Jeff Tucker|Why the moon looks yellow|Brown, Rayleigh|
...

My first approach was like:
select/extract a full list of articles into the application, eg.

SELECT 
   q.id, q.title
FROM 
   papers AS q
ORDER BY 
   q.c_year
and save the results of the query into an array (in the application). After this step, loop over the array of the returned information and retrieve authors and advisers (if any), via prepared statement (? is the paper's id) from the link table like:
APPLICATION_LOOP(paper_ids in array)
  SELECT 
      p.lastname, p.firstname, r.act_role 
  FROM 
      persons AS p, paper_person_roles AS r
   WHERE 
      p.id=r.person_id AND r.paper_id = ?
   # The application does further processing from here (pseudo):
   foreach record from resulting records
     if  record.act_role eq 'AUTHOR' then join to author_column
     if  record.act_role eq 'ADVISER' then join to avdiser_column
   end
   print id, author_column, title, adviser_column
APPLICATION_LOOP
This works so far and gives the desired output. Would it make sense to put the computation back into the DB?

I'm not very proficient in nontrivial SQL and can't find a solution with a single (combined or nested) select call. I tried sth. like

SELECT
    q.title 
    (CONCAT_WS(' ',
     (SELECT p.firstname, p.lastname AS aunames
      FROM persons AS p, paper_person_roles AS r
      WHERE q.id=r.paper_id AND r.act_role='AUTHOR')
     )
    ) AS aulist
FROM 
    papers AS q, persons AS p, paper_person_roles AS r
in several variations, but no luck ...

Maybe there is some chance?

Thanks in advance

r.b.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `papers` ADD INDEX `papers_idx_c_year` (`c_year`);
The optimized query:
SELECT
        q.id,
        q.title 
    FROM
        papers AS q 
    ORDER BY
        q.c_year

Related Articles



* original question posted on StackOverflow here.