Why is there such a huge performance difference in these two join statements?
The OR operator in your first statement is making the output of both queries totally different.  If you put brackets around the two items that are separated by the OR statement you will see the results are the same.

SELECT ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) 
FROM category c 
    INNER JOIN product p on (p.indID = c.id or p.catid = c.id) 
        and p.deleted = 1 
        and p.statusid = 1 
GROUP BY c.name


OR is evaluated after AND...