title: Speeding up mySQL query and avoiding 'copying to tmp table on disk'
body: <p>I have a database server with over 60G of RAM and SSD drives in RAID10. I'm trying to get a query to run that'll return millions of records (3-6M, most likely). I'm using the following configuration for mySQL --</p>
  
  <pre><code>[mysqld]
  max_connections = 500
  skip-external-locking
  key_buffer = 32M
  open_files_limit = 65535
  table_cache = 9552
  thread_cache = 50
  #table-definition-cache = 4096
  #table-open-cache = 10240
  sort_buffer_size = 512K
  net_buffer_length = 8K
  read_buffer_size = 256K
  read_rnd_buffer_size = 512K
  myisam_sort_buffer_size = 16M
  query_cache_size = 512M
  join_buffer_size = 1024M
  max_heap_table_size = 20G
  tmp_table_size = 20G
  wait_timeout = 120
  interactive_timeout = 120
  #innodb-flush-method = O_DIRECT
  #innodb-log-files-in-group = 2
  #innodb-log-file-size = 512M
  #innodb-flush-log-at-trx-commit = 1
  innodb-file-per-table = 1
  innodb-buffer-pool-size = 32G
  innodb_autoextend_increment=512
  innodb_thread_concurrency=18
  innodb_locks_unsafe_for_binlog = 1
  innodb_lock_wait_timeout=300
  slow_query_log = 1
  slow_query_log_file = /var/lib/mysql/slow.log
  key_buffer_size = 10G
  query_cache_limit = 256M
  
  [mysqldump]
  quick
  max_allowed_packet = 16M
  
  [mysql]
  no-auto-rehash
  
  [isamchk]
  key_buffer = 16M
  sort_buffer_size = 10M
  read_buffer = 2M
  write_buffer = 2M
  
  [myisamchk]
  key_buffer = 16M
  sort_buffer_size = 10M
  read_buffer = 2M
  write_buffer = 2M
  </code></pre>
  
  <p>The query I'm running is...:</p>
  
  <pre><code>SELECT DISTINCT
  Import_AcesApplication.id,
  Import_AcesApplication.sku,
  Parts.partterminologyname,
  BaseVehicle.YearID,
  Make.MakeName,
  Model.modelname,
  SubModel.SubModelName,
  CONCAT(EngineBase.Cylinders, ' CYL ', EngineBase.Liter, EngineBase.BlockType),
  Positions.position
  FROM
  Import_AcesApplication
  STRAIGHT_JOIN BaseVehicle
  ON Import_AcesApplication.base_vehicle_id=BaseVehicle.BaseVehicleID
  STRAIGHT_JOIN Parts 
  ON Import_AcesApplication.part_type_id=Parts.PartTerminologyID
  STRAIGHT_JOIN Make
  ON BaseVehicle.MakeID=Make.MakeID
  STRAIGHT_JOIN Model
  ON BaseVehicle.ModelID=Model.ModelID
  STRAIGHT_JOIN Vehicle
  ON Import_AcesApplication.base_vehicle_id=Vehicle.BaseVehicleID
  STRAIGHT_JOIN SubModel
  ON Vehicle.SubModelID=SubModel.SubModelID
  STRAIGHT_JOIN VehicleConfig
  ON Vehicle.VehicleID=VehicleConfig.VehicleID
  STRAIGHT_JOIN EngineConfig
  ON VehicleConfig.EngineConfigID=EngineConfig.EngineConfigID
  STRAIGHT_JOIN EngineDesignation
  ON EngineConfig.EngineDesignationID=EngineDesignation.EngineDesignationID
  STRAIGHT_JOIN EngineVIN
  ON EngineConfig.EngineVINID=EngineVIN.EngineVINID
  STRAIGHT_JOIN EngineBase
  ON EngineConfig.EngineBaseID=EngineBase.EngineBaseID
  STRAIGHT_JOIN Positions
  ON Positions.PositionID=Import_AcesApplication.position_id
  </code></pre>
  
  <p>EDIT: I've altered the query a bit...</p>
  
  <pre><code>SELECT DISTINCT
  Import_AcesApplication.id,
  Import_AcesApplication.sku,
  Parts.partterminologyname,
  BaseVehicle.YearID,
  Make.MakeName,
  Model.modelname,
  SubModel.SubModelName,
  CONCAT(EngineBase.Cylinders, ' CYL ', EngineBase.Liter, EngineBase.BlockType),
  Positions.position
  FROM
  Import_AcesApplication
  STRAIGHT_JOIN BaseVehicle
  ON Import_AcesApplication.base_vehicle_id=BaseVehicle.BaseVehicleID
  STRAIGHT_JOIN Parts 
  ON Import_AcesApplication.part_type_id=Parts.PartTerminologyID
  STRAIGHT_JOIN Make
  ON BaseVehicle.MakeID=Make.MakeID
  STRAIGHT_JOIN Model
  ON BaseVehicle.ModelID=Model.ModelID
  STRAIGHT_JOIN Vehicle
  ON Import_AcesApplication.base_vehicle_id=Vehicle.BaseVehicleID
  STRAIGHT_JOIN SubModel
  ON Vehicle.SubModelID=SubModel.SubModelID
  STRAIGHT_JOIN VehicleConfig
  ON Vehicle.VehicleID=VehicleConfig.VehicleID
  STRAIGHT_JOIN EngineConfig
  ON VehicleConfig.EngineConfigID=EngineConfig.EngineConfigID
  STRAIGHT_JOIN EngineBase
  ON EngineConfig.EngineBaseID=EngineBase.EngineBaseID
  STRAIGHT_JOIN Positions
  ON Positions.PositionID=Import_AcesApplication.position_id
  </code></pre>
  
  <p>I'm using STRAIGHT_JOIN to force the order since SELECT EXPLAIN showed it to be incorrect. I've setup my indexes on the tables, but the query seems to be getting stuck on the state of 'copying to tmp table on the disk'. I've tried different tips online like increasing the tmp_table_size and whatnot but nothing has helped.</p>
  
  <p>Can someone please help me figure this out so that the queries are faster?</p>
  
  <p>EDIT: The EXPLAIN results can be seen <a href="http://imgur.com/QMeJ08w" rel="nofollow">here.</a></p>