Mirepoix is the holy trinity of french cooking. However, this doesn't mean you must limit it to french cuisine. Use it as a base for a soup or sauce, and it'll give it extra, simple depth. Mirepoix goes great in just about everything sauce based. It'll put extra flavor in your alfredo, spice up any soup, and it's that secret flavor in most pasta sauces you've ever said "damn, I want this recipe" about. For a good base flavor I find that having them shredded to a pulp and mixed is unbeatable, but if you want a bit of texture, the method you want to use is called a brunoise. It's when you cut things into thin strips (or julienne) and then turn them 90 degrees and chop them into perfect cubes. This method makes the texture amazing, and is great for appearance if you can maintain a uniform thickness.
When I'm feeling particularly culinary, I keep a bulk amount of it in my fridge or freezer and pull it out and toss it in just about every soup or sauce I make. Freezing mirepoix paste will make it last forever and a day, so you can prepare it and pull it out as you need it.
PROTIP: Freeze it in an ice cube tray, and put the cubes in a ziploc bag. When you want to use them, take out a cube and drop it right in.