MySQL Optimization. Carsten’s Random Ramblings. « MySQL Pop Quiz #17 | Main | MySQL Pop Quiz #18 » FIND_IN_SET() :: MySQL Function of the Day By Carsten | March 25, 2008 Function name: FIND_IN_SETAliases: -Function type: String function Purpose: Return position of value in string of comma-separated values Description: FIND_IN_SET() looks for the first occurrence of a string within another string containing comma-separated values: FIND_IN_SET('r','c,a,r,s,t,e,n, ,p,e,d,e,r,s,e,n') => 3 Usage example: Suppose I have a table of vehicle types and colors, representing the available color range for vehicles in my shop.
Mysql> select * from vehicle_colors; +------+-------+ | type | color | +------+-------+ | bike | blue | | bike | green | | suv | blue | | van | green | +------+-------+ If I want to know the vehicles that are available in either blue or greenwhether they are available in blue or green or both I can get this information with a single query like so: MySQL manual entry on FIND_IN_SET() Thanks to Baron for suggesting this entry. [fadps] Database War Stories #3: Flickr - O'Reilly Radar. Continuing my series of queries about how “Web 2.0″ companies used databases, I asked Cal Henderson of Flickr to tell me “how the folksonomy model intersects with the traditional database.
How do you manage a tag cloud?” He replied: “lots of the ‘web 2.0′ feature set doesn’t fit well with traditional normalised db schema design. denormalization (or heavy caching) is the only way to generate a tag cloud in milliseconds for hundereds of millions of tags. you can cache stuff that’s slow to generate, but if it’s so expensive to generate that you can’t ever regenerate that view without pegging a whole database server then it’s not going to work.”
Here’s the full text of my exchange with Cal: The first question I asked was “what’s your database architecture?” Here’s what Cal had to say about that: Next, I asked about lessons learned in managing the data store, and any particular war stories that would make great illustrations of those lessons learned. Choosing innodb_buffer_pool_size. November 3, 2007 by Peter Zaitsev39 Comments My last post about Innodb Performance Optimization got a lot of comments choosing proper innodb_buffer_pool_size and indeed I oversimplified things a bit too much, so let me write a bit better description.
Innodb Buffer Pool is by far the most important option for Innodb Performance and it must be set correctly. I’ve seen a lot of clients which came through extreme sufferings leaving it at default value (8M). So if you have dedicated MySQL Box and you’re only using Innodb tables you will want to give all memory you do not need for other needs for Innodb Buffer Pool.
This of course assumes your database is large so you need large buffer pool, if not – setting buffer pool a bit larger than your database size will be enough. You also may choose to set buffer pool as if your database size is already larger than amount of memory you have – so you do not forget to readjust it later. The third important memory consumer would be OS cache.
Memcached. Data. Archiving. Configuration.