5 ms·
mySQL is very slow when it comes to joins, groups, etc. You are always better off with simple select's. If you are using for example PHP the only viable solutio
by z3t4 9y ago
mySQL is very slow when it comes to joins, groups, etc. You are always better off with simple select's. If you are using for example PHP the only viable solution is to have the db crunch it. But when using Go, NodeJS et al. you can pull the data out as a stream/array-like and apply filter/map/reduce and the logic would probably be easier to manage, rather then generating a complex SQL query. And would also allow you to stream the result to the client, instead of having the user wait for it all before they see anything. A lot of money could probably be saved by having the data on the client side, in for example web db, and only use the servers for backups and syncing the data between clients.
- amq 9y agoIn my experience, if you have a proper schema, even very complex, but thought-out, queries are instant on virtually any database size.
- z3t4 9y agoStuff like the group builder in the article is hard to reason about as there are so many combinations. MySQL is bad at optimizing O(n2), no matter how you design the schema it will be slow. The solution, like they probably did in the article, is to break it down into separate more simple O(n) queries.
- amichal 9y agoi have a 10+year old project on mysql doing 6+ way joins, groups, etc against 100million row tables in sub second time. It all depends on the indexing, disk layout and size of intermediate products etc. We dont know why they did "UNION ALL" with huge intermediate products (it seems like the query could be a single index/table scan on its face) but that is likely the slow down "EXPLAIN SELECT" would tell us