| Bug #47220 | Stored procedure varchar variable is not using string comparision | ||
|---|---|---|---|
| Submitted: | 9 Sep 2009 16:42 | Modified: | 11 Oct 2009 9:36 |
| Reporter: | Mats Gard | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.0.45 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[9 Sep 2009 19:57]
Sveta Smirnova
Thank you for the report. You showed 3 queries with same result. Please explain where is bug here.
[10 Sep 2009 11:25]
Mats Gard
The report was about the time it takes for the different queries. The first query was a SELECT with '' around the string in the WHERE. It took 0.00 sec. The second query was a SELECT without '' around the string in the WHERE. It took 0.79 sec. The third was a stored procedure with the same query. It took 0.86 sec. This makes me believe that the query in the stored procedure, behaves like the faulty second query. This is the bug.
[10 Sep 2009 20:12]
Sveta Smirnova
Thank you for the feedback.
I can not repeat described behavior with current development sources:
mysql> SELECT id FROM accounting_records WHERE acct_session_id = '175309415642856' LIMIT 1;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
mysql> SELECT id FROM accounting_records WHERE acct_session_id = 175309415642856 LIMIT 1;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
mysql> call test('175309415642856');
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
Please try with current version 5.0.85 and inform if problem still exists in your environment.
[11 Sep 2009 5:59]
Mats Gard
When I tested this, we had around 300000 rows in the table.
We have an index on the acct_session_id:
mysql> show index from accounting_records;
+--------------------+------------+-----------------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------+------------+-----------------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| accounting_records | 0 | PRIMARY | 1 | id | A | 267284 | NULL | NULL | | BTREE | |
| accounting_records | 0 | index_accounting_records_on_acct_session_id | 1 | acct_session_id | A | 267284 | NULL | NULL | | BTREE | |
| accounting_records | 1 | index_accounting_records_on_msisdn | 1 | msisdn | A | 33410 | NULL | NULL | | BTREE | |
| accounting_records | 1 | index_accounting_records_on_ggsn | 1 | ggsn | A | 2 | NULL | NULL | | BTREE | |
| accounting_records | 1 | index_accounting_records_on_sgsn | 1 | sgsn | A | 2 | NULL | NULL | | BTREE | |
| accounting_records | 1 | index_accounting_records_on_apn | 1 | apn | A | 1 | NULL | NULL | | BTREE | |
| accounting_records | 1 | index_accounting_records_on_ipc | 1 | ipc | A | 1 | NULL | NULL | | BTREE | |
| accounting_records | 1 | index_accounting_records_on_pc | 1 | pc | A | 1 | NULL | NULL | | BTREE | |
| accounting_records | 1 | index_accounting_records_on_framed_ip_address | 1 | framed_ip_address | A | 20560 | NULL | NULL | | BTREE | |
| accounting_records | 1 | index_accounting_records_on_start_time | 1 | start_time | A | 15722 | NULL | NULL | YES | BTREE | |
| accounting_records | 1 | index_accounting_records_on_stop_time | 1 | stop_time | A | 17818 | NULL | NULL | YES | BTREE | |
+--------------------+------------+-----------------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
11 rows in set (0.00 sec)
Then I tried EXPLAIN on all queries:
mysql> EXPLAIN SELECT * FROM accounting_records WHERE acct_session_id='182618332513685';
+----+-------------+--------------------+-------+---------------------------------------------+---------------------------------------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+-------+---------------------------------------------+---------------------------------------------+---------+-------+------+-------+
| 1 | SIMPLE | accounting_records | const | index_accounting_records_on_acct_session_id | index_accounting_records_on_acct_session_id | 255 | const | 1 | |
+----+-------------+--------------------+-------+---------------------------------------------+---------------------------------------------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM accounting_records WHERE acct_session_id=182618332513685;
+----+-------------+--------------------+------+---------------------------------------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------+---------------------------------------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | accounting_records | ALL | index_accounting_records_on_acct_session_id | NULL | NULL | NULL | 267284 | Using where |
+----+-------------+--------------------+------+---------------------------------------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
DELIMITER $$
DROP PROCEDURE IF EXISTS test$$
CREATE PROCEDURE `test`(IN p_acct_session_id varchar(253))
BEGIN
EXPLAIN SELECT id
FROM accounting_records
WHERE acct_session_id LIKE p_acct_session_id LIMIT 1;
END$$
DELIMITER ;
mysql> call test('182618332513685');
+----+-------------+--------------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | accounting_records | ALL | NULL | NULL | NULL | NULL | 267284 | Using where |
+----+-------------+--------------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
As you can see, the stored procedure "test" is not using the index just like the slow query without the ''. The fast query with '' is using the index.
My conclusion is that there is a bug in how the stored procedure is handled, or have I written it in the wrong way?
[11 Sep 2009 7:27]
Sveta Smirnova
Thank you for the feedback.
Please note you use = in single SELECT statements while LIKE in the procedure. Please use LIKE in single SELECT statements too, because this can lead to different plan.
Anyway I can not repeat described behavior with test data.
With test provided:
explain SELECT id FROM accounting_records WHERE acct_session_id = '175309415642856' LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE accounting_records const acct_session_id acct_session_id 255 const 1
explain SELECT id FROM accounting_records WHERE acct_session_id = 175309415642856 LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE accounting_records ALL acct_session_id NULL NULL NULL 18 Using where
call test('175309415642856');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE accounting_records range acct_session_id acct_session_id 255 NULL 1 Using where
With LIKE in all 3 cases:
explain SELECT id FROM accounting_records WHERE acct_session_id LIKE '175309415642856' LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE accounting_records range acct_session_id acct_session_id 255 NULL 1 Using where
explain SELECT id FROM accounting_records WHERE acct_session_id LIKE 175309415642856 LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE accounting_records range acct_session_id acct_session_id 255 NULL 1 Using where
call test('175309415642856');
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE accounting_records range acct_session_id acct_session_id 255 NULL 1 Using where
Please run OPTIMIZE TABLE accounting_records, try again and if problem still exists provide minimum dataset problem is repeatable with.
[11 Sep 2009 8:07]
Mats Gard
Ok, I see that your SP is using the index, but my doesn't even though I use "LIKE" and after I have run "OPTIMIZE TABLE".
Maybe it is version dependent.
mysql> OPTIMIZE TABLE accounting_records;
+-----------------------------------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------------------------+----------+----------+----------+
| accounting_production.accounting_records | optimize | status | OK |
+-----------------------------------------------+----------+----------+----------+
1 row in set (12.57 sec)
DELIMITER $$
DROP PROCEDURE IF EXISTS test$$
CREATE PROCEDURE `test`(IN p_acct_session_id varchar(253))
BEGIN
EXPLAIN SELECT id
FROM accounting_records
WHERE acct_session_id LIKE p_acct_session_id LIMIT 1;
END$$
DELIMITER ;
mysql> call test('175309415642856');
+----+-------------+-------------------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | accounting_records | ALL | NULL | NULL | NULL | NULL | 267284 | Using where |
+----+-------------+-------------------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
So, if I have the chance I will update the version and try again.
[11 Sep 2009 8:20]
Sveta Smirnova
Thank you for the feedback. Please try to create minimum set of data you can repeat the problem with too: in my case procedure uses index in version 5.0.45 too.
[11 Sep 2009 9:03]
Mats Gard
I created a minimal table with 2 columns and 255 rows, but it still don't use the index.
CREATE TABLE `test_table` (
`id` int(11) NOT NULL auto_increment,
`acct_session_id` varchar(253) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_test_table_on_acct_session_id` (`acct_session_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO test_table (id, acct_session_id) SELECT id, acct_session_id FROM accounting_records LIMIT 255;
DELIMITER $$
DROP PROCEDURE IF EXISTS test_procedure$$
CREATE PROCEDURE `test_procedure`(IN p_acct_session_id varchar(253))
BEGIN
EXPLAIN SELECT id
FROM test_table
WHERE acct_session_id LIKE p_acct_session_id LIMIT 1;
END$$
DELIMITER ;
mysql> call test_procedure('293128542661108');
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test_table | ALL | NULL | NULL | NULL | NULL | 255 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Then I tried '' around the variable in the SP, now it will use the index!
mysql> CREATE PROCEDURE `test_procedure`(IN p_acct_session_id varchar(253))
-> BEGIN
-> EXPLAIN SELECT id
-> FROM test_table
-> WHERE acct_session_id LIKE 'p_acct_session_id' LIMIT 1;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> call test_procedure('293128542661108');
+----+-------------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+-------------+
| 1 | SIMPLE | test_table | range | index_test_table_on_acct_session_id | index_test_table_on_acct_session_id | 255 | NULL | 1 | Using where |
+----+-------------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
But of course, this will not work finding any row, now it is looking for the string 'p_acct_session_id'.
mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS test_procedure$$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE PROCEDURE `test_procedure`(IN p_acct_session_id varchar(253))
-> BEGIN
-> SELECT id
-> FROM test_table
-> WHERE acct_session_id LIKE 'p_acct_session_id' LIMIT 1;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> call test_procedure('293128542661108');
Empty set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
[11 Sep 2009 9:36]
Sveta Smirnova
Thank you for the feedback. But in my case index is still used. I will upload test case used shortly. Please send us dump of 255-rows table.
[11 Sep 2009 9:37]
Sveta Smirnova
test case
Attachment: bug47220.test (application/octet-stream, text), 1.18 KiB.
[11 Sep 2009 10:20]
Mats Gard
mysqldump --routines accounting_production test_table > test_table.sql
Attachment: test_table.sql (application/octet-stream, text), 8.08 KiB.
[11 Sep 2009 17:07]
Sveta Smirnova
Thank you for the feedback. It still uses index in my case. Please try current version 5.0.85 and if problem still exists indicate accurate version of MySQL package you are using and send us your configuration file.
[11 Oct 2009 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: It looks to me that when using a varchar variable in stored procedures WHERE, it is not doing the comparsion as a string comparision. The times it takes to find the right row is similar to when using no ' ' around the string: acct_session_id = 175309415642856 instead of: acct_session_id = '175309415642856' If you know any other reason and how I can speed up the stored procedure, please feel free to enlighten me! How to repeat: mysql> desc accounting_records; +-------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | msisdn | varchar(15) | NO | MUL | | | | framed_ip_address | int(10) unsigned | NO | MUL | | | | start_time | datetime | YES | MUL | NULL | | | stop_time | datetime | YES | MUL | NULL | | | acct_session_time | int(11) | YES | | NULL | | | acct_session_id | varchar(253) | NO | UNI | | | | ggsn | int(10) unsigned | NO | MUL | | | | sgsn | int(10) unsigned | NO | MUL | | | | apn | varchar(60) | NO | MUL | | | | ipc | varchar(60) | NO | MUL | | | | pc | varchar(60) | NO | MUL | | | +-------------------+------------------+------+-----+---------+----------------+ 12 rows in set (0.00 sec) mysql> show create procedure test; +-----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Procedure | sql_mode | Create Procedure | +-----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | | CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(IN p_acct_session_id varchar(256)) BEGIN SELECT id FROM accounting_records WHERE acct_session_id LIKE p_acct_session_id LIMIT 1; END | +-----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT id FROM accounting_records WHERE acct_session_id = '175309415642856' LIMIT 1; +--------+ | id | +--------+ | 213714 | +--------+ 1 row in set (0.00 sec) mysql> SELECT id FROM accounting_records WHERE acct_session_id = 175309415642856 LIMIT 1; +--------+ | id | +--------+ | 213714 | +--------+ 1 row in set (0.79 sec) mysql> call test('175309415642856'); +--------+ | id | +--------+ | 213714 | +--------+ 1 row in set (0.86 sec) Query OK, 0 rows affected (0.86 sec)