标签云

微信群

扫码加入我们

WeChat QR Code

I am really interested in how MySQL indexes work, more specifically, how can they return the data requested without scanning the entire table?It's off-topic, I know, but if there is someone who could explain this to me in detail, I would be very, very thankful.


dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html

2019年10月20日35分12秒

This is a very broad question. If you have a specific example of a query that won't use an index, and you don't know why, you could post it and people might help.

2019年10月20日35分12秒

SELECT * FROM members WHERE id = '1' - so why with index it works faster? What that index does here?

2019年10月20日35分12秒

That looks like a query that just looks up a specific, indexed record (perhaps identified by primary key). The index makes this faster because it is stored in memory, the corresponding index row can be looked at and it contains a pointer to where the actual data is stored. So MySQL can go to the exact location in the table without having to scan the table.

2019年10月20日35分12秒

Very well, thank you!

2019年10月21日35分12秒

You are explaining what is it, not how technically it works internally.

2019年10月20日35分12秒

Tutu Kumari: See the question's revisions; feel free to also revise the answer to fit the current question (note the various engines and index types - see e.g. the documentation here: dev.mysql.com/doc/refman/8.0/en/index-btree-hash.html )

2019年10月21日35分12秒

Will following queries have advantage in above case?1.SELECT last_name, first_name FROM person WHERE last_name= "Constantine"2. SELECT last_name, first_name FROM person WHERE last_name LIKE "%Constantine"

2019年10月20日35分12秒

First querry will, second query will not. Use EXPLAIN: dev.mysql.com/doc/refman/5.5/en/explain.htmlFor indexing second query with MySQL, you have to use FULLTEXT INDEX: dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

2019年10月20日35分12秒

I upvoted you because you were at 127 and the #1 answer was at 256. I couldn't avoid making everything nice and clean, binary-wise.

2019年10月20日35分12秒

This was new information for me "order that you query these fields matters a lot." thanks.

2019年10月20日35分12秒

i like this answer more than the accepted answer. thanks

2019年10月20日35分12秒

This is called binary search.

2019年10月20日35分12秒

Thanks, finally a answer that explains why it is quicker and not just how the db functions with indexes.

2019年10月20日35分12秒

Why it's 7 steps?

2019年10月20日35分12秒

The actual number of steps is highly dependent on the data - number of unique value and distribution across your range. 7 is the theoretical max for 100 values. Full discussion of how to calculate the number of steps here stackoverflow.com/questions/10571170/…

2019年10月20日35分12秒

The most common MySQL index is a B+Tree which works similarly to a binary search but not quite the same. The algorithmic complexity is the same but the way it searches is not. See en.wikipedia.org/wiki/B-tree

2019年10月20日35分12秒

Nice article. I don't know SQL Server, but the basic workings look very similar. (metanote: disabling CSS styles in the 2nd linked article unhides the content)

2019年10月20日35分12秒

Welcome to Stack Overflow! I've noted that all your answers link to your own videos. Please note that overt self promotion is not allowed.

2019年10月20日35分12秒

He wants to promote his videos. LOL

2019年10月21日35分12秒