Mysql temporary, filesort issue

Question

From my research, I'm not even sure it is possible to avoid the temporary / filesort with the following query due to the group by and order by. I'm also not one to ask without doing a ton of research on my own. But if anyone can help figure out how to avoid the filesort or point me in the correct direction - even if its completely re-writing the query or suggesting something code side, it would be much appreciated. I've completely hit a wall trying to figure this one out. There's a link to the output of the explain at the bottom.

explain select CONCAT(scfs.name, ' ',scfs.state,' ',scfs.zip_code) as scfname, scfs.zip_code, IF(date(s.scan_datetime) <= date(NOW()),date(s.scan_datetime),null) as scandate, count(*) as total,
                    sum(case when s.delivery_status = 1 then 1 else 0 end) as final

                     from order_addresses oa
                         left join pkg_data_unique s
                            on oa.trace_code = s.pkg_trace_code
                         inner join scf_zip_codes z
                            on SUBSTR(oa.zip,1,3) = z.zip_code
                         inner join scfs scfs
                            on z.scf_zip_code = scfs.zip_code



                    where oa.order_id = 160387

                    group by 1,2,3
                    order by scfs.zip_code, scandate

CREATE ORDER ADDRESSES

CREATE TABLE order_addresses ( id int(10) unsigned NOT NULL AUTO_INCREMENT, order_id int(11) NOT NULL, name varchar(100) COLLATE utf8_unicode_ci NOT NULL, address varchar(100) COLLATE utf8_unicode_ci NOT NULL, address2 varchar(100) COLLATE utf8_unicode_ci NOT NULL, city varchar(50) COLLATE utf8_unicode_ci NOT NULL, state varchar(15) COLLATE utf8_unicode_ci NOT NULL, zip char(5) COLLATE utf8_unicode_ci NOT NULL, zip4 int(11) NOT NULL, imb_digits char(31) COLLATE utf8_unicode_ci NOT NULL, trace_code char(20) COLLATE utf8_unicode_ci NOT NULL, created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', deleted_at timestamp NULL DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY order_address_unique (order_id,imb_digits,name,address,address2,city,state,zip), KEY order_addresses_order_id_index (order_id), KEY order_addresses_name_index (name), KEY order_addresses_address_index (address), KEY order_addresses_city_index (city), KEY order_addresses_state_index (state), KEY order_addresses_zip_index (zip), KEY order_addresses_imb_digits_index (imb_digits), KEY order_addresses_trace_code_index (trace_code), KEY order_id_trace_code (order_id,trace_code) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=487714542 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT

PKG DATA UNIQUE TABLE

CREATE TABLE pkg_data_unique ( id int(10) unsigned NOT NULL AUTO_INCREMENT, zip char(5) COLLATE utf8_unicode_ci NOT NULL, opcode int(11) NOT NULL, pkg_trace_code char(20) COLLATE utf8_unicode_ci NOT NULL, scan_datetime datetime NOT NULL, original_scan_datetime datetime NOT NULL, delivery_status int(11) NOT NULL, created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (id), UNIQUE KEY pkg_data_unique_pkg_trace_code_index (pkg_trace_code) USING BTREE, KEY pkg_data_unique_zip_index (zip), KEY pkg_data_unique_opcode_index (opcode), KEY pkg_data_unique_scan_datetime_index (scan_datetime), KEY pkg_data_unique_delivery_status_index (delivery_status), KEY pkg_data_unique_original_scan_datetime (original_scan_datetime) ) ENGINE=InnoDB AUTO_INCREMENT=490667214 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT

SCF ZIP CODES

CREATE TABLE scf_zip_codes ( id int(10) unsigned NOT NULL AUTO_INCREMENT, scf_zip_code varchar(3) COLLATE utf8_unicode_ci NOT NULL, zip_code varchar(3) COLLATE utf8_unicode_ci NOT NULL, created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (id), KEY scf_zip_codes_scf_zip_code_index (scf_zip_code), KEY scf_zip_codes_zip_code_index (zip_code) ) ENGINE=InnoDB AUTO_INCREMENT=916 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT

SCFS

CREATE TABLE scfs ( id int(10) unsigned NOT NULL AUTO_INCREMENT, label_id bigint(20) unsigned NOT NULL, zip_code varchar(5) COLLATE utf8_unicode_ci NOT NULL, name varchar(255) COLLATE utf8_unicode_ci NOT NULL, state varchar(255) COLLATE utf8_unicode_ci NOT NULL, locale_key varchar(255) COLLATE utf8_unicode_ci NOT NULL, created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (id), UNIQUE KEY scfs_label_id_unique (label_id), UNIQUE KEY scfs_zip_code_unique (zip_code), KEY scfs_name_index (name), KEY scfs_state_index (state) ) ENGINE=InnoDB AUTO_INCREMENT=196 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT

Image of Explain Output

Answer 1

figure out how to avoid the filesort or point me in the correct direction

The alternative(s) involve changing the intent of your query.

    group by  1,2,3
    order by  scfs.zip_code, scandate

The GROUP BY may involve a filesort an temp table.

The ORDER BY will certainly involve (another) filesort and temp table because it does not match the GROUP BY. No, shuffling to GROUP BY 2,1,3 (since col 2 is the zip code) won't help.

If you can live with this, GROUP BY 2,1,3 ORDER BY 2,1,3, then you can get rid of one filesort and have approximately the same output.

Is your query "slow"? 'Filesort' is not the main performance killer.

There are a few things that can speed up the query some:

• Zipcodes (and some other things) don't need utf8; change the column to CHARACTER SET ascii.
• Secondary keys, when used, need to drill down the PRIMARY KEY BTree to find the row. Consider which, if any, secondary indexes could be promoted to PRIMARY KEY.
• date(s.scan_datetime) <= date(NOW()) --> s.scan_datetime <= CURDATE()
• sum(case when s.delivery_status = 1 then 1 else 0 end) => sum(case when s.delivery_status = 1) since a boolean expression evaluates to 1 or 0.
• INDEX(a), INDEX(a,b) --> the first can be DROPped without loss of functionality.
• scfs has 3 unique keys; id seems useless. Promote zipcode to be the PK and make it CHAR(5) CHARACTER SET ascii. It will be 5 bytes instead of id's 4 bytes.
• Don't blindly use (255); pick a smaller (but safe) value.