If you have to store big texts in MySQL you have to choose in between using a big VARCHAR column and the TEXT type. So what are the differences between using for example a VARCHAR(2000) and TEXT?

The difference between TEXT and the VARCHAR type is how MySQL is storing the data. TEXT (and BLOB) data is stored off the table, leaving only a pointer to the actual storage.

So using VARCHAR is faster when the size of the data is reasonable. How big the performance difference is depends on the data and the used hardware.

Due the row length limit of 65,535 bytes the data you can store in the table itself is limited (Using utf8 2000 chars could use up to 6000 bytes).

Last October I posted an example and description of a common_lookup table. It was intended to show how common_lookup tables support drop down selections in web forms. However, it wasn’t adequate to show how they work with existing data, and the function only supported fresh queries.

This post goes to the next level, and shows how to use foreign keys to preselect values for display in web forms. It also rewrites the prior function so that it supports querying existing data and inserting new data.

Let’s start with data stored in join between two tables – the member and contact tables. The internal lookup uses the customers name from the contact table to find the membership account information in the member table.

1
2 …

To get a briest status overview of your MySQL server, you can use the command \\n, it will give you a fast idea of the version you are running, the most important settings and an overview of the amount of running queries.

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.29, for debian-linux-gnu (i686) using readline 6.2

Connection id:          993822
Current database:       
Current user:           root@85.50.57.117
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.66-0+squeeze1 (Debian)
Protocol version:       10
Connection:             h via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 23 days 21 hours 50 min 56 sec

Threads: 1  Questions: 12676338  Slow queries: 12  Opens: 21832  Flush tables: 1  Open …

Percona CEO Peter Zaitsev leads a track at the inaugural Percona MySQL University event in Raleigh, N.C. on Jan. 29, 2013.

Thank you to everyone who attended Percona MySQL University in Raleigh, N.C. We had a great turnout and a lot of positive feedback!

What’s next for Percona MySQL University? Next week we’re going to hold events in Montevideo and Buenos Aires which are gathering significant local interest!

In March we’re focusing on Toronto – We’ll be having a team meeting and …

Many people use MySQL in the console to execute a simple command, but did you know that MySQL can format the result of the query directly to XML or html?

The option -X will output the query result as XML
-H can be used to generate a HTML table containing the data.

$ mysql -X -e "select * from test" test

<?xml version="1.0"?>


  
        152710
  

Output as HTML

$ mysql -H -e "select * from test" test

With our newest release of Percona XtraDB Cluster, I would like to highlight a very nice ability to recovery a node and bring it back to the cluster with an incremental transfer after a crash.
This feature was available even in previous release, but now I want to give some details.

So, MySQL crashes from time to time and this a fact of life. HA solution is exactly needed to deal with an one node failure and allowing whole cluster continuing to work.

The idea is, if a node crashed, after it recovered – we just transfer all changes that happened in the cluster, while the node was down. It sounds easy in words, but proven hard when it comes to implementation. It all comes to the question: if mysqld crashes, how do we know what is the last transaction was executed. For a single InnoDB instance it is …

I’m getting more and more concerned about the current Oracle approach to MySQL security. And the fact that I was solely responsible for the security@mysql.com for about ten years, doesn’t make it easier, on the contrary, it only emphasizes changes in the attitude.

Starting from the obvious — somewhat slower response to critical bug fixes, which can be expected, Oracle is a big company, right? Very little information about security vulnerabilities is disclosed, CPUs are carefully stripped from anything that might help to understand the problem, it takes hours to map them to code changes. Heck, even test cases are kept private now. This seriously smells …

The MariaDB project is pleased to announce the immediate availability of the following new stable (GA) MariaDB versions:

• MariaDB 5.5.29  — Release Notes, Changelog, Downloads
• MariaDB 5.3.12  — Release Notes, Changelog, …

In my last two posts, I wrote about setting up replication with MySQL 5.6 using Global Transaction Identifiers. Even when I set up replication “the old-fashioned way“, one thought always enters my mind – did all of the data copy over to the slave? And, even after the master/slave has been running for a while, I am always wondering if the data in the slave matches the master. Or did the change that I made to that table make it over to the slave? It is probably more of a case of paranoia on my part, as MySQL replication is very reliable and works really well.

…

The duplicate records in a table can be found using below query.

Query 1:

SELECT *, count(pro) as cnt FROM list GROUP BY pro HAVING cnt > 1;

Illustration: The above query will find the duplicate records from list table. The cnt column will show that the duplicate records is available for how many times.

Query 2:

SELECT * FROM list WHERE pro in (SELECT pro FROM list GROUP BY pro HAVING count(pro) > 1);

Illustration: The above query will show all the duplicate records from list table. The duplicate records is based on pro columns.

Tags: DuplicateRecordsCategory:  …