Re: Revisited: Working with a billion rows

[Rick James]

The JOIN version of the SELECT is definitely the better one. It is probably something subtle that is making it run so slow.

Please provide SHOW CREATE TABLE and SHOW TABLE STATUS for each table.

> record like "1%"
Is `record` a VARCHAR? What percent of the total number of rows match '1%'?

If `record` is INT, then use a numeric comparison, such as
record >= 1000 AND record < 2000
Otherwise, the optimizer gives up and scans the entire table.

Turn that DELETE into a SELECT (JOINing the two tables); then let's discuss how to optimize the SELECT.

> Production queries will be very few - maybe 10 per day - but they'll need to be quick.
While you are at it, show us those SELECTs.