MySQL Forums
Forum List  »  Performance

Re: SELECT never completes
Posted by: James Cobban
Date: July 07, 2015 02:16AM

Peter Brawley Wrote:
-------------------------------------------------------
> Left joins are expensive, indexes don't much help
> the left side. But A tbler index on
> (tblir,idtype,idet,preferred) might help, likewise
> a tblnx index on (idir,surname,order), in each
> case try it with and without the last column
> listed (they look like low-specificity columns).
> You'll have to experiment: put in an index, then
> run Explain Extended.

Thank you. Just adding an index to tblER for the two record association fields IDIR and IDType resulted in the command completing in 2.6 seconds! The combination of those two fields uniquely associates the event with a specific record in a specific table. Usually there will be less than a dozen event records associated with any other specific record in the database. The fields IDET and Preferred are part of the description of the event and in other situations are likely to be specified in the WHERE clause.

Most individuals in the database only have one tblNX record, since they have only one name. Women have one extra record for each time they were married, but there will almost never be more than a handful of such records, so the IDIR key should be enough to improve the performance of the JOIN. The other keys on that record are for use in the WHERE clause.
>
> ( (a && b ) or a ) is logically equivalent to ( a
> ), so simplify the Where clause to
> tblNX.Surname='carruthers' and tblnx.order>=0.

The two expressions you summarize as "a" are actually slightly different. The first has an equal sign and the second a greater than sign. But thanks for looking closely at the command to try and help me out.

>
> Without indexes, joins across 3 tables with 50k
> rows each require the query engine to read
> 50k*50k*50k=125 trillion rows of data! Your
> query's number is 127k*62.5k*62, nearly 500
> trillion. The query needs indexes but the engine
> can find none to use.

A very clear explanation of what is going on. Since the tables in question actually each have closer to 100,000 rows the situation is about 8 times worse than your summary. The explain with the addition of that one index on tblER displays:

> explain SELECT tblIR.*, EBirth.EventD AS EBirthD, EDeath.EventD AS EDeathD, tblNX.Surname AS indexsurname, tblNX.GivenName AS indexgivenname, tblNX.`Order` AS indextype FROM tblIR JOIN tblNX ON tblNX.IDIR=tblIR.IDIR 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) WHERE FLOOR(tblIR.BirthSD/10000)>=1800 AND FLOOR(tblIR.BirthSD/10000)<=1900 AND ((tblNX.Surname='carruthers' AND tblNX.GivenName>='john') OR tblNX.Surname>'carruthers') AND tblNX.`Order`>=0 ORDER BY tblNX.`Surname`, tblNX.`GivenName`, EBirth.`EventSD` LIMIT 50;
+----+-------------+--------+------+------------------------+------------+---------+------------------------------------+--------+----------------------------------------------+
| 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 | Using where |
| 1 | SIMPLE | EBirth | ref | RecordLink | RecordLink | 7 | jcobban_genealogy.tblNX.IDIR,const | 1 | Using where |
| 1 | SIMPLE | EDeath | ref | RecordLink | RecordLink | 7 | jcobban_genealogy.tblNX.IDIR,const | 1 | Using where |
+----+-------------+--------+------+------------------------+------------+---------+------------------------------------+--------+----------------------------------------------+
4 rows in set (0.01 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
1582
July 06, 2015 10:08PM
774
July 06, 2015 11:51PM
Re: SELECT never completes
1238
July 07, 2015 02:16AM
739
July 09, 2015 12:55AM


Sorry, you can't reply to this topic. It has been closed.

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.