MySQL Left Join, Group By, Order By, Limit = Horrible Performance

Question

I am currently developing an application that allows users to search a database of documents using various parameters and return a set of calculated results. I am creating it in PHP / MySQL, which is not my usual development platform, but it has been great so far.

The problem I am facing is that in order to return a full set of results, I have to use LEFT JOIN for each table, which completely destroys my performance. The person who designed the database said that the query I use will return the correct results, so I have to use. The query below, I am by no means a SQL guru and can use some help with this.

I thought it would be better to split the request into subqueries? Below is my current request:

    SELECT d.title, d.deposition_id, d.folio_start, d.folio_end, pl.place_id, p.surname, p.forename, p.person_type_id, pt.person_type_desc, p.age, d.manuscript_number, dt.day, dt.month, dt.year, plc.county_id, c.county_desc
 FROM deposition d 
 LEFT JOIN person AS p ON p.deposition_id = d.deposition_id 
 LEFT JOIN person_type AS pt ON p.person_type_id = pt.person_type_id 
 LEFT JOIN place_link AS pl ON pl.deposition_id = d.deposition_id 
 LEFT JOIN date AS dt ON dt.deposition_id = d.deposition_id 
 LEFT JOIN place AS plc ON pl.place_id = plc.place_id 
 LEFT JOIN county AS c ON plc.county_id = c.county_id
 WHERE 1 AND d.manuscript_number = '840' 
 GROUP BY d.deposition_id ORDER BY d.folio_start ASC
 LIMIT 0, 20

Any help or guidance would be greatly appreciated!

Deposition table:

CREATE TABLE IF NOT EXISTS `deposition` (
  `deposition_id` varchar(11) NOT NULL default '',
  `manuscript_number` int(10) NOT NULL default '0',
  `folio_start` varchar(4) NOT NULL default '0',
  `folio_end` varchar(4) default '0',
  `page` int(4) default NULL,
  `deposition_type_id` int(10) NOT NULL default '0',
  `comments` varchar(255) default '',
  `title` varchar(255) default NULL,
  PRIMARY KEY  (`deposition_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Date table

CREATE TABLE IF NOT EXISTS `date` (
  `deposition_id` varchar(11) NOT NULL default '',
  `day` int(2) default NULL,
  `month` int(2) default NULL,
  `year` int(4) default NULL,
  PRIMARY KEY  (`deposition_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Person_Type

CREATE TABLE IF NOT EXISTS `person_type` (
  `person_type_id` int(10) NOT NULL auto_increment,
  `person_type_desc` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`person_type_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=59 ;

Answer 1

. , , , , , , . . MySQL phpMyAdmin .

ALTER TABLE ADD INDEX (deposit_id, manuscript_number);

, , . . , "LEFT JOIN person AS p ON p.deposition_id = d.deposition_id", , .

ALTER TABLE ADD INDEX (_);

Answer 2

, , .. .

, , , .

:

SELECT  d.title, d.deposition_id, d.folio_start, d.folio_end, pl.place_id, p.surname, p.forename, p.person_type_id, pt.person_type_desc, p.age, d.manuscript_number, dt.day, dt.month, dt.year, plc.county_id, c.county_desc
FROM    deposition d
LEFT JOIN
        person p
ON      p.id = 
        (
        SELECT  id
        FROM    person pi
        WHERE   pi.deposition_id = d.deposition_id
        ORDER BY
                pi.deposition_id, pi.id
        LIMIT 1
        )
LEFT JOIN
        place_link AS pl
ON      pl.id = 
        (
        SELECT  id
        FROM    place_link AS pli
        WHERE   pli.deposition_id = d.deposition_id
        ORDER BY
                pli.deposition_id, pi.id
        LIMIT 1
        )
LEFT JOIN
        date AS dt
ON      dt.id = 
        (
        SELECT  id
        FROM    date AS dti
        WHERE   dti.deposition_id = d.deposition_id
        ORDER BY
                dti.deposition_id, pi.id
        LIMIT 1
        )
LEFT JOIN
        place AS plc
ON      plc.place_id = pl.place_id 
LEFT JOIN
        county AS c
ON      c.county_id = plc.county_id
WHERE   d.manuscript_number = '840' 
ORDER BY
        d.manuscript_number, d.folio_start
LIMIT   20

deposition (manuscript_number, folio_start),

(deposition_id, id) person, place_link date.

Answer 3

LEFT JOIN, . person person_type? deposition date? A place county?

, , , LEFT JOIN INNER JOIN.

( ):

person.deposition_id
date.deposition_id
place_link.deposition_id
place_link.place_id

The table datelooks bad design; I can't think of a reason to have a date table instead of just putting a column of type date(or datetime) in the table deposition. And date- a terrible name for a table, because it is a reserved SQL word.