Query a table with 100M rows

Question

I have 103216342 rows in a table with 4 columns in a MySQL database. When I am querying it, it will just hang. Even a normal select query is not executed. What do I have to do with this table so that I am able to perform queries on it?

CREATE TABLE IF NOT EXISTS `dealerzipcoderanges` (
  `DealerZipCode` varchar(5) NOT NULL,
  `FromZipCode` varchar(5) NOT NULL,
  `ZipCodeName` varchar(50) NOT NULL,
  `Miles` double DEFAULT NULL,
  PRIMARY KEY (`DealerZipCode`,`FromZipCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I have three tables, Inventories ,Dealerships, and dealerzipcoderanges. Two of them are properly indexed (with primary key int). I am getting records from joining all three tables. Joining two (Inventories and Dealerships) worked properly, but when I joined the dealerzipcoderanges table, I have problems. It's buffered for infinite time and respond nothing.

Inventories is almost 1.5GB, Dealerships is 200 MB, and dealerzipcoderanges (with primary key varchar) is 4.3GB.

Users query with zipcodes for Inventories with a distance in miles between zipcodes. We have to map dealer's zipcode and user's zipcode to map the proper distance.

SELECT * 
FROM inventories 
  INNER JOIN dealerships 
    ON inventories.DealershipId = dealerships.DealershipId 
  INNER JOIN dealerzipcoderanges 
    ON dealerships.ZIP = dealerzipcoderanges.DealerZipCode 
WHERE dealerzipcoderanges.Miles = '100' 
  AND dealerzipcoderanges.FromZipCode = '12345'; 

Inventories

   CREATE TABLE IF NOT EXISTS `inventories` (
  `InventoryId` int(11) NOT NULL AUTO_INCREMENT,
  `DealershipId` int(11) NOT NULL,
  `DateInserted` datetime NOT NULL,
  `DateRemoved` datetime DEFAULT NULL,
  `RemovedBy` varchar(50) DEFAULT NULL,
  `VIN` varchar(20) NOT NULL,
  `Stock` varchar(20) DEFAULT NULL,
  `Year` int(11) DEFAULT NULL,
  `Make` varchar(25) DEFAULT NULL,
  `Model` varchar(50) DEFAULT NULL,
  `ModelNumber` varchar(25) DEFAULT NULL,
  `Body` varchar(50) DEFAULT NULL,
  `StandardBody` varchar(50) DEFAULT NULL,
  `Trim` varchar(50) DEFAULT NULL,
  `Miles` int(11) DEFAULT NULL,
  `CarFaxReportURL` varchar(255) DEFAULT NULL,
  `CarFaxOneOwner` int(11) DEFAULT NULL,
  `Description` longtext,
  `EPACity` varchar(10) DEFAULT NULL,
  `EPAHighway` varchar(10) DEFAULT NULL,
  `ExtColor` varchar(50) DEFAULT NULL,
  `ImageCount` int(11) DEFAULT NULL,
  `IntColor` varchar(50) DEFAULT NULL,
  `SellingPrice` int(11) DEFAULT NULL,
  `VideoURL` varchar(255) DEFAULT NULL,
  `PhotoURL` longtext,
  `ImageModifiedDate` datetime DEFAULT NULL,
  `ImageLastImportDate` datetime DEFAULT NULL,
  `RemoveFlag` int(11) DEFAULT NULL,
  `Status` varchar(10) NOT NULL DEFAULT '1' COMMENT '1=''Active'' , 0=''Move To Trash''',
  `StatusChangeDate` datetime NOT NULL,
  `ImportMethodTypeId` int(11) DEFAULT NULL,
  PRIMARY KEY (`InventoryId`),
  KEY `IX_Inventory_DealershipId` (`DealershipId`),
  KEY `IX_Inventory_SearchFields` (`Make`),
  KEY `IX_Inventory_VIN` (`VIN`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=881752 ;

Dealerships

   CREATE TABLE IF NOT EXISTS `Dealerships` (
  `DealershipId` int(11) NOT NULL,
  `DealerGroupId` int(11) DEFAULT NULL,
  `InventoryProviderId` int(11) DEFAULT NULL,
  `Dealership` varchar(255) NOT NULL,
  `PhoneNumber` varchar(13) DEFAULT NULL,
  `TrackingNumber` varchar(13) DEFAULT NULL,
  `AuthorizedDealerName` varchar(50) DEFAULT NULL,
  `RepresentativeEmail` varchar(255) DEFAULT NULL,
  `WebsiteURL` varchar(255) DEFAULT NULL,
  `Logo` varchar(255) DEFAULT NULL,
  `Hours` varchar(255) DEFAULT NULL,
  `Description` longtext,
  `Services` longtext,
  `Address` varchar(50) DEFAULT NULL,
  `City` varchar(50) DEFAULT NULL,
  `State` varchar(2) DEFAULT NULL,
  `ZIP` varchar(5) DEFAULT NULL,
  `LatitudeLongitude` varchar(255) DEFAULT NULL,
  `InventoryAuthorize` tinyint(4) DEFAULT NULL,
  `SalesPersonId` int(11) DEFAULT NULL,
  `Manufacturer` varchar(20) DEFAULT NULL,
  `ManufacturerParentId` int(11) DEFAULT NULL,
  `DateCreated` datetime DEFAULT NULL,
  `DateActivated` datetime DEFAULT NULL,
  `ActivateReason` varchar(50) DEFAULT NULL,
  `DateCanceled` datetime DEFAULT NULL,
  `DateModified` datetime DEFAULT NULL,
  `ManufacturerImport` varchar(3) DEFAULT NULL,
  `ManufacturerDealerCode` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`DealershipId`),
  KEY `IX_Dealerships_DateActivated` (`DateActivated`),
  KEY `IX_Dealerships_State` (`State`),
  KEY `IX_Dealerships_ZIP` (`ZIP`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Answer 1

For

AND dealerzipcoderanges.FromZipCode = '12345'; 

it would be much better to have

PRIMARY KEY (`FromZipCode`, `DealerZipCode`)

Still better would be to also have

INDEX(FromZipCode, Miles)

If you really mean WHERE Miles < 100.

What you currently have cannot use any index on your 100M-row table, so it will "hang forever". With the indexes above, it will run a lot faster.

Answer 2

I would add index on inventories.DealershipId and dealerships.ZIP. Hard to say what is happening without EXPLAIN plan. I would be also sceptic about adding indexes on double data type columns.