Query a table with 100M rows

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) DEFAUL

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.

StackExchange Database Administrators Q#165514, answer score: 3