subquery performance
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: Abbreviated Column contents: TR123, RG456 Full Identifier Column contents: TR 123 abc, RG 456 def 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 2. select rows from Full Identifier Column based on #1 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. 2. Here's query that runs, but does not return: select name, address from testTable where FullIdentifier in ( select concat ( substring ( AbbreviatedIdentifier,1,2) , , substring(AbbreviatedIdentifier from 3) , % ) from testTable where name like 'Daisy')); 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. Thanks
Re: subquery performance
Thanks for the detailed feedback. I do not create the data so I can't modify it. Your illustration of the table is correct. I'll try to implement some of your feedback. Thanks On 9/25/06, Michael Stassen [EMAIL PROTECTED] wrote: 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
stuck on localhost authentication
I'm apologize for posting an apparently simple question, but I'm stuck. I'm
trying to get my java application to connect to mysql on the same server.
The server is redhat with mysql 5.1.11. I'd done this with mysql 4.x in the
distant past.
I add a user with the command:
insert into user (user,host) values ('jeff','localhost');
flush privileges;
grant all on databaseName.* to jeff;
select user,host from user; returns
jeff localhost
In Java, I use:
DriverManager.getConnection( /localhost/databaseName , 'jeff',null );
when the application tries to connect, DriverManager.getConnection() gets a
bad handshake error.
So I set the password in mysql with:
set password for jeff = password('xyz');
now select user,host, password from user; returns 2 rows
jeff localhost
jeff % *4232432323
I think this is the problem - the following getConnection() method is
directed to the 2nd entry because it has a password, but it's not localhost
so my localhost-based Java app is denied.
In Java, I use:
DriverManager.getConnection( /localhost/tm , 'jeff','xyz' );
Then I get an authentication failed error.
I've also tried:
create user 'jeff'; but that created a % host entry,
not localhost
What should the user table look like for a localhost user? Which commands
get me there?
Thanks
sporadic batch update problem
Sporadically, the last few entries of a batch are not written. I'm writing
to a mysql database using JDBC. Here's a short version of my code. Does
anyone have suggestions on possible causes or other diagnostics?
class DatabaseWriter{
int writeCount=0;
public DatabaseWriter(){
PreparedStatement preparedStatement = connection.prepareStatement(insert
into msgpersecond ( time , count , sendercompid , targetcompid )
values ( ? , ? , ? , ? ));
connection.setAutoCommit( false ); // turn off auto-Commit
}
public void process(Object input){
preparedStatement.setFloat( 2 , event.msgPerSecond );
preparedStatement.addBatch( );
writeCount++:
if (writeCount 50) {
updateCounts = preparedStatement.executeBatch( );
connection.commit( );
preparedStatement.clearBatch( );
writeCount=0;
}
}
}
process() gets called a lot. The code usually works fine, but sometimes 3
to 20 or so records that definitely are added to the batch but don't get
written.
I'd greatly appreciate any suggestions.
Thanks
optimal number of connections?
Our Java application has several threads that use JDBC to write to several tables. Currently, we use one JDBC connection. Would performance improve if we used a connection per table? Is a single connection bad for some reason? Since connection pooling is a hot topic, I'm wondering if we're missing out somehow. Thanks Jeff
changing the datadir on .rpm SuSE install?
Can the the datadir be changed with a .rpm install on SuSE? Our SA gave us a large partition on /export. We installed the .rpm and were running fine with the datadir set to the default /var/lib path. So I need to move the datadir to /export. Since we're just moving to Production, we do not need the data currently residing on /var/lib. Going forward, we need to read/write to /export. I have not been able to get mysql running with the datadir set to /export. I read in the documentation that the datadir is set at build time for some distributions, but it also explains how to change the datadir in my.cnf, etc so I'm confused. To move datadir to /export, I: 1. Changed the datadir parameter to /export in the /etc/my.cnf file 2. Reran mysql_database_install script. 3. Verified that all the appropriate files and directories appear to have installed under /export. However, the mysqld_safe script says it's: - using /export for data - using a .pid file in /export - exits without starting mysql. There are no other error messages. Can datadir be set to /export successfully? If yes, is there some other configuration that needs to be changed? Thanks Jeff
