我有一个包含假日交易的表,所以为了给您一个概念,每一行将包含以下数据位:
Departure airport
Arrival airport
Start date
Duration
Hotel destination
Resort
Hotel name
Hotel rating
A few tiny integer columns for 1s and 0s.
Price
Date time the row was updated
现在,所有这些交易都是从3张桌子上打包的,它们是
flights
,
accommodation
和
transfers
包装是为了找到每个变化最便宜的交易,如,每个出发机场,持续时间,董事会的基础等。
我要导入的表大约有5000万行,导入速度非常慢。
我已经删除了索引,这有很大的不同,但是现在当我重新将索引添加回表中时,所有数据都在表中,这需要永远完成。
我想知道有没有一种快速批量加载数据的方法,或者有没有一种在添加数据后更快地将索引添加回表中的方法?
创建表
` `
CREATE TABLE `iv_deals` (
`aid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Deal Autonumber PK',
`startdate` DATE NULL DEFAULT NULL COMMENT 'Holiday Start Date',
`startdatet` TINYINT(2) NOT NULL DEFAULT '0',
`depairport` CHAR(3) NULL DEFAULT NULL COMMENT 'Departure Airport IATA Code',
`arrairport` CHAR(3) NULL DEFAULT NULL COMMENT 'Arrival Airport IATA Code',
`destination` VARCHAR(30) NULL DEFAULT NULL COMMENT 'Holiday Destination',
`resort` VARCHAR(30) NULL DEFAULT NULL COMMENT 'Holiday Resort',
`hotel` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Holiday Property Name',
`iv_PropertyID` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Holiday Property ID',
`rating` VARCHAR(2) NULL DEFAULT NULL COMMENT 'Holiday Property Star Rating',
`board` VARCHAR(10) NULL DEFAULT NULL COMMENT 'Holiday Meal Option',
`duration` TINYINT(2) UNSIGNED NULL DEFAULT '0' COMMENT 'Holiday Duration',
`2for1` TINYINT(1) UNSIGNED NULL DEFAULT '0' COMMENT 'Is 2nd Week FREE Offer, 0 = False, 1 = True',
`3for2` TINYINT(1) UNSIGNED NULL DEFAULT '0' COMMENT 'Is 3rd Week FREE Offer, 0 = False, 1 = True',
`3and4` TINYINT(1) UNSIGNED NULL DEFAULT '0' COMMENT 'Is 3rd and 4th Week FREE Offer, 0 = False, 1 = True',
`4for3` TINYINT(1) UNSIGNED NULL DEFAULT '0' COMMENT 'Is 4th Week FREE Offer, 0 = False, 1 = True',
`freebb` VARCHAR(2) NULL DEFAULT NULL COMMENT 'Free Week Meal Option',
`adults` TINYINT(1) UNSIGNED NULL DEFAULT '0' COMMENT 'Number of Adults',
`children` TINYINT(1) UNSIGNED NULL DEFAULT '0' COMMENT 'Number of Children',
`infants` TINYINT(1) UNSIGNED NULL DEFAULT '0' COMMENT 'Number of Infants',
`price` SMALLINT(4) UNSIGNED NULL DEFAULT '9999' COMMENT 'Price',
`carrier` VARCHAR(40) NULL DEFAULT NULL COMMENT 'Flight Carrier IATA Code',
`DateUpdated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`aid`, `startdatet`),
UNIQUE INDEX `Unique` (`startdate`, `depairport`, `arrairport`, `iv_PropertyID`, `board`, `duration`, `adults`, `children`, `startdatet`),
INDEX `ik_Price` (`price`),
INDEX `ik_Destination` (`destination`),
INDEX `ik_Resort` (`resort`),
INDEX `ik_DepAirport` (`depairport`),
INDEX `ik_Startdate` (`startdate`),
INDEX `ik_Board` (`board`),
INDEX `ik_FILTER_ALL` (`price`, `depairport`, `destination`, `resort`, `board`, `startdate`),
INDEX `iv_PropertyID` (`iv_PropertyID`),
INDEX `ik_Duration` (`duration`),
INDEX `rating` (`rating`),
INDEX `adults` (`adults`),
INDEX `DirectFromPrice` (`iv_PropertyID`, `depairport`, `arrairport`, `board`, `duration`, `adults`, `children`, `startdate`),
INDEX `DirectFromPrice_wo_depairport` (`iv_PropertyID`, `arrairport`, `board`, `duration`, `adults`, `children`),
INDEX `DirectFromPrice_w_pid_dep` (`iv_PropertyID`, `depairport`, `adults`, `children`, `price`),
INDEX `DirectFromPrice_w_pid_night` (`iv_PropertyID`, `duration`, `adults`, `children`),
INDEX `DirectFromPrice_Dur_Board` (`iv_PropertyID`, `duration`, `board`, `adults`, `children`),
INDEX `join_index` (`destination`, `startdate`, `duration`)
)
COLLATE='utf8_general_ci'
AUTO_INCREMENT=1258378560
;
` `
