I have multiple tables in a mysql database. Each table has about 10 million rows. I have multiple queries, and often need to join multiple tables in every query, which seems to be causing some performance issues.
Currently I'm using Myisam for all tables - all tables has indexes on all columns that I join on. The columns that I join on are int and varchar(6) types.
Are there any good guidelines when designing tables to improve execution time for join intensive queries?
Should I perhaps switch to InnoDB? I tried creating a new table, with InnoDB as storage engine and my varchar(6) column as a primary key, but I saw no performance gain at all when joining against that table.