S-Video is the third worst and is third best in terms of pre-HDMI consoles. It looks okay on an HDTV and looks good on a CRT. The design of the plug sucks, but the video quality is better than composite video because S-Video separates the luminance and chrominance. The colour is still a little compressed and has a bit of bleeding, but it lacks rainbow banding, dot crawl, and a really blurry image. If your console or computer outputs S-Video, whether it be modded or not, it will most likely look pretty good. You will definitely notice the difference between composite and S-Video.
I would recommend that you at least use this connection with your consoles and computers. All mainstream consoles, except anything before the Super Nintendo, some PAL N64s, any PAL GameCube, Wii, or Wii U, and anything after the NTSC-U and NTSC-J Wii U support S-Video. Many earlier consoles can be modded to output S-Video, like the Sega Master System, Sega Genesis, Atari 2600, NES, etc.