Thanks to whoever.

Decoux: Naftilos76: writing queries where the data types are unknown is a recipe for disaster.

Colosimo: Decoux: do you have any advice regarding that?

Decoux: Naftilos76: writing dynamic sql, by examining the information_schema

Decoux: Naftilos76: also, if you join on a varchar column and the ch****ts are different, it won’t use indexes, and the condition will likely fail

Ivener: I can do that but it would require making things more complex like sending additional params and introducing a lot of if coditions just because i have to differentiate between = and like

Clyatt: Decoux: Not only in this case

Decoux: Naftilos76: it would lead to sensible sql, however

Clyatt: Naftilos76: In some cases WHERE id LIKE ‘1234’ alone can prevent using index. CAST almost certainly does it

Domingue: It is under testing so i will stay with it as is for the moment

Decoux: Naftilos76: do as you please, but you did ask for my advice.

Clyatt: Naftilos76: That you fix problem with non-latin chars by using CAST also sounds like mess

Blevens: Salle: it may be just me guys, as i said it is under testing

Clyatt: Naftilos76: That’s good! You have time to fix it then :

Clyatt: Naftilos76: Please try EXPLAIN SELECT with your query and compare the output of your: WHERE CASTid AS CHAR LIKE ‘99916’ vs WHERE id = 99916

Genualdi: Salle: What you are proposing is the right way to do things. I would never do casting if i was dealing with ints. Can’t you understand that?

Decoux: Naftilos76: we understand that you’re writing sql for unknown schemas, and that it’s at the core of your mistakes.

Ramiez: Salle: the column ‘id’ is not always an id, it may be anything

Clyatt: Naftilos76: Ouch . do you mean you are mixing integers and strings into same column called id?

Decoux: Naftilos76: since you seem intent on continuing with your current approach, I’m not sure this discussion needs to continue

Clyatt: Naftilos76: Sorry that’s exactly what we with Decoux are talking about. You don’t write WHERE condition for *unknown* columns

Clyatt: Naftilos76: At the time you send the query to the server you must know all the column types

Wallaert: No, guys the column ‘id’ is not always ‘id’, it may be ‘title’ varchar, char, text etc, it may be created_at datetime, date

Decoux: Naftilos76: yes, yes, and I gave you the solution for this, which you dismissed.

Clyatt: Naftilos76: let me show you what difference your cast makes:

Clyatt: Naftilos76: Do you notice the little difference between 1 and 529311144 ?

Shanley: Salle, i am not following, what do you mean?

Clyatt: Naftilos76: Then look again at my pastie

Clyatt: Naftilos76: I have table with 500 million rows. WHERE id = 535856 will retrieve single value from the PRIMARY key to fetch that row. For fetching the same signle row yours where castid as char = 535856 will do full index scan comparing all the 529311144 values to the provided number. That’s without using LIKE at a

Burlew: Salle: you mean that it makes a difference on how many rows query returns? Wouldn’t that be solved with ‘limit 1’ ?

Clyatt: Naftilos76: Both queries return exactly the same single row!

Clyatt: Naftilos76: One of them will return it in matter of miliseconds. The other will need hours to achieve the same

Decoux: Naftilos76: you will face many performance issues in the future, unless you reconsider your choices now

Karras: Salle: i get your point guys

Decoux: Perfect. Then we’ll bid you farewell.

Neubecker: I will find the column data types and use a proper compare expression!

Decoux: Naftilos76: do whatever you want, it’s your box.

Clyatt: Naftilos76: If you don’t know the column types in advance you have serious design problem

Derick: Thanks to whoever recommended pt-show-grants, it’s the ****. 😀