MySQL text matching

[mmoutadayne]

Hello All,

I have a table of ~5,000,000 records (ID, name, title) and a second table of ~2,000,000 records (ID, name, title).

What i need to do is return the ID's from the first table where there is no exact match with a name/title in the second table. So if a row in table 1 is (3, Tom, Arnold) and there is a row in table 2 (5, Tom, Arnold)... I would NOT return 3.
Conversly, if there is no row in table 2 with name=Tom, title=Arnold then I WOULD return 3.

But, when i say exact match I mean a match that ignores case and removes trailing white space. So " ToM" = "tom"

I know how to do this matching but the way in which I would do it would take FOREVER to run... I was wondering if anyone has any idea on how to write a lightning fast query to complete this objective

The String columns (name, title) are Varchars so I think this means i cannot use the Match keyword?

Please help

Lots of advance thanx,
Mohamed

 

[r937]

select t1.name
     , t1.title
  from table1 as t1
left outer
  join table2 as t2
    on t1.name  = t2.name
   and t1.title = t2.title 
 where t2.name is null

string comparisons are case-insensitive by default

you'll want to have an index on both columns

r937.com | rudy.ca

 

[mmoutadayne]

Thank-you for the prompt reply.

I should have posted the query that i had come up with in my original post because that is the same one ... I guess what i want to know is if there is another way that might be faster because this query ran for well over a day before it crashed!

Thank-you for the heads up about the case-insensitive stuff. How does MySql handle leading and trailing white space in string comparisons?

Thanks again,
Mohamed

 

[r937]

trailing spaces are trimmed at the time when the data value is saved (YAMSF - yet another mysql "silent" feature)

leading spaces are kept, so if you suspect those, you will have to use the TRIM function

you have indexes and it still ran over a day? whoa

you'll need expert database tuning advice, then

r937.com | rudy.ca

 

[mmoutadayne]

Thanks for your help... looks like I'll have to add TRIM.
My DB admin is on vacation so i have to do this solo .

If anyone has any ideas i'd love to hear them

Sincerely,
Mohamed