SQL - Does the order of WHERE conditions matter?

No, the order of the WHERE clauses does not matter.

The optimizer reviews the query & determines the best means of getting the data based on indexes and such. Even if there were a covering index on the category_id and author columns - either would satisfy the criteria to use it (assuming there isn't something better).

SQL is declarative.

In your example, you have told the engine/optimizer what you want... it will now work out the best way to do that (within reason and "cost" which would be off topic).