Replace indexes on core_url_rewrite and cms_page with case-sensitive collations.

[colinmollenhour]

By default MySQL uses case-insensitive collations (e.g. utf_general_ci) which has the effect that a lookup like WHERE path = '/about-us' can match records like /About-Us. In my opinion for URL matching this is the wrong behavior and if a user wanted to match /About-Us they should have to add a rewrite manually. This could have negative impact on SEO if one page can be accessed via multiple urls (content duplication). Case sensitivity is assumed everywhere else in the stack as far as I know so I think this is just an oversight that the indexes match case-insensitively. If someone really wants to be case-insensitive they are probably better off creating a global redirect for any urls with upper-case letters to the equivalent all lower-case url. I propose that these indexes be dropped and added with a case-sensitive collation to fix this issue. (I can submit a PR if the proposal is accepted)

Note, I've also seen an issue with nix filesystems that are case-sensitive yet the media gallery tables are not case-sensitive so it is possible to have two files like image.jpg and image.JPG and rows in the database with the correct value but due to the index a query for image.jpg would actually return both rows.. I don't think this comes up in core code but I discovered it when my image de-duplicating script acted on rows that I didn't expect it to.. :( So I could fix this index as well.

Any other places where indexes should probably be swapped out with their case-sensitive equivalents?

For that matter, I don't think Magento even specifies collations at all and it is left up to the database defaults.. I know in my databases my tables have different types of collations, probably due to the default switching at some point after an upgrade..