Indexing on SQL Table

Deutomo

— 1 min read

So i learn quickly how to index table on SQL and what is use for

i dont really understand too much but it just for making the searching is faster and reduce searching steps.

If using "SELECT * FROM users WHERE city = 'New York' ORDER BY balance DESC;"

thats query tooks 3 steps, the searching of the users, filtering the city, and reorder descending. For more detail just add keyword EXPLAIN ANALYZE in the query, like this "EXPLAIN ANALYZE SELECT * FROM users WHERE city = 'New York' ORDER BY balance DESC;". It will show the steps and what is the need of the searching.

So for come up with that problem for making it faster we can try to create an index for some column so it just search the column single time without scans all columns.

Indexing a column is just make a new pointer table that reference to the address of the rest of the data with have that column (using hash?). So if the city is the index column it just show the each city with the pointer of all data with that city. So it can be faster to scan because it just scan on that all address from that city instead.

To create an index table is simple just using keyword CREATE INDEX.

"CREATE INDEX city_idx ON users (city);"

thats will create index on city column and when check using EXPLAIN ANALYZE with that same query at the first, it will show 3 steps too but on the last steps it just searching on that single index not to all the data.

But we can create multiple index too so it can be more spesifically, example

"CREATE INDEX city_balance_idx ON users (city,balance);"

thats will create index on city and balance column so it just combine that possibility to get the rest of the data.

So if using EXPLAIN ANALYZE like the first query using combined index. It will show it search on single index with 1 step. It is will be really fast.

But not all things need to be index because index can slower the database too so this is the rule.

  • index that you need not all things
  • try avoid index large data, like emails because it can slower the database
  • always use EXPLAIN ANALYZE to check the steps if your doubt

thats it, i learn from this video: SQL Indexing

Read more