Not the answer you need?
Register and ask your own question!

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

NaginiNagini ContributorCurrent User Role Beginner
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

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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
  • NaginiNagini Contributor Current User Role Beginner
    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
  • NaginiNagini Contributor Current User Role Beginner
    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;
  • NaginiNagini Contributor Current User Role Beginner
    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.
  • NaginiNagini Contributor Current User Role Beginner
    as_fm_tunnel_master and as_fm_shift_info are Master tables which has very less data.
  • NaginiNagini Contributor Current User Role Beginner
    Waiting for the reply... pls help out
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi Nagini;

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

    -Scott
  • NaginiNagini Contributor Current User Role Beginner


    id
    select_type
    table
    type
    possible_keys
    key
    key_len
    ref
    rows
    Extra


    1
    SIMPLE
    pd
    index_merge
    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
    dl_dla_package_details_tunnel_id_idx,dl_dla_package_details_message_type_idx,dl_dla_package_details_site_code_idx
    9,63,63
    NULL
    487453
    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


    1
    SIMPLE
    s
    ref
    PRIMARY,dl_as_fm_shift_info_shift_id_idx
    PRIMARY
    8
    fm_package_db.pd.shift_id
    1



    1
    SIMPLE
    t
    ref
    dl_as_fm_tunnel_master_tunnel_id_idx
    dl_as_fm_tunnel_master_tunnel_id_idx
    8
    const
    1



    1
    SIMPLE
    b
    ref
    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
    dl_dla_more_bar_codes_message_id_idx
    8
    fm_package_db.pd.Message_ID
    1
    Using where; Distinct


    1
    SIMPLE
    d
    ref
    dl_dla_more_devices_message_id_idx,dl_dla_more_devices_device_id_idx
    dl_dla_more_devices_message_id_idx
    8
    fm_package_db.b.Message_ID
    2
    Using where; Distinct

  • NaginiNagini Contributor Current User Role Beginner
    Hi Scott,

    Have posted the Explain result of the query, above. Pls have a look n suggest suitable solution
  • wagnerbianchiwagnerbianchi Remote DBA Current User Role Patron
    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: http://www.percona.com/doc/percona-t...y-checker.html

    Additionally, are you able to provide the profiler of this query?

    http://www.percona.com/blog/2012/02/20/how-to-convert-show-profiles-into-a-real-profile/
  • NaginiNagini Contributor Current User Role Beginner


    4
    12
    Copying to tmp table
    275.547847
    NULL
    NULL
    NULL
    NULL
    NULL
    NULL
    NULL
    NULL
    NULL
    NULL
    NULL
    JOIN::exec
    .\sql_select.cc
    1938

  • NaginiNagini Contributor Current User Role Beginner
    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.nemesscott.nemes MySQL Sage Current User Role Patron
    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
  • NaginiNagini Contributor Current User Role Beginner
    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.
  • NaginiNagini Contributor Current User Role Beginner
    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.
  • NaginiNagini Contributor Current User Role Beginner
    Pls look at the Explain outputs for both Windws and Linux environments of Mysql:

    Explain output for Linux Mysql:


    id
    select_type
    table
    type
    possible_keys
    key
    key_len
    ref
    rows
    Extra


    1
    SIMPLE
    t
    ref
    dl_as_fm_tunnel_master_tunnel_id_idx
    dl_as_fm_tunnel_master_tunnel_id_idx
    8
    const
    1
    Using index condition; Using temporary; Using filesort


    1
    SIMPLE
    pd
    range
    dl_dla_package_details_receipt_time_stamp_idx
    dl_dla_package_details_receipt_time_stamp_idx
    5
    NULL
    1958726
    Using index condition; Using where; Using join buffer (Block Nested Loop)


    1
    SIMPLE
    <subquery2>
    eq_ref
    <auto_key>
    <auto_key>
    8
    fm_package_db.pd.Message_ID
    1
    NULL


    2
    MATERIALIZED
    b
    range
    dl_dla_more_bar_codes_message_id_idx,dl_dla_more_bar_codes_bar_code_number_idx
    dl_dla_more_bar_codes_bar_code_number_idx
    5
    NULL
    1804091
    Using index condition; Using MRR


    2
    MATERIALIZED
    d
    ref
    dl_dla_more_devices_message_id_idx,dl_dla_more_devices_device_id_idx
    dl_dla_more_devices_message_id_idx
    8
    fm_package_db.b.Message_ID
    2
    Using where




    Explain output for Windows:


    id
    select_type
    table
    type
    possible_keys
    key
    key_len
    ref
    rows
    Extra


    1
    PRIMARY
    pd
    range
    dl_dla_package_details_receipt_time_stamp_idx
    dl_dla_package_details_receipt_time_stamp_idx
    5
    NULL
    2535664
    Using where


    1
    PRIMARY
    t
    ref
    dl_as_fm_tunnel_master_tunnel_id_idx
    dl_as_fm_tunnel_master_tunnel_id_idx
    8
    const
    1



    2
    DEPENDENT SUBQUERY
    b
    ref
    dl_dla_more_bar_codes_message_id_idx,dl_dla_more_bar_codes_bar_code_number_idx
    dl_dla_more_bar_codes_message_id_idx
    8
    func
    1
    Using where; Using temporary


    2
    DEPENDENT SUBQUERY
    d
    ref
    dl_dla_more_devices_message_id_idx,dl_dla_more_devices_device_id_idx
    dl_dla_more_devices_message_id_idx
    8
    func
    2
    Using where





    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.
  • NaginiNagini Contributor Current User Role Beginner
    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.nemesscott.nemes MySQL Sage Current User Role Patron
    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 SureshNisha Suresh Entrant Current User Role Beginner
    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;
  • Nisha SureshNisha Suresh Entrant Current User Role Beginner
    We have done profiling in both the instance and got the result like below:
    In Windows with MySQL5.5:

    Status
    Duration
    Source_function
    Source_file
    Source_line


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000018
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000008
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000015
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000043
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000013
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000024
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000007
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000006
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000013
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000013
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000013
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000022
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000013
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000022
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000013
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000007
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000007
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000013
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000013
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000013
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000007
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000007
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000013
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000023
    JOIN::exec
    .\sql_select.cc
    2349


    executing
    0.000001
    JOIN::exec
    .\sql_select.cc
    1791


    Copying to tmp table
    0.000012
    JOIN::exec
    .\sql_select.cc
    1938


    Sending data
    0.000031
    JOIN::exec
    .\sql_select.cc
    2349


    end
    0.000003
    mysql_select
    .\sql_select.cc
    2585


    removing tmp table
    0.000005
    free_tmp_table
    .\sql_select.cc
    10929


    end
    0.000002
    free_tmp_table
    .\sql_select.cc
    10954


    query end
    0.000002
    mysql_execute_command
    .\sql_parse.cc
    5132


    freeing items
    0.000162
    mysql_parse
    .\sql_parse.cc
    6156


    logging slow query
    0.000001
    log_slow_statement
    .\sql_parse.cc
    1744


    cleaning up
    0.000005
    dispatch_command
    .\sql_parse.cc
    1712

  • Nisha SureshNisha Suresh Entrant Current User Role Beginner
    In Linux with MySQL 5.6 server the result is:

    Status
    Duration
    Source_function
    Source_file
    Source_line


    starting
    0.000237
    NULL
    NULL
    NULL


    checking permissions
    0.00001
    check_access
    sql_parse.cc
    5302


    checking permissions
    0.000005
    check_access
    sql_parse.cc
    5302


    checking permissions
    0.000006
    check_access
    sql_parse.cc
    5302


    checking permissions
    0.000005
    check_access
    sql_parse.cc
    5302


    checking permissions
    0.000008
    check_access
    sql_parse.cc
    5302


    Opening tables
    0.000042
    open_tables
    sql_base.cc
    5017


    init
    0.000164
    mysql_prepare_select
    sql_select.cc
    1050


    System lock
    0.00002
    mysql_lock_tables
    lock.cc
    304


    optimizing
    0.000067
    optimize
    sql_optimizer.cc
    138


    statistics
    0.000572
    optimize
    sql_optimizer.cc
    362


    preparing
    0.000085
    optimize
    sql_optimizer.cc
    485


    Creating tmp table
    0.00005
    create_intermediate_table
    sql_executor.cc
    210


    Sorting result
    0.000008
    make_tmp_tables_info
    sql_select.cc
    5248


    executing
    0.000007
    exec
    sql_executor.cc
    110


    Sending data
    155.054843
    exec
    sql_executor.cc
    190


    Creating sort index
    0.801326
    sort_table
    sql_executor.cc
    2504


    end
    0.000029
    mysql_execute_select
    sql_select.cc
    1105


    removing tmp table
    0.00012
    free_tmp_table
    sql_tmp_table.cc
    1868


    end
    0.000008
    free_tmp_table
    sql_tmp_table.cc
    1897


    query end
    0.000009
    mysql_execute_command
    sql_parse.cc
    5001


    closing tables
    0.000028
    mysql_execute_command
    sql_parse.cc
    5049


    freeing items
    0.035736
    mysql_parse
    sql_parse.cc
    6438


    logging slow query
    0.000031
    log_slow_do
    sql_parse.cc
    1879


    cleaning up
    0.000028
    dispatch_command
    sql_parse.cc
    1783



    Both the instance has the same data. It seems like sending data is taking more time in Linux MySQL 5.6. Is the difference because of MySQL Versions or OS Difference.

    Please help to resolve this.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi Nisha;

    My guess would be that your disks are a lot slower on the Linux 5.6 server. Aside from that, you basically are comparing apples and oranges, so that is difficult. You are comparing Linux vs Windows, 5.5 vs 5.6, and two entirely different servers of unknown configuration.

    So I'd look for what the big server differences are, like disks/memory/CPU. You could also install 5.5 on the Linux server to see if it performs the same, or 5.6 on the Windows server. That will confirm if it's the server itself, which is likely.

    -Scott
  • Nisha SureshNisha Suresh Entrant Current User Role Beginner
    Hi Scott,

    Please find the system comparison as follows:

    Edition
    RAM
    System Type
    MySQL Version


    Windows 7 Proffessional
    4 GB
    32 bit
    5.5.35


    Fedora 2.0
    1.9 GiB
    64 Bit
    5.6.24



    One thing we noticed is in Linux Machine without order by the query is taking less than 3 secs and with order by the query is taking 145 secs.

    While in profiling it says "sending data" is taking 144 secs

    Nisha
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi Nisha;

    Your Linux host is likely quite a bit underpowered compared to your Windows host. All you've given us so far is the memory, which is half the size on Linux. So the linux side probably has less CPU and and slower disks as well. So that is one issue. The other issue is that your Windows MySQL configuration is likely granting MySQL more resources to start with. So at very least you need to compare the configs and see what the differences are. Particularly your innodb_buffer_pool_size, sort_buffer_size, and read_rnd_buffer_size. The ORDER BY slows it down a lot because it is not optimize and likely has little resources to work with to do the manual processing needed.

    Once you get the system problems sorted out, you need to determine what queries you are actually trying to optimize. I gave you some examples above, but you said the WHERE clause will change, and you cannot optimize a moving target like that. Identify what queries are slow and try to index them. Once they run faster, identify the next slow bunch, and so on. If you have many queries that have different WHERE clauses but share a few common fields in the WHERE clause, try optimizing those matching columns to give you the benefit over more query footprints.

    -Scott
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.