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: http://pastie.org/10416455
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 ****. 😀