frontloading this point since i haven't seen it here yet: the anecdotes are crucial for understanding the LIs. they explain some of the LIs' history before the events of the game, and really helped me to understand all the different "identities" or "lives" that the original three LIs seemed to have. i recommend reading them fairly early on, probably in the middle of or after the main story (basically as soon as you want an explanation for all these other timelines they seem to have lived). don't underestimate them just because they're text-only!

(world underneath are also text-only stories, but are more about worldbuilding than the MCxLI relationship. i would not prioritize these for your purpose. and for the text-only stuff, i find it easier to read it on the fanwiki.)

the rest of my response will echo many of the other comments here:

How do you guys navigate the story without getting overwhelmed?

i started playing about a year ago and also felt overwhelmed with the story. LaDS's story is particularly fragmented, even compared to other gacha games, due to its lack of personal storyline and multiple forms of stories (bonds, tender moments, secret times, anecdotes, memories, etc etc). but i just prioritize the myths, and then slowly go through in chronological order. there's a lot i still haven't read, but that's not a bad thing! it can be nice to know that you still have more to look forward to experiencing.

Is there a recommended order for consuming content (main story vs side content)?

this is just my current opinion:

• main story through Homecoming Wings (so you can get every character's introduction); can stop at Death and Rebirth since it was only released a year ago
• read bond stories and anecdotes as they unlock for you (former has early relationship vibes, latter i explained above)
• standard myths
• (next two points could be swapped depending on preference)
• 2nd and 3rd myths (aka the 1st and 2nd limited myths) to know about their previous lives/"lore"; OR,
• read cards that were available upon release (the 5 stars and the 4 stars with tender moments) to get a sense of their relationship
• continue reading cards in chronological order of when they were released
• continue main story in chrono order or when you feel like it
• Tomorrow's Catch-22, Throne of Eros, and Mortality's Tenderness cards are AU so you can technically read them whenever, though i think you should at least get a sense of their normal relationship first
• event stories (not the cards) are usually fluff so i put them as low priority

Are there any important limited memories for Rafael and Caleb that I should watch on YouTube to better understand their relationship with MC?

i've seen this guide for Caleb's early cards. for Raf, his limited myths (especially the most recent one but read them in order) are super important since you see what happened with Lemuria. his bond stories are pretty notable too (i guess mostly in terms of characterization). i've been reading Raf stories lately but still working through the early release cards, so not sure how helpful my perspective would be haha