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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
