My dreamhost plan offers me to use cloudflare CDN to speed up my pages. My page uses PHP/MySQL. I was wondering how this works, because everytime the page is loaded it must execute the PHP code and the MySQL query anyways on the dreamhost servers, so cloudlfare can't speed it up, right?
You need to understand what a CDN is. CDN means Content Delivery Network, rephrazed; a network to deliver your content.
Indeed, CloudFlare can't speed up your PHP scripts or MySQL queries. CloudFlare will host your static resources (images, videos, JS and CSS files, etc.) on many servers over the world to permit to all your visitors to download them faster (in relation to their location) when visiting your website.
Just to help clarify...
If you want to extend our caching beyond static content, as mentioned, you can use PageRules to accomplish this.
Really not going to help speed up mysql queries.
A CDN will cache your websites resources that are declared cacheable (or based on some rules) near your users, on data centers spread over the world. As someone real smart said before I was born, "Latency is king". It means that any millisecond "nearer" to your users (data travels no faster than light) will drop response time sharply. I've tested Amazon.com CDN CloudFront, on a real ecommerce website, time dropped 300ms for home page, which may convert to 3% more sales, only by bringing every one of the 85 product pictures 4ms faster.
You can even go further speeding up a website by setting up some content optimizing platform in front of it. The path would then become: User -> CDN -> Content optimizer -> Your web server