I have a database with three tables and I need to cross reference the first table against the other two to create a fourth table of consolidated information. All the tables have one field which is common, this is the MSISDN (mobile / cell telephone number) and is at least 10 digits long.
Table 1 - 819,248 rows
Table 2 - 75,308,813 rows
Table 3 - 17,701,196 rows
I want to return all the rows from Table 1 and append some of the fields from Tables 2 and Table 3 when there's a matching MSISDN.
My query has been running now for over 24 hours and I have no way of knowing how long something like this should take.
This type of query may be a regular project - is there a way to significantly reduce the query time?
I have indexed tables 2 and 3 with MSISDN and the fields I need to return.
My query is like this:
create TABLE FinishedData
Table1.ADDRESS, table1.POSTAL, table1.MOBILE,
table1.FIRST, table1.LAST, table1.MID, table1.CARRIER,
table1.TOWN, table1.ID, table2.status as 'status1',
table2.CurrentNetworkName as 'currentnetwork1',
table2.DateChecked as 'datechecked1', table3.Status as 'status2',
table3.DateChecked as 'datechecked2'
table1 left join (table2, table3)
on (right(table1.MOBILE, 10) = right(table2.MSISDN, 10)
AND right(table1.MOBILE,10) = right(table3.MSISDN,10))
The kill performance issue is with right function When you use this function, MySQL can't use indexes.
My suggest is:
With this little change MySQL will can take indexes to make your joins.
1) Create new columns:
Alter table table2 add column r_MSISDN varchar(200); update table2 set r_MSISDN = reverse( MSISDN ); Alter table table3 add column r_MSISDN varchar(200); update table3 set r_MSISDN = reverse( MSISDN );
2) New join:
... from table1 left join (table2, table3) on (right(table1.MOBILE, 10) = left(table2.r_MSISDN, 10) AND right(table1.MOBILE,10) = left(table3.r_MSISDN,10))