Multiple keys/indeces/constraints when joining three tables

Question

I'm getting more and more confused as I try to distinguish from the ambiguities of these terms. I have a query that is taking longer than necessary simply because I cannot get the key on on table to work for the other joins.

I have only one column that is "Unique" in t1, there are others which are 73.8% unique and I cannot figure out how to incorporate them as Keys, constraints, indexes or whatever it is I'm looking for.

SELECT t1.*, 
    (SELECT
         t3.comments
     FROM dashboard_data.unit_comments As t3
     WHERE t1.sr=t3.sr) comments,
    (SELECT
         t4.priority
     FROM dashboard_data.units_to_ship As t4
     Where t1.rma=t4.rma) priority
FROM report_tables.idle_report As t1

So, basically, I want to make SR and RMA their own keys so that I can utilize keys for all tables in this query but after spending a day searching the web and reading different sources' interpretations of these terms, I still can't figure out what it is I need to do to optimize this query.

I apologize because I know this is asked a lot but even after reading some of the other questions and answers I can't seem to apply them to my specific case. Can I even use keys for this? Please let me know if you need more info. Also, I was going to paste in the EXPLAIN SELECT but I can't figure out how to easily format a tab delimited (excel) copy. I didn't see it in the full reference for formatting.

Answer 1

Okay, so I think I may have finally found the answer. Primary Keys are Unique and Only Unique, I can have a primary key and still have separate indeces. So I will try using ALTER TABLE to create indexes for RMA and SR.

Still not sure what constraints are, from what I've gathered, I guess it's just a synonym for key or index.

These are my findings:

• Indeces OR PKs can be single or multiple columns
• Indeces OR PKs can be prefixes
• There can be ONLY ONE PK
• There can be MANY indeces
• PK MUST be unique
• Indeces can be unique OR non-unique

Am I correct in my above statements, did I miss any (at least for the basic info)?