The Heap™ – Consultancy ©®

5:37 AM

Morning

Morning

@ypercubeᵀᴹ Thanks, really nice

6:03 AM

Morning

In sql server if i have a index having columns: ic1,ic2,ic3,ic4. Is DBMS smart enough to pick the right index and columns if the order of columns in a query is random? for eg. a query which has columns in where clause in this order --> ic4,ic1,ic3.

@MYGz order is not important. but which columns are used in the WHERE, is. And so is important the order of columns in the index.

Can you share the query?

6:18 AM

For instance, in that specific scenario there's no filter on ic2, which means ic3 and ic4 won't participate in index seek using that index.

6:43 AM

@ypercubeᵀᴹ I see. right now using where clause on 2-3 columns on 1st table with around 5M recs, 2-3 columns from 2nd table with 14M recs and 3-4 columns from 3rd table with 2M recs.

Ran into index issues day before yest.

Query returning just 1.2k records from application side took 28 mins.

After fixing index returned in 4 seconds.

@AndriyM So if all are present then order doesn't matter.

@MYGz The order of predicates in WHERE doesn't matter, that's right.

gbn

7:36 AM

@AndriyM Correct, but it also means it might evaluate this in the unexpected "wrong" order (compated to left to right evluation in other languages) "isnumeric(somecol) = 1 and somecol = 2"

probably doesn't apply here

Yeah, I think the question was specifically about the cases where the predicates were merely shuffled around but would still allow the index to be used for seek.

Andy K

7:56 AM

evening and morning

Philᵀᴹ

8:29 AM

Morning

9:12 AM

@MYGz Yes. And we all assume here that the conditions use equality (i.e. WHERE col1 = 1 AND col2 = 2 AND col3 = 'some_text' ...). If you have different, range conditions (IN, >=, >, <, <=), things change.

9:31 AM

@ypercubeᵀᴹ Oh. Performance hit for range conditions?

@MYGz No. I meant the best index may be different.

I see.

WHERE col1 = 1 AND col2 = 2 AND col3 >= 7 will work better with different index than the WHERE col1 >= 1 AND col2 = 2 AND col3 = 7

Aha. Is this related to sort order (Ascending and Descending) of columns within index?

It has to do with that btree indexes are good for equality conditions

or when all conditions except the last one (in column index order) are equality and the last one is range condition

So the first WHERE will like best an (col1, col2, col3) or a (col2, col1, col3)index.

the second WHERE will like best an (col2, col3, col1) or (col3, col2, col1) index

If the "best" is not available, the query may still use a not perfect fit index but it will need to do extra work (scan instead of seek), i.e. read a larger part of or the whole index.

10:03 AM

Oh. Thanks. Quite deep topic.

Colin 't Hart

Afternoon

@MYGz Following on from @ypercubeᵀᴹ's answer: And of course, how much of the index will be scanned depends on the values in the query. When the DBMS makes its execution plan, it estimates how much of the index will be scanned based on the value in the query and the statistics it has of the data in the table.

So if there are several different indexes to chose between, the DBMS will choose the one that results in what it estimates will be the fewest rows scanned -- more accurately, the least total amount of work when the whole query is said and done.

10:56 AM

Is this polite enough?

Welcome to DBA.SE. We appreciate your participation. However, the community does expect a certain quality in the questions and answers posted. Please consider reading the following article: How do I write a good answer? (Help Centre) In the section Answer the question there is a short sentence which reads: Brevity is acceptable, but fuller explanations are better. Please edit your answer and add some details. Thank you. — hot2use 40 secs ago

...or is it to condescending?

11:11 AM

@Colin'tHart I see. Thanks for info.

Colin 't Hart

@hot2use I would remove the "Thank you." at the end.

@Colin'tHart Thanks for FB

A: error 2013 (hy000) lost connection to mysql server during query while load of mysqldump

11:53 AM

Any specific reason why my comment was removed from this answer?

@A_funs was right, inspecting the system log yields this: Aug 14 08:04:15 centos-php55 kernel: Killed process 8597 (mysqld) total-vm:7395680kB, anon-rss:3351108kB, file-rss:0kB, shmem-rss:0kB Aug 14 08:04:15 centos-php55 mysqld_safe[7848]: /usr/bin/mysqld_safe: line 200: 8597 Killed ...

I have seen similar comments from moderators on other answers asking the same.

What was the comment?

I had asked how his answer differed from previous answers and that OP had stated that memory wasn't an issue.

I don't think I violated the CoC, but I guess nowadays, as you have already pointed out, it could be that I did "violate" the new "be nice(er)" policy.

He then answered (in a comment) that "Oh I didn't notice that, but let's leave it here anyway". Which has since been removed too.

Oh, then maybe a moderator saw that the answerer was unlikely to edit the post, and deemed the comment exchange unnecessary or something. I mean, it does somewhat work as an explanation, but I wouldn't say it would justify the decision to remove the comments for me personally, but hey

Ah well.

I can't imagine any of our mods to go by that, to be honest, so maybe there was something else.

12:08 PM

Hmmm.

12:47 PM

@hot2use After the author responded to the point you raised, the comment had served its purpose, so it was no longer needed.

I think giving it some time might be beneficial. If the community saw the point in @hot2use's comment and showed its support by upvoting it and the answerer saw that, maybe the reaction would eventually be different.

@PaulWhite Thanks for the feedback.

@AndriyM +1

@AndriyM That's fine. I have reinstated them. I was on the page for a different reason and cleaned up comments that looked obsolete as usual. I know I can rely on you all to flag those comments when they are truly no longer needed.

Thanks

@PaulWhite +1 for cleaning up.

I was contemplating the fact that the comment cleaner had probably struck again.

A: Why don't people read the question before editing and commenting?

12:59 PM

The other thing to bear in mind here is that answers that answer the question are still valid answers. It might be unnecessary, in some sense, to have multiple answers saying the same thing, but one never knows.

I get paid to write words in English. As part of becoming someone who gets paid to write words in English, I had to learn a very hard lesson. This is true, whether you like it or not: When you write something, and someone else doesn't understand it after the amount of reading they put into it...

and specifically this comment:

These are excellent observations; I'd add that it is often impossible to write an explanation that will be clear to all readers; some of the best writers circumvent this by stating the same thing in multiple ways, but this can be challenging to achieve without becoming tedious. Hence the value inherent in collecting multiple (conceptually-identical) answers and multiple (cross-linked duplicate) questions... Also, the ability to use an editor's work to view your work through someone else's eyes is one of the most-overlooked educational aspects of Stack Overflow. — Shog9 ♦ Sep 9 '15 at 16:47

I thought that was quite interesting.

1:24 PM

When you aren't quite familiar with the subject but it's interesting enough for you to be trying to understand what the poster is talking about, it's very likely that something will keep confusing you because of the lack of knowledge. You can't blame the poster they didn't try their best to explain things.

But in general I agree that it's good to have answers explaining the same thing differently