Archive for the ‘MySQL’ Category

MySQL Tricks

Posted: June 8, 2014 in MySQL
Tags:
TIP 1:

    Find out who is doing what, and kill the process if needed.
    This example kills Id 657.

    mysql> show processlist;
    show processlist;
    +-----+------+-----------+---------+---------+-------+-------+------------------+
    | Id  | User | Host      | db      | Command | Time  | State | Info             |
    +-----+------+-----------+---------+---------+-------+-------+------------------+
    | 657 | prog | localhost | weather | Sleep   | 28619 |       | NULL             |
    | 782 | prog | localhost | weather | Sleep   |   853 |       | NULL             |
    | 785 | prog | localhost | NULL    | Query   |     0 | NULL  | show processlist |
    +-----+------+-----------+---------+---------+-------+-------+------------------+
    3 rows in set (0.00 sec)

    mysql>kill 657

    Or, from the command line, to kill process 782

    [root@third-fl-71 mysql]# mysqladmin processlist
    +-----+------+-----------+---------+---------+------+-------+------------------+
    | Id  | User | Host      | db      | Command | Time | State | Info             |
    +-----+------+-----------+---------+---------+------+-------+------------------+
    | 782 | prog | localhost | weather | Sleep   | 2676 |       |                  |
    | 785 | prog | localhost |         | Sleep   | 1823 |       |                  |
    | 793 | root | localhost |         | Query   | 0    |       | show processlist |
    +-----+------+-----------+---------+---------+------+-------+------------------+
    [root@third-fl-71 mysql]#

    [root@third-fl-71 mysql]# mysqladmin kill 782

    Note, the following can also be helpful

    mysql> show status;
        or
    mysql> show status\G
        also
    mysql> show innodb status;
        or
    mysql> show table status like '%';

        The above gives you create time and other information.





TIP 2:

    Clean up binary log files.  For a default install they may be in

          /usr/local/var/
       or
          /var/lib/mysql/

    with names ending in -bin.000001,-bin.000002,..  The following 
    command may help find out where the logs are located.

         mysql> show variables like '%home%';
         +---------------------------+-----------------+
         | Variable_name             | Value           |
         +---------------------------+-----------------+
         | bdb_home                  | /var/lib/mysql/ | 
         | innodb_data_home_dir      |                 | 
         | innodb_log_group_home_dir | ./              | 
         +---------------------------+-----------------+


    mysql> reset master;
    reset master;
    Query OK, 0 rows affected (0.02 sec)

    See (Tip 24:) details working with binary log files and (Tip 25:) explains
    how to setup logging. (Tip 37:) shows have to setup MASTER and SLAVE 
    replication.



TIP 3:

    Can the order of the columns in a create statement make a difference? YES

       create table t (
          a int,
          b int,
          timeUpdate timestamp,
          timeEnter timestamp );

    The first timestamp will always be the "automatically generated" time. So
    if the record is updated, or inserted, this time gets changed. If the
    order is changed, "timeEnter" is before "timeUpdate", then,  "timeEnter"
    would get updated.  First timestamp column updates automatically.

    Note, in the table above timeEnter will only get updated if passed a null
    value.

         insert into t (a,b,timeEnter) values (1,2,NULL);

    Hints: Need mm-dd-yyyy hh:mm:ss format?

      select a,b,DATE_FORMAT(timeUpdate,'%m-%d-%Y %T'),DATE_FORMAT(timeEnter,'%m-%d-%Y %T') from t;
      +------+------+---------------------------------------+--------------------------------------+
      | a    | b    | DATE_FORMAT(timeUpdate,'%m-%d-%Y %T') | DATE_FORMAT(timeEnter,'%m-%d-%Y %T') |
      +------+------+---------------------------------------+--------------------------------------+
      |    3 |    2 | 04-15-2004 19:14:36                   | 04-15-2004 19:15:07                  |
      |    3 |    2 | 04-15-2004 19:14:39                   | 04-15-2004 19:15:07                  |
      |    5 |    5 | 00-00-0000 00:00:00                   | 04-15-2004 19:15:53                  |
      |    1 |    2 | 00-00-0000 00:00:00                   | 04-15-2004 19:20:15                  |
      +------+------+---------------------------------------+--------------------------------------+
      4 rows in set (0.00 sec)



TIP 4:

   Connect, create table and select with Perl (Linux).  First the DBI module is needed, which
   can be installed from the system prompt as follows:

       # perl -MCPAN -e shell
       cpan> install DBI
       cpan> install DBD::mysql

   The following is an example program:


          #! /usr/bin/perl -w
          #  Copyright (GPL) Mike Chirico mchirico@users.sourceforge.net
          #
          #  Program does the following:
          #     o connects to mysql
          #     o creates perlTest if it doesn't exist
          #     o inserts records
          #     o selects and displays records
          #
          #  This program assumes DBI
          #
          #  perl -MCPAN -e shell
          #  cpan> install DBI
          #  cpan> install DBD::mysql
          #
          #
          #
          #
          #

          use strict;
          use DBI;

          #  You will need to change the following:
          #     o database
          #     o user
          #     o password
          my $database="yourdatabase";
          my $user="user1";
          my $passwd="hidden";
          my $count = 0;
          my $tblcreate= "
            CREATE TABLE IF NOT EXISTS perlTest (
              pkey int(11) NOT NULL auto_increment,
              a int,
              b int,
              c int,
              timeEnter timestamp(14),
             PRIMARY KEY  (pkey)

             ) ";

          my $insert= "
              insert into perlTest (a,b,c)
               values (1,2,3),(4,5,6),(7,8,9)";

          my $select="
              select a,b,c from perlTest ";



          my $dsn = "DBI:mysql:host=localhost;database=${database}";
          my $dbh = DBI->connect ($dsn, $user, $passwd)
              or die "Cannot connect to server\n";



          my $s = $dbh->prepare($tblcreate);
             $s->execute();
             $s = $dbh->prepare($insert);
             $s->execute();



             $s = $dbh->prepare($select);
             $s->execute();


          while(my @val = $s->fetchrow_array())
          {
              print " $val[0]  $val[1]  $val[2]\n";
              ++$count;
          }
          $s->finish();

          $dbh->disconnect ( );

          exit (0);



TIP 5:

     Remove duplicate entries.  Assume the following table and data.

              CREATE TABLE IF NOT EXISTS dupTest (
                pkey int(11) NOT NULL auto_increment,
                a int,
                b int,
                c int,
                timeEnter timestamp(14),
               PRIMARY KEY  (pkey)

               );

               insert into dupTest (a,b,c) values (1,2,3),(1,2,3),
                  (1,5,4),(1,6,4);



       mysql> select * from dupTest;
       select * from dupTest;
       +------+------+------+------+---------------------+
       | pkey | a    | b    | c    | timeEnter           |
       +------+------+------+------+---------------------+
       |    1 |    1 |    2 |    3 | 2004-04-16 10:55:35 |
       |    2 |    1 |    2 |    3 | 2004-04-16 10:55:35 |
       |    3 |    1 |    5 |    4 | 2004-04-16 10:55:35 |
       |    4 |    1 |    6 |    4 | 2004-04-16 10:55:35 |
       +------+------+------+------+---------------------+
       4 rows in set (0.00 sec)

       mysql>

       Note, the first two rows contains duplicates in columns a and b. It contains
       other duplicates; but, leaves the other duplicates alone.

          mysql> ALTER IGNORE TABLE  dupTest ADD UNIQUE INDEX(a,b);

          mysql> select * from dupTest;
          select * from dupTest;
          +------+------+------+------+---------------------+
          | pkey | a    | b    | c    | timeEnter           |
          +------+------+------+------+---------------------+
          |    1 |    1 |    2 |    3 | 2004-04-16 11:11:42 |
          |    3 |    1 |    5 |    4 | 2004-04-16 11:11:42 |
          |    4 |    1 |    6 |    4 | 2004-04-16 11:11:42 |
          +------+------+------+------+---------------------+
          3 rows in set (0.00 sec)

 

MySQL Limitaions

Posted: January 14, 2013 in MySQL
Tags: ,

GENERAL LIMITATIONS OF MYSQL

 

32-bit binaries cannot address more than 4 Gbyte of memory. This is not a MySQL limitation, this is a technical limitation.

BLOB‘s are limited to 1 Gbyte in size even thought you use LONGBLOB because of a limitation in the MySQL protocol: The protocol limit for max_allowed_packet is 1GB.

 


LIMITATIONS OF MYSQL 4.1

Limitations of Joins

In MySQL 4.1, the maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view. 

Limitations of the MyISAM storage engine

There is a limitation of 232 (~4.2 Mia) rows in a MyISAM table. You can increase this limitation if you build MySQL with the –with-big-tables option then the row limitation is increased to 264 (1.8 * 1019) rows.

 


LIMITATIONS OF MYSQL 5.0

Limitations of Joins

The maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view. This also applies to LEFT and RIGHT OUTER JOINS.

Limitations of the MyISAM storage engine

Large files up to 63-bit file length are supported.

There is a limitation of 264 (1.8 * 1019) rows in a MyISAM table.

The maximum number of indexes per MyISAM table is 64. You can configure the build by invoking configure with the –with-max-indexes=N option, where N is the maximum number of indexes to permit per MyISAM table. N must be less than or equal to 128.

The maximum number of columns per index is 16.

The maximum key length is 1000 bytes. This can be changed by changing the source and recompiling. 

 


LIMITATIONS OF THE INNODB STORAGE ENGINE

A table cannot contain more than 1000 columns.

The internal maximum key length is 3500 bytes, but MySQL itself restricts this to 1024 bytes.

The maximum row length, except for VARCHARBLOB and TEXT columns, is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes. LONGBLOB and LONGTEXT columns must be less than 4 Gbyte, and the total row length, including also BLOB and TEXT columns, must be less than 4 Gbyte.

Although InnoDB supports row sizes larger than 65535 internally, you cannot define a row containing VARCHARcolumns with a combined size larger than 65535.

The maximum tablespace size is 4 Mia database pages (64 Tbyte). This is also the maximum size for a table.

 


LIMITATIONS OF MYSQL 5.1

Limitations of Joins

The maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view. 

Limitations of Partitions

The limitation of partitions with MySQL is 1024 (internal mail). But one have to increase open_files_limit. See also: [Lit.]

 


LIMITATIONS OF MYSQL CLUSTER

Max attributes/columns in an index: 32

Max number of attributes (columns and indexes) in a table: 128

Max number of table: 1792 (v5.0)

Max size in bytes of a row is 8052 byte, excluding blobs which are stored separately.

Max number of nodes in a cluster: 63, max. number of data nodes: 48 (in v5.0/5.1)

Max number of nodes in a cluster: 255 in CGE.

Max number of metadata objects: 20320.

Max attribute name length: 31 characters.

Max database + table name length: 122 characters.

 

Ref & Source: http://www.fromdual.ch

Understand JOINS in RDBMS

Posted: January 14, 2013 in MySQL
Tags: ,

“JOIN” is a SQL keyword used to query data from two or more related tables. Unfortunately, the concept is regularly explained using abstract terms or differs between database systems. It often confuses me. Developers cope with enough confusion, so this is my attempt to explain JOINs briefly and succinctly to myself and anyone who’s interested.

Related Tables

MySQL, PostgreSQL, Firebird, SQLite, SQL Server and Oracle are relational database systems. A well-designed database will provide a number of tables containing related data. A very simple example would be users (students) and course enrollments:

‘user’ table:

id name course
1 Alice 1
2 Bob 1
3 Caroline 2
4 David 5
5 Emma (NULL)

MySQL table creation code:


CREATE TABLE `user` (
	`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(30) NOT NULL,
	`course` smallint(5) unsigned DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

The course number relates to a subject being taken in a course table…

‘course’ table:

id name
1 HTML5
2 CSS3
3 JavaScript
4 PHP
5 MySQL

MySQL table creation code:


CREATE TABLE `course` (
	`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(50) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Since we’re using InnoDB tables and know that user.course and course.id are related, we can specify a foreign key relationship:


ALTER TABLE `user`
ADD CONSTRAINT `FK_course`
FOREIGN KEY (`course`) REFERENCES `course` (`id`)
ON UPDATE CASCADE;

In essence, MySQL will automatically:

  • re-number the associated entries in the user.course column if the course.id changes
  • reject any attempt to delete a course where users are enrolled.
important: This is terrible database design!

This database is not efficient. It’s fine for this example, but a student can only be enrolled on zero or one course. A real system would need to overcome this restriction — probably using an intermediate ‘enrollment’ table which mapped any number of students to any number of courses.

JOINs allow us to query this data in a number of ways.

INNER JOIN (or just JOIN)

SQL INNER JOINThe most frequently used clause is INNER JOIN. This produces a set of records which match in both the user and course tables, i.e. all users who are enrolled on a course:


SELECT user.name, course.name
FROM `user`
INNER JOIN `course` on user.course = course.id;

Result:

user.name course.name
Alice HTML5
Bob HTML5
Carline CSS3
David MySQL

LEFT JOIN

SQL LEFT JOINWhat if we require a list of all students and their courses even if they’re not enrolled on one? A LEFT JOIN produces a set of records which matches every entry in the left table (user) regardless of any matching entry in the right table (course):


SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id;

Result:

user.name course.name
Alice HTML5
Bob HTML5
Carline CSS3
David MySQL
Emma (NULL)

RIGHT JOIN

SQL RIGHT JOINPerhaps we require a list all courses and students even if no one has been enrolled? A RIGHT JOIN produces a set of records which matches every entry in the right table (course) regardless of any matching entry in the left table (user):


SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;

Result:

user.name course.name
Alice HTML5
Bob HTML5
Carline CSS3
(NULL) JavaScript
(NULL) PHP
David MySQL

RIGHT JOINs are rarely used since you can express the same result using a LEFT JOIN. This can be more efficient and quicker for the database to parse:


SELECT user.name, course.name
FROM `course`
LEFT JOIN `user` on user.course = course.id;

We could, for example, count the number of students enrolled on each course:


SELECT course.name, COUNT(user.name)
FROM `course`
LEFT JOIN `user` ON user.course = course.id
GROUP BY course.id;

Result:

course.name count()
HTML5 2
CSS3 1
JavaScript 0
PHP 0
MySQL 1

OUTER JOIN (or FULL OUTER JOIN)

SQL FULL OUTER JOINOur last option is the OUTER JOIN which returns all records in both tables regardless of any match. Where no match exists, the missing side will contain NULL.

OUTER JOIN is less useful than INNER, LEFT or RIGHT and it’s not implemented in MySQL. However, you can work around this restriction using the UNION of a LEFT and RIGHT JOIN, e.g.


SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id
UNION
SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;

Result:

user.name course.name
Alice HTML5
Bob HTML5
Carline CSS3
David MySQL
Emma (NULL)
(NULL) JavaScript
(NULL) PHP

I hope that gives you a better understanding of JOINs and helps you write more efficient SQL queries.

 

Reference: http://www.sitepoint.com