Speed up sql - keys

Databases
1

Hello,

I’m experiencing slow sql lookup in my product table which has more than 300.000 entries. It takes very for mysql (using php) to lookup for specific entry. I use varchar permalink and int site_id to match the row.

How can I speed up this lookup?

Should creating index on 2 columns (site_id and permalink) help?
If so, for how much should this speed up the lookup?
What else can I do?

I tried doing this but I get the following error:

ALTER TABLE `store`.`products` ADD UNIQUE `permalink_index` ( `site_id` , `permalink` )

MySQL said: Documentation
#1062 - Duplicate entry 'test-product-name' for key 'permalink_index'

This seems like 2 products with test-product-name exist for the same website, but if I search for this permalink I get no results.

Thanks a lot for help!

2

run this –

SELECT store.products.*
  FROM ( SELECT site_id
              , permalink
           FROM store.products
         GROUP
             BY site_id
              , permalink
         HAVING COUNT(*) > 1 ) AS dupes
INNER
  JOIN store.products 
    ON store.products.site_id    = dupes.site_id    
   AND store.products.permalink  = dupes.permalink
3

And then delete the entries and make the index key?

4

This query shows 636 total rows // of 389.000

Should I delete those duplicates?

5

i wouldn’t do that just yet

first, you should find out why there are duplicates, decide if they are legitimate or not, and do you want to prevent them in future

6

Yes, I definitely do.

7

What is the best way to delete those duplicates?

I also want to do prioritization (sort) which duplicates should be deleted…

8

can you explain how this would work, to decide which row to keep…