2024-03-25 22:25:37 PDT - Karthik Rayala (Public comment)
Please find the Explain plan and table structure


MariaDB [spider_2repmote_nodes]> show create table lots\G
*************************** 1. row ***************************
Table: lots
Create Table: CREATE TABLE `lots` (
`lot_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`lot_num` bigint(20) NOT NULL COMMENT 'Lot Number',
`cntry_cd` varchar(3) DEFAULT 'USA' COMMENT 'Country Code',
`lot_desc` varchar(255) NOT NULL COMMENT 'Lot Description',
`slr_comp_cd` varchar(50) DEFAULT NULL COMMENT 'Seller Company Code',
`slr_num` varchar(20) DEFAULT NULL COMMENT 'Seller Number',
`csh_tag` varchar(50) DEFAULT NULL COMMENT 'Cash Tag',
`dmg_cd` varchar(10) DEFAULT NULL COMMENT 'Damage Code',
`lot_stg` bigint(10) DEFAULT NULL COMMENT 'Lot Stage',
`lot_status` varchar(50) DEFAULT NULL COMMENT 'Lot Status',
`loss_type_cd` varchar(100) DEFAULT NULL COMMENT 'Loss type Code',
`lot_type` varchar(50) DEFAULT NULL,
`lot_model_year` int(11) DEFAULT NULL,
`make` varchar(30) DEFAULT NULL COMMENT 'Make of the Lot',
`model` varchar(50) DEFAULT NULL COMMENT 'Model of the Lot',
`color` varchar(50) DEFAULT NULL COMMENT 'Color',
`vin_num` varchar(20) DEFAULT NULL COMMENT 'VIN Number',
`mbr_num` varchar(100) DEFAULT NULL COMMENT 'Member Number',
`clm_num` varchar(100) DEFAULT NULL COMMENT 'Claim Number',
`odo_rdng` int(11) DEFAULT NULL COMMENT 'Odometer Reading',
`parent_lot_num` int(11) DEFAULT NULL COMMENT 'Parent Lot Number',
`assign_entry_dt` datetime DEFAULT NULL COMMENT 'Assignment Entry Date',
`loss_date` date DEFAULT NULL COMMENT 'Loss Date',
`qr_cd_uid` varchar(100) DEFAULT NULL COMMENT 'Gate Pass UID',
`qr_cd` varchar(10) DEFAULT NULL COMMENT 'Gate Pass QR Code',
`qr_cd_add_dt` datetime DEFAULT NULL COMMENT 'Gate Pass QR Code Added Date',
`qr_cd_exp_dt` datetime DEFAULT NULL COMMENT 'Gate Pass QR Code Expiry Date',
`haskey_cd` char(1) DEFAULT NULL COMMENT 'Lot Has Keys - Yes, No or Uknown',
`resp_fclty_id` int(11) NOT NULL COMMENT 'Responsible Facility/Yard ID',
`status` char(1) NOT NULL DEFAULT 'A' COMMENT 'Value is A or I, to indicate status is Active or Inactive',
`crt_dt` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Creation Timestamp',
`crt_user` varchar(255) NOT NULL COMMENT 'Creation User',
`last_upd_dt` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Last Update Timestamp',
`last_upd_user` varchar(255) NOT NULL COMMENT 'Last Update User',
`source_sys` varchar(255) DEFAULT '' COMMENT 'Source from which record came from',
`lot_suffix` varchar(50) DEFAULT NULL COMMENT 'Lot Suffix',
`calc_odo_brnd` varchar(10) DEFAULT NULL COMMENT 'Calculated Odometer Brand',
`close_type_cd` varchar(10) DEFAULT NULL COMMENT 'Close Type Code',
`lot_stg_desc` varchar(255) DEFAULT NULL COMMENT 'Lot Stage Description',
`diy_start_date` date DEFAULT NULL COMMENT 'Days in Yard Start Date',
`slr_bid_apprv_flg` tinyint(4) DEFAULT NULL COMMENT 'Seller Bid approval flag',
`slr_btba_req_flg` tinyint(4) DEFAULT NULL COMMENT 'Bid to be approved Required? (Y/N)',
`old_lot_num` bigint(20) DEFAULT NULL COMMENT 'Old Lot Number',
`phy_fclty_id` int(11) DEFAULT NULL COMMENT 'Physical Facility/Yard ID',
`pkup_dspch_assgn_dtl_id` bigint(20) DEFAULT NULL COMMENT 'Pickup Dispatch Assignment Detail Id',
`dlvry_dspch_assgn_dtl_id` bigint(20) DEFAULT NULL COMMENT 'Delivery Dispatch Assignment Detail Id',
`chrgs_clrd_dt` datetime DEFAULT NULL COMMENT 'Charges Cleared Date',
`prmsd_dt` datetime DEFAULT NULL COMMENT 'Promised Date',
`pkup_clrd_dt` datetime DEFAULT NULL COMMENT 'Pickup Cleared Date',
`pkup_dt` datetime DEFAULT NULL COMMENT 'Picked-Up Datetime',
`check_in_dt` datetime DEFAULT NULL COMMENT 'Check-In Date',
`rcvd_dt` datetime DEFAULT NULL COMMENT 'Received Date',
`pkup_req_flg` tinyint(4) DEFAULT NULL COMMENT 'Pickup Required Flag',
`proquote_amt` decimal(12,5) DEFAULT NULL COMMENT 'Proquote Amount',
`dlvry_dt` datetime DEFAULT NULL COMMENT 'Vehicle delivery datetime',
`cprt_slct_cd` varchar(10) DEFAULT NULL COMMENT 'N - Not Copart Select, P - Potential Copart Select, C - Copart Select, Default - Blank/Empty',
`lot_clsd_dt` datetime DEFAULT NULL COMMENT 'Lot Closed DateTime',
`slr_lot_desc` varchar(255) DEFAULT NULL COMMENT 'Seller Lot Description',
`has_trlr_flg` tinyint(4) DEFAULT NULL COMMENT 'Has Trailer Flag- 1/0/NULL',
`lnkd_lot_num` bigint(20) DEFAULT NULL COMMENT 'Linked Lot Number',
`mnl_cprt_slct_flg` tinyint(4) DEFAULT 0 COMMENT 'Manual Copart Select Flag',
`lly_dt` datetime DEFAULT NULL COMMENT 'Stamp LLY date to record lot level phenomenon in UTC',
`y2y_flg` tinyint(4) DEFAULT NULL COMMENT 'Yard 2 Yard Requested Lot',
`transit_facility_id` int(11) DEFAULT NULL COMMENT 'Transit to Facility/Yard ID',
`sale_lght_cd` int(20) DEFAULT NULL COMMENT 'Sale Light Code:0 - No Light, 1 - Green, 2 - Yellow',
`mnl_sale_lght_ovrd_flg` tinyint(4) DEFAULT NULL COMMENT 'Manual Sale Light Override Flag',
`rcvd_by` varchar(255) DEFAULT NULL COMMENT 'Receieved By',
`eng_dspl` varchar(10) DEFAULT NULL COMMENT 'Engine Displacement',
`slr_insp_flg` tinyint(4) DEFAULT 0 COMMENT 'Seller Inspection Flag',
`trans_type_cd` varchar(30) DEFAULT NULL,
PRIMARY KEY (`lot_id`),
UNIQUE KEY `lots_lot_num_IDX` (`lot_num`),
KEY `lots_cntry_cd_IDX` (`cntry_cd`),
KEY `lots_lot_stg_IDX` (`lot_stg`),
KEY `lots_vin_num_IDX` (`vin_num`),
KEY `lots_qr_cd_IDX` (`qr_cd`),
KEY `lots_clm_num_IDX` (`clm_num`),
KEY `lots_resp_fclty_id_lot_stg_IDX` (`resp_fclty_id`,`lot_stg`),
KEY `IDX_lots_01` (`status`,`lot_stg`,`resp_fclty_id`,`lot_num`),
KEY `lots_prmsd_dt_IDX` (`prmsd_dt`),
KEY `lots_assign_entry_dt_IDX` (`assign_entry_dt`),
KEY `lots_last_upd_dt_IDX` (`last_upd_dt`),
KEY `lots_slr_comp_cd_IDX` (`slr_comp_cd`),
KEY `lots_lnkd_lot_num_idx` (`lnkd_lot_num`),
KEY `IDX_lots_IX01` (`lot_stg`,`lot_num`),
KEY `IDX_lots_IX02` (`phy_fclty_id`),
KEY `IDX_lots_IX03` (`lot_status`,`cntry_cd`,`lot_clsd_dt`),
KEY `lots_lly_dt_IDX` (`lly_dt`),
KEY `IDX_lots_IX05` (`phy_fclty_id`),
KEY `lot_images_06_IDX` (`lot_suffix`),
KEY `idx_lots_19` (`lot_clsd_dt`,`lot_stg`),
KEY `IDX_lots_IX20` (`old_lot_num`)
) ENGINE=SPIDER AUTO_INCREMENT=146125609 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='server "spider1", table "lots"'
1 row in set (0.000 sec)

MariaDB [spider_2repmote_nodes]>
MariaDB [spider_2repmote_nodes]>
MariaDB [spider_2repmote_nodes]> show create table lot_extension\G
*************************** 1. row ***************************
Table: lot_extension
Create Table: CREATE TABLE `lot_extension` (
`lot_num` bigint(20) NOT NULL,
`fclty_row` varchar(50) DEFAULT NULL COMMENT 'Yard Row',
`rcvd_date` date DEFAULT NULL,
`rcvd_by` varchar(255) DEFAULT NULL COMMENT 'Lot Received by Employee',
`lic_plt` varchar(50) DEFAULT NULL COMMENT 'License Plate',
`lic_plt_state_cd` varchar(10) DEFAULT NULL COMMENT 'License Plate State Code',
`lic_plt_cntry_cd` varchar(10) DEFAULT NULL COMMENT 'License Plate Country Code',
`lic_plt_exp_mmyy` varchar(10) DEFAULT NULL COMMENT 'License Plate Expiry Month and Year',
`assign_src_cd` varchar(10) DEFAULT NULL COMMENT 'Assignment Source',
`sec_dmg_cd` varchar(10) DEFAULT NULL COMMENT 'Secondary Damage Code',
`chrgs_clrd_dt` date DEFAULT NULL COMMENT 'Cleared for Charges Date',
`pickup_clrd_dt` date DEFAULT NULL COMMENT 'Cleared for Pickup Date',
`pickedup_dt` datetime DEFAULT NULL COMMENT 'Pickup Verified Datetime',
`qr_cd_uid` varchar(100) DEFAULT NULL COMMENT 'Gate Pass UID',
`qr_cd` varchar(10) DEFAULT NULL COMMENT 'Gate Pass QR Code',
`qr_cd_add_dt` datetime DEFAULT NULL COMMENT 'Gate Pass QR Code Added Date',
`qr_cd_exp_dt` datetime DEFAULT NULL COMMENT 'Gate Pass QR Code Expiry Date',
`qr_uid_exp_dt` datetime DEFAULT NULL COMMENT 'Gate Pass QR UID Expiry Date',
`status` varchar(1) NOT NULL DEFAULT 'A',
`crt_dt` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Creation Timestamp',
`crt_user` varchar(255) NOT NULL COMMENT 'Creation User',
`last_upd_dt` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Last Update Timestamp',
`last_upd_user` varchar(255) NOT NULL COMMENT 'Last Update User',
`source_sys` varchar(255) DEFAULT NULL COMMENT 'Source from which record came from',
`veh_size_cd` varchar(10) DEFAULT NULL COMMENT 'Vehicle Size',
`body_style` varchar(255) DEFAULT NULL COMMENT 'Body Style',
`fuel_type` varchar(255) DEFAULT NULL COMMENT 'Fuel Type',
`blucar_flg` tinyint(4) DEFAULT NULL COMMENT 'Blue Car Flag',
`abi_cat_cd` varchar(10) DEFAULT NULL COMMENT 'Lot Category Code',
`veh_type` varchar(10) DEFAULT NULL COMMENT 'Vehicle Type',
`sale_ttl_rcvd_dt` datetime DEFAULT NULL COMMENT 'Sale Title Received Date from DMV',
`auc_dt` date DEFAULT NULL COMMENT 'Auction Date',
`drive_status` char(1) DEFAULT NULL COMMENT 'Drive Status',
`nmvtis_cd` char(1) DEFAULT NULL COMMENT 'NMVTIS Code',
`fut_pkup_date` date DEFAULT NULL COMMENT 'Future Pickup Date',
`fut_pkup_flg` tinyint(4) DEFAULT NULL COMMENT 'Future Pickup Flag',
`clr_pkup_flg` tinyint(4) DEFAULT NULL COMMENT 'Cleared for Pickup Flag',
`ttl_avail_flg` tinyint(4) DEFAULT NULL COMMENT 'Title Available Flag',
`owner_id` bigint(20) DEFAULT NULL COMMENT 'Owner ID',
`insrd_id` bigint(20) DEFAULT NULL COMMENT 'Insured ID',
`rcvry_dt` date DEFAULT NULL COMMENT 'Recovery Date',
`pkup_hld_dt` date DEFAULT NULL COMMENT 'Pickup Hold Date',
`pkup_hld_rsn` varchar(1000) DEFAULT NULL COMMENT 'Pickup Hold Reason',
`assgn_truck_type_cd` varchar(10) DEFAULT NULL COMMENT 'Assignment Truck Type',
`bal_due_amt` decimal(22,7) DEFAULT NULL COMMENT 'Balnce Due Amount',
`pkup_site_id` bigint(20) DEFAULT NULL COMMENT 'Pickup Site ID',
`dlvry_site_id` bigint(20) DEFAULT NULL COMMENT 'Delivery Site ID',
`pol_num` varchar(50) DEFAULT NULL COMMENT 'Policy Number',
`lot_stg_desc` varchar(255) DEFAULT NULL COMMENT 'Lot Stage Description',
`sale_date` date DEFAULT NULL COMMENT 'Sale Date',
`acv` decimal(22,7) DEFAULT NULL COMMENT 'Actual Cash Value',
`repr_cost` decimal(22,7) DEFAULT NULL COMMENT 'Repair Cost',
`hld_sale_flg` tinyint(1) DEFAULT NULL COMMENT 'Hold For Sale Flag',
`plnd_auc_date` date DEFAULT NULL COMMENT 'Planned Auction/Sale date',
`auc_event` int(11) DEFAULT NULL COMMENT 'To track the generated events',
`has_vin` char(1) DEFAULT NULL COMMENT 'Has VIN Plate',
`ttl_only_flg` tinyint(1) DEFAULT NULL COMMENT 'Title Only Flag',
`ownr_rtn_flg` tinyint(1) DEFAULT NULL COMMENT 'Owner Retain Flag',
`legal_sale_date` date DEFAULT NULL COMMENT 'Legal Sale Date',
`sale_facility_id` bigint(20) DEFAULT NULL COMMENT 'Sale Facility Number',
`offsite_flg` tinyint(4) DEFAULT NULL COMMENT 'Offsite Flag',
`hld_sale_actn_date` date DEFAULT NULL COMMENT 'Hold for Sale Action Date',
`slr_hld_flg` tinyint(4) DEFAULT NULL COMMENT 'Seller Hold Flag',
`img_360_flg` tinyint(4) DEFAULT NULL COMMENT 'Image 360 Flag',
`btba_flg` tinyint(4) DEFAULT NULL COMMENT 'Bid to be approved Flag',
`txt_ownr_flg` tinyint(4) DEFAULT NULL COMMENT 'Flag For Sending Text Message To Owner',
`proceeds_dt` datetime DEFAULT NULL COMMENT 'Proceeds date time',
`assgn_to_auc` char(1) DEFAULT NULL COMMENT 'Assign Auction Date - Yes, No, Unassigned',
`inv_dt` datetime DEFAULT NULL COMMENT 'Inventory date',
`slr_ref_num` varchar(50) DEFAULT NULL COMMENT 'Seller Reference Number',
`min_bid` decimal(22,7) DEFAULT NULL COMMENT 'Minimum Bid',
`stlmnt_dt` datetime DEFAULT NULL COMMENT 'Settlement Datetime',
`cat_id` bigint(20) DEFAULT NULL COMMENT 'Cat Event Id',
`lot_slvg_type` char(1) DEFAULT NULL COMMENT 'Lot Salvage Type',
`nmvtis_status_cd` varchar(20) DEFAULT NULL COMMENT 'NMVTIS Status Code',
`per_licplt_flg` tinyint(4) DEFAULT NULL COMMENT 'Flag For Personal License Plate Flag',
`num_lic_plts` int(11) DEFAULT NULL COMMENT 'Number or License Plates',
`per_lic_plt` varchar(10) DEFAULT NULL COMMENT 'Personal License',
`lic_plt_dstrd` varchar(10) DEFAULT NULL COMMENT 'License Plate Destroyed',
`lic_plt_status` varchar(10) DEFAULT NULL COMMENT 'License Plate Status',
`nmvtis_last_eval_dt` datetime DEFAULT NULL COMMENT 'NMVTIS last evaluation date',
`nmvtis_eval_rsn` varchar(500) DEFAULT NULL COMMENT 'NMVTIS Evaluation Reason',
`m1_rcvd_dt` datetime DEFAULT NULL COMMENT 'M1 received Date',
`plc_rlsd_flg` tinyint(1) DEFAULT 0 COMMENT 'Released by Police flag',
`key_loc` varchar(10) DEFAULT NULL COMMENT 'Location of the Key - Office, Lot, etc',
`ftlty_flg` tinyint(4) DEFAULT NULL COMMENT 'Fatality Flag',
`ftlty_cmnt` varchar(1000) DEFAULT NULL COMMENT 'Fatality Comments',
`reg_cert_num` varchar(255) DEFAULT NULL COMMENT 'Registration Certification - V5C Log Book for UK',
`reg_cert_flg` tinyint(4) DEFAULT NULL COMMENT 'Registration Certification Flag - V5C Log Book for UK',
`clmnt_flg` tinyint(4) DEFAULT NULL COMMENT 'Claimant Flag',
`adv_chrg_discr_flg` tinyint(4) DEFAULT 0 COMMENT 'Advance Charges Discrepancy Flag',
`tow_type_cd` varchar(50) DEFAULT NULL COMMENT 'Tow Type Code',
`plnd_auc_by` varchar(255) DEFAULT NULL COMMENT 'Name of the user who planned the auction date',
`confm_code` bigint(20) DEFAULT NULL COMMENT 'Confirmation Code',
`ca_clrd_dt` datetime DEFAULT NULL COMMENT 'Clearance Datetime',
`spcl_hndlng_flg` tinyint(1) DEFAULT NULL COMMENT 'special handling flag',
`adj_cntct_pref_type` varchar(20) DEFAULT NULL COMMENT 'Adjustment Contact Preference type',
`spcl_notes` varchar(5000) DEFAULT NULL COMMENT 'Special Notes',
`is_acv_plgd_flg` tinyint(4) DEFAULT NULL COMMENT 'Is ACV Plugged Flag',
`plgd_acv` decimal(9,2) DEFAULT NULL COMMENT 'Plugged ACV',
`is_acv_req_flg` tinyint(4) DEFAULT NULL COMMENT 'Is ACV Required Flag',
`has_livery_flg` tinyint(4) DEFAULT NULL COMMENT 'Livery Flag',
`has_strpped_parts` tinyint(4) DEFAULT NULL COMMENT 'Stripped Parts Flag',
`tyres_inflated_flg` tinyint(4) DEFAULT NULL COMMENT 'Tyres Inflated Flag',
`has_sprwhl_flg` tinyint(4) DEFAULT NULL COMMENT 'Spare Wheel Flag',
`has_chrg_cbl_flg` tinyint(4) DEFAULT NULL COMMENT 'Charge Cable Flag',
`has_prcl_shlf_flg` tinyint(4) DEFAULT NULL COMMENT 'Parcel Shelf Flag',
`foldawy_add_seat_flg` tinyint(4) DEFAULT NULL COMMENT 'Fold Away Additional Seat Flag',
`v5_prsnt_flg` tinyint(4) DEFAULT NULL COMMENT 'vFive Present Flag',
`prsnl_bag_filled_flg` tinyint(4) DEFAULT NULL COMMENT 'Personal In Bag Filled Flag',
`prsnl_eff_bag_filled_flg` tinyint(4) DEFAULT NULL COMMENT 'Personal Eff Bag Filled Flag',
`sd_card_flg` tinyint(4) DEFAULT NULL COMMENT 'SD Card Flag',
`rear_media_pkg_flg` tinyint(4) DEFAULT NULL COMMENT 'Rear Media Package Flag',
`seven_seats_flg` tinyint(4) DEFAULT NULL COMMENT 'Seven Seats Flag',
`floor_mat_flg` tinyint(4) DEFAULT NULL COMMENT 'Floor Mar Flag',
`msg_entmnt_flg` tinyint(4) DEFAULT NULL COMMENT 'Missing Entertainment Flag',
PRIMARY KEY (`lot_num`),
KEY `lot_extension_last_upd_dt_IDX` (`last_upd_dt`),
KEY `lot_extn_lic_plt_IDX` (`lic_plt`),
KEY `lot_extn_pickedup_dt_IDX` (`pickedup_dt`),
KEY `lot_extension_nmvtis_last_eval_dt_IDX` (`nmvtis_last_eval_dt`),
KEY `lot_extn_auc_dt_IDX` (`auc_dt`),
KEY `IDX_lot_extension_IX02` (`nmvtis_status_cd`,`lot_num`),
KEY `IDX_lot_extension_IX03` (`fclty_row`),
KEY `IDX_lot_extension_IX04` (`cat_id`),
KEY `IDX_lot_extension_IX05` (`dlvry_site_id`),
KEY `IDX_lot_extension_IX06` (`pkup_site_id`),
KEY `IDX_lot_extension_IX07` (`blucar_flg`)
) ENGINE=SPIDER DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='server "spider2", table "lot_extension"'
1 row in set (0.000 sec)

MariaDB [spider_2repmote_nodes]>
MariaDB [spider_2repmote_nodes]>
MariaDB [spider_2repmote_nodes]>
MariaDB [spider_2repmote_nodes]> explain select count(1) from lots l inner join lot_extension le on l.lot_num = le.lot_num ;
+------+-------------+-------+--------+------------------+------------------+---------+---------------------------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+------------------+------------------+---------+---------------------------------+----------+-------------+
| 1 | SIMPLE | l | index | lots_lot_num_IDX | lots_lot_num_IDX | 8 | NULL | 35851118 | Using index |
| 1 | SIMPLE | le | eq_ref | PRIMARY | PRIMARY | 8 | spider_2repmote_nodes.l.lot_num | 1 | Using index |
+------+-------------+-------+--------+------------------+------------------+---------+---------------------------------+----------+-------------+
2 rows in set (0.001 sec)

MariaDB [spider_2repmote_nodes]> explain select count(1) from lots l inner join lot_extension le on l.lot_num = le.lot_num \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: l
type: index
possible_keys: lots_lot_num_IDX
key: lots_lot_num_IDX
key_len: 8
ref: NULL
rows: 35851118
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: le
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: spider_2repmote_nodes.l.lot_num
rows: 1
Extra: Using index
2 rows in set (0.000 sec)

MariaDB [spider_2repmote_nodes]>



2024-03-25 22:25:37 PDT - Karthik Rayala (Public comment)
Please find the Explain plan and table structure


MariaDB [spider_2repmote_nodes]> show create table lots\G
*************************** 1. row ***************************
Table: lots
Create Table: CREATE TABLE `lots` (
`lot_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`lot_num` bigint(20) NOT NULL COMMENT 'Lot Number',
`cntry_cd` varchar(3) DEFAULT 'USA' COMMENT 'Country Code',
`lot_desc` varchar(255) NOT NULL COMMENT 'Lot Description',
`slr_comp_cd` varchar(50) DEFAULT NULL COMMENT 'Seller Company Code',
`slr_num` varchar(20) DEFAULT NULL COMMENT 'Seller Number',
`csh_tag` varchar(50) DEFAULT NULL COMMENT 'Cash Tag',
`dmg_cd` varchar(10) DEFAULT NULL COMMENT 'Damage Code',
`lot_stg` bigint(10) DEFAULT NULL COMMENT 'Lot Stage',
`lot_status` varchar(50) DEFAULT NULL COMMENT 'Lot Status',
`loss_type_cd` varchar(100) DEFAULT NULL COMMENT 'Loss type Code',
`lot_type` varchar(50) DEFAULT NULL,
`lot_model_year` int(11) DEFAULT NULL,
`make` varchar(30) DEFAULT NULL COMMENT 'Make of the Lot',
`model` varchar(50) DEFAULT NULL COMMENT 'Model of the Lot',
`color` varchar(50) DEFAULT NULL COMMENT 'Color',
`vin_num` varchar(20) DEFAULT NULL COMMENT 'VIN Number',
`mbr_num` varchar(100) DEFAULT NULL COMMENT 'Member Number',
`clm_num` varchar(100) DEFAULT NULL COMMENT 'Claim Number',
`odo_rdng` int(11) DEFAULT NULL COMMENT 'Odometer Reading',
`parent_lot_num` int(11) DEFAULT NULL COMMENT 'Parent Lot Number',
`assign_entry_dt` datetime DEFAULT NULL COMMENT 'Assignment Entry Date',
`loss_date` date DEFAULT NULL COMMENT 'Loss Date',
`qr_cd_uid` varchar(100) DEFAULT NULL COMMENT 'Gate Pass UID',
`qr_cd` varchar(10) DEFAULT NULL COMMENT 'Gate Pass QR Code',
`qr_cd_add_dt` datetime DEFAULT NULL COMMENT 'Gate Pass QR Code Added Date',
`qr_cd_exp_dt` datetime DEFAULT NULL COMMENT 'Gate Pass QR Code Expiry Date',
`haskey_cd` char(1) DEFAULT NULL COMMENT 'Lot Has Keys - Yes, No or Uknown',
`resp_fclty_id` int(11) NOT NULL COMMENT 'Responsible Facility/Yard ID',
`status` char(1) NOT NULL DEFAULT 'A' COMMENT 'Value is A or I, to indicate status is Active or Inactive',
`crt_dt` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Creation Timestamp',
`crt_user` varchar(255) NOT NULL COMMENT 'Creation User',
`last_upd_dt` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Last Update Timestamp',
`last_upd_user` varchar(255) NOT NULL COMMENT 'Last Update User',
`source_sys` varchar(255) DEFAULT '' COMMENT 'Source from which record came from',
`lot_suffix` varchar(50) DEFAULT NULL COMMENT 'Lot Suffix',
`calc_odo_brnd` varchar(10) DEFAULT NULL COMMENT 'Calculated Odometer Brand',
`close_type_cd` varchar(10) DEFAULT NULL COMMENT 'Close Type Code',
`lot_stg_desc` varchar(255) DEFAULT NULL COMMENT 'Lot Stage Description',
`diy_start_date` date DEFAULT NULL COMMENT 'Days in Yard Start Date',
`slr_bid_apprv_flg` tinyint(4) DEFAULT NULL COMMENT 'Seller Bid approval flag',
`slr_btba_req_flg` tinyint(4) DEFAULT NULL COMMENT 'Bid to be approved Required? (Y/N)',
`old_lot_num` bigint(20) DEFAULT NULL COMMENT 'Old Lot Number',
`phy_fclty_id` int(11) DEFAULT NULL COMMENT 'Physical Facility/Yard ID',
`pkup_dspch_assgn_dtl_id` bigint(20) DEFAULT NULL COMMENT 'Pickup Dispatch Assignment Detail Id',
`dlvry_dspch_assgn_dtl_id` bigint(20) DEFAULT NULL COMMENT 'Delivery Dispatch Assignment Detail Id',
`chrgs_clrd_dt` datetime DEFAULT NULL COMMENT 'Charges Cleared Date',
`prmsd_dt` datetime DEFAULT NULL COMMENT 'Promised Date',
`pkup_clrd_dt` datetime DEFAULT NULL COMMENT 'Pickup Cleared Date',
`pkup_dt` datetime DEFAULT NULL COMMENT 'Picked-Up Datetime',
`check_in_dt` datetime DEFAULT NULL COMMENT 'Check-In Date',
`rcvd_dt` datetime DEFAULT NULL COMMENT 'Received Date',
`pkup_req_flg` tinyint(4) DEFAULT NULL COMMENT 'Pickup Required Flag',
`proquote_amt` decimal(12,5) DEFAULT NULL COMMENT 'Proquote Amount',
`dlvry_dt` datetime DEFAULT NULL COMMENT 'Vehicle delivery datetime',
`cprt_slct_cd` varchar(10) DEFAULT NULL COMMENT 'N - Not Copart Select, P - Potential Copart Select, C - Copart Select, Default - Blank/Empty',
`lot_clsd_dt` datetime DEFAULT NULL COMMENT 'Lot Closed DateTime',
`slr_lot_desc` varchar(255) DEFAULT NULL COMMENT 'Seller Lot Description',
`has_trlr_flg` tinyint(4) DEFAULT NULL COMMENT 'Has Trailer Flag- 1/0/NULL',
`lnkd_lot_num` bigint(20) DEFAULT NULL COMMENT 'Linked Lot Number',
`mnl_cprt_slct_flg` tinyint(4) DEFAULT 0 COMMENT 'Manual Copart Select Flag',
`lly_dt` datetime DEFAULT NULL COMMENT 'Stamp LLY date to record lot level phenomenon in UTC',
`y2y_flg` tinyint(4) DEFAULT NULL COMMENT 'Yard 2 Yard Requested Lot',
`transit_facility_id` int(11) DEFAULT NULL COMMENT 'Transit to Facility/Yard ID',
`sale_lght_cd` int(20) DEFAULT NULL COMMENT 'Sale Light Code:0 - No Light, 1 - Green, 2 - Yellow',
`mnl_sale_lght_ovrd_flg` tinyint(4) DEFAULT NULL COMMENT 'Manual Sale Light Override Flag',
`rcvd_by` varchar(255) DEFAULT NULL COMMENT 'Receieved By',
`eng_dspl` varchar(10) DEFAULT NULL COMMENT 'Engine Displacement',
`slr_insp_flg` tinyint(4) DEFAULT 0 COMMENT 'Seller Inspection Flag',
`trans_type_cd` varchar(30) DEFAULT NULL,
PRIMARY KEY (`lot_id`),
UNIQUE KEY `lots_lot_num_IDX` (`lot_num`),
KEY `lots_cntry_cd_IDX` (`cntry_cd`),
KEY `lots_lot_stg_IDX` (`lot_stg`),
KEY `lots_vin_num_IDX` (`vin_num`),
KEY `lots_qr_cd_IDX` (`qr_cd`),
KEY `lots_clm_num_IDX` (`clm_num`),
KEY `lots_resp_fclty_id_lot_stg_IDX` (`resp_fclty_id`,`lot_stg`),
KEY `IDX_lots_01` (`status`,`lot_stg`,`resp_fclty_id`,`lot_num`),
KEY `lots_prmsd_dt_IDX` (`prmsd_dt`),
KEY `lots_assign_entry_dt_IDX` (`assign_entry_dt`),
KEY `lots_last_upd_dt_IDX` (`last_upd_dt`),
KEY `lots_slr_comp_cd_IDX` (`slr_comp_cd`),
KEY `lots_lnkd_lot_num_idx` (`lnkd_lot_num`),
KEY `IDX_lots_IX01` (`lot_stg`,`lot_num`),
KEY `IDX_lots_IX02` (`phy_fclty_id`),
KEY `IDX_lots_IX03` (`lot_status`,`cntry_cd`,`lot_clsd_dt`),
KEY `lots_lly_dt_IDX` (`lly_dt`),
KEY `IDX_lots_IX05` (`phy_fclty_id`),
KEY `lot_images_06_IDX` (`lot_suffix`),
KEY `idx_lots_19` (`lot_clsd_dt`,`lot_stg`),
KEY `IDX_lots_IX20` (`old_lot_num`)
) ENGINE=SPIDER AUTO_INCREMENT=146125609 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='server "spider1", table "lots"'
1 row in set (0.000 sec)