Mysql Join with pipe-separeted field -

i have 2 tables join way:

update wm_rendi rendi join wm_acquisti on rendi.isrc_isbn = wm_acquisti.barcode set rendi.user_id = wm_acquisti.user_id rendi.user_id null

this query takes 24 ho hours complete...

this wm_acquisti table, +18'000 records:

user_id    barcode 12         600001 56         500003 12         600007 12         800018 32         100004 56         500007 ...        ...

this wm_rendi table, 910'040 records now:

user_id      isrc_isbn null         600001 null         500003 ...          ...

so, because of loooooong taking of query, thought shorten wm_acquisti way:

user_id    barcode 12         600001|600007|800018 56         500003|500007 32         100004

the point have join these 2 tables based on barcode, takes waaaay long.

is there way of obtaining same join using shorter table (pipe separated barcodes) or other method?

you could write query emulate join operation. don't want to. slow, ugly, , unmaintainable.

remember, speed of join affected not number of rows in table, number of binary comparison operations must performed. things affect

the number of data points each table. how points stored immaterial. still need compare each barcode in 1 table each barcode in other.
indexes. remember join without indexes takes o(nxm) binary comparisons n # of rows in smaller table , m # of rows in larger table. on other hand, index lookup of value o(log(n)) if query optimizer smart, should take o(nlog(m)) opertions. in case, indexed field around 45,000 times faster. if don't have indexes, or rmdbs isn't using them, that's flag.

likewise ake sure wm_rendi.user_id indexed, shorten null lookup order of magnitude.

make sure columns have unique contstraints of course.

as several folks have pointed out, there never reason store delimited data in table. breaks normality, slows down queries , makes bad.

Plus Code