Help comparing two fields from seperate tables - MySQL

Hi

I'm quite a newbie so please bear with me:


I have two tables, both with similar data. I am trying to check if the title from one table appears in another. the issue is that when I run what I think I should be running it kind of crashes the server. I guess this is because there is no field that really links the two tables?

select g.title, d.title
from table1 as g, table2 as d
where substr(g.title, 0, 15) = substr(d.title, 0, 15)

Code (markup):

I have had to use the substr() as both titles start the same but some have added text after that will not match.

 

A join may work in this case. This could be really memory heavy...

SELECT g.title FROM table1 as g LEFT JOIN tabe2 AS d ON LEFT(g.title,15) = LEFT(d.title,15)

Is there a specific reason that the title's need to be stripped down to 15 chars?

 

You should create INDEX on title fields in both the tables. This will boost the performance.

Learn how to create index.

 

thanks - I created an index on both of these as I had hoped it would speed things up.

I tried the above query but it seems to have returned items that are not matched, the reason for the 15 characters is some of the items are the same but with slightly different title variations for example:

gibson 45-567890 scriber
gibson 45-567890 scriber available here

So I am trying to just take the first 15 characters. It may be necessary to just take the first ten or maybe less but I was trying to get the query to function correctly first.

Thanks for the help

 

LEFT JOIN query will return all the results. Run your own query.

 

I think if you do an index on the first 15 characters of each title field, and a Left Join query, you should have a usable solution.

To do the index, you would do this:
CREATE INDEX title_15_1 ON table1 (title(15));

repeat for the other table with a different index name.