If you have a table with a lots of rows, and you know that one of the columns will be used often as a search criteria, you can add an index for that column to improve the search performance. To add an index, you can use the "CREATE INDEX" statement as shown in the following script:
mysql> CREATE TABLE wb(id INTEGER PRIMARY KEY,
subject VARCHAR(80) NOT NULL,
description VARCHAR(256) NOT NULL,
create_date DATE NULL);
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE INDEX wb_subject ON wb(subject);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
Is it helpful?
Add CommentView Comments
Ques 97. How To Convert Numeric Values to Character Strings?
You can convert numeric values to character strings by using the CAST(value AS CHAR) function as shown in the following examples:
SELECT CAST(4123.45700 AS CHAR) FROM DUAL;
4123.45700
-- How to get rid of the last 2 '0's?
SELECT CAST(4.12345700E+3 AS CHAR) FROM DUAL;
4123.457
SELECT CAST(1/3 AS CHAR);
0.3333
-- Very poor conversion
Is it helpful?
Add CommentView Comments
Ques 98. How To Get a List of Indexes of an Existing Table?
If you want to see the index you have just created for an existing table, you can use the "SHOW INDEX FROM tableName" command to get a list of all indexes in a given table. The tutorial script below shows you a nice example:
mysql> SHOW INDEX FROM test;
Is it helpful?
Add CommentView Comments
Ques 99. How To Convert Character Strings to Numeric Values?
You can convert character strings to numeric values by using the CAST(string AS DECIMAL) or CAST(string AS SIGNED INTEGER) function as shown in the following examples:
SELECT CAST('4123.45700' AS DECIMAL) FROM DUAL;
4123.46
-- Very poor conversion
SELECT CAST('4.12345700e+3' AS DECIMAL) FROM DUAL;
4123.46
-- Very poor conversion
SELECT CAST('4123.45700' AS SIGNED INTEGER) FROM DUAL;
4123
SELECT CAST('4.12345700e+3' AS SIGNED INTEGER) FROM DUAL;
4
-- Very poor conversion
Is it helpful?
Add CommentView Comments
Ques 100. How To Drop an Existing Index in MySQL?
If you don't need an existing index any more, you should delete it with the "DROP INDEX indexName ON tableName" statement. Here is an example SQL script:
mysql> DROP INDEX wb_subject ON test;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0