Renaborges.com

Speed Up Your MySQL Searches: The Power of Secondary Indexes

May 23, 2024

Imagine a massive warehouse filled with boxes. You need to find a specific box containing red objects, but all the boxes are just piled on top of each other. Going through every box would take forever.

This is where organisation comes in. In MySQL databases, indexes act like a filing system for your data. 

Types of Indexes

There are different types of indexes. Common ones include:

  • Primary keys (unique identifiers for rows)

  • Unique indexes (ensure no duplicate values in a column)

  • Secondary indexes (used for filtering and sorting) are the ones we will talk about today.

What are Secondary Indexes?

Think of a secondary index as a separate list specifically for a particular section (column) in your warehouse. Let's say you have a table storing information about products in your online store. A secondary index can be created on the "colour" column. This index would list all the products and their corresponding colours, separate from the main table.

Why Use Secondary Indexes?

  • Faster Filtering: When you search for red objects (products with "colour" as "red"), MySQL can quickly look at the colour index instead of scanning every single product in the warehouse (table). It's like having a handy reference guide!

  • Efficient Sorting: For instance, if you want to sort products by colour, using a secondary index on the "colour" column helps MySQL sort things based on a specific feature (like colour) much faster!

Creating Secondary Indexes: A Step-by-Step Example

Let's create a secondary index on the "colour" column in our product table named products:

  1. Open your MySQL command line.

  2. Use the CREATE INDEX statement: Here's the command format:

mysql> CREATE INDEX index_name ON table_name (column_name); Example of a command:

mysql> CREATE INDEX colour_index ON products (colour); This command creates an index named colour_index on the colour column of the products table.

Using Your New Index

Now, whenever you run a query that filters or sorts based on colour, MySQL will (hopefully!) utilize the colour_index to speed things up. For example:

mysql> SELECT * FROM products WHERE colour = 'red';  -- Filter by colour Remember:

  • Indexes are for specific columns: Create secondary indexes on columns frequently used for filtering and sorting in your queries.

  • Balance is key: Too many indexes can slow down writes, so choose them wisely.

 

Git Basics

May 11, 2024

Today, we're learning about Git, a system that lets you track changes to your code over time.

This guide will equip you with the essential Git skills to start using version control locally straight away. Here's what we'll cover: 

  • What is Git and why is it awesome? 
  • Installing Git on your machine 
  • Basic Git commands to navigate version control 
  • Putting it all together with a practical example 
  • Untrack and/or remove files 

Git: Your Code's Best Friend 

With Git you can: 

  • Travel back in time: If you acci...


Continue reading...
 

Discovering Docker & Kubernetes - Work in Progress (WIP)

July 21, 2023

·       About Docker

·       Namespaces

·       Control Groups

·       Container Image

·       Container Runtimes

·       Container Orchestration

·       Kubernetes Cluster

·       Kubernetes Main Resources (Pods, Services, Replication Controllers, Persistent Volume, Persistent Volume Claims)

·       Docker Commands

·       Docker Client Verbs

·       Managing Containers

Docker uses a client-server architecture, described below:    

                                 Client                      ...


Continue reading...
 

Ping from a script

July 3, 2023

Method: We can write our own script using the ping command to query list of IP addresses and check whether they are alive or not as follows:

 

#!/bin/bash

#Filename: ping.sh

# Change base address 192.168.0 according to your network.

 

for ip in 192.168.0.{1..255} ;

do
       #Amount of packets transmitted "-c 2"
       #/dev/null is a virtual file. This will discard anything written to it.

ping $ip -c 2 &> /dev/null ;

if [ $? -eq 0 ];

then

echo $ip is alive

fi

done

 

...
Continue reading...
 

Git main commands

July 3, 2023

Git status

Git add .      (this adds everything or choose specific file to be added)

Git commit -m "my message"

Git push

Enter passphrase for key '/c/Users/rxxxxxxx/.ssh/id_rsa':



Continue reading...
 

For Machines Without Telnet Installed

July 3, 2023

cat < /dev/tcp/IP/PORT

cat < /dev/tcp/192.90.70.200/3306

OR

curl -v telnet://ip:port'

curl -v telnet://192.90.70.200:3306

curl -v telnet://192.90.70.2001:3306



Continue reading...
 

Helper Script

July 3, 2023

#!/bin/bash

 

echo "----------------------------"

echo "Sourcing Functions:"

echo "----------------------------"

echo " - nodes_up: Checks what hosts are up or down using nmap"

echo " - slbs_status: Check if NGINX Process and Status in AZ1 & AZ2"

echo " - slbs: Check if NGINX nodes are up / down using nmap in AZ1 & AZ2"

echo " - service_db_netwk: Checks connectivity between the Service in AZ1 and MySQL in AZ2 and vice-versa"

echo " - tail_service_log: Tail 100 lines of the service.lo...


Continue reading...
 

IF statements arguments / flags / options / operation

July 3, 2023

Test operators

-e file exists

-f file exists and is not a directory

-s file is not empty

-d directory exists

-x file is executable (for the user running the test)

-r file is readable (for the user running the test)

-w file has write permission (for user running the test)

-h / -L file is a symbolic link

! "not"

 

Compound Comparison

-a logical 'and' similar to &&

-o logical 'or' similar to ||

 

Integer Comparison

-eq is equal to

-ne is not equal to

-gt or > is greater t...


Continue reading...
 

Learning awk

July 3, 2023

Print column 1 & 2

ps | awk '{print $1,$2}'


To separate the columns titles you can use "\t" between the numbers:

ps | awk '{print $1"\t"$2}'


By default the field separator for awk is a space, if it's not the case tell awk which one it is by using F for field separator. In the example of /etc/passwd file the separator is semi-colon

awk -F ":" '{print $1}' /etc/passwd

You can also grep:

awk -F ":" '{print $1}' /etc/passwd | grep myservice

 

To search for every last field in the line, but r...


Continue reading...
 

Learning sed

July 3, 2023

sed > Stream editor allow filter and transform texts, find pattern and replace with whatever is that you want to replace with, it's like search and replace.

You can use other separators instead of slashes (/). Example pipe | or hash #

1 - sed substitution -- s for substitution. replaces only the first occurrence in each line of the file and save to a new file

sed 's/word/newword/' < oldfile > newfile

 

2 - replaces every occurrence of 'word'. g for global

sed 's/word/newword/g' < oldfi...


Continue reading...
 
blog comments powered by Disqus
blog comments powered by Disqus