6 ms·
Ranked Searches with SQL
- abstractbill 19y agoFor anyone wanting to make their searches fast, I would strongly recommend ditching the database. Write a program that sucks your entire database into memory (memory is cheap - unless you really have tons of data, you can afford to do this). Store records as objects or more simple structures, and index them in hash-tables. When you get a query, just look up the objects using the query as a set of keys into your hash-tables. If you take advantage of your knowledge of the data, you'll be able to do any intersections, filtering and sorting way faster than the database can. This is how I did things when I rewrote justin.tv's search engine. The bulk of the work took me two days, and it's fast (it'll be live in a few days, when we let our designer loose on it - right now it's functional but ugly).
- dfranke 19y agoHere we go again. I'll play my traditional opening move: "How are you handling ACID guarantees?" and then I'll direct you here: http://news.ycombinator.com/item?id=59885 http://news.ycombinator.com/item?id=59885
- abstractbill 19y ago"How are you handling ACID guarantees?" Maybe I'm missing something but I don't see how ACID plays into things. You don't imagine the search engine is putting stuff in to the database do you? From the link above, "Do any of these with your "big hash table" in less than 1 second" 1 second is eternity as far as my search engine is concerned. If I can't handle a request in less than a couple of milliseconds, I'd say I've failed. I've used this same approach in the past on a search engine that indexed 14 million records btw, and I didn't fail ;-)
- dfranke 19y ago14 million? Okay, that's not very much. A RAM-based approach is fine there.
- abstractbill 19y agoHeh, fair enough. Do many startups really have more than a few tens of millions of records to search though?
- dfranke 19y agoI can't speak statistically, but the one I work for now does and the one I'm about to start will too (but in the latter case I'm writing a DBMS, so that's kind of a pathological example). If all your content is human-generated and O(n) in size, then 10^7 is a lot. The big numbers come when your data is produced by a simulation, or when you're doing OLAPey things with O(n^2)-sized intermediate results. Real-life example: keeping a log of all users, the IPs they've logged in from, and when, and looking for evidence of sockpuppet accounts.
- nostrademons 19y agoDaily quote data for the stock exchanges is about 50 GB now. If you do any sort of intraday analysis, you're looking at terabytes of data...
- henning 19y agoWell, Berkeley DB is totally ACID and it's fast as shit. But BDB isn't a database, it's more of a storage engine, which is why complex queries of the kind you like to cherrypick are obviously a poor fit for it.
- dfranke 19y agoI'd call it a database, just not a relational one. It has everything except a query compiler.
- run4yourlives 19y agoHow do you keep it persistent between individual web sessions? Do you run it as a separate process and just make the call when you need it?
- neilc 19y ago... or you can just use a proper full-text search package (e.g. tsearch2 for PostgreSQL), and get all that for free, with a lot more features (stemming, dictionaries, sophisticated ranking, etc.). For non-trivial queries, it is also likely to be at least as efficient as your home-grown search package is likely to be, unless you put a lot of effort into optimizing it yourself. But hey, rolling your own text search from scratch is fun, so don't let me stop you :)
- emmett 19y agoWe're using tsearch2 right now. It's not very flexible, and it has lots of irritating gotchas - or I'm not a competent enough sysadmin to use it properly.
- henning 19y agoOr you could use Lucene (either the native Java version or one of its many ports) and leave the information retrieval to the experts. Do you really want to implement stemming, WordNet integration, etc. yourself? No, you don't, if you know what's good for you.