mysql indexing

mega_hurtz

Is it true that mysql will only choose one index per table when performing a query?

tomhath

The index determines the order in which it accesses the rows as it tries to satisfy the "where" clause. I don't know the internals of mysql, but I can't imagine how it (or any other database engine) could perform a search using more than one index per table.

[deleted]

"The index determines the order in which it accesses the rows as it tries to satisfy the "where" clause."

Don't you mean:
"An index is an abbreviated version of the real table, making it much faster to read than the original table. The engine can use this data to satisfy the where clause without accessing the real (slow) table."

I don't see any reason why mysql would not use more than one index per table. Then again, mysql is very limited.

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

tomhath

No, I meant what I said. An index is stored in a B-tree structure, mysql searches through the tree for a specific value. A
B-tree is much different than an abbreviated layout of the real table. You can have multiple column indexes, but you can only traverse one tree at a time.

This is from the manual:

5.4.3 How MySQL Uses Indexes
Indexes are used to find rows with a specific value of one column fast. Without an index MySQL has to start with the first record and then read through the whole table until it finds the relevant rows. The bigger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly get a position to seek to in the middle of the data file without having to look at all the data. If a table has 1000 rows, this is at least 100 times faster than reading sequentially. Note that if you need to access almost all 1000
rows it is faster to read sequentially because we then avoid disk seeks.

All MySQL indexes (PRIMARY, UNIQUE, and INDEX) are stored in B-trees. Strings are automatically prefix- and end-space compressed.

[deleted]

"You can have multiple column indexes, but you can only traverse one tree at a time."

Yes, but the b-tree still contains data from the real table, there's just less of it, and it's structured more efficiently for searching.

I still don't see a reason why MySQL wouldn't be able to use two indexes to satisfy the 'where' clause. Maybe not at the same time, but surely it can use two indexes if the 'where' clause has two elements?

A forum, a FAQ, what else do you need?