How to speed up mysql query against a 204 million record table
All columns on both tables are indexed.


Enough said.  That is not the right way to go about indexing.


  WHERE db2.tb2.c1 = db1.tb1.c1


db2.tb2 needs INDEX(c1) -- Keep in mind that a PRIMARY KEY is a UNIQUE key is an INDEX, so do not redundantly add INDEX if you already have PRIMARY KEY(c1).


  and c6='X' and c9 &lt;='y' and c10>= 'Z' and c12>='N'


Needs a compound index; recommending adding these three, since there are no clues of which would be best:

INDEX(c6, c9)
INDEX(c6, c10)
INDEX(c6, c12)


At the same time, remove INDEX(c6) because it is now redundant.  For further discussion, Cookbook on making an INDEX, given a SELECT.


  tmp_table_size = 3G
  max_heap_table_size = 1G


Very dangerous.  Are you swapping?  Swapping is much worse on performance than lowering some of the settings.  Each SELECT that needs a tmp table will allocate 1GB; if you have lots of such queries running at the same time, you will rapidly chew up RAM.  Lower both of those to their defaults.


  key_buffer = 5G


That is for MyISAM indexes.  If you are not using MyISAM, set it to 20M.  InnoDB is the preferred engine; 26G (out of 36GB) is reasonable if all of your tables are InnoDB.

There may be more suggestions; see how far you get with these.

Edit


  DISTINCT(c1),c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12


May not mean what you think -- DISTINCT is not a function, and the parens are irrelevant.  That de-dups the entire set of 12 expressions.  Also, it is probably irrelevant, unless you actually have duplicate rows.

Is c1 your PRIMARY KEY?  Hence UNIQUE?  So the GROUP BY is also irrelevant.  Removing it may help speed.