HN Explorer

Show HN: IMDb SQL Best Movie Finder (imdb-sql.com)

129 points by afiodorov ↗ HN
I've built a static web app called IMDb SQL Best Movie Finder that lets you query a database of 1.5 million IMDb titles using SQL directly in your browser. It’s entirely client-side, so all the data processing happens locally on your machine — no server involved.

81 comments

[ 4.0 ms ] story [ 151 ms ] thread
cranberryturkey
this is pretty cool. pretty slow though. maybe scrape in parallel?
afiodorov
It's slow on the first load as you download a 100mb parquet file with all the IMDb data. It'll be cached on the second load though (using browser's indexeddb to cache).

Not sure what you mean by scraping in parallel.

maleldil
100 Mb is a lot of data to download. Maybe you should be upfront about it and, hopefully, require some user input before you do it.
netsharc
Groan... Are you hosting this 100MB (and not mb)? If yes, I hope the bill will teach a lesson. If not, 100 visitors is already 10GB of bandwidth, that's quite abusive.

Maybe I'm just an old fart, and blowing 10GB of data is cheap nowadays?

Jach
OVH and Hetzner offer cheap unmetered VPS instances, Cloudflare CDN doesn't have any bandwidth restrictions... Yes 10GB of data is cheap. If they were serving off EC2, then after the first 100 GB of the month it's 9 cents/GB or 90 cents per 10GB, which is absurd, but still not terribly expensive in the grand scheme of things: what would be a surprise lesson-teaching bill, $90 after a TB / 10,000 visitors?

I'm surprised some users think it's a lot on an individual scale. Consider an ideal persona for this submission: query some movies, then go stream several GBs for one. The 100 MB payload isn't much in comparison. I admit it is kind of bad form for mobile users who might be on metered data plans, and a warning and trigger for manual action for those devices would be kinder.

One thing that might help reset your notion of what a lot of bandwidth is would be to browse around with your network tab in the developer console open for a day. nytimes home page is 14 MB and they get a ton of traffic. Even a corporate blog on the HN front page right now that could just be the tiny compressed text is 2 MB. Single image loads on many pages can be 1 MB or more. Glancing at the submission, the response headers indicate it seems they're serving from S3 Cloudfront, which is free for the first TB per month, though after that it gets back to absurd pricing. AWS is not price competitive.

jafitc
I think you should consider trimming that file.

Exclude movies with very low number of rating or potentially very low scores too.

The long tail reduction would be significant

afiodorov
I initially loved looking for obscure stuff, e.g. setting region to soviet union. It surely is the case that 99% of the users want 10% of the data at most. I'll have to work ability to select the file and download & cache it only if the relevant query is asking for it.
thimabi
I have never imagined that simply by using your app it would download and cache a 100 MB file on my phone. If I hadn’t read the discussion here, I wouldn’t even know about it.

That’s an irresponsible waste of both bandwidth and storage. You should have really made that clear in the website.

afiodorov
A 100 mb is not much - about 2 mins on youtube
smusamashah
Is that data file also in your github repo or is it a third party source?
afiodorov
The data was produced using this script: https://github.com/afiodorov/imdb-sql/blob/main/notebooks/im...

It is on hosted on http://www.imdb-sql.com/imdb01-11-2024.parquet

In fact the main reason this project exists is

a) I wrote a jupyter notebook ages ago that'd join the raw data into a queryable form.

b) I committed it and forgot about it after the initial viewing of top movies/series.

c) For the Halloween I wanted to find a good-rated horror movie, a genre I don't watch much.

d) I found my notebook but it was a drag to get it running again, first pandas would keep throwing OOM errors so I had to migrate to polars. Secondly I had to find a spare working laptop since iPad is my primary off-work computing device as of late. Finally the schema is not so intuitive - I need to look up things like region/language codes.

I want something I can just leave unattended but still be able to use a few months later without going through a dev-env set-up. A static website with all the data is one potential way of avoiding maintaining it as it should keep running, just like an exe would keep running on Windows in the past.

I avoid maintaining personal projects as my software job requires me to maintain software and I don't find it enjoyable to do the same in my spare time.

dewey
The raw data is available here in case you are curious: https://datasets.imdbws.com
cube2222
It's so nice that they make this data available in such a fashion.

That said, it would be amazing to have (anonymized) data about raw ratings per person, and be able to build your own personal recommenders based on that... One can dream!

account42
> It's so nice that they make this data available in such a fashion.

Is it or are they just trying to preempt a more open alternative.

dewey
It always seems more like an forgotten artifact on their end that is used for universities and research. They are not really a competitor to the open platforms as contributing on IMDb is very limited.
croisillon
what are open competitors to IMDb?
dewey
TMDB and many others, some specific to Anime or other types of content.
miah_
I really like imcdb, or The Internet Movie Car Database. Want to know what cars were in a movie? Check imcdb!

https://www.imcdb.org/

croisillon
haha, thanks, we need a movie database database!
Cyph0n
TMDB, TVDB, AniDB & Anilist (anime)
plopz
You should check out movielens, they have a couple recommenders as well as some data sets you can download.
bambax
Interesting! But it seems these datasets don't contain the description of each movie? Do you know where tools such as Jellyfin or Plex get them? Do they simply scrape the website?
JamesSwift
By default, Jellyfin uses TMDB (TheMovieDb) and OMDB (The Open Movie Database) as its metadata providers
soco
Took me a while to discover the SQL can be edited...
afiodorov
Sorry about that. I prefer for my UI to be discoverable.

P.S. We can share links too https://www.imdb-sql.com/?query=select+1

The SQL flavor is https://duckdb.org/docs/sql/query_syntax/select

Pikamander2
> I prefer for my UI to be discoverable.

I'm not quite sure what you mean by this.

If you gave the SQL a light gray border and perhaps overflow-y:scroll, it would make it much more obvious that it can be edited directly without needing any other adjustments.

afiodorov
Thanks for the suggestion, will try to improve soon.

If anyone with actual css experience wants to open a PR in the meantime I'll be very happy!

_thisdot
Taking a look right now. I found the source code from another comment in this HN thread. Would be better if you linked it in app
srmarm
Yeah, I also struggled with the purple box - I assumed I'd change the options, click run and it'd reload the data accordingly. Almost quit before I realised you have to build and then run. If the changes in the purple box were reflected in the SQL code preview that would be much better.

That being said it's an impressive demo of what the browser can do without breaking a sweat! Will try and discover some new stuff to watch now!

nasarquis
Nice. It would be great if you include a filter by apps such as Netflix, Prime and Max
croisillon
as someone who is tinkering in that area, i say really nice job!
paol
Seems to be some normalization problem with the data, right in the 1st page of the default query there's a duplicate entry.
afiodorov
Good spot, will deduplicate in the next iteration.

However titles are repeated often due to the region/language variations.

paol
Since you're denormalizing to a single table, I think the correct way to handle this would be to aggregate all the titles into the title column.

Although "Untitled Pixar Animation Project" is basically garbage data, but that's a harder problem to solve...

afiodorov
deduped all rows with a simple .uniq() call in polars before saving
wickedOne
nicely done!

would be even better if modifications to the actual select part would somehow persist when building the query

afiodorov
Will think about improving the ui in this aspect...
resurge
Hah, someone else trying to find some good movies they haven't watched?

I've also been working on something recently to quickly search in the IMDB datasets. No hosted version yet though.

https://github.com/jeroenpelgrims/imdbsearch

Mainly also to learn some rust/htmx.

yoavm
Love seeing things like this! I think static databases are underrated and we're going to see more of that soon. I'm working on a similar project - https://github.com/bjesus/teatime - for book reading. Any Github repo can be a database source, as long as it contains some JSON configuration file and has the matching repo tags.
kaon_
This is cool. I don't watch so many movies anymore, so there must be many good ones i've missed. Is there a functioning natural language query somewhere?

"I want to watch a science fiction movie that is not a super hero movie. Movies I love are Dark City and Daybreakers. Prioritize movies of short length, nothing more than 2h30. Filter out movies I've already rated, here's my Watchlist"

afiodorov
It'd be trivial to ask LLM to generate the query; but since it's a client-side app there's nowhere to store the api key - so each user would have to supply one, which is a bit of an awkward experience.
6510
You need to watch Just Imagine 1930.

The movie has countless things we know from much later productions and countless things not seen elsewhere.

It takes place in the distant future, 1980, a place where everyone has a flying car, people have numbers in stead of names, the government decides who you can marry and babies come out of vending machines.

One IMDB comment described it as: I sat on the tip of my chair wondering if it could get any stranger and then it did!

Crazyontap
One neat trick I discovered for finding good movies on IMDb is to delve into the review history of users who share my unique tastes, especially when they diverge from mainstream opinions.

For example, I found "Paris, Texas" to be pretty disappointing, yet most reviews were overwhelmingly positive. So, I sought out others who also didn't enjoy it and explored their review history to find movies we both agreed on.

Occasionally, you'll hit the jackpot and find an avid reviewer whose taste aligns perfectly with yours, providing a treasure trove of excellent movie recommendations.

I like to call these users my "IMDb doppelgangers."

cptaj
So, obvious question, is there a database of IMDB reviews?
wodenokoto
There used to be a recommendation engine called tastekid where you could search for multiple movie likes and dislikes in the same query.

Another plus was you didn’t even need to create a profile. Maybe that was their downfall

munchler
This is the premise of Criticker.com. You rate movies, and it will show you recommendations from similar movie watchers. They call it a "Taste Compatibility Index".

https://www.criticker.com/tcis/

jogi21
Criticker has been around since 2005, is super accurate with movie and tv recommendations and they're now owned by a big company!
smusamashah
Is it querying an external dataset or is the whole dataset loaded in browser first?

Currently there are only few columns, can there be more e.g. I was looking for movies based on age rating for example (for my kid or myself)

nzealand
Try a Bayesian sort.

You will get a better result.

SELECT * EXCLUDE (titleType, primaryTitle, language) FROM 'imdb01-11-2024.parquet' WHERE (region is null and titleType = 'movie') ORDER BY ( (numVotes * averageRating)+(1000007))/ (numVotes+1000000) desc LIMIT 100

afiodorov
That's awesome. Care to explain? Looks like Laplace estimator of some kind?

P.S. Query sharing is supported:

https://www.imdb-sql.com/?query=SELECT+*+EXCLUDE+%28titleTyp...

afiodorov
Ah 7 might have been a typo. May be it was meant to be *7? Then that's exactly the Laplace estimator. We start with a prior of 1_000_000 votes of 7.0 and then adjust as we accumulate real votes.
nzealand
Yup. The correct query is:

SELECT * EXCLUDE (titleType, primaryTitle, language) FROM 'imdb01-11-2024.parquet' WHERE (region is null and titleType = 'movie') ORDER BY ( (numVotes * averageRating)+(100000 * 7))/ (numVotes+100000) desc LIMIT 100

bufferoverflow
Looks like it's dead.
afiodorov
First load takes some time, it is downloading a parquet that's a 100mb and has more than 1.5 million titles of series and movies.
coryfklein
IMDb must have a lot of Indian users!
afiodorov
Bollywood is the second largest film industry!