Createing a new Index in SQL when current records don't meet that index

Question

Hey all-

I'd like to add an index to a table that already contains data. I know that there a few records currently in the table that are not unique with this new index. Clearly, MySQL won't let me add the index until all of them are.

I need a query to identify the rows which currently have the same index. I can then delete or modify these rows as necessary. The new index contains 6 fields.

Thanks-

Jonathan

Answer 1

You can use a GROUP BY with HAVING to find the offending fields.

SELECT   IndexField
FROM     YourTable
GROUP BY IndexField
HAVING COUNT(*) > 1