Sheeri Dot Org – Page 58 – Musings on topics including product management, tech, art.

How I Work

Edit: apparently as I was writing this, Dave put out the call for others to write their own. So, I *have* been asked….

I find Dave Rosenberg’s “How I Work” series fascinating, so I thought I would post how I work, and some tips I’ve picked up along the way.

I enjoyed How Brian Aker Works the best, mostly because some of the ideas were new and fresh to me — for example, polling e-mail every 30 minutes (and thinking about moving to once every hour).

Now, most people I know would say “But I MUST respond to e-mail, the faster the better!” To that I say, “What if you were in a meeting?” Most people will call if they want an immediate answer, and if you’re truly in a meeting, they’ll leave a voicemail message. I wouldn’t suggest polling your pager every 30 minutes, but for me, e-mail is a non-emergency medium and thus Brian’s technique is pure genius for not getting distracted by e-mail.

Not that anyone asked, but:

What is your role?
I am a MySQL Database for Online Buddies, Inc — a company committed to developing on-line communities that provide members with safe, friendly and exciting sites through which members can express themselves and interact with one another as they wish. (That’s straight from Marketing.)

What is your computer setup?
At work, a Dell desktop running Windows. It does not matter what OS I use as most of what I do involves logging into a database or server or researching/reading on the ‘net. I’m fond of saying “All I need is a web browser and a way to SSH and I’m happy.” At home I have a Windows laptop work gave me (also a Dell), although when I buy my own home computers they are Apple laptops.

What desktop software applications do you use daily?
Firefox. I find Safari (Mac OS X) bloated, taking too long to open. Internet Explorer is dead to me until they get tabbed browsing.

I also use Microsoft Outlook to deal with work e-mail, iTunes for music, and SecureCRT for SSH’ing. I’ll use Excel or Word for reporting and reading documents sent to me, but I only have to fire that up a few times a week.

I use Trillian for IM. I use EditPadLite for small text files I need to read on my desktop,

What websites do you visit every day?
gmail, planetmysql, http://gather.com, livejournal, http://everythingsysadmin.com/, and wunderground.com for the weather.

What mobile device or cell phone do you use?
I have a Motorola RAZR. I like the flip phone and the low weight, but some of the missing features (like being able to type more than one letter as an index to my phone book) really annoy me. I also have a Verizon PocketPC which I use during an emergency to login to our machines if I’m not near a computer.

Do you use IM?
Yes. For short technical requests or conversations, or “Have you done this yet?” I find it works well. Much like the phone, if I’m too busy to talk, I don’t respond. And much like the phone, people respect my need for few distractions and rarely use it. My colleagues walk into my office as much as they IM, and almost never call.

Do you use a VoIP phone?
Not yet, although one of my tasks this week is to start using Skype.

Do you have a personal organization theory?
Oh yes. I organize everything! I always need a to-do list, as I’m easily distracted. I keep it short — my to-do list is the tasks I expect to work on that day. I use the whiteboard in my office to keep track of weekly tasks and erase them when they are done — there are no more than a dozen at one time. There is always something new that comes up, so I’m never left with nothing on my list, and if I worry about something being forgotten I will put it into request tracking software. My high tendency toward distraction means I might put off certain tasks, but I do so by reading websites, journals, lists and researching. So I end up constantly learning and never needing to “find time” to read that technical magazine.

Physically I organize in piles. Mostly everything is in one to-do pile — ie, if I have a schema I’m modifying I’ll have it printed out with my notes. I’ll clean that out around once a week, and like the whiteboard, there’s never much in it. That’s not to say I’m busy, but if something is in there more than a few weeks, chances are it does not belong there.

I have always had some kind of organizer/planner. It’s something that needs to be small enough to take with me. I learned early on that paper is better than electronics for me, as I can drop my paper organizer all I want with no consequences. It does not sync to my desktop, but I also don’t have to worry about losing data. If I got along better with electronics I’d have a palm synched with LifeBalance. I love the software, but remembering to print out my schedule so I’d have a copy on me at all times did not work.

Anything else?
I cannot recommend Limoncelli’s Time Management for System Administrators (published by O’Reilly) enough if you are finding your current way of working not good enough, or even if you think it’s good enough and want to be sure.

I’d love to see other folks’ ways of working.

Edit: apparently as I was writing this, Dave put out the call for others to write their own. So, I *have* been asked….

I find Dave Rosenberg’s “How I Work” series fascinating, so I thought I would post how I work, and some tips I’ve picked up along the way.

I enjoyed How Brian Aker Works the best, mostly because some of the ideas were new and fresh to me — for example, polling e-mail every 30 minutes (and thinking about moving to once every hour).

Not that anyone asked, but:

What desktop software applications do you use daily?
Firefox. I find Safari (Mac OS X) bloated, taking too long to open. Internet Explorer is dead to me until they get tabbed browsing.

I use Trillian for IM. I use EditPadLite for small text files I need to read on my desktop,

What websites do you visit every day?
gmail, planetmysql, http://gather.com, livejournal, http://everythingsysadmin.com/, and wunderground.com for the weather.

Do you use a VoIP phone?
Not yet, although one of my tasks this week is to start using Skype.

I’d love to see other folks’ ways of working.

Sakila Knit

One of the things I did this weekend was knit the pattern I’d made for Sakila, the dolphin in the MySQL logo. Click on the image for a bigger picture:

The only problem is I have no idea what to do with it. I have more of the orange and blue yarn. I thought I would make it into a purse but it turned out much wider than I expected. I could make it into a big handbag but I don’t think I’d use it. Any suggestions?

The pattern itself:

dolphin: (56 sts wide by 59 rows) When I say "k to stitch marker and remove", I mean knit to the stitch marker, and take the stitch marker off.


cast on 56 sts.

knit 2 rows
I had to tweak the pattern mid-knit, so I *think* this is the correct pattern.  If the count looks OK for rows 40-56 then there's nothing to worry about.  Please let me know if you try this.  I used size 9 needles and Lily Sugar & Cream Yarn in orange and navy blue.  As you can see in the picture above, it turns out to be over a foot wide.

1) k4, p5, k1, place stitch marker, k to end 2) p to stitch marker & remove, k7, p3 3) k2, p3, k1, p6, k2, place stitch marker, k to end 4) p to stitch marker & remove, k4, p6, k2, p2 5) k2, p3, k6, p7, k4, place stitch marker, k to end 6) p to stitch marker & remove, k10, p6, k3, p3 7) k4, p2, k12, p5, k2, place stitch marker, k to end 8) p to stitch marker & remove, k6, p12, k3, p4 9) k5, p3, k14, p4, k2, place stitch marker, k to end 10) p to stitch marker & remove, k4, p16, k2, p to end 11) k6, p3, k5, p2, k9, p4, k1, place stitch marker, k to end 12) p to stitch marker & remove, k3, p10, k3, p4, k3, p to end 13) k7, p3, k5, p2, k11, p3, k1, place stitch marker, k to end 14) p to stitch marker & remove, k3, p19, k2, p to end 15) k8, p2, k20, p4, k1, place stitch marker, k to end 16) p to stitch marker & remove, k4, p20, k3, p to end 17) k8, p3, k21, p3, k1, place stitch marker, k to end 18) p to stitch marker & remove,k4, p20, k2, p to end 19) k10, p2, k21, p3, k1, place stitch marker, k to end 20) p to stitch marker & remove,k3, p21, k2, p to end 21) k11, p3, k21, p2, k1, place stitch marker, k to end 22) p to stitch marker & remove, k3, p21, k2, p to end 23) k12, p2, k22, p2, k1, place stitch marker, k to end 24) p to stitch marker & remove, k3, p23, k1, p to end 25) k12, p1, k23, p3, k1, place stitch marker, k to end 26) p to stitch marker & remove,k3, p24, k2, p to end 27) k11, p2, k25, p2, place stitch marker, k to end 28) p to stitch marker & remove, k2, p25, k3, p to end 29) k10, p2, k26, p3, place stitch marker, k to end 30) p to stitch marker & remove, k2, p27, k2, p to end 31) k10, p2, k5, p2, k20, p3, place stitch marker, k to end 32) p to stitch marker & remove, k2, p20, k3, p4, k3, p to end 33) k11, p2, k3, p4, k20, p3, k1, place stitch marker, k to end 34) p to stitch marker & remove, k3, p20, k5, p3, k2, p to end 35) k11, p2, k3, p5, k20, p4, k3, place stitch marker, k to end 36) p to stitch marker & remove, k6, p20, k3, p1, k2, p3, k1, p to end 37) k12, (*p2, k2* 2 times), p2, k22, p5, k1, place stitch marker, k to end 38) p to stitch marker & remove, k4, p23, k2, p3, k6, p to end 39) k13, p4, k4, p2, k25, p3, k1, place stitch marker, k to end 40) p to stitch marker & remove, k3, p25, k2, p5, k3, p to end 41) k23, p1, k26, p3, k4 42) p3, k3, p26, k2, place stitch marker, p to end 43) k to stitch marker & remove, p2, k26, p4, k2 44) p2, k8, p21, k2, place stitch marker, k1, p to end 45) k to stitch marker & remove, p2, k18, p8, k5 46) p9, k4, p17, k2, place stitch marker, k1, p to end 47) k to stitch marker & remove, p2, k18, p3, k9 48) p8, k4, p17, k2, place stitch marker, k1, p to end 49) k to stitch marker & remove, p3, k17, p4, k7 50) p5, k5, p18, k2, place stitch marker, p to end 51) k to stitch marker & remove, p3, k18, p4, k5 52) p5, k3, p19, k2 place stitch marker, p to end 53) k to stitch marker & remove, p3, k19, p2, k5 54) p4, k2, p19, k3, place stitch marker, p to end 55) k to stitch marker & remove, p4, k19, p2, k3 k 2 rows

One of the things I did this weekend was knit the pattern I’d made for Sakila, the dolphin in the MySQL logo. Click on the image for a bigger picture:

The pattern itself:

Continue reading “Sakila Knit”

Neat trick, and some notes

This falls under “I knew I could do this but I didn’t realize I could apply it this way!”

You can do

SELECT 1 from table1;

Which will return n rows, each row having 1 field whose value is 1. n is the number of rows in table1.

SELECT "string" from table1 works similarly.

However, I never considered using

SELECT "string" as "debug statement" to debug code.

For instance,

mysql> SELECT "SELECT foo from bar where baz>0" as "debug";; +---------------------------------+ | debug | +---------------------------------+ | SELECT foo from bar where baz>0 | +---------------------------------+ 1 row in set (0.00 sec)

Neat trick! This is why I follow the MySQL Users general list, because every so often a gem like this comes up. Plus, I can’t resist helping folks out. And if I’m not accurate, someone else will step up.

Some more random thoughts, as I’m thinking about them and did not really want to make a separate post for them — at the MySQL Users Conference, the rank on PlanetMySQL.com came up, from “the conference postings will increase my rank” to “so-and-so cheats and makes lots of little posts.”

Now, I was just happy to make the list. Of course, now that I did post a lot at the conference, my rank went up from about #12 to about #7. As an experiment, I’m attempting to publish an average of one post a day for May and see what my ranking is at the end of May. So far, so good. And of course, as always, all of my posts will have content!

I think it would be neat to have a little line on planetmysql.com that shows where “1 post a day”, “1 post a week” and “1 post a month” fall for the top posters.

This falls under “I knew I could do this but I didn’t realize I could apply it this way!”

You can do

SELECT 1 from table1;

Which will return n rows, each row having 1 field whose value is 1. n is the number of rows in table1.

SELECT "string" from table1 works similarly.

However, I never considered using

SELECT "string" as "debug statement" to debug code.

For instance,

I think it would be neat to have a little line on planetmysql.com that shows where “1 post a day”, “1 post a week” and “1 post a month” fall for the top posters.

How I have a successful MySQL User Group

Note the title is “How I have a successful MySQL User Group.” There’s more than one way to do it, I’m sure. There are 3 basic principles:

1) Try to do as little work as possible.

2) Make your colleagues do as little work as possible.

3) Always have a topic/presentation

These three principles will get you far, and should be weighted equally. Do not use principle # 1 as an excuse to not follow principle #3. As well, “doing work” includes “paying money”. With that being said:

Make your user group easy to get to. This has different meanings for different areas. It may mean near a major highway interchange, it may mean near a mass transit station. Whatever it means for you, make it easy.

When the Boston MySQL User Group first started, we had free space in an office building right in the city of Boston.

Pros	Cons
Free	Not enough parking
Close to the subway and train station	No free parking
The street was clearly labeled	We had to have a person stand by the door to let people in
The building was clearly labeled
There was a great pub next door

Then we moved to a space at MIT:

Pros	Cons
Free	Passers-by eat the pizza and drink the soda
Close to the subway and train station	We had to have good maps to find the location
Plenty of free parking

Now, you may not be so lucky to find a place that will give you space for free. Consider local universities. Also, local libraries (university or otherwise) usually have some meeting space where they might be able to host you. Our first space, an office space, was gotten through a contact at MySQL. Contact some companies who use MySQL and ask if they’ll lend you space — most tech companies have folks there at night anyway, and it’s free advertising for the company! There is a book company near you, or a Pearson VUE center. Remember: it does not hurt to ask. The worst people can say is “no”, and if it’s contributing toward education, a book company (Pearson Education, Apress, O’Reilly, etc) might sponsor it.

Getting a free space is key, particularly if it has A/V equipment for you to use (if you want to have lectures).

Granted, if you want to just have a freeform discussion, any pub or coffee shop or location will do.

You may think about having it in your home. Consider issues of personal safety, domestic distractions (kids, pets, etc), parking and transit, and the fact that most people feel comfortable going to their first meeting on “neutral” ground. However, if the culture surrounding where you live encourages it, go for it!

If you must pay for a location, get a company to sponsor it. Get their monetary and time commitment in writing if you can (ie, they will sponsor for a year).

Make the meetings easy to remember. Have them on the same day of the month (ie, 2nd Monday). Do not make them conflict with something similar (ie, PHP group, linux group). Try to have it at the same place each time.
Figure out what your group wants and give it to them.

Do they want lectures on specific topics? Maybe go to a place with wireless access and a “troubleshooting” meeting every so often. Maybe they want a blog with news, or war stories. Maybe a lending library would work. Anyone that comes to your meeting is looking for something. Ask each new member what they’re looking for. If they say “to learn more” then go with lectures — make sure to accomodate all skill sets, and have advanced lectures as well as beginning lectures. If they want to get to know each other better, go to a pub and talk about MySQL while you down a pint. Or run a charity fundraiser and donate the proceeds to a not-for-profit like the Electronic Freedom Foundation. There’s plenty the group can do, including having contests, or even starting a business together if you’re plucky.

Have incentives.

But remember rule #2, so keep costs as low as possible. Ask companies to sponsor dinner or light refreshments. Don’t be shy; ask book companies for books, T-shirts and buttons to give away as prizes. Again, the worst they can say is “no”, and I’ve found book companies and MySQL AB to be VERY accomodating, as well as local businesses. Ask company techies to present. Ask MySQL to send someone out to present.

Be available, and follow up when you say you will. Often times folks will have a question. Being able to respond in a timely manner is important. But if you speak out and make regular annoucements, and solicit feedback, folks will know you are accomodating.
Do not do work others can do. When someone asks if you can forward a job listing, have them post it to your group members. Or, better yet, have them come to your meeting and make an announcement in the first 5 minutes. This includes advertising! Find your local MySQL Sales Rep and ask them to help promote your group. Promote it on any site you can think of — in a blog, on Craigslist, heck, make flyers and post it around town if you have to. Get the word out there!
Have the right attitude. It’s not your meeting, it’s your group’s meeting. If they want different topics, ask someone to research and present. It’s OK if the research isn’t perfect or complete; many times your group will fill in with their experiences.
It’s OK to be wrong. No, really. If you don’t know the answer to a question, look it up later on, and follow up. Or ask the group if anyone knows. Also, presentations take time to make, and if you feel compelled to get every single detail right, they’ll take a lot longer. Keep an offline copy of the manual so you can look up anything you need to.
Ask others for help. Even if you think you can do it better or faster. Your group should be able to survive without you, so if you can’t make a meeting all is not lost. This includes asking others to do a presentation of any length — it can be a 10 minute “this is how we use MySQL and this is what our setup looks like” to an hour or more. It does not have to be intimidating. Ask folks to present workshops they’re presenting at conferences, or present notes from conferences they go to. People need to feel like part of the group if the group is to be successful.
If you can’t get a presentation, download one. MySQL is excellent at providing past webinars. Download it to your computer, and have a free lecture from an expert, that you can pause and rewind if you need to. MySQL Webinars. The Boston MySQL User Group videorecords the presentations and puts them online — see the links under “Presentations” at http://www.sheeri.net

Note: The Boston MySQL User Group is successful, but we don’t quite do everything above. I’d love to have more socialization, and I think once summer comes I’ll think of something geeky and fun to do outdoors, with little cost. Or maybe just a bowling trip.

Note the title is “How I have a successful MySQL User Group.” There’s more than one way to do it, I’m sure. There are 3 basic principles:

1) Try to do as little work as possible.

2) Make your colleagues do as little work as possible.

3) Always have a topic/presentation

Make your user group easy to get to. This has different meanings for different areas. It may mean near a major highway interchange, it may mean near a mass transit station. Whatever it means for you, make it easy.

When the Boston MySQL User Group first started, we had free space in an office building right in the city of Boston.

Pros	Cons
Free	Not enough parking
Close to the subway and train station	No free parking
The street was clearly labeled	We had to have a person stand by the door to let people in
The building was clearly labeled
There was a great pub next door

Then we moved to a space at MIT:

Pros	Cons
Free	Passers-by eat the pizza and drink the soda
Close to the subway and train station	We had to have good maps to find the location
Plenty of free parking

Getting a free space is key, particularly if it has A/V equipment for you to use (if you want to have lectures).

Granted, if you want to just have a freeform discussion, any pub or coffee shop or location will do.

If you must pay for a location, get a company to sponsor it. Get their monetary and time commitment in writing if you can (ie, they will sponsor for a year).

Make the meetings easy to remember. Have them on the same day of the month (ie, 2nd Monday). Do not make them conflict with something similar (ie, PHP group, linux group). Try to have it at the same place each time.
Figure out what your group wants and give it to them.

Have incentives.

Be available, and follow up when you say you will. Often times folks will have a question. Being able to respond in a timely manner is important. But if you speak out and make regular annoucements, and solicit feedback, folks will know you are accomodating.
Do not do work others can do. When someone asks if you can forward a job listing, have them post it to your group members. Or, better yet, have them come to your meeting and make an announcement in the first 5 minutes. This includes advertising! Find your local MySQL Sales Rep and ask them to help promote your group. Promote it on any site you can think of — in a blog, on Craigslist, heck, make flyers and post it around town if you have to. Get the word out there!
Have the right attitude. It’s not your meeting, it’s your group’s meeting. If they want different topics, ask someone to research and present. It’s OK if the research isn’t perfect or complete; many times your group will fill in with their experiences.
It’s OK to be wrong. No, really. If you don’t know the answer to a question, look it up later on, and follow up. Or ask the group if anyone knows. Also, presentations take time to make, and if you feel compelled to get every single detail right, they’ll take a lot longer. Keep an offline copy of the manual so you can look up anything you need to.
Ask others for help. Even if you think you can do it better or faster. Your group should be able to survive without you, so if you can’t make a meeting all is not lost. This includes asking others to do a presentation of any length — it can be a 10 minute “this is how we use MySQL and this is what our setup looks like” to an hour or more. It does not have to be intimidating. Ask folks to present workshops they’re presenting at conferences, or present notes from conferences they go to. People need to feel like part of the group if the group is to be successful.
If you can’t get a presentation, download one. MySQL is excellent at providing past webinars. Download it to your computer, and have a free lecture from an expert, that you can pause and rewind if you need to. MySQL Webinars. The Boston MySQL User Group videorecords the presentations and puts them online — see the links under “Presentations” at http://www.sheeri.net

Open Source and Money

Matt Asay wrote an article about open source leakage. It’s quite good, and got me thinking.

First I thought, “Open source companies do not ‘lose’ revenue to non-paying customers, they just do not gain revenue from them.” But that’s based on the model of open-source software I have in my head that open source software usually starts out as a free, collaborative effort, and if enough folks get enough steam and come up with a business model (aka “a way to get paid”), then they form a company around the open source software.

Simplifying that model: open source software is free until it’s not.

Saying there is leakage does not do justice to the fact that the river flowed freely until the company came along and dammed up the river. Sure, maybe there’s a big leak, but there’s a lot more not leaking than there is leaking.

But open source != free. And it’s not required, either.

Take a for-pay e-book. You buy a license for a personal copy of a book, and read it. You’re not supposed to make copies of the e-book, or redistribute it, etc under the terms of your license.

However, you can “delve into the source code” of an e-book. You cannot change it and redistribute it claiming you authored it. You can, however, change the words in the book to make it more meaningful for yourself. There’s nothing to stop you from annotating the work. The source is open — all the words are there for you to play with.

Now, open source is like that e-book. There’s nothing that says open source HAS to be free. By convention, it has been. Patents are good for keeping secrets and making money. The open source movement shuns patents. But they’re not shunning the making money. They’re shunning the secretive nature of it.

I once had a housemate who was vegan, whose brother owned a restaurant 3,000 miles away. She made the best vegan pancakes, and refused to give out the recipe because it was her brother’s secret recipe. Now, vegan pancakes are not that complicated. There are about 5 ingredients that could go into them. Why the need for the secret? Because her brother would lose business? Restaurants produce cookbooks all the time; I doubt business would die if the recipe got out.

And that’s what open source is all about — “I have this great recipe for vegan pancakes, and I want to share it with you.”

Let me be clear: I think that open source companies deserve to be paid for their work. Much of the time the products are excellent. That does not mean it’s bug-free. (I live in the United States, and I think it’s one of the best countries to live in, but that does not mean we do everything right….far from it!) Most of this is a semantic rant.

I find it amusing that it used to be difficult to convince big companies that open source was good, because upper management equated free with bad. Now that we’ve convinced some of them, we’re upset that it’s difficult to convince big companies that they should pay for something we give them for free.

I think MySQL actually has a sane licensing policy, and I think they’re going in the right direction with MySQL Network. Having free software and for-pay technical service and support seems like a good mix….for MySQL. I can certainly see that being abused by a company that has a bad product, intentionally, to get more $$ out of customers because they are forced to get support — much like Remedy requires lots of customization before it actually can work. MySQL is much better than that.

I think MySQL in particular would do well to offer “Optimization Consulting” for a fee. I know they offer that already, but particularly call it that, as I am always hearing about companies looking for a MySQL consultant for a few weeks to help them optimize their servers.

Matt Asay wrote an article about open source leakage. It’s quite good, and got me thinking.

Simplifying that model: open source software is free until it’s not.

But open source != free. And it’s not required, either.

Take a for-pay e-book. You buy a license for a personal copy of a book, and read it. You’re not supposed to make copies of the e-book, or redistribute it, etc under the terms of your license.

And that’s what open source is all about — “I have this great recipe for vegan pancakes, and I want to share it with you.”

Mysql vs. Postgresql

http://tinyurl.com/gvwml

And the winner is….MySQL.

http://tinyurl.com/gvwml

And the winner is….MySQL.

Selectivity and Index Performance

Let’s look into selectivity, as this is an important topics when looking at index performance. (Oooh, I said “performance”, watch everyone’s ears perk up!).

This will probably answer the questions “Why isn’t MySQL using my index?” or “Why is my query so slow when I have an index on it?”

Selectivity describes how different values of a field are. It is a number from 0-1, although you can also think of it as a percentage. A value of 1, or 100%, means that each value in the field is unique. This happens with UNIQUE and PRIMARY keys, although non-unique fields may have a selectivity of 1 — for example, a timestamp value in a not-often-used table.

To calculate this, you take the total number of DISTINCT records and divide by the total number of records.

My company has a large Users table, so I grabbed some statistics off of that:

+----------+ | count(*) | +----------+ | 817666 | +----------+ 1 row in set (0.63 sec)

+--------------------------+ | count(distinct username) | +--------------------------+ | 817666 | +--------------------------+ 1 row in set (1.63 sec)

So the selectivity is 81766/81766, or 1. If this were not a UNIQUE KEY already, it’s a good candidate for one.

the “created” field is a timestamp for when the user record was created
+-------------------------+ | count(distinct created) | +-------------------------+ | 811227 | +-------------------------+ 1 row in set (2.04 sec)

As I expect, there are *some* duplicates, but for the most part, everyone has a different creation time. 811227/817666 = 0.99.

+--------------------+ | count(distinct IP) | +--------------------+ | 544694 | +--------------------+ 1 row in set (2.35 sec)

This is interesting — lots of people logon from public places, or use their friends’ computers, so there are duplicate IP’s (the last IP used to login is associated with a user, so it’s a 1-to-1 relationship). The selectivity here is 0.67.

+-------------------------+ | count(distinct browser) | +-------------------------+ | 25699 | +-------------------------+ 1 row in set (1.70 sec)
This is what the server reports the user’s browser is. It records the last browser used by the user. This gives us about a 0.03 for selectivity.

+---------------------+ | count(distinct age) | +---------------------+ | 83 | +---------------------+ 1 row in set (0.63 sec)

There are only 83 different reported ages on our site. That makes the selectivity of age 0.000101508. That is very low, effectively zero.

So why is this important? I’m glad you asked….

MySQL has a cost-based optimizer. This means that MySQL calculates the costs of different ways of performing a query and then chooses the cheapest one. Sounds reasonable, right? Well, calculating the costs is an inexact science. In order to calculate the exact cost, the optimizer would actually have to run the query. So an estimate is taken, and the estimate is wrong sometimes. Most of the time the estimate is correct.

In contrast, some database systems allow a rule-based optimizer. This means that no matter what the data state, the database uses rules to figure out the “optimal” path to the query. In most enterprise-level database systems, a cost-based optimizer performs better than a rule-based optimizer. In other words, there are so many exceptions to the rules that the calculation overhead is worth it.

(Just to clarify, in both systems, the correct result set will be generated. The optimizer determines the path to the information.)

This cost-based optimizer uses selectivity information when it decides whether or not to use an index.

But what does this mean for me?

Well, in the example above, this means if you want to query folks by age or age group, it’s useless to put an index on it. It’s a waste of cpu time and disk I/O to have an index for something with such a low selectivity. The optimizer will NEVER use it.

I’ve heard that the optimizer will do a full table scan if it calculates it will return more than 30% of the table. Why is that number so low, why not more like 50 or 75%? Well, first the server has to go to the index, search the index, and find if the index record matches. Then it needs to follow the index record’s pointer to the real record on disk. And the MySQL gurus have decided that around 30% is the place where using the index is slower than just doing a full table scan.

(Note: I’m not exactly sure what the # is but I’ve heard it’s around 30%. As well, at the user conference I saw graphs that showed that for the most part this was true. I thought it was in Jay Pipes’ Performance Tuning presentation, but the graphs are not in the slides. Pointers are appreciated.)

So in this case, should we put an index on browser? Well, this is one of those cases where I’d think about how often we’d be doing queries and how much we care about server performance doing a report versus server performance while inserting or updating a record. If we really care one way or another, go that way. And document!

Another thing to consider is the nature of the data. For something like age, that’s not going to change. Sure, we might have some 120 year olds eventually, but there’s not going to be that much variance. For browsers, there will only be more and more types put out, considering different version numbers and OS configurations of standard browsers as well as mobile phone browsers.

However, if it does not matter, or if it’s too difficult to decide which is more important (your boss says “we can’t be slow during normal usage OR during reporting!”) I default to MySQL — it’s better at optimization than I am. I’d probably put an index so MySQL could decide whether to do a full table scan or use an index, particularly given that I expect the number of browsers to keep increasing.

For something like IP, that has a selectivity of 0.67, so putting an index on it is worth it if we query on IPs a lot.

I hope this article has been helpful!

Jay Pipes has a great article on using the INFORMATION_SCHEMA to find out selectivity:
http://tinyurl.com/kfffp