Re: No response to SELECT with IN clause
Posted by:
Rick James
Date: April 22, 2014 12:01AM
Yeah, do what Peter says. Other comments...
KEY `Surname` (`Surname`), KEY `GivenName` (`GivenName`)
-->
KEY(Surname, GivenName)
> PRIMARY KEY (`ID`),
> UNIQUE KEY `IDIR` (`IDIR`),
Both are INTs, so why not get rid of ID and simply have
PRIMARY KEY(IDIR)
I see this pattern recurring 7 times; it is probably better done as another table:
`BirthD` varchar(100) DEFAULT NULL,
`BirthSD` int(10) DEFAULT NULL,
`IDLRBirth` int(10) DEFAULT NULL,
`IDARBirth` int(10) DEFAULT NULL,
`BirthNote` longtext,
Furthermore, it looks like you start with 4 of them, then gradually added the other 3. If they had been in another table, it would have been much less work.
The other table would have these fields:
IDIR, type, D, SD, IDLR, IDAR, Note
where type is ENUM('Birth', 'Death', ...)
Also the Tags could either be a separate table, or a bit string or a SET.
Virtually all the fields are NULLable; is that realistic?
> LIMIT 20 OFFSET 0
After getting rid of the OR, this "compound" index may help:
INDEX(IDType, IDLREvent, IDIR)
What's the size (MB) of the tables?
What's the value of key_buffer_size?
That smells like "pagination via limit and offset". There are better ways to do such; let's discuss that after you do the other cleanups.