MySQL Forums
Forum List  »  MyISAM

mysql adding collation to my where clause (5.5 Ubuntu)
Posted by: Dewey Gaedcke
Date: June 16, 2011 09:06AM

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!!

Options: ReplyQuote


Subject
Views
Written By
Posted
mysql adding collation to my where clause (5.5 Ubuntu)
4819
June 16, 2011 09:06AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.