mysql adding collation to my where clause (5.5 Ubuntu)
Guys,
I'm sending a query to mysql (from LuaSQL), and my query looks like this:
SELECT ddd_last, ddd_first, ddd_middle, ddd_dob, ddd_num_typ, ddd_vio_id
INTO v_ddd_last, v_ddd_first, v_ddd_middle, v_ddd_dob, v_ddd_num_typ, v_ddd_vio_id
FROM ddd_dpsdl_data
WHERE ddd_number = v_vio_dl_padded LIMIT 1;
MySQL is converting and running it like this:
SELECT ddd_last, ddd_first, ddd_middle, ddd_dob, ddd_num_typ, ddd_vio_id
INTO v_ddd_last, v_ddd_first, v_ddd_middle, v_ddd_dob, v_ddd_num_typ, v_ddd_vio_id
FROM fta.ddd_dpsdl_data
WHERE ddd_number = NAME_CONST('v_vio_dl_padded',_utf8'0025162390' COLLATE 'utf8_general_ci') LIMIT 1
That collation step is causing a full table scan, rather than use the index (primary key) and over 15 million records, that's kinda slow ;-)
I capture the query being actually executed from show processlist....
I believe I have collation_connection, collation_database, collation_server all set properly and I'm even coercing the value in v_vio_dl_padded into utf8_general_ci before I run my query....this is driving me crazy.
All feedback would be much appreciated!!