Displaying duplicate fields comparing more than one field

dirkadirka

After searching through answers and trying multiple approaches I still cannot figure this out.

I am trying to search my database for rows that have duplicate listings in 3 fields. (not the whole row). I can get the duplicates to list just fine, problem is it is only listing the first occurrence and not the entire list. I need to see all the duplicates listed!!!

This is what I am currently using

SELECT * FROM resumes group by LAST_NAME, FIRST_NAME, JOB_TITLE having count(*) > 1

I have tried things like

SELECT *, COUNT(LAST_NAME&&FIRST_NAME&&JOB_TITLE) occurrences FROM resumes GROUP BY LAST_NAME,FIRST_NAME,JOB_TITLE HAVING occurrences > 1

but of course that does the same thing...I've also tried to do a simpler single field search using distinct

SELECT distinct (a.LAST_NAME, a.RESUME_ID from resumes a join resumes b on (a.LAST_NAME=b.LAST_NAME and a.RESUME_ID <> b.RESUME_ID) GROUP BY LAST_NAME

but running the last causes the server to hang and obviously is only using the LAST_NAME field.

please help 😕

dirkadirka

ok that last bit I had edited poorly, I am trying this now

SELECT distinct * from resumes a join resumes b on (a.LAST_NAME=b.LAST_NAME and a.FIRST_NAME=b.FIRST_NAME and a.JOB_TITLE=b.JOB_TITLE and a.RESUME_ID != b.RESUME_ID)

but its sllloooooowwww...and I'm not even sure it works (still running haha)

dirkadirka

Ok the last bit of code works, but its SUPER slow...terrribly slow. There has to be a much better way of doing this. Thoughts?

dirkadirka

Any ideas on how to speed this process up? It works but runs way too slow for me to make live. Any help would be greatly appreciated! 🙂

johanafm

It shouldn't be that slow. Do you have INDEX (last_name, first_name, job_title)?

dirkadirka

It's extremely slow, so much so it breaks half the website...it does work unerringly however.

Now it's my turn to be slow, what do you mean by INDEX?

(oh and thanks for the reply!!!) 🙂

johanafm

Indexes is what speeds up data retreival, by reducing the number of blocks that have to be read from disk. Since each block access takes 5+ ms lacking appropriate indexes is bad. On the other hand, if you have lots of insert and few select statements, indexes could lower overall performance.

Using what DB? Here's the MySQL documentation on alter table syntax. I don't know for sure if you need a multicolumn index. Check the differences with EXPLAIN, or hope someone more skilled at that comes along.
But in either case, this should help

ALTER TABLE resumes ADD INDEX (LAST_NAME)

And then the same thing for first name and job title. There is no need to index resume_id if it's set as primary key.