Need help optimizing mysql query comparing two tables

Question

This is kind of a follow up to my previous question link.

Some time has passed so I wasn't sure if I needed to update that one or create a new one, especially since the parameters of my issues have changed a little.

--

I have a large (simple) table of phone numbers (>5 million and growing). The table was set up as follows:

| AreaCode | Local | Created |

• 'Local' is the 7 digits following the 3 digit area code
• 'Created' is just a timestamp
• AreaCode and Local are both indexed
• I used to have an ID column as my primary key but removed it when I partitioned my table. I set up 10 partitions by range on AreaCode ( <100, <200, <300, ~ <999)

I am uploading csv files of numbers (up to 250k rows), sanitizing and cleaning the input via PHP, then creating a temp table and inserting data into it.

After this point, I am having a lot of trouble. With smaller data sizes (10k-25k rows), I don't really have any problems. But when I'm trying to compare my temp table containing 250k+ rows against my master database, it takes incredibly long.

I've tried the 2 following queries, and not much luck with any of them.

Using inner join

SELECT a.* FROM master_table a
INNER JOIN temp_table b
ON a.AreaCode = b.AreaCode
AND a.Local = b.Local;

I found this suggestion on a website and tried it

SELECT b.* FROM temp_table b
WHERE b.AreaCode
IN (
    SELECT a.AreaCode
    FROM master_table a
    WHERE a.AreaCode = b.AreaCode
    AND a.Local = b.Local
);

I apologize for the long list questions but my mastery of mysql is weak.

1. Am I making a mistake by not having a primary and/or unique key? Since every phone number is unique, I wasn't sure that having an ID column would benefit me.
2. Am I right to partition my master table or is this slowing me down?
3. I have indexes on my AreaCode and Local columns. When I create temporary tables, should I be creating indexes on the same columns there as well?
4. Please help me fix my query so it doesn't take so long!!

Answer 1

To answer your questions:

1. I do not see how you are enforcing uniqueness with your current indexes. You could have a compound primary index on areaCode and local to enforce this uniqueness. I would have some sort of primary key for sure. I would question through whether you ever needed to query local without areaCode to determine if you need individual indexes on. Personally, I would probably use an autoincrement primary key and a compound unique index on those two field if I was going to be referencing that data in other tables (like if I wanted to related phone numbers to users or something) as I find it less cumbersome to relate tables when using a single key.

2. 5 million rows is not that big of a table. Probably premature to partition. Also, depending on the ratios of different areas codes in your database, and the access pattern for these, this may not be a good partitioning scheme.

3. If you are going to work with on-disk temporary tables and going to be joining with these large data sets, you need to provide indexes.

4. You have two different queries that do two different things here. If the intent is ultimately to insert this data into the master table data, I don't understand why you are trying to do a join at all. You could simply do something along the lines of:

>

 INSERT INTO master_table (`areaCode`, `local`)
 SELECT SELECT `areaCode`, `local`
 FROM temp_table
 ON DUPLICATE KEY UPDATE UPDATE `created` = NOW() /* You can add this line is you want to update the time stamp */