MySQL Index
An index is a data structure such as B-Tree that improves the speed of data retrieval on a table at the cost of additional writes and storage to maintain it.
Indexes are a type of lookup table that is used to improve the performance of SELECT queries. Without an index, MySQL performs lookups by sequential searching the table. It has to start with the first record and then read the whole table to find the relevant rows. Conversely, if the table has an index for the columns in question, MySQL can quickly find the data without having to look at all the rows in a table.
The query optimizer may use indexes to quickly locate data without having to scan every row in a table for a given query.
Indexes can consist of one or more fields up to a total of 16 columns. For certain data types, you can even index a prefix of the column. One way to think about multiple-column indexes is like a sorted array containing values that are created by concatenating the values of the indexed columns. For this reason, indexes on multiple fields are also known as “concatenated indexes”.
CREATE INDEX cannot be used to create a PRIMARY KEY.
Syntax to create an index.
CREATE INDEX [index name] ON [table name]([column name]);
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
CREATE INDEX emailIndex ON user(email);
EXPLAIN SELECT * FROM play_ground.user WHERE email = 'lau@gmail.com';
Multiple column index
A multiple-column index is also know as composite index.
Syntax to create a multiple-column index
CREATE INDEX [index name] ON [table name]([column1,column2,column3,...]);
The query optimizer cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. In the where clause, all columns in the multiple index must be used otherwise the index will not be used.
CREATE INDEX complexIndex ON user(name,email);
SELECT * FROM user WHERE name = 'Lau' AND email = 'lau@gmail.com';
The above query works because in the WHERE clause, name and email are both used.
SELECT * FROM user WHERE email = 'lau@gmail.com';
The above query does not use our complexIndex as it does not have both of the composite index columns(name and email).
May 7, 2020Linux prepend content to a file
Prepend content to a file
Use sed with -i option. You can also specify the line to which the content will prepend to.
sed -i '' '1s/^/package home;/" Home.java
Prepend content to multiple files
Here I need to add a package to 200 java files. To do this manual would be tedios.
for f in *.java do sed -i "" "1s/^/package home;/" $f done
CSS Margin
margin properties are used to create space around elements, outside of any defined borders.
margin: top right bottom left;
margin: 10px 15px 20px 25px;
top – 10px
right – 15px
bottom – 20px
left – 25px
margin: top right/left bottom;
margin: 10px 20px 25px;
top – 10px
right/left – 20px
bottom – 25px
margin: top/bottom right/left ;
margin: 10px 20px;
top/bottom – 10px
right/left – 20px
margin: top/right/left/bottom;
margin: 10px;
top/bottom/right/left – 10px
<div class="row">
<div class="col-4">
<h4>Margin</h4>
</div>
<div class="col-8" id="margin_1">
Hi my name is Folau
</div>
</div>
<style>
#margin_1{
margin: 20px;
border: 2px solid blue;
}
</style>
Linux Search/Filters
Grep
Grep command stands for “global regular expression print”. grep command filters the content of a file which makes our search easy.
grep {search-key-word} {filename}
// search for word lisa in test.txt file grep 'lisa' test.txt
grep -n
The -n option display the line number
grep -n 'lisa' test.txt
grep -v
The -v option displays lines not matching to the specified word.
// search for any word that is not lisa in test.txt file grep -v 'lisa' test.txt
grep -i
The -i option filters output in a case-insensitive way.
grep -i 'lisa' test.txt
grep -w
By default, grep matches the given string/pattern even if it found as a substring in a file. The -w option to grep makes it match only the whole words.
grep -w 'lisa' test.txt
Sed
SED command in UNIX is stands for stream editor and it can perform lot’s of function on file like, searching, find and replace, insertion or deletion. Though most common use of SED command in UNIX is for substitution or for find and replace. By using SED you can edit files even without opening it, which is much quicker way to find and replace something in file, than first opening that file in VI Editor and then changing it.
Replace a string
// replace the word lisa with lisak sed 's/lisa/lisak/' test.txt // To edit every word, we have to use a global replacement 'g' sed 's/lisa/lisak/g' test.txt // replace the second occurence of lisa with lisak sed 's/lisa/lisak/2' test.txt // replace from 2nd occurence to the all occurrences sed 's/lisa/lisak/2g' test.txt // replace lisa with lisak on line 2 sed '2 s/lisa/lisak/' test.txt
Delete lines from a file
sed ‘nd’ {filename}
// delete 3rd line in test.txt sed '3d' test.txt // delete from 3rd to 6th line sed '3,6d' test.txt // delete from 3rd to the last line // sed '3,$d' test.txt
Iterating