Showing entries 11 to 18
« 10 Newer Entries
Displaying posts with tag: variables (reset)
What’s the isolation level do you use for InnoDB ?

In relation to these two posts from Justin Swanhart and Anders Karlsson about transaction isolation levels, I thought it was interesting to do a little survey to get an idea of the most commonly used isolation levels.

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll. Related Posts :

[Read more]
MySQL User Defined Variables

When I have discovered MySQL User-defined variables at the first time, I didn’t pay much attention to them and didn’t realize their strength. At first, one can think that they are just one of the many unnoticed MySQL features. Well, I was wrong. Here, I will try to summarize their strength, though I think there is […]

Videos of Pythian Sessions from the 2010 O’Reilly MySQL Conference and Expo

Here’s a sneak peek at a video matrix — this is all the videos that include Pythian Group employees at the MySQL conference. I hope to have all the rest of the videos processed and uploaded within 24 hours, with a matrix similar to the one below (but of course with many more sessions).

Title Presenter Slides Video link
(hr:min:sec)
Details (Conf. site link)
Main Stage
Keynote: Under New Management: Next Steps for the Community Sheeri K. Cabral (Pythian) N/A 18:16
[Read more]
MySQL status variables

In MySQL5 MySQL implemented changes so you can view global status (counters) and session counters. In 5.1 MySQL extended this and implemented this information in the INFORMATION_SCHEMA.GLOBAL_STATUS and INFORMATION_STATUS.SESSION_STATUS tables.

Global status

mysql> SELECT * FROM GLOBAL_STATUS WHERE VARIABLE_NAME = 'COM_SELECT'\G*************************** 1. row *************************** VARIABLE_NAME: COM_SELECTVARIABLE_VALUE: 2076641 row in set (0.02 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Com_select';+---------------+--------+| Variable_name | Value |+---------------+--------+| Com_select | 207664 | +---------------+--------+1 row in set (0.03 sec)
Session status
mysql> SHOW SESSION STATUS LIKE 'Com_select';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_select | 13 | +---------------+-------+1 row in set (0.02 sec)

mysql> …

[Read more]
MySQL vs InfoBright optimizer battle

MySQL instance running an Infobright engine


mysql> explain SELECT COUNT(ac.UID) FROM ACTIVE ac JOIN ALL a;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| 1 | SIMPLE | ac | ALL | NULL | NULL | NULL | NULL | 124426 | |
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 7594256 | |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
2 rows in set (0.01 sec)

mysql> SELECT COUNT(ac.UID) FROM ACTIVE ac JOIN ALL a ON ac.UID=a.UID;
+---------------+
| COUNT(ac.UID) |
+---------------+
| 17466 |
+---------------+
1 row in set (0.23 sec)

[Read more]
Contradiction for the Day

Today’s contradiction:

MySQL has server variables named new and old.

The new variable can be set per-session and globally, and is dynamic. The old variable is not dynamic, and only global in scope. Both default to FALSE in MySQL 5.1.

According to the manual, the new variable:

was used in MySQL 4.0 to turn on some 4.1 behaviors, and is retained for backward compatibility.

That same page notes the following about the old variable:

when old is enabled, it changes the default scope of index hints to that used prior to MySQL 5.1.17. That is, index hints with no FOR clause apply only to how indexes are used for row retrieval and not to resolution of ORDER BY or GROUP BY clauses.

That’s right — the …

[Read more]
I apparently have_community_features

Do you have_community_features? I do!

SHOW GLOBAL VARIABLES LIKE 'have_community_features';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| have_community_features | YES   |
+-------------------------+-------+
1 row in set (0.00 sec)

I am pretty sure this is one of those variables that MySQL has put in as an unused placeholder, but for now, it is not even documented as unused (as are table_lock_wait_timeout, http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html, and a Google search restricted to the site dev.mysql.com turns up only one match, …

[Read more]
Using a DELETE w/ a JOIN and LIMITing the number of rows deleted.

I've come up with some interesting workarounds for missing features using @session variables and I'd like to share one with you today: DELETE ... JOIN ... LIMIT N;

okay, so we've got two tables, one with many duplicates and one with no duplicates.
We want to join the tables using 'id', but only want to delete N duplicates from t1.
MySQL's DELETE does not include LIMIT support when JOIN is used, so we need to work around
that using some fancy footwork:


mysql> select * from t1;
+------+------+
| id | abc |
+------+------+
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
+------+------+
8 rows in set (0.00 sec)

mysql> select * from t2;
+----+------+
| id | xyz |
+----+------+
| 1 | 1 |
+----+------+
1 row …

[Read more]
Showing entries 11 to 18
« 10 Newer Entries