Greetings
I have a table with the following table structure -
Field Type Collation Null Key
Default Extra
------- ------------- ----------------- ------
------ ------- ------
email char(75) latin1_swedish_ ci
is_sent enum('Y','N') latin1_swedish_ ci YES
N
id int(11) binary PRI
0
I store information about people email whom i need to
send mails and whether a mail has been sent to them or
not.
When I execute a query something like this -
select id/1024,
concat(sum(conv (substring(md5( email),1,8),16, 10)),
sum(conv(substr ing(md5(email), 9,16),16,10))) as b from
email_table_cop y group by id/1024 order by 1 limit 5;
I get a rsultset -
id/1024 b
------- -------------------------------
0.00 909388403840711 996371492430000
0.01 220573218709550 488768333706000 0
0.02 167446312149930 844338296345400 0
0.03 177059085847852 656162631165500 0
0.04 337698121818927 827722269035700 0
When I execute a query like -
select id/1024, @a:=md5(email),
concat(sum(conv (substring(@a,1 ,8),16,10)),
sum(conv(substr ing(@a,9,16),16 ,10))) as b from
email_table_cop y group by id/1024 order by 1 limit 5;
I get a resultset -
id/1024 @a:=md5(email) b
------- --------------------------------
--------------------------------
0.00 a0cb77a048bcafa 0c122a97c48c4dc 2f
132236437342566 953951965499400 0
0.01 3d3de1a6987b54f a31c9290a0eaa98 7d
119448797341041 284795164108600 00
0.02 0b84e00a70f4d0b 2cfc38449e5ab8b 4b
276678297684241 708186218316000
0.03 025ab61135c8ed4 a329dd5670801e8 66
548751011430198 35333537694000
0.04 f53e627643b62f9 bda102b0612f28e c3
411845628615266 700430955928000 0
Shouldnt be the value of `b` be same both the times.
By executing the second query i belive i do md5
conversion only once which should significantly boost
the result on a big table.
Am I doing something wrong?
Also, what is the best way to remove the md5 key
column from the resultset as I dont need that.
Thanks in advance
Karam
_______________ _______________ ____
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw
I have a table with the following table structure -
Field Type Collation Null Key
Default Extra
------- ------------- ----------------- ------
------ ------- ------
email char(75) latin1_swedish_ ci
is_sent enum('Y','N') latin1_swedish_ ci YES
N
id int(11) binary PRI
0
I store information about people email whom i need to
send mails and whether a mail has been sent to them or
not.
When I execute a query something like this -
select id/1024,
concat(sum(conv (substring(md5( email),1,8),16, 10)),
sum(conv(substr ing(md5(email), 9,16),16,10))) as b from
email_table_cop y group by id/1024 order by 1 limit 5;
I get a rsultset -
id/1024 b
------- -------------------------------
0.00 909388403840711 996371492430000
0.01 220573218709550 488768333706000 0
0.02 167446312149930 844338296345400 0
0.03 177059085847852 656162631165500 0
0.04 337698121818927 827722269035700 0
When I execute a query like -
select id/1024, @a:=md5(email),
concat(sum(conv (substring(@a,1 ,8),16,10)),
sum(conv(substr ing(@a,9,16),16 ,10))) as b from
email_table_cop y group by id/1024 order by 1 limit 5;
I get a resultset -
id/1024 @a:=md5(email) b
------- --------------------------------
--------------------------------
0.00 a0cb77a048bcafa 0c122a97c48c4dc 2f
132236437342566 953951965499400 0
0.01 3d3de1a6987b54f a31c9290a0eaa98 7d
119448797341041 284795164108600 00
0.02 0b84e00a70f4d0b 2cfc38449e5ab8b 4b
276678297684241 708186218316000
0.03 025ab61135c8ed4 a329dd5670801e8 66
548751011430198 35333537694000
0.04 f53e627643b62f9 bda102b0612f28e c3
411845628615266 700430955928000 0
Shouldnt be the value of `b` be same both the times.
By executing the second query i belive i do md5
conversion only once which should significantly boost
the result on a big table.
Am I doing something wrong?
Also, what is the best way to remove the md5 key
column from the resultset as I dont need that.
Thanks in advance
Karam
_______________ _______________ ____
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw