Jeff Drew wrote:
> I have a query with a subquery which does not throw an error, but does
not
> return either. I've been testing the query using mysql Query Browser
and
> the poor dolphin jumps only once a minute or so ;) I use MYSQL's
> excellent error documentation heavily so if the query doesn't throw an
> error, I'm at a disadvantage. Is there an optimization or just better
> syntax to use?
>
> THE DATA
> I have a table that contains an abbreviated identifier and a full
> identifier. The real data is a bit messy so here's a sanitized example:
Please don't do that. When you don't show us the real problem, you
increase the
odds of getting the wrong solution.
> Abbreviated Column contents: TR123, RG456
> Full Identifier Column contents: TR 123 abc, RG 456 def
Do I understand correctly that your table looks something like
AbbreviatedIdentifier FullIdentifier Name Address
--------------------- -------------- ------------
---------------
TR123 TR 123 abc a name an address
RG456 RG 456 def another name another
address
with FullIdentifier as the primary key? If so, that's a poor table
design,
which is almost certainly causing your problems. See below.
> THE QUERY
> My intent is to:
> 1. select some of the Abbreviated Column and convert that to a selection
> for the Full Identifier Column by:
> - extracting the first 2 characters
> - inserting a space
> - selecting the last 3 characters
> - appending "%" so I can match any of the last 3 characters in
the
> Full Identifier
...in the subquery. The % character is the wildcard for LIKE
matches. There is
no wildcard for IN. IN is followed by a list of values, one of which must
match
exactly for the row to be selected, so this won't work as intended.
> 2. select rows from Full Identifier Column based on #1
That's very convoluted. How do you expect this to help?
I'm guessing that FullIdentifier is your primary key. Because it's a
long,
messy string, you are finding it slow to use it to select rows,
particularly
when you need to find rows with a particular substring buried in the
key. You
hoped that a shorter string might match more quickly, enabling you to
narrow
down the number of rows where the full id has to be examined. That will
never
work as you've described it. The subquery might run faster, but then you
must
do the full id comparison anyway to find rows which match the subquery
list
(FullIdentifier IN ...). You've actually added overhead. This *might*
work in
some situations if you had an integer primary key to use to do the
matching
between inner and outer query, but there would probably still be a better
way.
In any case, the reality is that MySQL doesn't optimize subqueries all
that
well, so they are seldom the best way to speed up a query. It may be the
case
that your subquery is being treated as DEPENDENT, in which case it is
being run
once for each row found in the outer query. That is, once for every row
in your
table. Since your query never seems to return, I'd bet that's the case
here.
> PROBLEMS
> I think I have two problems:
> 1. "in... %" syntax is not present in any examples I've seen. They are
> all "like... %" so "in" may not work.
Right, it won't.
> 2. Here's query that runs, but does not return:
<reformatted>
>
> SELECT name, address
> FROM testTable
> WHERE FullIdentifier
> IN ( SELECT CONCAT(SUBSTRING(AbbreviatedIdentifier,1,2), " " ,
> SUBSTRING(AbbreviatedIdentifier FROM 3) , "%" )
> FROM testTable
> WHERE name LIKE 'Daisy'));
Is this really your query? As I've already mentioned, the % won't work
for IN.
Also, neither FullIdentifier nor AbbreviatedIdentifier is mentioned in
the
selection criteria, so the problem you tried to fix with the subquery is
not
present in this query. Finally, there is no wildcard in the string that
name is
supposed to match, so there is no need for LIKE. The intent of this query
is
equivalent to
SELECT name, address
FROM testTable
WHERE name = 'Daisy';
which should be as fast as possible if there is an index on name.
> My left join attempt complained because the data is all in one table.
Is
> there a better solution than my FullIdentifier in(select... ?
>
> I am not an SQL expert so I'd appreciate any ideas on how to correct
this
> query.
When I see a string primary key that looks like "TR 123 abc", I find it is
usually a safe bet that the "TR" means something (a code, a manufacturer,
...),
the "123" means something (a production run, a part number, a plant
number, ...)
and the "abc" means something (some detail, a team code, ...). In other
words,
you've crammed the answers to 3 questions into one column. That's almost
never
a good idea.
I would (for example):
#Create an auto_inc id column and 3 separate columns for each of the id
parts
ALTER TABLE testTable
DROP PRIMARY KEY,
ADD id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST,
ADD code CHAR(2) NOT NULL AFTER id,
ADD part INTEGER UNSIGNED NOT NULL AFTER code,
ADD detail CHAR(3) NOT NULL AFTER model;
#Split the identifier into the new columns
UPDATE testTable
SET code = SUBSTRING(AbbreviatedIdentifier,1,2),
part = SUBSTRING(AbbreviatedIdentifier FROM 3),
detail = SUBSTRING(FullIdentifier FROM 8);
#Delete the no-longer-needed identifier columns
#and require the code-model-detail combination is unique
ALTER TABLE testTable
DROP AbbreviatedIdentifier,
DROP FullIdentifier,
ADD UNIQUE (code, model, detail);
Now your table looks like this:
id code model detail name ...
-- ---- ----- ------ ------------
1 TR 123 abc a name ...
2 RG 456 def another name ...
(Of course, you'll want to change "code", "model", and "detail" to column
names
that actually describe the three parts of your FullIdentifier, and you'll
probably need to change the column definitions to match your needs.)
Now you can run queries like:
SELECT CONCAT(code, " ", model, " ", detail) AS FullIdentifier, name,
address
FROM testTable
WHERE code = 'TR' AND model = 123
AND detail LIKE '%b%';
The multi-column index on (code, model, detail) should make this
efficient.
Of course, I've based all this on your "sanitized" query. If I'm
off-target,
include the output of "SHOW CREATE TABLE testTable" in your next post,
along
with the results of putting EXPLAIN in front of your real query.
Michael
