3

I'm trying to understand a huge performance difference that I'm seeing in equivalent code. Or at least code I think is equivalent.

I have a table with about 10 million records on it. It contains a field, which is indexed defined as: USPatentNum char(8)

If I set a variable withing MySql to a value, it takes over 218 seconds. The exact same query with a string literal takes under 1/4 of a second.

In the code below, the first select statement (with where USPatentNum = @pn;) takes forever, but the second, with the literal value (where USPatentNum = '5288812';) is nearly instant

mysql> select @pn := '5288812';
+------------------+
| @pn := '5288812' |
+------------------+
| 5288812          |
+------------------+
1 row in set (0.00 sec)


mysql> select patentId, USPatentNum, grantDate from patents where USPatentNum = @pn;
+----------+-------------+------------+
| patentId | USPatentNum | grantDate  |
+----------+-------------+------------+
|   306309 | 5288812     | 1994-02-22 |
+----------+-------------+------------+
1 row in set (3 min 38.17 sec)

mysql> select @pn;
+---------+
| @pn     |
+---------+
| 5288812 |
+---------+
1 row in set (0.00 sec)


mysql> select patentId, USPatentNum, grantDate from patents where USPatentNum = '5288812';
+----------+-------------+------------+
| patentId | USPatentNum | grantDate  |
+----------+-------------+------------+
|   306309 | 5288812     | 1994-02-22 |
+----------+-------------+------------+
1 row in set (0.21 sec)

Two questions:

Why is the use of the @pn so much slower? Can I change the select statement so that the performance will be the same?

fishtoprecords
  • 2,394
  • 7
  • 27
  • 38

2 Answers2

1

Declare @pn as char(8) before setting its value.

I suspect it will be a varchar as you do it now. If so, the performance loss is because MySql can't mach the index with your variable.

idstam
  • 2,848
  • 1
  • 21
  • 30
  • what syntax do I use to do the declaration? – fishtoprecords Dec 06 '11 at 17:48
  • declare @pn as varchar(8); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare – fishtoprecords Dec 06 '11 at 17:58
  • Sorry. MsSql mode in my brain (and the wrong datatype) Try: select @pn := CAST('5288812' AS CHAR(8)) – idstam Dec 06 '11 at 18:10
  • Still no joy. (extra space to between @ and pn due to SO) 
    mysql> select @ pn := CAST('5288812' AS CHAR(8));
| @ pn := CAST('5288812' AS CHAR(8)) |
+-----------------------------------+
| 5288812                           |
1 row in set (0.03 sec)

mysql> select patentId, USPatentNum, grantDate from patents where USPatentNum = @ pn limit 10;
|   306309 | 5288812     | 1994-02-22 |
1 row in set (1 min 21.90 sec)

mysql> select patentId, USPatentNum, grantDate from patents where USPatentNum = '5288812' limit 10;

|   306309 | 5288812     | 1994-02-22 |
1 row in set (0.10 sec)
    – fishtoprecords Dec 06 '11 at 18:20
  • Bummer, then I don't have a clue. – idstam Dec 06 '11 at 19:02
-1

It doesn't matter whether you use constant or @var. You get different result because the second time MySQL gets results from cache. If you execute once again your scenario but trade places queries with const and with @var you will get them same results (but with another value). First will be slowed, second will be fast.

Hope it helps

ravnur
  • 2,772
  • 19
  • 28