An alternative to using ALTER TABLE to add an index is to use the CREATE INDEX command.
They are equivalent, except that CREATE INDEX cannot be used for creating a PRIMARY KEY

These two commands are equivalent:
ALTER TABLE classics ADD INDEX(author(20));
CREATE INDEX author ON classics (author(20));

The way to achieve fast searches is to add an index, either when creating a table or at any time afterward. But the decision is not so simple. For example, there are different index types, such as a regular INDEX, a PRIMARY KEY, or a FULLTEXT index. Also, you must decide which columns require an index, a judgment that requires you to predict
whether you will be searching any of the data in each column. Indexes can get more complicated too, because you can combine multiple columns in one index. And even when you’ve decided that, you still have the option of reducing index size by limiting the amount of each column to be indexed. If we imagine the searches that may be made on the classics table, it becomes apparent that all of the columns may need to be searched.

ALTER TABLE classics ADD INDEX(author(20));
ALTER TABLE classics ADD INDEX(title(20));
ALTER TABLE classics ADD INDEX(category(4));
ALTER TABLE classics ADD INDEX(year);
DESCRIBE classics;

The first two commands create indexes on the author and title columns, limiting each index to only the first 20 characters. For instance, when MySQL indexes the following title:

Markku Aro
It will actually store in the index only the first 20 characters:
Markku Aro

This is done to minimize the size of the index, and to optimize database access speed. I chose 20 because it’s likely to be sufficient to ensure uniqueness for most strings in these columns. If MySQL finds two indexes with the same contents, it will have to waste time going to the table itself and checking the column that was indexed to find
out which rows really matched. With the category column, currently only the first character is required to identify a string as unique (P for POP, I for Iskelmä, and C for Classic), but I chose an index of four characters to allow for future categories that may share the first three
characters. You can also reindex this column later, when you have a more complete set of categories. And finally, I set no limit to the year column’s index, because it has a clearly defined length of four characters.

As things stand, the table classics works and can be searched without problem by MySQL—until it grows to more than a couple of hundred rows. At that point, database accesses will get slower and slower with every new row added, because MySQL has to search through every row whenever a query is issued. This is like searching through every book in a library whenever you need to look something up.

Of course, you don’t have to search libraries that way, because they have either a card index system or, most likely, a database of their own. And the same goes for MySQL, because at the expense of a slight overhead in memory and disk space, you can create a “card index” for a table that MySQL will use to conduct lightning-fast searches.

Deleting a table is very easy indeed. But, because I don’t want you to have to reenter all the data for the classics table, let’s quickly create a new table, verify its existence, and then delete it. You can do this by typing the commands:

CREATE TABLE datatiedot(trash INT);
DESCRIBE datatiedot;
DROP TABLE datatiedot;
SHOW tables;

Actually, upon reflection, you might decide that the page count column pages isn’t actually all that useful for this particular database, so here’s how to remove that column by using the DROP keyword:
ALTER TABLE classics DROP pages;

Remember that DROP is irreversible. You should always use it with
caution, because you could inadvertently delete entire tables (and
even databases) with it if you are not careful!

Rename example command is: ALTER TABLE classics CHANGE type category VARCHAR(16);

Note the addition of VARCHAR(16) on the end of this command. That’s because the CHANGE keyword requires the data type to be specified, even if you don’t intend to change it, and VARCHAR(16) was the data type specified when that column was initially created as type.

Changing a column’s data type also makes use of the ALTER command, this time in conjunction with the MODIFY keyword. To change the data type of the column year from CHAR(4) to SMALLINT (which requires only 2 bytes of storage and so will save disk space):D, enter the following command:
ALTER TABLE classics MODIFY year SMALLINT;
When you do this, if the conversion of data type makes sense to MySQL, it will automatically change the data while keeping the meaning. In this case, it will change each string to a comparable integer, so long as the string is recognizable as referring to an integer.

Renaming a table, like any other change to the structure or meta-information about a table, is achieved via the ALTER command. So, for example, to change the name of the table classics to klassikot, you would use the following command: ALTER TABLE classics RENAME klassikot;

If you tried that command, you should revert the table name by entering the following, so that later examples in this chapter will work as printed:
ALTER TABLE klassikot RENAME classics;

To add data to a table, use the INSERT command. Let’s see this in action by populating the table classics with the data.

Example 🙂 Populating the classics table
INSERT INTO classics(author, title, type, year)
VALUES(‘Markku Aro’,’30 suosikkia’,’POP’,’1998′);
INSERT INTO classics(author, title, type, year)
VALUES(‘Lenni Kalle’,’parhaat’,’klassikko’,’1811′);
INSERT INTO classics(author, title, type, year)
VALUES(‘Esa Mäkelä”,’Ketsuppia saksasta’,’POP’,’1990′);
INSERT INTO classics(author, title, type, year)
VALUES(‘Matti ja Teppo”,’kautta aikain parhaat’,’iskelmä’,’1975′);
INSERT INTO classics(author, title, type, year)
VALUES(‘Suvi Teräsniska’,’30 parhainta ‘,’POP’,’2020′);

After every second line, you should see a Query OK message. Once all lines have been entered, type the following command, which will display the table’s contents.

Use next command to read content:
SELECT * FROM classics;

BINARY(n) Exactly n (<= 255) As CHAR but contains binary data
VARBINARY(n) Up to n (<= 65535) As VARCHAR but contains binary data

The BINARY data types store strings of bytes that do not have an associated character set.