Databases
StackOverflow2013
Postsdbo
POSpeeding up mySQL query and avoiding 'copying to tmp table on disk'
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSpeeding up mySQL query and avoiding 'copying to tmp table on disk'
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload