This has to be a dumbo-misconfiguration.
mysql Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (i686) using readline 6.2
Connecting with a local mysql command line via localhost, but also connecting via remote mysql command lines and/or applications using the C API.
I have a stored procedure that was previously behaving just fine, I'm importing things to a new box, and I noticed that one of the stored procedures consistently requires 1.4seconds, while the SQL contained within takes 1ms to execute.
Procedure:
CREATE PROCEDURE `auth`.`p_get_user_subscriptions`
(
i_callsign VARCHAR(16)
, i_password TEXT
)
LANGUAGE SQL
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE l_not_found TINYINT(1) UNSIGNED DEFAULT 0 ;
-- How to tell if no record was found
DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_not_found = 1 ;
-- Look for a single entry
SELECT IF ( c.password = i_password, 1, 0 ) AS password_match
, IF ( s.status = 'active', 1, 0 ) AS is_active
, p.player_id AS id
, IF ( ps.status = 'active', 1, 0 ) AS is_playnet
, (
SELECT GROUP_CONCAT(DISTINCT(a.arenaID))
FROM auth_subscribed s
INNER JOIN auth_subarenaassoc a USING (subscriptionID)
INNER JOIN auth_arenas aa USING (arenaID)
WHERE s.customer_id = c.customer_id
AND s.status = 'active'
GROUP BY s.customer_id
ORDER BY a.arenaID
) AS subs
FROM `auth`.`players` p
INNER JOIN `auth`.auth_customer c USING (customer_id)
INNER JOIN `auth`.auth_subscribed s USING (customer_id)
LEFT OUTER JOIN `auth`.auth_subscribed ps
ON (ps.customer_id = p.customer_id AND ps.subscriptionid = 9)
WHERE c.username = i_callsign
ORDER BY s.status ASC
LIMIT 1
;
Doing an explain extended on the query:
+----+--------------------+-------+--------+-----------------------------------------------+-------------+---------+-----------------------------+------+----------+----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+--------+-----------------------------------------------+-------------+---------+-----------------------------+------+----------+----------------------+
| 1 | PRIMARY | c | const | PRIMARY,username | username | 18 | const | 1 | 100.00 | Using filesort |
| 1 | PRIMARY | p | const | customer_id | customer_id | 8 | const | 1 | 100.00 | |
| 1 | PRIMARY | ps | const | customer_id,idx_as_substatcust,idx_as_subcust | customer_id | 16 | const,const | 0 | 0.00 | unique row not found |
| 1 | PRIMARY | s | ref | customer_id | customer_id | 8 | const | 6 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | aa | index | PRIMARY | PRIMARY | 4 | NULL | 3 | 100.00 | Using index |
| 2 | DEPENDENT SUBQUERY | a | ref | arenaID,idx_asaa_sub_arena | arenaID | 4 | auth.aa.arenaid | 1 | 100.00 | Using index |
| 2 | DEPENDENT SUBQUERY | s | eq_ref | customer_id,idx_as_substatcust,idx_as_subcust | customer_id | 16 | const,auth.a.subscriptionID | 1 | 100.00 | Using where |
+----+--------------------+-------+--------+-----------------------------------------------+-------------+---------+-----------------------------+------+----------+----------------------+
Running the query directly:
+----------------+-----------+------+------------+------+
| password_match | is_active | id | is_playnet | subs |
+----------------+-----------+------+------------+------+
| 0 | 1 | 6125 | 0 | NULL |
+----------------+-----------+------+------------+------+
1 row in set (0.01 sec)
But calling the procedure with the same parameters:
mysql> call auth.p_get_user_subscriptions('fred', '1234');
+----------------+-----------+------+------------+------+
| password_match | is_active | id | is_playnet | subs |
+----------------+-----------+------+------------+------+
| 0 | 1 | 6125 | 0 | NULL |
+----------------+-----------+------+------------+------+
1 row in set (1.66 sec)
Query OK, 0 rows affected (1.66 sec)
The only thing I can think of is that this is some kind of collation/character set issue; I note that "show create procedure" ends with:
END | latin1 | latin1_swedish_ci | ucs2_general_ci |
And we are using utf8 / utf8_general_ci as the character set and collation for the server and clients. Could that be causing it?
The new server's my.cnf:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
default-character-set=utf8
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
default-character-set=utf8
collation-server=utf8_general_ci
character-set-server=utf8
character-set-database=utf8
[mysqld]
user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
bind-address = 0.0.0.0
skip-name-resolve
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M
default-character-set=utf8
collation-server=utf8_general_ci
Edited 2 time(s). Last edit at 03/23/2012 03:14PM by Oliver Smith.