MySql Order by on a huge joined datasets is taking too long time.

Nagini · April 17, 2015, 4:55am

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

scott.nemes · April 17, 2015, 10:13am

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

Nagini · April 20, 2015, 5:29am

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

Nagini · April 20, 2015, 5:32am

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;

Nagini · April 20, 2015, 6:56am

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.

Nagini · April 20, 2015, 7:58am

as_fm_tunnel_master and as_fm_shift_info are Master tables which has very less data.

Nagini · April 23, 2015, 12:59am

Waiting for the reply… pls help out

scott.nemes · April 23, 2015, 2:11pm

Hi Nagini;

Please post the EXPLAIN output for the query and I’ll take a look.

-Scott

Nagini · April 27, 2015, 2:06am

[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]

Nagini · April 27, 2015, 2:08am

Hi Scott,

Have posted the Explain result of the query, above. Pls have a look n suggest suitable solution

wagnerbianchi · April 27, 2015, 1:33pm

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.


#: 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-t...y-checker.html[/URL]

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]

Nagini · April 28, 2015, 1:22am

[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]

Nagini · April 28, 2015, 1:27am

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.

scott.nemes · April 28, 2015, 12:23pm

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

Nagini · April 30, 2015, 6:00am

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.

Nagini · April 30, 2015, 6:09am

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.

Nagini · April 30, 2015, 6:30am

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]<auto_key>[/TD]
[TD]<auto_key>[/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.

Nagini · April 30, 2015, 6:32am

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.

scott.nemes · April 30, 2015, 9:58am

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

Nisha_Suresh · May 4, 2015, 7:11am

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;

Topic		Replies	Views
ORDER BY ..LIMIT using indexes still slow Other MySQL® Questions	4	570	April 14, 2008
optimize help for order by Other MySQL® Questions	1	340	August 16, 2007
optimization for inner join.. order by.. limit with large order by data Other MySQL® Questions	4	590	January 13, 2010
Order by using an Inner Join not working in Linux environment of Mysql Other MySQL® Questions	1	641	April 23, 2015
Mysql not using index for ORDER BY ? Other MySQL® Questions	2	525	May 13, 2007

MySql Order by on a huge joined datasets is taking too long time.

Related topics