All posts tagged mysql

When you work in coffee shops you get to make friends with the regulars. Note that I said “in coffee shops,” not “for coffee shops.” I spend most of my afternoons working on my laptop, maybe two or three hours a day. I have no other occupation. Actually, I don’t like work that much. If I accepted a $15/Hr job I would need to work all day, five or six days a week; no thanks. I’d rather charge $50 an hour, thank you. How can I do that?

Computers these days come with a flurry of bundled software. It didn’t used to be that way. You bought a computer, turned it on, and all you got was a blinking cursor. The computer out of the box could do nothing, not even an addition. You had to program it, give it a list of instructions in a language it could use. This hasn’t really fundamentally changed. You just get the “free” software included. Same goes for a web site. Old web pages were simple texts with links, sometimes images. Today they do all kinds of things. You still have to tell the server how to do this, by writing a program.

It isn’t as hard as you might imagine. Think of a program as a recipe. You have a list of ingredients and instructions on how to make a cake. Someone needs to follow those instructions in order to make a good cake. Well, that’s programming. You’re the chef, the computer is the cook who follows your instructions. Great thing is, the cook never makes mistakes! On the downside, he speaks a weird language you haven’t heard before..

Could you do it? Most likely. The main issue people have with programming is patience. Imagine a chef developing a new cake… How many cakes will he botch up before finally getting what he wants? Programming is as much about solving glitches than it is about writing code. It can be very frustrating, but you learn to take a deep breath and keep on going. If you can do this, as well as sit down and concentrate on a task for a while, you are very likely to become a decent programmer.

There is a lot of work in web site development, linking sites to databases and getting servers to talk to each other, like when processing a credit card. I am looking at Android application development right now to see if it could be my cup of tea.. I got my first programming gig after reading a Perl language book and studying for a month… The book cost me $50 and I made $2K the following month using what I had learned. I wouldn’t suggest Perl today but you get the picture.

How much is it going to cost you? Nothing! Only time. These days all you need to know is online. You can go to Udemy and get classes for free or for a reasonable fee. It will take you a while if you never wrote code before, but don’t give up. Sweat it now and make a decent living for the rest of your life. Programmers get paid anywhere from $20 to $200 an hour.

So what language to learn? Apple uses Objective-C and Android uses Java. For the web, learn PHP. If you want to work on servers, Python is very useful and a good starter language. C++ is still a very good option which can lead to pretty complicated (and well paid) work. Learn it along Objective C if you want to get into iOS programming. For Window$ applications I’d suggest Delphi, but finding good support for it has never been easy, though it is a great development platform. There are hundreds of programming languages, if not more, but these are the main ones. I’d suggest not getting into obscure or experimental languages unless you’re already fluent in a marketable one. Don’t forget to learn some database management skills for which I suggest MySQL.

If you like computers and can think analytically, why settle for minimum wage? As a programmer, you can work anywhere in the world if you have an Internet connection. You can choose to work two hours a day or sixteen, it’s up to you. I prefer to work less, charge more and enjoy my free time.

I answer that question fairly often. There used to be very few choices. When I was about fourteen, my parents bought me a Sinclair ZX81 with a whopping 1kb of memory. After you turned it on, a prompt appeared, then, nothing… Pretty disappointing at first. You had to learn the Basic programming language to get it to do anything. I’ll be forever grateful to them for spending the equivalent of a thousand dollars on technology that at the time, was just a novelty. Today I make a living sitting in coffee shops sipping frappuccinos and writing code; better than digging trenches…

We have many other choices these days, and deciding which language to learn first can be a difficult decision. I will assume here that your goal is to learn skills you can market to hopefully make a decent living. Otherwise, go ahead and learn anything you’d like.

Most current languages are based on C. There is a lot of hoopla about Object Oriented Programming, and you certainly will need to know OOP, but as a first language, C can’t be beaten. C++ and Objective C are both subsets of C, and if you learn both, you can program on any platform there is, Windows, Linux, Mac, and iOS. Java is also based on C, and platform independent. For web programming, PHP uses a lot of C-like syntax. You simply can’t go wrong with C. Sure, it can be a pain in the ass. Pointers and memory management are, at first, rather annoying. It does however ingrain in you good programming practices an you will appreciate the higher level languages when you get to that point.

So, how do you learn C? Sams Publishing has the best programming books in the business. I suggest “Teach Yourself C in 21 Days,” by Jones Aitken. It provides you with a timeline to follow, reading and working on one chapter per day. Sure, you won’t be selling software after 21 days, but you will have a solid base upon which to build.

Where to go from there? It depends on whether you plan on developing for Windows or Apple OSX. For Windows, C++ is the natural progression. Java is also a good choice, and will allow you to code for Android devices. Objective C is used on all Apple products, Macs and the iPhone, iPod and iPad family of devices. If it was only for Macs, I wouldn’t bother. Not that I have anything against Macs. I just bought a Mac Mini, and OSX is superior to both Linux and Windows (which for the later isn’t very hard). Programming for Linux, well, there isn’t much money there, as you would be competing with hordes of programmers working for free.

I would definitely suggest checking out C++, Objective C and Java for your Post-C learning adventure. Sams Publishing has great books in their 21-days or 24-hours series to learn them. For Objective C, see the O’Reilly book: “Programming in Objective-C” by Stephen G. Kochan.

There are three languages I would like to mention on top of these choices. They are, in my opinion, excellent and worth a good look.

The first one is Python, and excellent platform independent scripting language which can be used to write command line tools, and even full fledged graphical applications, if you ever wanted to take it that far. I use Python almost daily to write database management programs. Perl used to be my first choice for such tasks, but Python is more organized, and has many modules available to do practically anything. And excellent book to learn Python is “Learning Python” by Mark Lutz, from O’Reilly.

A note on O’Reilly books. In my opinion they are of two kinds: The first is great books, even for the beginner, with clear explanations and easy to follow. The second is incomprehensible techno babble that is only readable by autistic savants. There is no middle ground. So, when you buy a book from them, make sure it falls in the first category.

The second is Borland (now Embarcadero) Delphi. The second language I learned after Basic was Turbo Pascal. It is a very good, fast and simple compiled language. For some reason I fail to understand, it has fallen out of favor; same goes for Delphi, which uses Pascal at it’s core. However, Embarcadero released Delphi XE2, which allows you to program for Windows, Mac and iOS at the same time. There is no faster Rapid Application Development tool anywhere, to my knowledge. I have a handful of shareware programs written in Delphi, and plan to use it again, when I can afford the $900 Professional version.

At last, for the web, PHP is the right choice. You can use PHP with Ajax, Javascript and of course HTML. PHP is great to connect to databases like MySQL. I use PHP daily for my customers and myself. Note that Ajax and HTML are not programming languages.

I hope you will find my suggestions useful. Have fun coding and don’t overdose on coffee!

Here are a few MySQL date selection queries:





This week (Sunday-Today):


Last 7 Days:


This Month:


Last Month:
SELECT * FROM table WHERE adate BETWEEN date_format(NOW() – INTERVAL 1 MONTH, ‘%Y-%m-01’) AND last_day(NOW() – INTERVAL 1 MONTH);

I have my aviation site Planenews on a FreeBSD server. As traffic increased, I was getting more database errors. Looking around the web for clues, I discovered that FreeBSD did not have a default my.cnf file in /usr/local/etc. You can find sample files in /usr/local/share/mysql. I used my-huge.cnf, renamed it to my.cnf, put it in /usr/local/etc, et voila (don’t forget to restart MySQL)!

Problem solved? Nope.. I was still getting errors at peak traffic. I then found mysqltuner, a Perl diagnosis tool for MySQL. I was missing a few variables in my.cnf. See the file below, and notice the additions under “Added by Gil.”

# The following options will be passed to all MySQL clients
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
port            = 3306
socket          = /tmp/mysql.sock
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 128M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4

# Added b Gil:
# max_connections 250 crashes my server, use with caution..
#max_connections = 250
wait_timeout = 180
interactive_timeout = 45
tmp_table_size = 64M
max_heap_table_size = 32M

# Disable Federated by default

# Replication Master Server (default)
# binary logging is required for replication

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/
#log-update     = /path-to-dedicated-directory/hostname

max_allowed_packet = 16M

# Remove the next comment character if you are not familiar with SQL

key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M


The site seems to be running fine now, with no errors. I guess I will have to wait for a story to make it to a major social networking site to see if it really can take a heavy load. Please tell me about your optimization tips, and how you prepared for traffic spikes…