Category Archives: mysql

How to Implement Natural Sorting in MySQL – Sky Blue Sofa Blog

How to Implement Natural Sorting in MySQLPosted by Dave Rogers // December 20, 2013 // in Blog // 0 CommentsIn the programming world, Natural Sorting is taking a list of things and sorting them, well, the way a human naturally would. Take this list, sorted in natural order, for example:col_name————— test1test2test3test4test5test6test7test8test9test10test11test12MySQL sorting doesn’t work, naturallyIn PHP, you could use the natsort function to sort an array to return an alphanumeric list in the order given above. But we’re talking about MySQL here. In MySQL we would normally run this query:

via How to Implement Natural Sorting in MySQL – Sky Blue Sofa Blog.

MySQL COUNT() function with group by – w3resource

Great example of count() and Group By functions in MySQL.

In this page we have discussed how to use MySQL COUNT() function with GROUP BY.

Example :

The following MySQL statement will show number of author for each country. The GROUP BY clause groups all records for each country and then COUNT() function in conjunction with GROUP BY counts the number of authors for each country.

- See more at: http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-count-with-group-by.php#sthash.MHVqWlWO.dpuf

http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-count-with-group-by.php

Google swaps out MySQL, moves to MariaDB • The Register

Updated Google is migrating its MySQL systems over to MariaDB, allowing the search company to get away from the Oracle-backed open source database.

The news came out at the Extremely Large Databases (XLDB) conference in Stanford, California on Wednesday, one month after El Reg reported that Google had assigned one of its engineers to the MariaDB Foundation. News of the swap was not an official announcement by Google, it came out during a presentation by Google senior systems engineer Jeremy Cole on the general state of the MySQL ecosystem.


Google swaps out MySQL, moves to MariaDB • The Register
.

10 Easy Steps to a Complete Understanding of SQL – Tech.Pro

To many programmers, SQL is a bit of a beast. It is one of the few declarative languages out there, and as such, behaves in entirely different ways from imperative, object-oriented, or even functional languages (although, some say that SQL is also somewhat functional).

I’m writing SQL every day, embracing SQL with myOpen Source Software jOOQ. I thus feel compelled to bring the beauty of SQL a bit closer to those of you still struggling with it. The following tutorial is destined for

  • readers who have already worked with SQL but never completely understood it
  • readers who know SQL well but have never really thought about its syntax
  • readers who want to teach SQL to others

This tutorial will focus on SELECT statements only. Other DML statements will be covered in another tutorial. Here are…


10 Easy Steps to a Complete Understanding of SQL – Tech.Pro.

MariaDB – a really simple Replacement for MySQL

Well Dang… I always felt Oracles ‘purchase’ of Sun and thus MySQL will eventually have a negative effect on MySQL. I started to feel that today when I couldn’t download the new version of MySQL Workbench without signing up as an Oracle Developer… Then I started reading the MySQL Wikipedia page about the history and what it going on with licensing and specifically the “Legal and Acquisition Impacts” – which brought me to MariaDB. At some point in the next decade, we’re going to see a transition from MySQL to MariaDB in the popular Open Source projects. Here’s some additional information about MariaDB.

MariaDB is a drop-in replacement for MySQL.

MariaDB strives to be the logical choice for database professionals looking for a robust, scalable, and reliable SQL server. To accomplish this, Monty Program works to hire the best and brightest developers in the industry, work closely and cooperatively with the larger community of users and developers in the true spirit of Free and open source software, and release software in a manner that balances predictability with reliability.

Here are some important resources to get you started. All links will redirect you to external sites, noted in parentheses.

MariaDB vs. MySQL – Features
MariaDB vs. MySQL – Compatibility
MariaDB Success Stories
Contributing to the MariaDB Project
More About MariaDB
MariaDB and the community

https://mariadb.org/

 

RDBMS Database Normalization Vs. DeNormalization for speed and performance

the new hot shot coder / friend (he really is a solid coder) was calling me out on my database architecture. why do you have redundant data, it’ll slow the database down. you don’t need the data in this table if it’s connected through a join. i was telling him, for display logic, it’s faster to avoid the CPU + Disk hit to fetch/query a join.

On Jun 1, 2011, at 1:19 AM, Xxxx Xxxxxxxx wrote:

What you know about Boyce Codd Normal form.

http://en.wikipedia.org/wiki/Database_normalization

 

My response:

yeh yeh… i was a systems major in cawlige. i’ve been building production DB driven sites for 10+ years…

i do follow RDBMS and normalization. i would argue against normalization at all times. definitely using a 1/0 bit is faster than looking through a one to one or many to one relationship because you’ll take a disk + cpu hit.

one nice thing about the INNODB builds the indexing and relationships and stores them in memory. this requires lots of memory, but makes shit faster for the relationship query.

this guy pretty much sums up my thoughts…

Database Denormalization – What is it?

Rookie database administrators are taught in school to always keep their databases normalized. A normalized database is one where you minimize duplication of data by keeping groups of data in separate tables and linking (using indexes) between the tables.

For example, as part of the SoftwareProjects CRM application, we store customer contact information in a ‘customers‘ table, and financial transactions in a ‘transactions‘ table. Both tables are keyed to a ‘customer_id‘, allowing easy lookup of all transactions belonging to an individual customer.

Whenever we take on new clients, we typically find clean, normalized tables with multiple indexes connecting tables together. At first glance the concept of database normalization makes perfect sense – it’s clean, easy to understand and makes it easy to keep data is in sync.

In the real world of high traffic websites and users demanding sub-second response times, database normalization is a fatal mistake.

A de-normalized database is one where you keeps redundant frequently-accessed data, duplicated across several tables, making it possible to fulfill a user request without having to look at more than a single table.

Remember: The key to high performance database access is sticking to single-table SELECT queries with short indexes.

 

————————————————————————————————-

from here: http://www.softwareprojects.com/resources/programming/t-database-performance-optimization-denormalization-1410.html

with pretty good discussions below.

http://www.mysqlperformanceblog.com/2008/12/22/high-performance-click-analysis-with-mysql/

MySQL 5 move data directory (datadir) to new mount RAID location on UBUNTU 10

i setup a RAID 5 system for our mysql database and wanted to move it over on ubuntu 10.

MOVE MYSQL

first stop mysql
service mysql stop

i’m paranoid, so i check the status
service mysql status

copy your MySQL database files to the new location. in this instance my RAID drive is located at:
/media/speedy/mysql

let’s move database:
mv /var/lib/mysql /media/speedy/mysql

change ownership/group to mysql:
sudo chown mysql:mysql /media/speedy/mysql

i make a backup of the existing database name mysql-bu (move it):
mv /var/lib/mysql /var/lib/mysql-bu

make a new symbolic link and check perms:
sudo ln -s /mnt/mysql /var/lib/mysql

CHANGE MySQL config file, my.cnf to reflect new data directory(datadir):
datadir         = /media/speedy/mysql

UPDATE APPARMOR

o.k., after you’ve done all of this, apparmor will try and get in your way if you use these new settings.

Edit apparmors mysql config file

/etc/apparmor.d/usr.sbin.mysqld

replace the original MySQL paths with your new MySQL location:

/media/speedy/mysql/ r,
/media/speedy/mysql/** rwk,

***WARNING*** if you make a back up of the usr.sbin.mysqld don’t put it in the apparmor.d directory! Otherwise, when you try to restart apparmor, it will load both configuration files. This had me stumped for hours………..

Now, MySQL should be ready to run in your new location.

PHPMyAdmin needs to be reconfigured:

dpkg-reconfigure phpmyadmin

I wanted to move mysqld.sock to the RAID drive to help with performance.

Reconfigure PHP to use the new socket:
MYSQL_SOCKET
 

Ubuntu 10.x completely remove and clean MySQL installation

In my attempt to move the MySQL datadir I have a messed up MySQL installation. Here’s how I’ve completely removed MySQL 5.1 from Ubuntu 10.x. i have a messed up installation and need to completely remove MySQL and re-install it. Warning, this will delete *everything* associated with MySQL on ubuntu! It does give you a fresh install!

using apt:
apt-get –purge remove mysql-server
apt-get –purge remove mysql-client
apt-get –purge remove mysql-common

apt-get autoremove
apt-get autoclean

using aptitude (some people claim aptitude does a better job of removing dependencies):
aptitude remove mysql-client
aptitude remove mysql-server
aptitude remove mysql-common

see if anything depends on the installed packages
apt-cache rdepends mysql-server
apt-cache rdepends mysql-client

if you’ve changed apparmor settings change them back inside here:
pico /etc/apparmor.d/usr.sbin.mysqld

delete preferences(the next find command will delete everything):
rm -rf /etc/mysql

find all files with “mysql” on / and delete them:
find / -iname ‘mysql*’ -exec rm -rf {} \;

after all of the above do a clean install
aptitude install mysql-server mysql-client

check to see if mysql is running:
service mysql status

and you should get the response running

mysql start/running, process 3296

check with mysqladmin:

mysqladmin -u root -p status