CODEWITHSUNDEEP
mysqlsql
MRyui
MRyui

Reputation: 31

mySQL (and MSSQL), using both indexed and non-indexed columns in where clause

The database I use is currently mySQL but maybe later MSSQL.

My questing is about how mySQL and msSQL takes care about indexed and nonindexed columns.

Lets say I have a simple table like this:

*table_ID -Auto increase. just a ID, indexed.
*table_user_ID -every user has a unique ID indexed
*table_somOtherID -some data..
*....

Lets say that I have A LOT!! of rows in this table, But the number of rows that every user add to this table is very small (10-100)

And I want to find one o a few specific rows in this table. a row or rows from a specific User(indexed column).

If I use the following WHERE clause: ..... WHERE table_user_ID= 'someID' AND table_someOtherID='anotherValue'.

Will the database first search for the indexed columns, and then search for the "anotherValue" inside of those rows, or how does the database handle this?

I guess the database will increase a lot if I have to index every column in all tables.. But what do you think, is it enough to index those columns that will decrease the number of rows to just ten maybe hundred?

Upvotes: 3

Views: 3070

Answers (3)

suhprano
suhprano

Reputation: 1733

In regards to SQL Server: The ordering of the indexes are important depending on how you query and how the indexes are structured. If you create an index on the columns

-table_user_id
-table_someotherID

The index is ordered by the table_user_id first. Example:

1-2
1-5
1-6
2-3
2-5
2-6

For the first record on the index, 1 being the table user id, and 2 being some other value. If you run a query with a where on table_user_id = blah, it will be very fast to use this index, since the table_user_id are indexed in order. But if you run a query that only uses table_someotherID in the WHERE clause, it might not even use this index, as instead of doing a quick seek in the index for the matching value, it will do a rough scan of the index (which is less efficient than a seek).

Also SQL Server has a INCLUDE feature that associate the columns you want in the SELECT clause to the index you create on the WHERE or JOIN columns.

So to answer your question, it all depends on how you create the indexes and how you query them. You're right not to think about indexing every column, as indexes take up storage and performance hit when you do inserts and updates on the table.

Upvotes: 0

bot403
bot403

Reputation: 2161

Database optimizers generally work on a cost basis on indexes by looking at all the possible indexes to use based on the query. In your specific case it will see 2 columns - table_user_ID with an index and someOtherID without an index. If you really only have 10-100 rows per userID then the cost of this index will be very low and it will be used. This is because the cardinality is high and the DB can only read the few rows it needs and not touch the other rows for every other user its not interested in. However, if the cost to use the index is very high (very few unique userIDs and many entries per user) it might actually be more efficient to not use the index and scan the whole table to prevent random seeking action as it jumps around the table grabbing rows based on the index.

Once it picks the index then the DB just grabs the rows that match that index (10 to 100 in your case) and try to match them against your other criteria searching for rows where someOtherID='anotherValue'

Upvotes: 5

RichardTheKiwi
RichardTheKiwi

Reputation: 107786

But the number of rows that every user add to this table is very small (10-100)

You only need to index the user_id. It should give you good performance regardless of your query, as long as it includes the user_id in the filter. Until you have identified other use cases, it will pretty much work as you state

Will the database first search for the indexed columns, and then search for the "anotherValue" inside of those rows, or how does the database handle this?

Yes (in layman terms that is close).

Upvotes: 2

Related Questions