Mysql help please :)

Webmaster Discussion
#1

Hoping someone can help me out with a MYSQL question.

What I need to do is compare the rows of two tables columns and find matches… The rows within the two columns have different data structures.

Database: TESTDATA

TABLE: A COLUMN 4 (The rows for this column will only contain a single “word” or a word with an underscore “word_word”.

For example:

COLUMN 4
sunny
day
sunny_day
cloudy

TABLE: B COLUMN 5 (The rows for this column could be empty, contain single words, multiple words and/or words with underscores…

COLUMN 5
sunny day cloudy
day cloudy rainy_day
cloudy sunny
cloudy rain sunny_day day
day

TABLE: A COLUMN 4 is the master table. I’m only looking for entries with an underscore in TABLE: A COLUMN 4. The first match was “sunny_day” and it was found in TABLE: B COLUMN 5… That’s what I’m looking for.

I can’t figure out the MYSQL command that will allow me to look through each row of “TABLE: A COLUMN 4” for rows that contain underscores ‘%_%’ (for example “sunny_day” would match) and see if there is a match in “TABLE: B COLUMN 5”. If there is I want the output “sunny_day”. If not proceed to the next row…

Thanks so much…

Kevin.

#2

try this one

SELECT table_b.column_b
FROM table_b
INNER JOIN table_a ON table_b.column_b LIKE CONCAT(’%’,table_a.column_a,’%’)
WHERE table_a.column_a LIKE ‘%_%’

#3

I think what you’re asking for is all the values that contain an underscore in table A, column 4 that can also be found somewhere in a value for table B, column 5.


SELECT `tableA`.`column4`
FROM `tableA`
JOIN `tableB` ON (`tableA`.`column4` LIKE CONCAT('%',`tableB`.`column5`,'%'))
WHERE `tableA`.`column4` LIKE "%_%"
GROUP BY `tableA`.`column4`
#4

Thanks so so much for the help guys…

Unfortunately, I’m not having much luck but I don’t think it’s the queries you provided. I think the problem I’m encountering is due to the size of these rows. Table A has 90,000+ rows and Table B has 150,000+ rows. When I try to run the queries it appears that mysql gets so busy it stops responding to new queries therefore taking my sites offline until I stop mysql and restart it. UGH.

In the mean time, I think I’ve come up with another way to accomplish what I’m try to do by just dumping the data from the rows and use a perl script to sort through it getting me the results I need. Or at least that’s what I’m going to try next. Wish me luck… LOL

Thanks again…
Kevin.

#5

Try indexes for both columns. Not fulltext ones.

#6

That’s not really all that much data. Mostly depends on the amount of data in the columns. If they’re VARCHAR it should be pretty speedy. But if you’re over the limits of VARCHAR and need TEXT columns, yeah, that would get pretty slow.

Given that you’re doing a contains search, I’m not sure how much indexing will help. Your query requires a full-table scan. Sounds like your server is low on memory. If you’re using INNODB and the tables can fit into the INNODB buffer pool, the query should be fairly quick, even if you’re using TEXT.

#7

Thanks guys for the help…I know very little about Mysql other than how to perform rather basic queries.

Jay Table A COLUMN A is VARCHAR and Table B COLUMN B is TEXT. The second table is the one that can have multiple words within the row (much more data than Table A). The server has 32GIGS of Ram.

I was able to work on the script I spoke of yesterday, last night, and I think it’s going to be able to do what I need… And it’s fast :slight_smile: Just going to put the final touches on it tonight.

Kevin.