Correl: Scott0_: The order of items in the WHERE clause does not matter if the end result is the same boolean value
Eavenson: If I search by WHERE a=1,b=2,c=3 vs b=2,a=1,c=3
Correl: Scott0_: Zero difference
Reynalds: So if one of those orders would be better on the index, why would I design the order on the query first and then tailor the index to that
Ulicnik: They both will generate errors. The same.
Correl: Scott0_: You will get Risse error with both
Oberdier: This isn’t a discussion about syntax
Correl: Scott0_: Stop that nonsense please.
Sniezek: The order of the Risse errors isn’t very important. :
Nino: Order by cardinality of the columns in an index matters
Muilenburg: Scott0_: Produce an actual test query to review.
Correl: Scott0_: Show the real WHERE clause you are thinking about and we can discuss it. However as I already said the order there does not matter if the result of the entire WHERE clause does not change
Gieck: Scott0_: Create the query first. Then decide which indexes will help.
Franta: Doesn’t the WHERE order have to match the order of the columns in the index?
Malotte: In order for the index to be used?
Correl: Scott0_: What order are you talking about for god sake?
Doto: Scott0_: Shouldn’t be tough to test.
Correl: Scott0_: What order? WHERE a=1,b=2,c=3 is invalid in SQL so please show us the real WHERE clause
Keiter: Scott0_: the commas are misleading in that example.
Correl: Scott0_: a AND b is equivalent to b AND a by all possible means
Pili: Evne when the available index is a,b ?
Correl: Scott0_: What makes you think it could possibly matter?
Kleber: Scott0_: Do the test. See if you can cause a change by adjusting the WHERE clause order of expressions.
Matto: I haven’t touched an index in 2 years, there a number of reasons why my recall may be incorrect, this is why I was asking
Swancutt: Scott0_: there’s a matrix here, that I Can explicate easily.
Nourse: Scott0_: Sure. But a simple test would be an easy way to learn about the behavior.
Swancutt: WHERE a = 1, b = 2, c = 3 all 3 components used
Swancutt: WHERE a 1, b = 2, c = 3 only ‘a’ used
Correl: Swancutt: Please don’t use invalid syntax
Laabs: Swancutt will not survive long.
Correl: Swancutt: Also he is worried if changing of the order in WHERE with ANDs will matter. It does not
Avalos: Xgc: great ill wait 30 minutes so that I can test some things out on my 600M row table.
Correl: Scott0_: Why don’t you test it with smaller table?
Semaan: Scott0_: Simple tests.
Camano: Scott0_: CREATE TABLE .; INSERT INTO .;
Pilarz: Scott0_: Part of development is to learn how to verify your ***umptions. Don’t just ask people. We don’t know anything.
Omahony: Yeah I was figuring there would be a chart or do***ent somewhere that would outline the rules like Swancutt provided which would definitely be faster than building and filling a table with data and then running tests on that table
Karban: Scott0_: Even if you decide to ask, don’t trust the answer. Verify it.
Wheelan: Im not going to test ***umptions, im trying to get the rules so that I can build on the rules, not on ***umptions
Swancutt: The entire rule is “leftmost prefix, index in cardinality order”, but salle is right. you will understdand why better if you run the queries yourself. :
Swancutt: Ugh, “leftmost prefix, equality condition, index in cardinality order”
Gliwski: Scott0_: So you like to make lots of ***umptions and then find out much later that some are not correct.
Sloman: Scott0_: Books can be wrong. People can be wrong. Nicely formed lists of rules can be wrong.