MySQL Forums
Forum List  »  Performance

SELECT never completes
Posted by: James Cobban
Date: July 06, 2015 10:08PM

I am trying to improve the performance of a key query on my site by trying to do more of the work in a single SELECT with appropriate JOINs rather than a SELECT followed by separate SELECTs for the information from the other tables. You can try the current implementation out at http://www.jamescobban.net/FamilyTree/legacyIndex.php. With the current implementation it takes about 35 seconds to obtain the desired information about 50 individuals from the family tree database. If you like you can add the parameter "?Debug=Y" to actually see the main database commands that are issued.

As I have tried to refactor the database to bring it into a normal form, with each piece of information present in only one table, I have moved things like the name and dates of birth and death out of the main record into the name table and event table, which requires using JOINs to be able to work on them as if they were still in the main table. So my query now looks like:

SELECT tblIR.*, EBirth.EventD AS EBirthD, EDeath.EventD AS EDeathD ,tblNX.Surname AS indexsurname, tblNX.`Order` AS indextype FROM tblIR LEFT JOIN tblER AS EBirth ON EBirth.IDIR=tblIR.IDIR AND EBirth.IDType=0 AND EBirth.IDET=3 AND EBirth.Preferred=1 LEFT JOIN tblER AS EDeath ON EDeath.IDIR=tblIR.IDIR AND EDeath.IDType=0 AND EDeath.IDET=6 AND EDeath.Preferred=1 JOIN tblNX ON tblNX.IDIR=tblIR.IDIR WHERE ((tblNX.Surname='carruthers' AND tblNX.GivenName>='john') or tblNX.Surname>'carruthers') and tblNX.`Order`>=0 ORDER BY tblNX.Surname, tblIR.`GivenName`, tblIR.`BirthSD` LIMIT 50

The contents of the WHERE clause vary depending upon which parameters are specified by the user as input to the script. I join the event table entries for birth and death so the user can limit the response by birth and death date, and the name table entries so that an individual can have multiple surnames particularly as a consequence of marriage.

The above query, which is just to get the first individuals whose name is John Carruthers or greater, never completes. I have waited up to half an hour.

Explain says:
+----+-------------+--------+------+------------------------+------+---------+------------------------------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+------------------------+------+---------+------------------------------+--------+----------------------------------------------------+
| 1 | SIMPLE | tblNX | ALL | IDIR,Surname,GivenName | NULL | NULL | NULL | 127050 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | tblIR | ref | IDIR | IDIR | 5 | jcobban_genealogy.tblNX.IDIR | 1 | NULL |
| 1 | SIMPLE | EBirth | ALL | NULL | NULL | NULL | NULL | 62449 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | EDeath | ALL | NULL | NULL | NULL | NULL | 62449 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+--------+------+------------------------+------+---------+------------------------------+--------+----------------------------------------------------+
4 rows in set (0.06 sec)

The table definitions are:

CREATE TABLE `tblIR` (
`ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`IDIR` INT(10) UNSIGNED DEFAULT NULL,
`Surname` VARCHAR(120) DEFAULT NULL,
`SoundsLike` VARCHAR(4) DEFAULT NULL,
`GivenName` VARCHAR(120) DEFAULT NULL,
....."I haven't yet deleted all of the inappropriate fields from this table"...
PRIMARY KEY (`ID`),
UNIQUE KEY `IDIR` (`IDIR`),
KEY `Surname` (`Surname`),
KEY `GivenName` (`GivenName`) )
ENGINE=InnoDB AUTO_INCREMENT=109026 DEFAULT CHARSET=utf8

CREATE TABLE `tblER` (
`IDER` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`IDIR` INT(10) UNSIGNED DEFAULT NULL,
`IDET` INT(10) UNSIGNED DEFAULT NULL,
`Order` SMALLINT(5) DEFAULT NULL,
`EventD` VARCHAR(100) DEFAULT NULL,
`EventSD` INT(10) DEFAULT NULL,
`IDLREvent` INT(10) UNSIGNED DEFAULT NULL,
`Desc` LONGTEXT,
`GEDTag` VARCHAR(30) DEFAULT NULL,
`EventExclude` TINYINT(3) UNSIGNED DEFAULT NULL COMMENT 'Don''t ever add Exclude or SrcExclude as fields. Some older files had these already.',
`IDType` TINYINT(3) UNSIGNED DEFAULT NULL,
`IDAR` INT(10) UNSIGNED DEFAULT NULL,
`Description` VARCHAR(1023) DEFAULT NULL,
`SentenceOverride` VARCHAR(255) DEFAULT NULL,
`qsTag` TINYINT(3) UNSIGNED DEFAULT NULL,
`RGExclude` TINYINT(3) UNSIGNED DEFAULT NULL,
`Kind` TINYINT(3) UNSIGNED DEFAULT '0',
`LDSTempleReady` TINYINT(3) UNSIGNED DEFAULT NULL,
`Preferred` TINYINT(3) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`IDER`) )
ENGINE=InnoDB AUTO_INCREMENT=63831 DEFAULT CHARSET=utf8

CREATE TABLE `tblNX` (
`IDNX` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`IDNR` INT(10) UNSIGNED DEFAULT NULL,
`IDIR` INT(10) UNSIGNED DEFAULT NULL,
`Order` SMALLINT(5) DEFAULT NULL,
`MarriedNameCreatedBy` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`MarriedNameMarIDID` INT(10) UNSIGNED DEFAULT NULL,
`Prefix` VARCHAR(120) DEFAULT NULL,
`Title` VARCHAR(120) DEFAULT NULL,
`Surname` VARCHAR(120) DEFAULT NULL,
`GivenName` VARCHAR(120) DEFAULT NULL,
`SoundsLike` VARCHAR(4) DEFAULT NULL,
`UserRef` VARCHAR(50) DEFAULT NULL,
`AKANote` LONGTEXT,
`PreferredAKA` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`BirthSD` INT(10) NOT NULL DEFAULT '-99999999',
`SrchTag` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
`qsTag` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`IDNX`),
KEY `IDIR` (`IDIR`),
KEY `Surname` (`Surname`),
KEY `GivenName` (`GivenName`),
KEY `SoundsLike` (`SoundsLike`) )
ENGINE=InnoDB AUTO_INCREMENT=285799 DEFAULT CHARSET=utf8

Options: ReplyQuote


Subject
Views
Written By
Posted
SELECT never completes
1687
July 06, 2015 10:08PM
832
July 06, 2015 11:51PM
1301
July 07, 2015 02:16AM
801
July 09, 2015 12:55AM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.