CODEWITHSUNDEEP
mysql
joe
joe

Reputation: 237

Speed up MySQL that contains a large table

We have a MySQL table hosted on an Amazon AWS server, which is some times very very slow while querying. I'm considering restructuring the table with better indexing and columns data types.

Here is the create table structure : 

SHOW CREATE TABLE export_users;

+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| exportusers | CREATE TABLE `exportusers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `UAccountID` varchar(50) CHARACTER SET utf8 NOT NULL,
  `ApplyURL` text,
  `CityName` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `ClassList` text,
  `UCompanyID` varchar(20) DEFAULT NULL,
  `UContactCompany` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `UContactEmail` varchar(512) CHARACTER SET utf8 DEFAULT NULL,
  `UContactFax` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `UContactName` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `UContactPhone` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `CountryName` char(2) CHARACTER SET utf8 NOT NULL,
  `DateCreated` datetime NOT NULL,
  `DateModified` datetime NOT NULL,
  `DateSysCreated` datetime NOT NULL,
  `DateSysModified` datetime NOT NULL,
  `DegreeCode` varchar(200) DEFAULT NULL,
  `DegreeCodeDecoded` varchar(512) CHARACTER SET utf8 DEFAULT NULL,
  `DisplayCity` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `DisplayuserID` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `ExperienceCode` varchar(200) DEFAULT NULL,
  `ExperienceCodeDecoded` text,
  `ExternalKey` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `GeoUSZip5` varchar(5) DEFAULT NULL,
  `HostSite` char(2) CHARACTER SET utf8 NOT NULL,
  `IndustryCode` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
  `IndustryCodeDecoded` text,
  `IsBOFuser` tinyint(1) DEFAULT NULL,
  `IsDiversityuser` tinyint(1) DEFAULT NULL,
  `userID` varchar(20) NOT NULL,
  `userDesc` text,
  `userFunctionCode` text,
  `userFunctionCodeDecoded` text,
  `userReq` text,
  `userSkinDID` varchar(20) DEFAULT NULL,
  `userTitle` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
  `userType` varchar(512) CHARACTER SET utf8 DEFAULT NULL,
  `userTypeDesc` text,
  `userTypeCodeDecoded` text,
  `Latitude` decimal(10,5) DEFAULT NULL,
  `Longitude` decimal(10,5) DEFAULT NULL,
  `Location` varchar(70) CHARACTER SET utf8 DEFAULT NULL,
  `ManagementCode` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `MaximumExp` int(11) DEFAULT NULL,
  `MinimunExp` int(11) DEFAULT NULL,
  `Onet` varchar(10) DEFAULT NULL,
  `OnetTitle` text,
  `BeginDate` datetime NOT NULL,
  `EndDate` datetime NOT NULL,
  `PayBaseH` decimal(10,2) DEFAULT NULL,
  `PayBaseL` decimal(10,2) DEFAULT NULL,
  `PayBonus` decimal(10,2) DEFAULT NULL,
  `PayComm` decimal(10,2) DEFAULT NULL,
  `PayOther` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `PayPer` varchar(8) DEFAULT NULL,
  `PayType` char(3) DEFAULT NULL,
  `PostalCode` varchar(10) DEFAULT NULL,
  `PostingPath` varchar(20) NOT NULL,
  `Relocate` tinyint(1) DEFAULT NULL,
  `RelocateOptions` varchar(5) DEFAULT NULL,
  `ScreenerID` varchar(20) DEFAULT NULL,
  `SiteID` varchar(1024) CHARACTER SET utf8 DEFAULT NULL,
  `SliceList` text,
  `StateName` char(30) CHARACTER SET utf8 DEFAULT NULL,
  `Status` varchar(50) NOT NULL,
  `TextPay` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
  `TotalPay` decimal(10,2) DEFAULT NULL,
  `TownName` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `TravelCode` varchar(200) DEFAULT NULL,
  `TravelCodeDecoded` text,
  `UpgradeList` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_jobs_on_UAccountID` (`UAccountID`),
  KEY `index_jobs_on_CountryName` (`CountryName`),
  KEY `index_jobs_on_DateCreated` (`DateCreated`),
  KEY `index_jobs_on_DateModified` (`DateModified`),
  KEY `index_jobs_on_DateSysCreated` (`DateSysCreated`),
  KEY `index_jobs_on_DateSysModified` (`DateSysModified`),
  KEY `index_jobs_on_HostSite` (`HostSite`),
  KEY `index_jobs_on_userID` (`userID`),
  KEY `index_jobs_on_BeginDate` (`BeginDate`),
  KEY `index_jobs_on_EndDate` (`EndDate`),
  KEY `index_jobs_on_PostingPath` (`PostingPath`),
  KEY `index_jobs_on_Status` (`Status`)
) ENGINE=InnoDB AUTO_INCREMENT=7907436 DEFAULT CHARSET=utf8mb4 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.27 sec)

Upvotes: 1

Views: 50

Answers (1)

wurde
wurde

Reputation: 2627

Consider Normalization of your schema. Breaking up that one very large entity into much smaller related entities will have the greatest impact on your querying speed.

Normalization involves decomposing a table into less redundant (and smaller) tables but without losing information; defining foreign keys in the old table referencing the primary keys of the new ones. The objective is to isolate data so that additions, deletions, and modifications of an attribute can be made in just one table and then propagated through the rest of the database using the defined foreign keys.

Upvotes: 1

Related Questions