Indexes on SQL statement

Question

New to learning SQL and just wondering about how indexes work, for example (primary keys as bolded):

Hotel (hotelNo, hotelName, city),
Room (roomNo, hotelNo, type, price),
Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo),
Guest (guestNo, guestName, guestAddress)

Query:

SELECT 
    r.roomNo, r.type, r.price 
FROM 
    Room r, 
    Booking b, Hotel h 
WHERE 
    r.roomNo = b.roomNo 
    AND b.hotelNo = h.hotelNo 
    AND h.hotelName = "Hilton" 
    AND r.price > 200

Can someone explain how I would use any indexing to improve query performance? I was thinking of just sorting it by price, that way the SQL Server doesn't have to check every line manually but are there any other indexes that should be built upon? Additionally, are primary keys always clustered indexes?

If you are just learning SQL, why aren't you learning proper, explicit, **standard** `JOIN` syntax? — Gordon Linoff, Feb 12 '20 at 01:26
@SALEH Can you explain what you mean. Also, how do we prove that we should index certain things? Or is it just common sense? — Lizzy, Feb 12 '20 at 01:29
I am assuming you are using `mysql` as database. The above DML creates a new index on a column of a table in mysql. Determining the necessity to add index to a table depends on myriads of factors. A criteria for indexing can be that a certain read query is expected to be executed much more frequently on a table. In that case, its desirable to have some indexing strategy for those queries. An ideal candidate for indexing in that case is the column(s) whose values are used at `where` clause of that query — SALEH, Feb 12 '20 at 01:36
You can take a look [here](https://dba.stackexchange.com/questions/56/how-to-determine-if-an-index-is-required-or-necessary) to get more insights. Its a fairly common topics. Google searching will present you more useful results — SALEH, Feb 12 '20 at 01:39
Before we even get to your question, here is some advice that I hope is helpful; 1. Use ANSI SQL (INNER JOIN) not "comma" joins. 2. When asking SQL Questions, please specify the "brand" in the tags. Often "SQL" is mixed up with "Microsoft SQL Server", but your SQL might be running on Oracle or MS Access or MySQL — Nick.Mc, Feb 12 '20 at 05:21
[Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Feb 12 '20 at 07:26
Note that `AND h.hotelName = "Hilton"` is invalid standard SQL (unless you have a **column** named `"Hilton"`) In "SQL" string constants need to be enclosed in single quotes. Double quotes are used for identifiers. If you are _learning_ SQL, then please learn the correct syntax right from the start — , Feb 12 '20 at 07:28

score 0 · Answer 1 · answered Feb 12 '20 at 05:08

I am not going to answer directly to your question, but will try to give a general idea so that you can yourself improve your database query performance based on your use-cases of your application(as it may vary):

No need to index on your primary key
Index on those fields which fields may/will be needed to search/query in your database frequently.
While indexing any column, try to make sure that columns's values are low abundant(not that much important though)
Try index on multiple column, if it is done, no need to index those columns in single
Must maintain the indexing order in your query after where clause, otherwise the result will have negative impact on db.
Also, keep in mind, indexing consumes your db-space too! Good indexing can help improving performance while bad indexing will down your system.

For additional side note, you can check this link What columns generally make good indexes?

Happy indexing !

Indexes on SQL statement

1 Answers1