ansaurus

Question

Answer 1

+1 A:

select first_name, last_name, member.id member_id, bases.name, bases.state
from bases, sell_transaction, member
where sell_transaction.base_id = bases.base_id
and member.id = sell_transaction.agentId;

I am going out on a limb here and guessing that agentID is the connection to member from sell_transaction, but it's not clear or obvious, and possibly incorrect (unless you clarify your data model).

FrustratedWithFormsDesigner 2010-08-10 16:22:32

agentId is not the connection to member, in fact I did not list it, but the sell_transaction table also has an id column that is referencing the id column in member

CitadelCSAlum 2010-08-10 16:33:40

@CitadelCSAlum: If you omit important columns it will be hard for us to help. You had two "I need..." statements but it was not clear if you wanted two separate result sets or one. I assumed one because that's what is usually wanted, and it sounded like you only wanted one query. Maybe you could give a sample of the expected results, and the queries you currently use.

FrustratedWithFormsDesigner 2010-08-10 17:05:23

I apologize, I did not purposefully omit the important columns, I am really looking for advice on what would be a better option, I can do nested select statements, I can do something similar to the statement above, or I can do a triple join type statement. Of the three, is there one that would be noticeably better then the other.

CitadelCSAlum 2010-08-10 18:35:03

CitadelCSAlum: Again, it's hard to know which strategy will work best for you without knowing more about your system. Try writing all three queries and run them through a query analyser to see which performs best. If you have indexed any of these columns, that will also have an impact on performance.

FrustratedWithFormsDesigner 2010-08-10 18:40:25

I have edited the above with the query that I have been using, hopefully it will give you a better idea of what I am using.

CitadelCSAlum 2010-08-10 20:50:22

@CitadelCSAlum: Yes, that does give me a much clearer idea of what you're doing. Unless you've experienced actual performance problems with this query, leave it alone. If you *do* have performance problems, use a query analyser on it, and then maybe index some of these tables. I would suggest an index on `sell_transaction.user_name` and `sell_transaction.duty` (hard to say if they should be a combined index or not), and `member.username`; I assume that `bases.base_id` is already indexed... These suggestions are only based on this query you gave.

FrustratedWithFormsDesigner 2010-08-10 20:59:55

ansaurus

tags:

views:

answers:

Best SQL Option for Performance

related questions