Servage Magazine

Information about YOUR hosting company – where we give you a clear picture of what we think and do!

MYSQL commands – Part 7

Friday, July 18th, 2014 by Servage

table_in_useRemoving a column

You may also decide, upon reflection, that the page count column pages isn’t actually all that useful for this particular database, so here’s how to remove that column using the DROP keyword:

ALTER TABLE classics DROP pages;

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

Deleting a table

Deleting a table is very easy indeed. But, because I don’t want you to have to re-enter all the data for the classics table, we won’t delete that one. Instead, let’s quickly create a new table, verify its existence, and then delete it by typing in the commands. The result of these four commands should look like this.

Creating, viewing, and deleting a table

CREATE TABLE disposable(trash INT);

DESCRIBE disposable;

DROP TABLE disposable;

SHOW tables;

Indexes

As things stand, the table classics works, and MySQL won’t have any problem searching it—until it grows to more than a couple of hundred rows, that is. 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—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.

Creating an Index

The way to achieve fast searches is to add an index, either when creating a table or at any time afterwards. But the decision is not so simple. For example, there are different index types, such as a regular INDEX, PRIMARY KEY, and FULLTEXT. 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 those columns. Indexes can also get complicated, because you can combine multiple columns in one index. And even when you’ve gotten to grips with all of that, you still have the option of reducing index size by limiting the amount of each column to be indexed.

Adding indexes to the classics table

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 both the author and title columns, limiting each index to only the first 20 characters. For instance, when MySQL indexes the following title:

The Adventures of Tom Sawyer

it will actually store in the index only the first 20 characters:

The Adventures of To

This is done to minimize the size of the index, and to optimize database access speed. I chose the value 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 (F for Fiction, N for Non-Fiction, and P for Play), but I chose an index of four characters to allow for future category types that may be unique only after four 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’s an integer, not a string.

Sources for further reading

MYSQL commands – Part 7, 4.5 out of 5 based on 2 ratings
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No comments yet (leave a comment)

You are welcome to initiate a conversation about this blog entry.

Leave a comment

You must be logged in to post a comment.