DynamoDB – How to Search by Attribute

depending on how you want to fetch the data. If you want to get all by item, then item as a primary key is a great choice. If you want to filter by item and url then i would add primary key Item and sort key url. If you want to have url and search for the item by url, i would add url as primary key and url as sort key. If you want to be able to do both, i would add primary key for item, sort key for url and then add new GSI with primary key url and sort key item.

I learned a lot about this from this conference: https://www.youtube.com/watch?v=jzeKPKpucS0 recommended.

I will approach your question from this point of view "MySQL does the work; 3rd party software tends to 'get in the way'"...

1) -- MySQL's FULLTEXT index would make that query very fast and efficient. Using LIKE is slow because it requires a table scan.

2) -- There are many discussions of lat/lng; the short answer is two single-column INDEX(latitude), INDEX(longitude). That will be "good enough" for medium-sized datasets.

3) -- INDEX(username).

500M users is about how many Internet-savvy people there in the world. That's more than anyone can expect. Still, the table you describe would fit in a single server (but not a 'micro' instance). With millions of "active" users, you would need to consider sharding and availability zones, etc.

Seriously... You are at the beginning stages; it is too soon to worry about 500M, or even 5M. Within a few months, even with fantastic growth, you will discover that the schema, the UI, the features, the everything else, will need revising. Sooooo... Plan on a major overhaul within 6 months.

Meanwhile, focus on getting a working prototype going on cheap hardware, with simple techniques.

Back to my first comment... This (and other) forum is littered with questions about how 3rd party software can't scale. Often my answer is "Here's what you need to do in MySQL; can you reverse engineer it into the 3rd party constraints?".

I don't know what "Global Secondary Indexes" means. It smells like a index across shards. If so, then when and if you get to sharding, it is necessary. But... Is it "one size fits all" and is it the best for your application? (My first choice in sharding is a 'hybrid' between 'hashing' and 'dictionary lookup'.)

Not even 1 application in 1000 needs sharding. Build your prototype without sharding. As you do, keep in mind what will have to happen when you do shard.