Yesterday I managed to optimize a query...
Went from 43 seconds to 0.0702 seconds.

For some reason mysql decided to copy the data of 4 huge tables into a temp table and do its operations there... (the copying to temp tale took 42/43 seconds)

Two composite indexes later and I saved the company hours of time over the course of a few months.

Feels good.

That really is the best feeling!

@dfox it really is, since I work part-time my colleague usually gives me this kind of stuff to do, and I absolutely love it, I optimized a lot more queries, but this is the biggest one is far

Had something similar for a freelance project. Had to introduce proper limits and offsets because in a cron job the script somehow couldn't use as many resources as when run by nginx.

Ahh, I know the feeling! :-)

Best feeling, optimise that query son, optimise it.

Optimization always makes me feel calm on the inside. Whether it be code or otherwise. Just knowing that you saved time is the best feeling ever.

What I love about this type of rants is you get to have an idea on what can be done, in other words think outside your box 😀

@Bitwise yeah, my colleague has 0 formal training with databases (and I barely have any either) it always completely blows his mind how important db knowledge is :D I got to teach him about indexes during the summer and composite indexes this week, which feels amazing since he's got a lot more experience in most programming fields :)

@jamescodesthing I'll keep doing it :D

@RiderExMachina it really is, I think that is the highest level of satisfaction, just knowing you removed one of the biggest frustrations from a department's job

@gitpush yeah, it's amazing once you start discovering all the possibilities :) and keep learning something every day :D

@FMashiro yup true spirit of devRant 😀

@FMashiro Would you mind writing an article on how to optimise the queries. Someone could use that.

I recommend postgresql for anything other than primary key lookups.

PK lookups in mysql are faster because tables are built as a part of the PK index.

MySQL can join tables in only one manner: block nested loops.

Postgresql can do nested loops, hash joins and sort merge joins.

Because in pg tables are stored as heaps, pg can also perform sequential scans which are faster to process if you need to read the majority of the table.

Oh, and don't start me on query analysis. PG is miles ahead. There is no 'explain analyze' in MySQL.

@nickhh I'm sure postgresql is as wonderful as you describe it, however, the company I work at doesn't use it, so I'm stuck for the better or the worst with mysql.

Luckily there is an explain command for mysql as well as a profiler, that goes into microsecond detail on what exactly is going on.

@cyberwalk3r I might, maybe I'll start a blog :)