# MySql Order by on a huge joined datasets is taking too long time. **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [April 17, 2015, 4:55am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167 "2015-04-17T04:55:39Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Nagini](https://avatars.discourse-cdn.com/v4/letter/n/3ec8ea/32.png) [@Nagini](https://forums.percona.com/u/Nagini)\ **Post date:** [April 17, 2015, 4:55am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/1 "2015-04-17T04:55:39Z")
I have to join many tables where in each table has huge volume of data and on such joined huge dataset, i have to apply Order by which is taking a very very longer time ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [April 17, 2015, 10:13am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/2 "2015-04-17T10:13:54Z")
Hi Nagini; If you post your query and create table information we might be able to offer some optimization advice. Otherwise, you can help an order by with an index potentially depending on your query. The general idea would be to put the column you are ordering by at the end of your index, however a large number of factors will prevent that index from being used, so it highly depends on your query and schema. -Scott ---
**Author:** ![Nagini](https://avatars.discourse-cdn.com/v4/letter/n/3ec8ea/32.png) [@Nagini](https://forums.percona.com/u/Nagini)\ **Post date:** [April 20, 2015, 5:29am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/3 "2015-04-20T05:29:27Z")
Hi Scott, Thanks for the reply. let me post the actual query and the tables structure. The query is: SELECT distinct pd.message\_id, pd.Receipt\_Time\_Stamp, pd.shift\_id, pd.Site\_Code, pd.Tunnel\_ID, pd.Package\_Number, pd.Package\_Read\_Status, pd.Iseq\_Number, pd.SxS\_Status, pd.host\_message, pd.package\_gap, pd.Parcel\_Length, pd.Parcel\_Width, pd.Parcel\_Height, pd.Image\_Files, tunnel\_name, s.shift\_name FROM fm\_package\_db.dla\_more\_bar\_codes b JOIN fm\_package\_db.dla\_more\_devices d ON b.message\_id = d.message\_id JOIN fm\_package\_db.dla\_package\_details pd ON pd.message\_id = b.message\_id JOIN fm\_local\_db.as\_fm\_tunnel\_master t ON pd.Tunnel\_ID = t.Tunnel\_ID JOIN fm\_local\_db.as\_fm\_shift\_info s ON s.shift\_id = pd.shift\_id WHERE receipt\_time\_stamp between ‘2015-03-30 00:00:00’ AND ‘2015-03-30 23:59:59’ and Message\_Type = ‘PackageInfo’ AND pd.Site\_Code = ‘SITE\_6’ AND (pd.Tunnel\_ID = ‘1’) AND (pd.shift\_id = 1 or pd.shift\_id = 2 or pd.shift\_id = 3) and (b.bar\_code\_number = 1 or b.bar\_code\_number = 2) and (d.device\_id = 2 or d.device\_id = 3 or d.device\_id = 8 or d.device\_id = 1005 or d.device\_id = 1021 or d.device\_id = 1049 or d.device\_id = 1057 or d.device\_id = 1081) order by receipt\_time\_stamp LIMIT 0 , 25 ---
**Author:** ![Nagini](https://avatars.discourse-cdn.com/v4/letter/n/3ec8ea/32.png) [@Nagini](https://forums.percona.com/u/Nagini)\ **Post date:** [April 20, 2015, 5:32am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/4 "2015-04-20T05:32:01Z")
the create queries for the tables involved in the above query are : CREATE TABLE `dla_package_details` ( `Message_ID` bigint(20) NOT NULL DEFAULT ‘0’, `Receipt_Time_Stamp` timestamp NULL DEFAULT CURRENT\_TIMESTAMP, `Message_Type` varchar(20) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT NULL, `Message_Status` char(1) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT ‘P’, `Site_Code` varchar(20) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT NULL, `Tunnel_ID` bigint(20) DEFAULT NULL, `shift_id` bigint(20) DEFAULT NULL, `Package_Number` bigint(20) DEFAULT NULL, `Package_Read_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT NULL, `Iseq_Number` bigint(20) DEFAULT NULL, `Host_Message` text CHARACTER SET utf8 COLLATE utf8\_bin, `Eseq_Number` bigint(20) DEFAULT NULL, `Bar_Code_1` text CHARACTER SET utf8 COLLATE utf8\_bin, `Bar_Code_2` text CHARACTER SET utf8 COLLATE utf8\_bin, `Bar_Code_3` text CHARACTER SET utf8 COLLATE utf8\_bin, `Bar_Code_4` text CHARACTER SET utf8 COLLATE utf8\_bin, `Bar_Code_5` text CHARACTER SET utf8 COLLATE utf8\_bin, `Bar_Code_6` text CHARACTER SET utf8 COLLATE utf8\_bin, `More_bar_Codes` char(1) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT ‘N’, `Dev_1_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_2_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_3_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_4_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_5_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_6_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_7_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_8_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_9_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_10_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_11_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_12_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_13_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_14_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_15_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_16_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_17_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_18_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_19_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_20_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_21_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `Dev_22_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `More_Devices` char(1) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT ‘N’, `Nbr_Short_Parcels` int(11) NOT NULL DEFAULT ‘0’, `Nbr_Short_Gaps` int(11) NOT NULL DEFAULT ‘0’, `Nbr_Lost_Barcodes` int(11) NOT NULL DEFAULT ‘0’, `Trigger_Length` int(11) NOT NULL DEFAULT ‘0’, `Package_Gap` int(11) NOT NULL DEFAULT ‘0’, `Conveyor_Speed` int(11) NOT NULL DEFAULT ‘0’, `SxS_Status` varchar(20) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT NULL, `Parcel_Length` decimal(5,2) DEFAULT NULL, `Parcel_Width` decimal(5,2) DEFAULT NULL, `Parcel_Height` decimal(5,2) DEFAULT NULL, `Parcel_Linear_Units` varchar(10) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT NULL, `LFT_Dimensions` text CHARACTER SET utf8 COLLATE utf8\_bin, `Parcel_Volume` decimal(5,2) DEFAULT NULL, `Parcel_Volume_Units` varchar(10) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT NULL, `Parcel_Weight` decimal(5,2) DEFAULT NULL, `Parcel_Weight_Units` varchar(10) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT NULL, `LFT_Parcel_Weight` decimal(5,2) DEFAULT NULL, `LFT_Parcel_Weight_Units` varchar(10) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT NULL, `Overlap_Prev` varchar(10) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT NULL, `Overlap_Next` varchar(10) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT NULL, `Image_Files` text CHARACTER SET utf8 COLLATE utf8\_bin, `Decode_Info` text CHARACTER SET utf8 COLLATE utf8\_bin, `Device_Read_Result` int(11) NOT NULL DEFAULT ‘0’, `unix_receipt_time_stamp` int(11) DEFAULT NULL, `Message_type_id` tinyint(4) DEFAULT NULL, PRIMARY KEY (`Message_ID`), KEY `dl_dla_package_details_message_type_idx` (`Message_Type`), KEY `dl_dla_package_details_site_code_idx` (`Site_Code`), KEY `dl_dla_package_details_shift_id_idx` (`shift_id`), KEY `dl_dla_package_details_package_number_idx` (`Package_Number`), KEY `dl_dla_package_details_package_read_status_idx` (`Package_Read_Status`), KEY `dl_dla_package_details_iseq_number_idx` (`Iseq_Number`), KEY `dl_dla_package_details_host_message_idx` (`Host_Message`(255)), KEY `dl_dla_package_details_package_gap_idx` (`Package_Gap`), KEY `dl_dla_package_details_sxs_status_idx` (`SxS_Status`), KEY `dl_dla_package_details_parcel_length_idx` (`Parcel_Length`), KEY `dl_dla_package_details_parcel_width_idx` (`Parcel_Width`), KEY `dl_dla_package_details_parcel_height_idx` (`Parcel_Height`), KEY `dl_dla_package_details_site_code_tunnel_id_idx` (`Site_Code`,`Tunnel_ID`), KEY `dl_dla_package_details_site_tunnel_type_time_idx` (`Site_Code`,`Tunnel_ID`,`Message_Type`,`Receipt_Time_Stamp`), KEY `dl_dla_package_details_unix_receipt_time_stamp_idx` (`unix_receipt_time_stamp`), KEY `dl_dla_package_details_receipt_time_stamp_idx` (`Receipt_Time_Stamp`), KEY `dl_dla_package_details_tunnel_id_idx` (`Tunnel_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `dla_more_bar_codes` ( `Message_ID` bigint(20) NOT NULL DEFAULT ‘0’, `Bar_Code_Data` text CHARACTER SET utf8 COLLATE utf8\_bin, `Bar_code_number` int(11) DEFAULT NULL, `Bar_code` text, KEY `dl_dla_more_bar_codes_message_id_idx` (`Message_ID`), KEY `dl_dla_more_bar_codes_bar_code_number_idx` (`Bar_code_number`), KEY `dl_dla_more_bar_codes_bar_code_idx` (`Bar_code`(255)), KEY `dl_dla_more_bar_codes_message_id_bar_code_idx` (`Message_ID`,`Bar_code`(255)), KEY `dl_dla_more_bar_codes_bar_code_bar_code_number)idx` (`Bar_code_number`,`Bar_code`(255)), CONSTRAINT `FK_DLA_PACKAGE_DETAILS_MESSAGE_ID` FOREIGN KEY (`Message_ID`) REFERENCES `dla_package_details` (`Message_ID`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `dla_more_devices` ( `Message_ID` bigint(20) NOT NULL DEFAULT ‘0’, `Dev_Details` text CHARACTER SET utf8 COLLATE utf8\_bin, `device_id` decimal(10,0) DEFAULT NULL, `read_status` char(1) DEFAULT NULL, KEY `dl_dla_more_devices_message_id_idx` (`Message_ID`), KEY `dl_dla_more_devices_device_id_idx` (`device_id`), CONSTRAINT `FK_DLA_MORE_DEVICES_DLA_PACKAGE_DETAILS_MESSAGE_ID` FOREIGN KEY (`Message_ID`) REFERENCES `dla_package_details` (`Message_ID`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ---
**Author:** ![Nagini](https://avatars.discourse-cdn.com/v4/letter/n/3ec8ea/32.png) [@Nagini](https://forums.percona.com/u/Nagini)\ **Post date:** [April 20, 2015, 6:56am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/5 "2015-04-20T06:56:02Z")
I have around 50 lakh records in each of dla\_package\_details, dla\_more\_bar\_codes and dla\_more\_devices and these three tables have to be joined, whihc results a big data set. On such dataset, the filters have to be applied and have to be ordered by any of the columns. What I foundis order by for such a bigger dataset is taking around 10 mins. Pls suggest how to optimize this query to get executed within 5 seconds. ---
**Author:** ![Nagini](https://avatars.discourse-cdn.com/v4/letter/n/3ec8ea/32.png) [@Nagini](https://forums.percona.com/u/Nagini)\ **Post date:** [April 20, 2015, 7:58am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/6 "2015-04-20T07:58:40Z")
as\_fm\_tunnel\_master and as\_fm\_shift\_info are Master tables which has very less data. ---
**Author:** ![Nagini](https://avatars.discourse-cdn.com/v4/letter/n/3ec8ea/32.png) [@Nagini](https://forums.percona.com/u/Nagini)\ **Post date:** [April 23, 2015, 12:59am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/7 "2015-04-23T00:59:15Z")
Waiting for the reply… pls help out ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [April 23, 2015, 2:11pm UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/8 "2015-04-23T14:11:42Z")
Hi Nagini; Please post the EXPLAIN output for the query and I’ll take a look. -Scott ---
**Author:** ![Nagini](https://avatars.discourse-cdn.com/v4/letter/n/3ec8ea/32.png) [@Nagini](https://forums.percona.com/u/Nagini)\ **Post date:** [April 27, 2015, 2:06am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/9 "2015-04-27T02:06:47Z")
[TABLE=“width: 1753”] [TR] [TD]id[/TD] [TD]select\_type[/TD] [TD]table[/TD] [TD]type[/TD] [TD]possible\_keys[/TD] [TD]key[/TD] [TD]key\_len[/TD] [TD]ref[/TD] [TD]rows[/TD] [TD]Extra[/TD] [/TR] [TR] [TD=“align: right”]1[/TD] [TD]SIMPLE[/TD] [TD]pd[/TD] [TD]index\_merge[/TD] [TD]PRIMARY,dl\_dla\_package\_details\_message\_type\_idx,dl\_dla\_package\_details\_site\_code\_idx,dl\_dla\_package\_details\_shift\_id\_idx,dl\_dla\_package\_details\_site\_code\_tunnel\_id\_idx,dl\_dla\_package\_details\_site\_tunnel\_type\_time\_idx,dl\_dla\_package\_details\_receipt\_time\_stamp\_idx,dl\_dla\_package\_details\_tunnel\_id\_idx[/TD] [TD]dl\_dla\_package\_details\_tunnel\_id\_idx,dl\_dla\_package\_details\_message\_type\_idx,dl\_dla\_package\_details\_site\_code\_idx[/TD] [TD]9,63,63[/TD] [TD]NULL[/TD] [TD=“align: right”]487453[/TD] [TD]Using intersect(dl\_dla\_package\_details\_tunnel\_id\_idx, dl\_dla\_package\_details\_message\_type\_idx,dl\_dla\_package\_details\_site\_code\_idx); Using where; Using temporary; Using filesort[/TD] [/TR] [TR] [TD=“align: right”]1[/TD] [TD]SIMPLE[/TD] [TD]s[/TD] [TD]ref[/TD] [TD]PRIMARY,dl\_as\_fm\_shift\_info\_shift\_id\_idx[/TD] [TD]PRIMARY[/TD] [TD=“align: right”]8[/TD] [TD]fm\_package\_db.pd.shift\_id[/TD] [TD=“align: right”]1[/TD] [TD] [/TD] [/TR] [TR] [TD=“align: right”]1[/TD] [TD]SIMPLE[/TD] [TD]t[/TD] [TD]ref[/TD] [TD]dl\_as\_fm\_tunnel\_master\_tunnel\_id\_idx[/TD] [TD]dl\_as\_fm\_tunnel\_master\_tunnel\_id\_idx[/TD] [TD=“align: right”]8[/TD] [TD]const[/TD] [TD=“align: right”]1[/TD] [TD] [/TD] [/TR] [TR] [TD=“align: right”]1[/TD] [TD]SIMPLE[/TD] [TD]b[/TD] [TD]ref[/TD] [TD]dl\_dla\_more\_bar\_codes\_message\_id\_idx,dl\_dla\_more\_bar\_codes\_bar\_code\_number\_idx,dl\_dla\_more\_bar\_codes\_message\_id\_bar\_code\_idx,dl\_dla\_more\_bar\_codes\_bar\_code\_bar\_code\_number)idx[/TD] [TD]dl\_dla\_more\_bar\_codes\_message\_id\_idx[/TD] [TD=“align: right”]8[/TD] [TD]fm\_package\_db.pd.Message\_ID[/TD] [TD=“align: right”]1[/TD] [TD]Using where; Distinct[/TD] [/TR] [TR] [TD=“align: right”]1[/TD] [TD]SIMPLE[/TD] [TD]d[/TD] [TD]ref[/TD] [TD]dl\_dla\_more\_devices\_message\_id\_idx,dl\_dla\_more\_devices\_device\_id\_idx[/TD] [TD]dl\_dla\_more\_devices\_message\_id\_idx[/TD] [TD=“align: right”]8[/TD] [TD]fm\_package\_db.b.Message\_ID[/TD] [TD=“align: right”]2[/TD] [TD]Using where; Distinct[/TD] [/TR] [/TABLE] ---
**Author:** ![Nagini](https://avatars.discourse-cdn.com/v4/letter/n/3ec8ea/32.png) [@Nagini](https://forums.percona.com/u/Nagini)\ **Post date:** [April 27, 2015, 2:08am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/10 "2015-04-27T02:08:23Z")
Hi Scott, Have posted the Explain result of the query, above. Pls have a look n suggest suitable solution ---
**Author:** ![wagnerbianchi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/wagnerbianchi/32/802_2.png) [@wagnerbianchi](https://forums.percona.com/u/wagnerbianchi)\ **Post date:** [April 27, 2015, 1:33pm UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/11 "2015-04-27T13:33:00Z")
I think that it’s best you do a review on the indexes you’ve got on those tables to try to gain some speed by design and then, try to review your query running the EXPLAIN. Due to the high number of possible keys, mainly on fm\_package\_db.dla\_package\_details table, I think that this query will slow down on the optimizer trying to execute the search testing all the possible keys. ```auto #: How many indexes has the column Tunnel_ID? Too many options to be tested out by optimizer... KEY `dl_dla_package_details_site_code_tunnel_id_idx` (`Site_Code`,`Tunnel_ID`), KEY `dl_dla_package_details_site_tunnel_type_time_idx` (`Site_Code`,`Tunnel_ID`,`Message_Type`,`Receipt_T ime_Stamp`), [...] KEY `dl_dla_package_details_tunnel_id_idx` (`Tunnel_ID`) ``` Review the whole model and perhaps remove duplicate indexes/columns form indexes. pt-duplicate-keys: [URL=“[pt-duplicate-key-checker — Percona Toolkit Documentation](http://www.percona.com/doc/percona-toolkit/2.1/pt-duplicate-key-checker.html)”][http://www.percona.com/doc/percona-t...y-checker.html[/URL]](http://www.percona.com/doc/percona-t...y-checker.html%5B/URL%5D) Additionally, are you able to provide the profiler of this query? [url][http://www.percona.com/blog/2012/02/20/how-to-convert-show-profiles-into-a-real-profile/[/url]](http://www.percona.com/blog/2012/02/20/how-to-convert-show-profiles-into-a-real-profile/%5B/url%5D) ---
**Author:** ![Nagini](https://avatars.discourse-cdn.com/v4/letter/n/3ec8ea/32.png) [@Nagini](https://forums.percona.com/u/Nagini)\ **Post date:** [April 28, 2015, 1:22am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/12 "2015-04-28T01:22:27Z")
[TABLE=“width: 1055”] [TR] [TD=“width: 64, align: right”]4[/TD] [TD=“width: 64, align: right”]12[/TD] [TD=“width: 138”]Copying to tmp table[/TD] [TD=“width: 77, align: right”]275.547847[/TD] [TD=“width: 64”]NULL[/TD] [TD=“width: 64”]NULL[/TD] [TD=“width: 64”]NULL[/TD] [TD=“width: 64”]NULL[/TD] [TD=“width: 64”]NULL[/TD] [TD=“width: 64”]NULL[/TD] [TD=“width: 64”]NULL[/TD] [TD=“width: 64”]NULL[/TD] [TD=“width: 64”]NULL[/TD] [TD=“width: 64”]NULL[/TD] [TD=“width: 64”]NULL[/TD] [TD=“width: 172”]JOIN::exec[/TD] [TD=“width: 96”].\sql\_select.cc[/TD] [TD=“width: 90, align: right”]1938[/TD] [/TR] [/TABLE] ---
**Author:** ![Nagini](https://avatars.discourse-cdn.com/v4/letter/n/3ec8ea/32.png) [@Nagini](https://forums.percona.com/u/Nagini)\ **Post date:** [April 28, 2015, 1:27am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/13 "2015-04-28T01:27:49Z")
Have tried to remove the duplicate indexes on Tunnel\_id column, which were created as Composite indexes. Even though thrs not much improvement. And when i ran the Profiling for this query, almost all the time in that query’s execution time is taken in ‘Copying to tmp table’ task. I tried to increase the max\_heap\_table\_size and tmp\_table\_size, even though its not better. ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [April 28, 2015, 12:23pm UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/14 "2015-04-28T12:23:05Z")
Hi Nagini; Try an index on: Message\_Type,Site\_Code,Tunnel\_ID,receipt\_time\_stamp (leave receipt\_time\_stamp at the end, but re-order the first 3 columns in order of most selective on the left to least selective on the right). Then try changing your d.device\_id ORs to a single IN. Generally if it’s more than 3, using IN instead of OR is a good idea. Note this is just a “generally it works better” recommendation and not a hard rule to follow. Once you make those changes, see if it runs any quicker and if not, see if the explain / profile for it changes any. -Scott ---
**Author:** ![Nagini](https://avatars.discourse-cdn.com/v4/letter/n/3ec8ea/32.png) [@Nagini](https://forums.percona.com/u/Nagini)\ **Post date:** [April 30, 2015, 6:00am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/15 "2015-04-30T06:00:14Z")
Meanwhile i was also trying on refining the query and i got it as below: SELECT pd.message\_id, pd.Receipt\_Time\_Stamp, pd.Tunnel\_ID, pd.Package\_Number, pd.Package\_Read\_Status, pd.Iseq\_Number, pd.SxS\_Status, pd.Parcel\_Length, pd.Parcel\_Width, pd.Parcel\_Height, pd.Image\_Files, t.tunnel\_name FROM fm\_package\_db.dla\_package\_details pd force index (dl\_dla\_package\_details\_receipt\_time\_stamp\_idx) JOIN fm\_local\_db.as\_fm\_tunnel\_master t ON pd.Tunnel\_ID = t.Tunnel\_ID WHERE receipt\_time\_stamp between ‘2015-03-29 00:00:00’ and ‘2015-03-29 23:59:59’ and pd.message\_id in (Select distinct b.Message\_ID from dla\_more\_bar\_codes b JOIN fm\_package\_db.dla\_more\_devices d ON b.message\_id = d.message\_id AND (b.bar\_code\_number = ‘1’ or b.bar\_code\_number = ‘2’) AND (d.device\_id = ‘2’ or d.device\_id = ’ 3’ or d.device\_id = ’ 8’ or d.device\_id = ’ 1005’ or d.device\_id = ’ 1021’ or d.device\_id = ’ 1049’ or d.device\_id = ’ 1057’ or d.device\_id = ’ 1081’)) and Message\_type = ‘PackageInfo’ AND (pd.Tunnel\_Id = ‘1’) ORDER BY Receipt\_Time\_Stamp desc LIMIT 0 , 25 If we can compare the sql that i had posted earlier with this, the chnages those are in bold viz., forcing it use an index and moving the barcode and device table filters to main query’s Where clause (as per the requirement, these two child tables are in the query only to filter out the message\_ids). This query is now running in Mysql Windows environment in \<6 seconds, but now the problem is the same query with the same DB instance remain to take 250 seconds in Linux environment of Mysql. Pls can any one let me know if there could be any differences as such in behaviour of Mysql in different OS environments. If yes, pls suggest the changes that i have to do for Linux. Pls help out. ---
**Author:** ![Nagini](https://avatars.discourse-cdn.com/v4/letter/n/3ec8ea/32.png) [@Nagini](https://forums.percona.com/u/Nagini)\ **Post date:** [April 30, 2015, 6:09am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/16 "2015-04-30T06:09:37Z")
Hi Scott, As per your post, did u mean that a composite index on all those columns has to be created, is it. If so, am thinking that it may not work out. Am saying this because the Where criteria of my query will change dynamically and it is my assumption that a composite index will work only if the columns match the Where criteria and COmposite index. Pls correct me if am wrong. And apart from this, from my previous post, the modified query is not working in Linux but is workingin Windows better. Pls suggest me something on this. ---
**Author:** ![Nagini](https://avatars.discourse-cdn.com/v4/letter/n/3ec8ea/32.png) [@Nagini](https://forums.percona.com/u/Nagini)\ **Post date:** [April 30, 2015, 6:30am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/17 "2015-04-30T06:30:37Z")
Pls look at the Explain outputs for both Windws and Linux environments of Mysql: Explain output for Linux Mysql: [TABLE=“width: 1439”] [TR] [TD]id[/TD] [TD]select\_type[/TD] [TD]table[/TD] [TD]type[/TD] [TD]possible\_keys[/TD] [TD]key[/TD] [TD]key\_len[/TD] [TD]ref[/TD] [TD]rows[/TD] [TD]Extra[/TD] [/TR] [TR] [TD=“align: right”]1[/TD] [TD]SIMPLE[/TD] [TD]t[/TD] [TD]ref[/TD] [TD]dl\_as\_fm\_tunnel\_master\_tunnel\_id\_idx[/TD] [TD]dl\_as\_fm\_tunnel\_master\_tunnel\_id\_idx[/TD] [TD=“align: right”]8[/TD] [TD]const[/TD] [TD=“align: right”]1[/TD] [TD]Using index condition; Using temporary; Using filesort[/TD] [/TR] [TR] [TD=“align: right”]1[/TD] [TD]SIMPLE[/TD] [TD]pd[/TD] [TD]range[/TD] [TD]dl\_dla\_package\_details\_receipt\_time\_stamp\_idx[/TD] [TD]dl\_dla\_package\_details\_receipt\_time\_stamp\_idx[/TD] [TD=“align: right”]5[/TD] [TD]NULL[/TD] [TD=“align: right”]1958726[/TD] [TD]Using index condition; Using where; Using join buffer (Block Nested Loop)[/TD] [/TR] [TR] [TD=“align: right”]1[/TD] [TD]SIMPLE[/TD] [TD][/TD] [TD]eq\_ref[/TD] [TD]\[/TD] [TD]\[/TD] [TD=“align: right”]8[/TD] [TD]fm\_package\_db.pd.Message\_ID[/TD] [TD=“align: right”]1[/TD] [TD]NULL[/TD] [/TR] [TR] [TD=“align: right”]2[/TD] [TD]MATERIALIZED[/TD] [TD]b[/TD] [TD]range[/TD] [TD]dl\_dla\_more\_bar\_codes\_message\_id\_idx,dl\_dla\_more\_bar\_codes\_bar\_code\_number\_idx[/TD] [TD]dl\_dla\_more\_bar\_codes\_bar\_code\_number\_idx[/TD] [TD=“align: right”]5[/TD] [TD]NULL[/TD] [TD=“align: right”]1804091[/TD] [TD]Using index condition; Using MRR[/TD] [/TR] [TR] [TD=“align: right”]2[/TD] [TD]MATERIALIZED[/TD] [TD]d[/TD] [TD]ref[/TD] [TD]dl\_dla\_more\_devices\_message\_id\_idx,dl\_dla\_more\_devices\_device\_id\_idx[/TD] [TD]dl\_dla\_more\_devices\_message\_id\_idx[/TD] [TD=“align: right”]8[/TD] [TD]fm\_package\_db.b.Message\_ID[/TD] [TD=“align: right”]2[/TD] [TD]Using where[/TD] [/TR] [/TABLE] Explain output for Windows: [TABLE=“width: 1493”] [TR] [TD]id[/TD] [TD]select\_type[/TD] [TD]table[/TD] [TD]type[/TD] [TD]possible\_keys[/TD] [TD]key[/TD] [TD]key\_len[/TD] [TD]ref[/TD] [TD]rows[/TD] [TD]Extra[/TD] [/TR] [TR] [TD=“align: right”]1[/TD] [TD]PRIMARY[/TD] [TD]pd[/TD] [TD]range[/TD] [TD]dl\_dla\_package\_details\_receipt\_time\_stamp\_idx[/TD] [TD]dl\_dla\_package\_details\_receipt\_time\_stamp\_idx[/TD] [TD=“align: right”]5[/TD] [TD]NULL[/TD] [TD=“align: right”]2535664[/TD] [TD]Using where[/TD] [/TR] [TR] [TD=“align: right”]1[/TD] [TD]PRIMARY[/TD] [TD]t[/TD] [TD]ref[/TD] [TD]dl\_as\_fm\_tunnel\_master\_tunnel\_id\_idx[/TD] [TD]dl\_as\_fm\_tunnel\_master\_tunnel\_id\_idx[/TD] [TD=“align: right”]8[/TD] [TD]const[/TD] [TD=“align: right”]1[/TD] [TD] [/TD] [/TR] [TR] [TD=“align: right”]2[/TD] [TD]DEPENDENT SUBQUERY[/TD] [TD]b[/TD] [TD]ref[/TD] [TD]dl\_dla\_more\_bar\_codes\_message\_id\_idx,dl\_dla\_more\_bar\_codes\_bar\_code\_number\_idx[/TD] [TD]dl\_dla\_more\_bar\_codes\_message\_id\_idx[/TD] [TD=“align: right”]8[/TD] [TD]func[/TD] [TD=“align: right”]1[/TD] [TD]Using where; Using temporary[/TD] [/TR] [TR] [TD=“align: right”]2[/TD] [TD]DEPENDENT SUBQUERY[/TD] [TD]d[/TD] [TD]ref[/TD] [TD]dl\_dla\_more\_devices\_message\_id\_idx,dl\_dla\_more\_devices\_device\_id\_idx[/TD] [TD]dl\_dla\_more\_devices\_message\_id\_idx[/TD] [TD=“align: right”]8[/TD] [TD]func[/TD] [TD=“align: right”]2[/TD] [TD]Using where[/TD] [/TR] [/TABLE] As per thse, the select\_type is different in outputs, why is it. Does both the terms mean the same or is thr any difference. ---
**Author:** ![Nagini](https://avatars.discourse-cdn.com/v4/letter/n/3ec8ea/32.png) [@Nagini](https://forums.percona.com/u/Nagini)\ **Post date:** [April 30, 2015, 6:32am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/18 "2015-04-30T06:32:28Z")
Also pls suggest what is going wrong from this Explain output. Am really confused. I am not that aware of analyzing the Explain output and to do the changes as per its remarks. ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [April 30, 2015, 9:58am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/19 "2015-04-30T09:58:39Z")
Hi Nagini; I’m a bit confused, as it sounds like you are trying to optimize a query that changes? That would be difficult of course, so you’d have to come up with a strategy to optimize each variation, assuming those variations occurred enough to warrant optimization. Otherwise the best you can do is find the common elements that generally exist in the WHERE clause for all variations and optimize that. As for why it may perform differently on your Windows vs Linux hosts, it would have to do with some difference in the environments. Do they both have the same exact hardware resources allocated? Are their MySQL versions the same? Are their MySQL configurations the same? Is there anything else running on the Linux host that is not running on the Windows host? If I was you I would try to focus on one area at a time. You seem to be trying to wrangle a lot of different things at once, which tends to not work well. When you have too many moving parts you often cannot tell what is causing what and end up going down the wrong path. -Scott ---
**Author:** ![Nisha\_Suresh](https://avatars.discourse-cdn.com/v4/letter/n/aca169/32.png) [@Nisha\_Suresh](https://forums.percona.com/u/Nisha_Suresh)\ **Post date:** [May 4, 2015, 7:11am UTC](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167/20 "2015-05-04T07:11:12Z")
Hi, I am Nisha from the same team as Nagini. Now with the following query we are getting an ordered result in Windows, MySQL Server 5.5 within 3 secs. But in Linux with MySQL Server 5.6 we are getting an ordered result in 155 secs SELECT pd.message\_id, pd.Receipt\_Time\_Stamp, pd.shift\_id, pd.Tunnel\_ID, pd.Package\_Number, pd.Package\_Read\_Status, pd.Iseq\_Number, pd.SxS\_Status, pd.host\_message, pd.package\_gap, pd.Parcel\_Length, pd.Parcel\_Width, pd.Parcel\_Height, pd.Image\_Files, t.tunnel\_name, s.shift\_name FROM fm\_package\_db.dla\_package\_details pd force index(dl\_dla\_package\_details\_receipt\_time\_stamp\_idx) JOIN fm\_local\_db.as\_fm\_tunnel\_master t ON pd.Tunnel\_ID = t.Tunnel\_ID JOIN fm\_local\_db.as\_fm\_shift\_info s ON s.shift\_id = pd.shift\_id where pd.message\_id in (Select distinct b.Message\_ID from dla\_more\_bar\_codes b JOIN fm\_package\_db.dla\_more\_devices d ON b.message\_id = d.message\_id where (b.bar\_code\_number = 1 or b.bar\_code\_number = 2)and bar\_code like ‘%9%’ and (d.device\_id = 2 or d.device\_id = 3 or d.device\_id = 8 or d.device\_id = 1005 or d.device\_id = 1021 or d.device\_id = 1049 or d.device\_id = 1057 or d.device\_id = 1081) ) and pd.receipt\_time\_stamp between ‘2015-03-01 00:00:00’ AND ‘2015-03-30 23:59:59’ and pd.Message\_Type = ‘PackageInfo’ AND (pd.Tunnel\_ID = ‘1’) AND (pd.shift\_id = 1 or pd.shift\_id = 2 or pd.shift\_id = 3) and pd.receipt\_time\_stamp like ‘%2015%’ and pd.Package\_Number like ‘%5%’ and pd.Package\_Read\_Status like ‘%R%’ and pd.Iseq\_Number like ‘%5%’ and pd.host\_message like ‘%5%’ and pd.Parcel\_Length like ‘%0%’ and pd.Parcel\_Width like ‘%0%’ and pd.Parcel\_Height like ‘%0%’ and s.shift\_name like ‘%S%’ and t.tunnel\_name like ‘%T%’ order by pd.receipt\_time\_stamp asc LIMIT 0,25; [Next page](https://forums.percona.com/t/mysql-order-by-on-a-huge-joined-datasets-is-taking-too-long-time/4167.md?page=2)