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.
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