slow complex query

[Tanja]

First happy new year to all

In database i have tables dog, owner and puppy. In dog are rows for breeder_id and owner_id - that is id in the owner table. In puppy there is row for id in owner.

I want to get all active breeders - that can be owners which have an bitch (max. 8years old) with breeding licence and / or have a litter last 8 years). People (owner) are able to have a bitch excepting puppies ...

My select runs only for one country (here germany) over 8 seconds - thats too long - and i need it for all countries in database .... The result is right ...

The select is in a function, i will post all here for better understanding.

function countrybreeder()
{
include(INCLUDE_DIR.'connecting_to_database.php');
$activebreeder = "
SELECT  
d.id AS dogid, d.breeder_id, d.date_of_birth, d.gender, d.owner_id, d.breeder_id, d.breeding_approval_since,
h.id AS dogid, h.breeder_id, h.date_of_birth, h.gender, h.owner_id, h.breeder_id, h.breeding_approval_since,

MAX(YEAR(h.date_of_birth)) AS lastlitter,
owner.id, owner.kennelname, owner.country, owner.country_short, owner.kennel_note,
puppy.breeder_id,
MAX(puppy.sollgeboren) AS birthday, 
DATE_ADD(MAX(puppy.sollgeboren), INTERVAL 84 DAY) AS database_dateadd,
DATE_SUB(MAX(puppy.sollgeboren), INTERVAL 60 DAY) AS database_datesub,
puppy.active AS showing
FROM owner
LEFT JOIN dog d ON  d.owner_id = owner.id
LEFT JOIN dog h ON  h.breeder_id = owner.id
LEFT JOIN puppy ON (owner.id = puppy.breeder_id AND puppy.active='1')


WHERE  
(
d.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR)
AND d.breeding_approval_since !='0000'
AND owner.kennelname !=''
AND owner.kennel_note =''
AND owner.country_short='de'
)
or
( 
h.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR)
AND owner.kennelname !=''
AND owner.kennel_note =''
AND h.breeder_id = owner.id
AND owner.country_short='de'
) 


GROUP BY country_short, kennelname

ORDER BY kennelname ASC
";

$stmt = $conn->prepare($activebreeder);
$stmt->execute();

$grouped = array();

while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
         $grouped[$row->country_short][] = $row;
}
return $grouped;

}

$countrybreeder = countrybreeder();

foreach ($countrybreeder as $country_short => $entries)
{


echo '<h3> '. $laender[$lang][''.$country_short.''] . ' ('.count($entries).')
</h3>';
echo '<div>';
echo '<p><a href="../map/breeder/map_of_'.$country_short.'">   <img src ="/breed/maps.png" alt="map" /></a></p>';		



foreach ($entries as $entry) {

echo '<p>';
echo '<a href="../person/'.$entry->id.'">' . $entry->kennelname. '</a>';
if (!empty($entry->kennel_note)) 
	{echo ' '.$entry->kennel_note;}
if ($entry->database_datesub <= $date && $entry->database_dateadd >= $date) {echo ' <span class="gradient">♥ ♥</span>';}
echo '</p>'; 
 
 

}
echo '</div>';
}	

Maybe it is the wrong way - what can i do to make it better and faster?

[Barand]

Your WHERE clause contains conditions on tables that are joined with a LEFT JOIN. This will make the query behave as though you were using INNER JOINS. If the query is functioning as you want (albeit slowly) try changing to INNER JOINS instead of LEFT JOINS, they are faster.

 

If not there already, you could also try putting indexes on date of birth and country.

[Tanja]

I changed all variants of join, sometimes with horrible results ....

Putting an index on rows - but query runs too long ...

Maybe (don´t now if possible) it will be better to make two queries, put each result in an array and merge ....

Good idea or bad?

[Psycho]

I agree with Barand regarding the LEFT JOINs. Unless you need records from the owner table that have no associated records from the other tables, just a normal JOIN will work.

 

Looking a little closer, I'm not sure you need these TWO joins with the  additional WHERE conditions

LEFT JOIN dog d ON d.owner_id = owner.id
LEFT JOIN dog h ON h.breeder_id = owner.id

WHERE
(
  d.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR)
  AND d.breeding_approval_since !='0000'
  AND owner.kennelname !=''
  AND owner.kennel_note =''
  AND owner.country_short='de'
)
or
(
  h.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR)
  AND owner.kennelname !=''
  AND owner.kennel_note =''
  AND h.breeder_id = owner.id
  AND owner.country_short='de'
)

Instead you could do just one JOIN on that table using the two join conditions. Then, looking at the two sets of WHERE conditions there are only some minor differences

 

1. AND d.breeding_approval_since !='0000'

So, you only care about the approval_since IF the record is joined on the owner_id

 

2. AND h.breeder_id = owner.id

That is a duplicate of the condition used to perform the JOIN and is superfluous.

 

This should work a little better (not tested)

SELECT
    o.id, o.kennelname, o.country, o.country_short, o.kennel_note,
    d.id AS dogid, d.breeder_id, d.date_of_birth, d.gender, d.owner_id, d.breeder_id, d.breeding_approval_since,
    MAX(YEAR(d.date_of_birth)) AS lastlitter,
 
    p.breeder_id,
    MAX(p.sollgeboren) AS birthday, 
    DATE_ADD(MAX(p.sollgeboren), INTERVAL 84 DAY) AS database_dateadd,
    DATE_SUB(MAX(p.sollgeboren), INTERVAL 60 DAY) AS database_datesub,
    p.active AS showing
 
FROM owner o
LEFT JOIN dog d
    ON d.owner_id = o.id OR
       (d.breeder_id = o.id AND d.breeding_approval_since !='0000')
 
LEFT JOIN puppy ON (o.id = p.breeder_id AND p.active='1')
 
 
WHERE d.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR)
  AND o.kennelname !=''
  AND o.kennel_note =''
  AND o.country_short='de'
 
GROUP BY o.country_short, o.kennelname
 
ORDER BY o.kennelname ASC

But, I'm not sure why you have the join from the dog table and the puppy table. If there are three records in the dog table to join on an owner and three records in the puppy table to join on the same owner record - you will end up with a total of nine records returned for that owner: 3 x 3 = 9. All three puppy records are joined on all three dog records. I can't imagine that this is correct since it would return a lot of duplicative data. Without knowing what you are really needing, it's hard to give any advise. Perhaps you need to do a subquery of the dog and puppy tables using a UNION and then joining that result on the owner table.

Edited January 12, 2017 by Psycho

[Tanja]

Active Breeders are all owners which have a.) a bitch with breedeing licence and under 8 years AND b.) which have a litter last 8 years  (f.e. my own bitch is 10, my last litter 2014). So that calling only owner will not show my kennel, but the breeder join.
The puppy table ist joined for actual puppies (if "sollgeboren" (=planned birthday) there is shown an icon x days before / after pl. birth to see directly in which kennel there are puppies. Sometimes breeders add a planned litter and bitch is empty -> active will go to zero....
Country_short is only for "de" for testing speed - later it must be for all countrys.

The different Join condition i must test after work (starts at 4 am)....

[Psycho]

OK, I'm not completely following your description, so let me ask a few questions:

 

1. First, provide a layman's description of what you want to report on. Your last response has some of that, but not all of it.: I think you want something along the lines of "I want a list of owners that meet the following conditions along with any associated puppies that meet these conditions". 

 

2. I still see a problem with how the dog and puppy tables are being used. If there are multiple corresponding records in the dog table and the puppy table, you will get duplicate data. E.g. if on owner has 3 matching records in the dog table and 5 matching records in the puppy table - there will be 15 records (3 x 5) returned in the query. I'm curious why the puppies are not joined on the dogs. If there is a relations ship between dogs and puppies - it would make more sense with what you are doing currently. If not (puppies and dogs are separate entities) then, as I stated before, you probably want to do a sub-query using those two tables with  a UNION

 

3. The original query was getting a lot of duplicate data (e.g. the owner id was getting returned in four(!) different fields - one for each table. It will be the same value in all four fields, so just pull the one from the owner table. When dealing with complex queries, I suggest starting with a query from just the first table to get that data. Then add additional tables one at a time with appropriate conditions/filters with the additional data from those tables that you need. Validate the results at each step before going on.

[Tanja]

There are three tables:
dog with birthday, gender, breeder id, owner id, breeding licence

owner with id, kennelname, kennel_note, and country (short) -> every owner can be also a breeder, if I breed a dog and you buy it there are two rows in owner
puppy with planned_birthday, breeder id and active (here can breeders insert a planned litter - also these ones which will have their first litter), this table is also used for showing planned litters in breed

I want to show
all owners which have a female dog under 8 years with breeding licence (dog.owner_id - owner.id)

all owners which have a kennelname without note and have a litter last 8 years (dog.breeder_id - owner.id)
-> there could be double names (i have a bitch with breeding licence under 8 years and my last litter was 2015)
and which of them planned a litter at all?

This query will show all, but without breeders with litters that hasn´t have a own bitch (i can rent your bitch if you don´t want to have a own kennel)

SELECT 

owner.id, 
owner.kennelname, owner.country, owner.country_short, owner.kennel_note,
dog.id AS dogid, dog.breeder_id, dog.date_of_birth, dog.gender, dog.owner_id,
puppy.breeder_id,

MAX(puppy.sollgeboren) AS birthday, 
DATE_ADD(MAX(puppy.sollgeboren), INTERVAL 84 DAY) AS database_dateadd,
DATE_SUB(MAX(puppy.sollgeboren), INTERVAL 60 DAY) AS database_datesub,
puppy.active AS showing
FROM owner
INNER JOIN dog ON dog.owner_id = owner.id 
LEFT JOIN puppy ON (dog.owner_id = puppy.breeder_id AND puppy.active='1')


WHERE  dog.gender='female' 
AND dog.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR)
AND owner.kennelname !=''
AND owner.kennel_note =''
AND dog.owner_id = owner.id


GROUP BY owner.country_short, kennelname

ORDER BY country_short  DESC

Testing with your Join this query runs much faster (1.53 seconds), but a.) not fast enough and b.) sending all breeders (also these one which never has a litter and an old bitch)

SELECT 

o.id, o.kennelname, o.country, o.country_short, o.kennel_note,
d.id AS dogid, d.breeder_id, d.date_of_birth, d.gender, d.owner_id, MAX(YEAR(d.date_of_birth)) AS lastlitter,
p.breeder_id,

MAX(p.sollgeboren) AS birthday, 
DATE_ADD(MAX(p.sollgeboren), INTERVAL 84 DAY) AS database_dateadd,
DATE_SUB(MAX(p.sollgeboren), INTERVAL 60 DAY) AS database_datesub,
p.active AS showing
FROM owner o


LEFT JOIN dog d
    ON (d.owner_id = o.id AND d.gender='female' AND d.breeding_approval_since !='0000' AND d.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR))
		OR
       (d.breeder_id = o.id AND  d.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR))



LEFT JOIN puppy p ON (o.id = p.breeder_id AND p.active='1' )

WHERE  o.kennelname !=''
AND o.kennel_note =''
AND o.country_short = 'de'


GROUP BY o.country_short, o.kennelname

ORDER BY o. country_short  DESC

Edited January 12, 2017 by Tanja

[benanamen]

How about posting an sql dump of your DB so we can work with it.

[Barand]

@benanamen: I have already requested that from her (via PM) but no response. Apparently she'd rather have us guessing.

[Psycho]

I think your current schema has problems. For example

 

every owner can be also a breeder, if I breed a dog and you buy it there are two rows in owner

 

If a "person" can have multiple "entity" types you should not create multiple records like that. Instead you should have one record for each "person" and then have separate tables for the data associated with those different entities. For example, you could have a person table (or whatever you want to name it) with an id, name, etc. that applies to all owners and breeders. Then, if you need additional data for people that are breeders, have a breeder table with the personID, kennel name, etc. I assume you can determine owners based on the fact that there are dogs and/or puppies associated.

 

 Also, you didn't really answer my questions. Based on these statements

 

 

I want to show

all owners which have a female dog under 8 years with breeding licence (dog.owner_id - owner.id)

all owners which have a kennelname without note and have a litter last 8 years (dog.breeder_id - owner.id)
-> there could be double names (i have a bitch with breeding licence under 8 years and my last litter was 2015)

and which of them planned a litter at all?

 

There is no reason why you are selecting data from the JOINed tables. I specifically asked that because your current design will necessarily return duplicative records which would make using the data difficult. When you retrieve the list of owners that meet the two conditions above, what additional data do you need from the associated tables?

[Tanja]

Oh sorry - i don´t saw the messages until now ...

Dog has 27000k rows....

[Tanja]

The table owner is for persons ...
f.e.

one person is stored with name, adress - the other also with kennelname

 

every owner can be also a breeder, if I breed a dog and you buy it there are two rows in owner

so in owner will be

id 1 Name Tanja    Kennel doggie  country_short de

id 2 Name Psycho Kennel (empty) country_short ca

and in dog it will be owner_id 2 (you) and breeder_id 1 (me)


in dog i need birthday (to get last litter or age of bitch)
in puppy i call actual matings

[benanamen]

@benanamen: I have already requested that from her (via PM) but no response. Apparently she'd rather have us guessing.

Yeah, so did I.