CODEWITHSUNDEEP
mysqlsql
Christopher
Christopher

Reputation: 691

Faster SQL query on two table matching

I have two tables that I am trying to perform matching on with the following query:

select * from task1,task2
where task1.From_Number=task2.To_Number
and task1.Start_Time<task2.Start_Time;

It will work eventually but is taking forever. The tables have 33 columns and one has around 45k rows and the other 500k rows. There are duplicates in various columns and no column is unique so there isn't a primary key. The tables were imported from spreadsheets.

There are a bunch of phone call logs and as mentioned, there are several duplicates in each column. What can I do to get this query to run faster? I am only matching against a few columns but need to print all columns and output the result into a csv.

Upvotes: 0

Views: 31

Answers (1)

Patrick Hofman
Patrick Hofman

Reputation: 156998

The best thing you can do is create an non-unique index on both columns in both tables.

Read the MySQL documentation on creating an index.

Something like:

create index task1_idx
on     task1
( From_Number
, Start_Time
)

And:

create index task2_idx
on     task2
( To_Number
, Start_Time
)

Upvotes: 1

Related Questions