One Query Pulling up Too Much / One Query Too Slow

[PCHomepage]

This may just take another pair of eyes to spot what I've missed. This query, which is trying to find some tenuous matches between tables is working but it is pulling up values that I am not selecting. Rather than just find matches with C4DA, it is also getting matches with C4GA. The query itself is built dynamically from a series of select boxes so there may be times when C4GA is also selected (ie.: kv.chipname IN ('C4DA','C4GA')) but it should not show unless it has been chosen! Can anyone spot what I've done wrong?

SELECT u.FileName, u.ID, u.UploadDate, u.UpdateDate, u.EntryType FROM dcs_keysvals kv, dcs_uploads u 
WHERE u.Segment1 
IN (SELECT DISTINCT chipname FROM dcs_keysvals WHERE chipname IS NOT NULL AND kv.chipname IN ('C4DA') ) 
AND (u.Segment3 IN (SELECT DISTINCT lot FROM dcs_keysvals WHERE lot IS NOT NULL AND kv.chipname IN ('C4DA') ) 
OR u.Segment2 
IN (SELECT DISTINCT lot FROM dcs_keysvals WHERE lot IS NOT NULL AND kv.chipname IN ('C4DA') )) 
AND u.EntryType <> 'QE' 
GROUP BY u.FileName 
ORDER BY u.FileName

And then there is this query for the select box itself which is also working but it is extraordinarily slow, sometimes even timing out even before any selections have been made. It too is built dynamically with each succesive select box being limited by choices made and automatically submitted from the last. The red differ from select box to select box and are created dynamically:

SELECT kv.chipname 
FROM dcs_keysvals kv 
WHERE kv.chipname IN (SELECT DISTINCT Segment1 FROM dcs_uploads WHERE EntryType <> 'QE') 
AND (kv.lot IN (SELECT DISTINCT Segment3 FROM dcs_uploads WHERE EntryType = 'Bench') 
OR kv.lot IN (SELECT DISTINCT Segment2 FROM dcs_uploads WHERE EntryType = 'Probe')) 
[COLOR=red]AND kv.chipname IN ('C4DA', 'C4GA')[/color] 
GROUP BY kv.[COLOR=red]chipname[/color] 
ORDER BY kv.[COLOR=red]chipname[/color]

 

[IanWaterman]

TRy

SELECT u.FileName, u.ID, u.UploadDate, u.UpdateDate, u.EntryType
FROM dcs_uploads u
inner join on (SELECT DISTINCT lot FROM dcs_keysvals WHERE lot IS NOT NULL AND kv.chipname IN ('C4DA') ) kv
on u.Segment1 = kv.lot
Where u.Segment1 = kv.lot
GROUP BY u.FileName
union
SELECT u.FileName, u.ID, u.UploadDate, u.UpdateDate, u.EntryType
FROM dcs_uploads u
inner join on (SELECT DISTINCT lot FROM dcs_keysvals WHERE lot IS NOT NULL AND kv.chipname IN ('C4DA') ))
AND u.EntryType <> 'QE') kv2
on u.Segment2 = kv2.lot
GROUP BY u.FileName
ORDER BY u.FileName

Your group is not very good, I know MYSQL allows this but you should really either group by the other fieldsu.ID, u.UploadDate, u.UpdateDate, u.EntryType too or find max/min of these fields to meet true ANSI syntax.

Ian

 

[PCHomepage]

Thank you. I had tried something like that originally but MySQL protested and it does the same with yours although it seems it should work:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON (SELECT DISTINCT lot FROM dcs_keysvals WHERE lot IS NOT NULL AND kv.chipname ' at line 3

It is really values from dcs_keysvals that I need, not values from dcs_uploads. Having said that, that is why I started my query on the table from which I wanted the values as it seemed more direct and I thought it would be faster but apparently not!

Just to clarify, the queries in my posting were two separate things. They are used on the same application but in different places although they are indeed quite similar.

 

[IanWaterman]

Try

SELECT u.FileName, u.ID, u.UploadDate, u.UpdateDate, u.EntryType 
FROM dcs_uploads u 
inner join on (SELECT DISTINCT lot FROM [b]dcs_keysvals kv1[/b] WHERE lot IS NOT NULL AND kv1.chipname IN ('C4DA') ) kv 
on u.Segment1 = kv.lot
Where u.Segment1 = kv.lot
GROUP BY u.FileName, u.ID, u.UploadDate, u.UpdateDate, u.EntryType 
union
SELECT u.FileName, u.ID, u.UploadDate, u.UpdateDate, u.EntryType 
FROM dcs_uploads u 
inner join on (SELECT DISTINCT lot FROM [b]dcs_keysvals kv1[/b] WHERE lot IS NOT NULL AND kv1.chipname IN ('C4DA') )) 
AND u.EntryType <> 'QE') kv2 
on u.Segment2 = kv2.lot
GROUP BY u.FileName, u.ID, u.UploadDate, u.UpdateDate, u.EntryType

I forgot to assign dcs_keysvals as kv see bold, also changed grouping. If you do not want to group on those items try changing to MAX or MIN

Ian

 

[PCHomepage]

Thank you but unfortunately it gives the same error as before, in the same line 3. My experimenting yesterday seemed to also find fault with that line from time to time but I did begin a query that works after a fasion, although it's still slow (not as slow as it was) but it is bringing up no results when the original was - eventually.

This one was for the second in my original posting above, which is the one that is mainly slowing everything down. The first isn't slowing it down but it is giving unexpected results. This one became show when I added the third join but it was fine before that. The red bits are dynamic and built from other selections in the same form in order to limit the selections in such a way that only valid entries will ultimately be found.

SELECT DISTINCT kv.[COLOR=red]chipname[/color] 
FROM dcs_keysvals kv 
INNER JOIN dcs_uploads u1 ON u1.Segment1=kv.chipname 
INNER JOIN dcs_uploads u2 ON u2.Segment2=kv.lot  
INNER JOIN dcs_uploads u3 ON u3.Segment3=kv.lot 
WHERE u1.EntryType <> 'QE' 
AND (u2.EntryType = 'Probe' 
OR u3.EntryType = 'Bench') 
[COLOR=red]AND kv.chipname IN ('C24B', 'C4BD') 
AND (u2.Segment2 IN ('7554909') 
OR u3.Segment3 IN ('7554909'))[/color] 
ORDER BY kv.chipname

Actually, that this query works at all was a surprise to me since it makes no sense! I know SQL reasonably well but so far nothing that seems logical works!

 

[IanWaterman]

As you are using inner joins I think it Would it be faster if you changed to

SELECT DISTINCT kv.chipname
FROM dcs_keysvals kv
INNER JOIN dcs_uploads u1
ON u1.Segment1=kv.chipname
and u1.EntryType <> 'QE'
INNER JOIN dcs_uploads u2
ON u2.Segment2=kv.lot
and u2.EntryType = 'Probe'
and u2.Segment2 IN ('7554909')
INNER JOIN dcs_uploads u3
ON u3.Segment3=kv.lot
and u3.EntryType = 'Bench'
and u3.Segment3 IN ('7554909')
WHERE kv.chipname IN ('C24B', 'C4BD')
ORDER BY kv.chipname

Ian

 

[PCHomepage]

Thank you. It runs very quickly now but brings no results when there should be several and the reason is that there will never be a time when Segment2 and Segment3 have the same value so I'll have to work around how to do that with this particular structure. That is why I had them each inside their own SELECT originally but clearly that was far too slow to be of any use. Both are being submitted dynamically so will always be part of the query for both but the result will be only from one or the other column.

 

[PCHomepage]

I rearranged the query slightly so that I could put an OR between the Segment2 and Segment3 portions and it still gave no results even when I removed the Segment2 and Segment3 lines. I realized that since also EntryType will never be both Bench and Probe at the same time in the same column, that was why no results so I added an OR between them but as soon as I did that and ran it, it bogged down to almost a minute run time!

It's likely I don't quite understand the INNER JOIN type since I don't think I've ever needed to use it before. However, looking over the query as it is now, it seems that it wants to always match u2.Segment2=kv.lot and u3.Segment3=kv.lot when in actually it will match only in one or the other when the EntryType is appropriate.

SELECT DISTINCT kv.chipname 
FROM dcs_keysvals kv 
INNER JOIN dcs_uploads u1 ON u1.Segment1=kv.chipname 
INNER JOIN dcs_uploads u2 ON u2.Segment2=kv.lot 
INNER JOIN dcs_uploads u3 ON u3.Segment3=kv.lot 
WHERE kv.chipname IN ('C24B', 'C4BD')
AND [COLOR=red](u2.Segment2 IN ('7554909')
OR u3.Segment3 IN ('7554909')[/color])
AND u1.EntryType <> 'QE'
AND [COLOR=red](u2.EntryType = 'Probe' 
OR u3.EntryType = 'Bench')[/color]
ORDER BY kv.chipname

 

[IanWaterman]

Go back to the UNIONs that will be fast and overcome the link clashes

Union will eliminate duplications

Union All will bring in duplicates

SELECT DISTINCT kv.chipname
FROM dcs_keysvals kv
INNER JOIN dcs_uploads u1
ON u1.Segment1=kv.chipname
and u1.EntryType <> 'QE'
union
SELECT DISTINCT kv.chipname
FROM dcs_keysvals kv
INNER JOIN dcs_uploads u2
ON u2.Segment2=kv.lot
and u2.EntryType = 'Probe'
and u2.Segment2 IN ('7554909')
union
SELECT DISTINCT kv.chipname
FROM dcs_keysvals kv
INNER JOIN dcs_uploads u3
ON u3.Segment3=kv.lot
and u3.EntryType = 'Bench'
and u3.Segment3 IN ('7554909')
WHERE kv.chipname IN ('C24B', 'C4BD')
ORDER BY kv.chipname

Ian

 

[PCHomepage]

Thank you very much for that. It does seem fast, however, it pulls up four different kv.chipname values when it should have been limited to the two entered. Also, it gives an error on the ORDER BY that kv.chipname is an unknown column. I'm away from my office at the moment but will test further when I return shortly.

 

[IanWaterman]

Sorry my mistake

Try

SELECT DISTINCT kv.chipname
FROM dcs_keysvals kv
INNER JOIN dcs_uploads u1
ON u1.Segment1=kv.chipname
and u1.EntryType <> 'QE'
WHERE kv.chipname IN ('C24B', 'C4BD')
union
SELECT DISTINCT kv.chipname
FROM dcs_keysvals kv
INNER JOIN dcs_uploads u2
ON u2.Segment2=kv.lot
and u2.EntryType = 'Probe'
and u2.Segment2 IN ('7554909')
WHERE kv.chipname IN ('C24B', 'C4BD')
union
SELECT DISTINCT kv.chipname
FROM dcs_keysvals kv
INNER JOIN dcs_uploads u3
ON u3.Segment3=kv.lot
and u3.EntryType = 'Bench'
and u3.Segment3 IN ('7554909')
WHERE kv.chipname IN ('C24B', 'C4BD')
ORDER BY chipname

Ian

 

[PCHomepage]

Thank you. I'll try it when I get to the office. Part of the problem is in the dynamic portion of the query as the table being queried (and the IN values) changes from chipname to lot and down the line as this query is inside a function that creates a series of select boxes. Their point is to show only valid entries in each based on choices from the previous entries.

 

[PCHomepage]

Running the query by itself works perfectly now, thanks! I had to rewrite the coding for the dynamic WHERE bits and after doing so, it seems to pull the proper values for the first select box. That is, before submitting, it pulls all related values; after submitting, it shows only those selected.

However, in the second select box, it still shows everything when it should show only those where the first select box had values. For each select box choices, the next select box should show only relevant values based on the values already submitted by the previous select boxes and that portion seems to not quite be working so I must have missed something.

The code with its dynamic fields . . .

SELECT DISTINCT kv.[COLOR=red]$Field[/color] 
FROM dcs_keysvals kv 
INNER JOIN dcs_uploads u1 
ON u1.Segment1=kv.chipname 
AND u1.EntryType <> 'QE' 
[COLOR=red]$WhereChipname[/color] 

UNION SELECT DISTINCT kv.[COLOR=red]$Field[/color] 
FROM dcs_keysvals kv 
INNER JOIN dcs_uploads u2 
ON u2.Segment2=kv.lot 
AND u2.EntryType = 'Probe' 
[COLOR=red]$WhereLot2 
$WhereChipname[/color]

UNION SELECT DISTINCT kv.[COLOR=red]$Field[/color] 
FROM dcs_keysvals kv 
INNER JOIN dcs_uploads u3 
ON u3.Segment3=kv.lot 
AND u3.EntryType = 'Bench' 
[COLOR=red]$WhereLot3 
$WhereChipname[/color]
ORDER BY [COLOR=red]$Field[/color]

. . . generates this query for the second select box:

SELECT DISTINCT kv.[COLOR=#4E9A06]lot[/color] 
FROM dcs_keysvals kv 
INNER JOIN dcs_uploads u1 
ON u1.Segment1=kv.chipname 
AND u1.EntryType <> 'QE' 
[COLOR=#4E9A06]WHERE kv.chipname IN ('C24B', 'C4BD')[/color] 

UNION SELECT DISTINCT kv.[COLOR=#4E9A06]lot[/color] 
FROM dcs_keysvals kv 
INNER JOIN dcs_uploads u2 
ON u2.Segment2=kv.lot 
AND u2.EntryType = 'Probe' 
[COLOR=#4E9A06]AND u2.Segment2 IN ('6719829') 
WHERE kv.chipname IN ('C24B', 'C4BD')[/color] 

UNION SELECT DISTINCT kv.[COLOR=#4E9A06]lot[/color] 
FROM dcs_keysvals kv 
INNER JOIN dcs_uploads u3 
ON u3.Segment3=kv.lot 
AND u3.EntryType = 'Bench' 
[COLOR=#4E9A06]AND u3.Segment3 IN ('6719829') 
WHERE kv.chipname IN ('C24B', 'C4BD')[/color] 
ORDER BY [COLOR=#4E9A06]lot[/color]