A few more things:
 Try running: ANALYZE TABLE commerce_items, collection_items, collections, collection_categories, users; If that helps matters, you will want to make sure you re-run it regularly, based on the frequency of updates to the tables in question.
 Can wee see the result of: SHOW INDEX FROM commerce_items; SHOW INDEX FROM collection_items; SHOW INDEX FROM collections; SHOW INDEX FROM collection_categories; SHOW INDEX FROM users;
 Can we see the result of: EXPLAIN [your new query]
 Why did you introduce the users table? (It doesn’t appear to be used anywhere)
[*] What table is dateExpires coming from? I would guess it’s table d, but you should use table.column notation for the sake of clarity. Here is the Biggie, your sub-select. I want to help you gain the skill off logically deciding what outcomes are possible, so you can then decide the most logical why to achieve them.
Pertaining only to the sub-select:
 My first instinct would be to say that your where clause should be simplified to: where e.categoryid IN (3,250,116,249,123,122,121,120,115,134,133,132,131,130,129,1 28,127,126,125,124,114,119,118,117,101,106,105,104,103,102,3 7,109,108,107,36,35,34,90,89,88,87,86,85,84,59,33,58,57,56,5 5,32,100,99,98,97,96,95,94,93,92,62,61,60,31,135,91,83,82,81 ,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,30,54 ,53,52,51,50,49,48,47,29,253,252,251,248,110,46,45,44,43,42, 41,40,39,38)
 Let us take this further. If out of the big long nasty list, we can be assured that every value will be present, we could drop the entire sub-select and your#9 could simply be the IN(…) condition that I specified above. Right? Are you with me so far? However, (let us call this point 2b) if only a few of the values in the nasty list are present (like maybe “250, 100, 50”) then your#9 will only be compared to those 3 instead of all 111. (yes I counted them.) That is to say, your#9 would be logically equivalent to: AND e.categoryid IN (250,100,50) Meaning that dropping the sub-select is not safe if you are unsure as to where or not all the values are present. Let us take this further, or (should I say) look at a broader view. Since the column being compared in your#9 is the same column being tested in the where clause of your sub-select, it doesn’t matter whether my point 2b returns the a sub-set of the nasty list or the whole list. That is to say, the sub-select adds nothing of value here. Logically, if point 2b is true then it makes no difference if your#9 is “AND e.categoryid IN (250,100,50)” or “AND e.categoryid IN (big long nasty list)” it will still only evaluate true for 250, 100, and 50.
I hope you can make sense of that.
In summary, lets try:
 change your#8 to use table.column notation.
 I still don’t like using “dateExpires-now() > 0” instead of “dateExpires>now()”, for 2 reasons. First, it is doing arithmetic and a compare, instead of doing just a compare. Next, I’m not sure that the arithmetic is valid (Set variable @n to the datetime value of exactly one day in the future via “set @n=addtime(now(),‘1 0:0:0’);”, then compare these: “select @n-now(), unix_timestamp(@n)-unix_timestamp(now()), @n>now();”, then set @n to now via “set @n=now();” and try that comparison again.) unless the values are encased in unix_timestamp() functions, which is expensive. (compare these: “select benchmark(1000000, @n>now());” and “select benchmark(1000000, unix_timestamp(@n)-unix_timestamp(now()));”) Benchmark is your friend!
 remove your#5, unless your#8 is actually resolving to"f.dateExpires"
 consider adding an index to dateExpires. Unless that table gets frequent inserts (which are slowed slightly by the need to update indexes), you should have an index since it is being compared in a where clause.
[*] change your#9 to: AND e.categoryid IN (3,250,116,249,123,122,121,120,115,134,133,132,131,130,129,1 28,127,126,125,124,114,119,118,117,101,106,105,104,103,102,3 7,109,108,107,36,35,34,90,89,88,87,86,85,84,59,33,58,57,56,5 5,32,100,99,98,97,96,95,94,93,92,62,61,60,31,135,91,83,82,81 ,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,30,54 ,53,52,51,50,49,48,47,29,253,252,251,248,110,46,45,44,43,42, 41,40,39,38) Do the happy dance. I suspect it will be warranted.