I keep getting CPU Exceeded Errors, What are some things I can do to fix this?

Ordering by surname is a common requirement, so it would make sense to create an index on surname. But in this example our employee table consists of thousands of people from Swaziland, and with the surname "Dlamini". So we need to index on firstname as well. The good news is that MySQL uses leftmost prefixing, which means that a multi-field index A,B,C will also be used to search not only for a,b,c combinations, but also A,B as well as just A.
In our example, this means that an index of the type
ALTER TABLE employee ADD INDEX(surname,firstname);
is used for a queries such as
EXPLAIN SELECT overtime_rate FROM employee WHERE surname='Madida';
as well as
EXPLAIN SELECT overtime_rate FROM employee WHERE surname='Madida' and firstname="Mpho";
which both result in

+--------+------+-------------------+---------+---------+-------+------+-----------+

|table   | type | possible_keys     | key     | key_len | ref   | rows |Extra      |

+--------+------+-------------------+---------+---------+-------+------+-----------+

|employee| ref  | surname,surname_2 | surname |      41 | const |    1 |where used |

+--------+------+-------------------+---------+---------+-------+------+-----------+

However, the query
EXPLAIN SELECT overtime_rate FROM employee WHERE firstname='Mpho';
does not use an index, as firstname is not available from the left of the index, as shown below.

+----------+------+---------------+------+---------+------+------+------------+

| table    | type | possible_keys | key  | key_len | ref  | rows | Extra      |

+----------+------+---------------+------+---------+------+------+------------+

| employee | ALL  | NULL          | NULL |    NULL | NULL |    3 | where used |

+----------+------+---------------+------+---------+------+------+------------+

If you needed this kind of query, you would have to add a separate index on firstname.

The Query Optimizer, OPTIMIZE and ANALYZE

The magic inside MySQL that decides which keys, if any, to use to in the query, is called the query optimizer. It takes a quick glance at the index to see which indexes are the best to use. Compare it to searching for a CD by the artist "Savuka", called "Third World Child", where there are 2 indexes, one alphabetical by artist name, and the other by album name. At a glance, you see that there are 20000 unique artists, and 400000 unique albums, so you decide to search by artist. But if you knew that there were 50 Savuka albums, and that Third World child is the only album starting with "T", your search criteria would change. You can provide similar information for the Optimizer by running
ANALYZE TABLE tablename;
This stores the key distribution for the table (running ANALYZE is equivalent to running myisamchk -a or myismachk --analyze).
Many deletes and updates leave gaps in the table (especially when you're using varchar, or in particular text/blob fields). This means there are more unnecessary disk I/O's, as the head needs to skip over these gaps when reading. Running
OPTIMIZE TABLE tablename
solves this problem. Both of these statements should be run fairly frequently in any well looked after system.
Another factor that most people don't use when indexing is to take advantage of short indexes. You don't have to index on the entire field. Our surname and firstname fields are 40 characters each. That means the index we created above is 80 characters. Inserts to this table then also have to write an additional 80 characters, and selects have 80 character blocks to maneuvre around (disk I/O is the primary hardware bottleneck, but that's for another day!). Try reducing the size of your index - in the example above, rather use.
ALTER TABLE employee ADD INDEX(surname(20),firstname(20));
Now our updates write to an index half the size, and selects have a smaller index to search. Both will be faster (unless you make the indexes too short - imagine a book index, instead of giving the full word, only contained the first letter of the word!. You'd spend a lot of time looking up "semaphore" and "saxophone" when you actually wanted "SQL". Don't do the same to MySQL!
The same applies to the original field definitions. In these days of ample disk space, we don't often worry about space. But smaller usually means faster, so defining our surname and firstname fields as CHAR (255) would be a mistake if the biggest firstname is never more than 20 characters! You don't want to cut names off, but remember that you can ALTER the field later if conditions change, and you need to allow for more characters. I also suggest using VARCHAR rather than CHAR (variable length characters rather than fixed length characters), even though many don't recommend this as they are more subject to fragmentation. I overcome this by using OPTIMIZE often.

What character sets are supported for mysql.

Log in to cPanel.

Once the CPanel is open click on mysql databases then click on the phpmy admin. This will open up the manager for mysql. Once here you can see the character sets for mysql.

MySQL supports 70+ collations for 30+ character sets. This section indicates which character sets MySQL supports. There is one subsection for each group of related character sets. For each character set, the allowable collations are listed.

You can always list the available character sets and their default collations with the SHOW CHARACTER SET statement

How do I configure Dreamweaver MX for PHP and MySQL?

Remote Info:
Access: FTP
FTP Host: domainname.com
Host Directory: public_html
(this is the folder that your site loads to)
Login: *********
Password: *********
Passive FTP Box is checked!

Testing Server:
Server Model: PHP/MySQL
Access: FTP
FTP Host: domainname.com
Host Directory: public_html
Login: *********
Password: *********
Passive FTP box is checked!
Url Prefix: http://www.domainname.com

MySQL Connections Dialog Box:
Connection Name: (whatever you name your connection)
MySQL Server: domainname.com, localhost, or 127.0.0.1
User Name: (a user that has access to MySQL)You create these and add them to the database in cpanel
Password: *******
Database: (you have to specify one of your DBs) Again create this in cpanel mysql area also add your ip address or % to your allowed ip access list. in the mysql section of cpanel.

How do I import a database using command line? (SSH)

**SSH access is needed for this process.

Also you will need the file in .sql format, meaning it can not be compressed in a .zip or .tar.gz file.

1) Once you have your sql file you will need to upload it to your server.

2) You will need to create the database if it does not exist.

3) Once this database is created and your .sql file is on the server log into the server through SSH

4) Once in SSH navigate to the directory where your .sql file is.

5) Once there you will want to run this command:

# mysql -uusername -ppassword database_name < file.sql

Note:There is no space between '-u' and the username, or between '-p' and the password
username is the username of the user with rights to the database if you are unsure what the is you can use your username you used to sign into SSH.
Password also follows same guide lines.
Make sure your database name has your username prefix with the _ (underscore) after it and the database name.
This command with ONLY work if you are in the directory where the .sql file is.

6) Execute this command and your database will import.

If you have issues please contact support.

I keep getting CPU Exceeded Errors, What are some things I can do to fix this?

Some knowledge of how indexes work allows you to use them more efficiently. Firstly, note that when you update a table with an index, you have to update the index as well, so there is a performance price to pay. But unless your system runs many more inserts than selects and the inserts need to be quick, and not the selects, this is a price worth paying.
What about if you want to select on more than one criteria? (As you can see, it only makes sense to index those fields you use in the WHERE clause.) The query:
SELECT firstname FROM employee;
makes no use of an index at all. An index on firstname is useless. But,
SELECT firstname FROM employee WHERE surname="Madida";
would benefit from an index on surname.
Let's look at some more complex examples where EXPLAIN can help us improve the query. We want to find all the employees where half their overtime rate is less than $20. Knowing what you do, you correctly decide to add an index on overtime_rate, seeing as that's the column in the where clause.
ALTER TABLE employee ADD INDEX(overtime_rate);
Now let's run the query.
EXPLAIN SELECT firstname FROM employee WHERE overtime_rate/2<20;

+----------+------+---------------+------+---------+------+------+------------+

| table    | type | possible_keys | key  | key_len | ref  | rows | Extra      |

+----------+------+---------------+------+---------+------+------+------------+

| employee | ALL  | NULL          | NULL |    NULL | NULL |    2 | where used |

+----------+------+---------------+------+---------+------+------+------------+

Not good at all! Every single employee record is being read. Why is this? The answer lies in the "overtime_rate/2" part of the query. Every overtime_rate (and hence every record) has to be read in order to divide it by 2. So we should try and leave the indexed field alone, and not perform any calculations on it. How is this possible? This is where your school algebra comes to the rescue! You know that 'x/2 = y' is the same as 'x = y*2'.We can rewrite this query, by seeing if the overtime_rate is less than 20*2. Let's see what happens.
EXPLAIN SELECT firstname FROM employee WHERE overtime_rate<20*2;

+--------+-------+---------------+---------------+---------+------+------+----------+

|table   | type  | possible_keys | key           | key_len | ref  | rows |Extra     |

+--------+-------+---------------+---------------+---------+------+------+----------+

|employee| range | overtime_rate | overtime_rate |       4 | NULL |    1 |where used|

+--------+-------+---------------+---------------+---------+------+------+----------+

Much better! MySQL can perform the 20*2 calculation once, and then search the index for this constant. The principle here is to keep your indexed field standing alone in the comparison, so that MySQL can use it to search, and not have to perform calculations on it.
You may say that I was being unfair, and should have phrased the request as "where the overtime rate is less than 40", but users seem to have a knack of making a request in the worst way possible!

How do I create an empty MySQL Database?

To create the Database, you would go to your cpanel

1. Log into the cpanel
2. Click the icon for MySQL
3. Create a user (make it short best result)
4. Create a DB (make it short best result)
5. Most important, Click Add User to DB button (authentication will not work if you
do not click this button)

After doing all these steps, you can create DB either phpmyadmin or use an online php script or perl script. Some examples of free softwares that incorporate this into their setup utility is ikonboard forum, phpBB2 forum.

Your username format will be: webusername_dbusername
Your DB format will be: webusername_dbname
Your hostaddress will be: localhost

For example if you have the following variables:

dbusername: admin
dbname: DB1
password: password

You will end up with the following format that you would place
in your connection string

Hostname: localhost
DBName: johndoe_DB1
DBUser: johndoe_admin
Password: password

Notice the password remains the same format.

The connection string can be either of any examples below:

For example, we will use the following Variables:

Hostname: localhost
DBName: johndoe_DB1
DBUser: johndoe_admin
DBPassword: password


Connection Strings
For PERL
$dbh = DBI->connect("DBI:mysql:johndoe_DB1:localhost","johndoe_admin","password");

For PHP

$dbh=mysql_connect ("localhost", "johndoe_admin", "password") or die ('I cannot connect to the database.');
mysql_select_db ("johndoe_DB1");

To create tables and other functions, you can use the PHPMyAdmin located in the MySQL
Link of the cpanel. Simply log into your cpanel, click the MySQL logo, scroll down to
the very bottom of the page and click the name PHPMYADMIN.

For more information on how to work with PHPMyAdmin youc an go to:

http://www.phpmyadmin.net

What version of MySQL are available?

Most hosting companies currently run the MySQL version 4.1.14-standard. They usually upgrade when cPanel upgrades their software.

How do I create a MySQL backup?

There are two ways to go about backing up your MySQL databases.

1)Use the backup utility in the control panel. (Easy way)

2)Manually backup using phpMyAdmin. To do this, follow these steps. (

Step 1: Login to your control panel http://yourdomain.com/cpanel

Step 2: Click on MySQL databases

Step 3: Scroll to the bottom and click on phpMyAdmin hyperlink

Step 4: Click on the dropdown to the left and choose the database you wish to export the backup from.

Step 5: Click on the Export Tab.

Step 6: Click the Select ALL link shown in blue. The check Save as file and click GO.

Step 7: You will be asked to save the .sql file to the hard drive. Click Save.

Step 8: Choose the location to save to and click Save. Your database has now been exported.

I keep getting CPU Exceeded Errors, What are some things I can do to fix this?

You know the scene. The database is just too slow. Queries are queuing up, backlogs growing, users being refused connection. Management is ready to spend millions on "upgrading" to some other system, when the problem is really that MySQL is simply not being used properly. Badly defined or non-existent indexes are one of the primary reasons for poor performance, and fixing these can often lead to phenomenal improvements. Consider an extreme example:

CREATE TABLE employee (

   employee_number char(10) NOT NULL,

   firstname varchar(40),

   surname varchar(40),

   address text,

   tel_no varchar(25),

   salary int(11),

   overtime_rate int(10) NOT NULL

);

To find employee Fred Jone's salary(employee number 101832), you run: SELECT salary FROM employee WHERE employee_number = '101832';
MySQL has no clue where to find this record. It doesn't even know that if it does find one matching, that there will not be another matching one, so it has to look through the entire table, potentially thousands of records, to find Fred's details.
An index is a separate file that is sorted, and contains only the field/s you're interested in sorting on. If you create an index on employee_number, MySQL can find the corresponding record very quickly (Indexes work in very similar ways to an index in a book. Imagine paging through a technical book (or more often, an scrambled pile of notes!) looking for the topic "Optimizing MySQL". An index saves you an immense amount of time!
Before we repair the table structure above, let me tell you about a most important little secret for anyone serious about optimizing their queries: EXPLAIN. EXPLAIN shows (explains!) how your queries are being used. By putting it before a SELECT, you can see whether indexes are being used properly, and what kind of join is being performed...
For example:

EXPLAIN SELECT employee_number,firstname,surname FROM employee WHERE employee_number= '10875';

+----------+------+---------------+------+---------+------+------+------------+

| table    | type | possible_keys | key  | key_len | ref  | rows | Extra      |

+----------+------+---------------+------+---------+------+------+------------+

| employee | ALL  | NULL          | NULL |    NULL | NULL |    2 | where used |

+----------+------+---------------+------+---------+------+------+------------+

So what are all these things?

• table shows us which table the output is about (for when you join many tables in the query)
• type is an important one - it tells us which type of join is being used. From best to worst the types are: system, const, eq_ref, ref, range, index, all
• possible_keys Shows which possible indexes apply to this table
• key And which one is actually used
• key_len give us the length of the key used. The shorter that better.
• ref Tells us which column, or a constant, is used
• rows Number of rows mysql believes it must examine to get the data
• extra Extra info - the bad ones to see here are "using temporary" and "using filesort"

Looks like our query is a shocker, the worst of the worst! There are no possible keys to use, so MySQL has to go through all the records (only 2 in this example, but imagine a really large table).
Now lets add the index we talked about earlier.
If we re-run the EXPLAIN, we get:

+----------+-------+---------------+---------+---------+-------+------+-------+

| table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |

+----------+-------+---------------+---------+---------+-------+------+-------+

| employee | const | PRIMARY       | PRIMARY |      10 | const |    1 |       |

+----------+-------+---------------+---------+---------+-------+------+-------+

The query above is a good one (it almost falls into the category of "couldn't be better"). The type of "join" (not really a join in the case of this simple query) is "const", which means that the table has only one matching row. The primary key is being used to find this particular record, and the number of rows MySQL thinks it needs to examine to find this record is 1. All of which means MySQL could have run this query thousands of times in the time it took you to read this little explanation.

I get permission denied errors when I try to run certain commands in mysql.

There are a few mysql queries that most hosting companies don't allow customers to use with their main account user(or any user for that matter). One of these commands is CREATE database. The only user that can create databases on many hosting companies systems is the mysql root user, and the only way to use its priveleges to create a new database is through the cpanel, under 'Mysql Databases'. Most hosting companies also don't allow either commands for LOAD DATA INFILE/OUTFILE, for security reasons. There are some alternatives to this, if you're trying to import or export a database. Here's two examples:

LOAD DATA INFILE ALTERNATIVE FROM SSH:
$ echo "source databasefile.sql" | mysql -u user -ppassword databasename

LOAD DATA OUTFILE ALTERNATIVE FROM SSH:
$ mysql -u user -ppassword databasename > outputfile.sql

I have a backup of my database that I need to import/transfer to my new account.

To do this you will need the file in .sql format, meaning it can not be compressed in a .zip or .tar.gz file.

1) Once you have your sql file you will need to log into your CPanel

2) In the CPanel you will find an Icon labeled MySQL Databases.

3) At the bottom of the screen in MySQL Databases you will see an option for PhpMyAdmin, click on this

4) Once in PhpMyAdmin you will see on the left side a drop down of your databases, select the database you would like to import to.

5) This will create some tabs at the top of the screen, click on SQL.

6) After clicking on SQL you will see a Text Box area and a file upload option.

7) Click browse on the file upload option and find your .sql file

8) After you have selected the file click GO and it will import your database.

How do I make my existing MySQL database compatible with the version provided?

If your version of MySQL is a later release than ours, you can export the database to make it compatible with ours. To do this you would run the below command from the command prompt on your server.

mysqldump --compatible=mysql40 -udb_USERNAME -pdb_PASSWORD db_name > FILENAME

Note: Replace db_name, db_USERNAME, db_PASSWORD, & FILENAME with the name of the database, the user, the password, and the file name, respectively.

What are the Basic Connection Settings for MySQL?

There are two different ways to connect to a MySQL database. Locally (from a script on the server, to the server), and Remotely (from a remote computer, using Shell, ODBC, or Navicat)

Locally, the settings are:
Hostname: localhost
Username: username_dbuser
Password: dbpassword
db_name: username_dbname
db_table: dbname_table

Remotely, the settings are the same, but you must use yourdomain.com in place of 'localhost'. As well, your IP address must be whitelisted, to get through hosting firewall. You can either call your hosting support with your domain name and IP address to be whitelisted.

I would like to dump the Table Structure for my MySQL Database, but none of the data.

The command line option from SSH is:

mysql -d -p database_name > file.sql

Where "database_name" is the name of your database, and "file.sql" is the name of the file you would like to write the table structure to. It will prompt you for a password (-p) and this is required. Normally mysqldump gives you the entire database, with the "-d" option, you tell it to not include the data, but just the table structure.

I can't log into MySQL from a remote computer using a MySQL admin tool(phpMyAdmin, Navicat, MySQLFront, MySQL-Admin, etc).

Our firewalls automatically block out any requests to connect to a MySQL database from a remote computer(anything other than localhost), unless your ip address is white listed on many hosting companies firewall.
How to add yourself to the whitelist:
1. Log into your control panel
2. Click on the 'Remote MySQL' icon under the databases category
3. Type your IP address (or a wild card: %) in the box
4. Click on 'Add Host'
5. Read the instructions written below the box
6. Follow that link to 'Customer Profile' to enter your IP there
7. That's It!

*Note: The 'Class C' Address means you will whitelist all ip adresses under the first three octects of the ip address(ie: everything under 192.168.0.*).

*Note: You can only add the ip address of the machine that you logged into the control panel with(security precaution).

I can't connect to my database locally or remotely, do I have the correct username and password?

Since most hosting companies run a shared hosting environment, most hosting companies have to uniquely identify your usernames to your account ONLY. They do this by attaching your system user name and an underscore("_") before all the databases and users YOU create.

Example:
I added a new database(or user) and I filled in the name: mydatabase
The Real Database Name: username_mydatabase

This applies to ONLY your database name and database username, you password stays normal.

Things to keep in mind:
- Your username can only be up to 7 characters long. They chop off everything else.
Example: billybob -> username_billybo
- You also need to remember to add you user to your database after the two are created.

Database Server/Host Address-
If your script or program is going to be accessing many hosting companies database locally, meaning it is stored on your account, the server/host address will always be: localhost
Just like that, lowercase and one word.

If you're using remote database management software, the server would either be your domain name: whatever.com, or your ip(if dns/nameservers aren't pointing to us): 192.168.0.1. Also, you will need your ip whitelisted on many hosting companies firewall.

When logging into phpMyAdmin, why do I get a 404 not found error?

This happens if you have logged into the cPanel. In order for phpMyAdmin to work, you must log in from yourdomainname.com/cpanel.

It does not work from behind a firewall, you will need to contact your local admin to disable the firewall or get the port opened (2088, 2082).

How can I connect to our MySQL database from Dream Weaver?

Follow the below steps very carefully.

Log into your account and click on "MySQL Databases" then under
"Users" create a new user and password if you don't already have one you want
to use.
Then add that user to the database you wish to work with through Dreamweaver.

At the bottom, add the "%" symbol to the "Add Host" listing, this is a
wildcard and will allow connection from any computer if they have the correct
user name and password.

Now open Dreamweaver, create a new blank PHP page, then at the top bar click on
"Site" then "New". Leave the "Local Info" as it is, but click on "Remote Info"
instead and change the access type to FTP.

Here are the settings you'll need to fill out:

FTP host = your domain name or IP (the IP can be found in the Control Panel)
Host Directory = public_html (or public_html/addondomain)
login = control panel (cpanel) username
password = control panel (cpanel) password

Next click on "Testing Server" then change the "Server Model" to
"PHP/MySQL" and access to FTP. Use the same account information as above, and
if Dreamweaver asks for a "Remote Info Page", simply use
http://www.yourdomain.com

Now to connect to your account and set up a connection to a database.

Up at the top menu bar in Dreamweaver click on "Window" then "Databases".
This will bring up a menu on the side bar, if it is not already up, where you
can click on the + button and set up a "MySQL Connection". Label it what you
would like in the "Connection Name" and use these settings:

MySQL Server = localhost
Username = The control panel username then "_" and MySQL database username
Example: cpanel_mysqlname
Password = Password to the MySQL database username
Database = Click "Select" and choose your database

Then you can test the connection to see if your settings are correct.

How do I restore a MySQL database?

1. Login to your control panel http://yourdomain.com/cpanel
2. Click on MySQL databases
3. Scroll to the bottom and click on phpMyAdmin hyperlink
4. Click on the Database you want to restore then click on SQL
5. Click the Browse button and select your .sql file
6. Click 'Go'

It may take long to upload your .sql and populate the DB so a broadband
connection would help. This will overwrite the DB that you
currently have up.

How to import and export a MySQL Database

MySQL Tutorial - Import or Export A Database
This tutorial section deals with mysqldump which is a tool to import and export MySQL databases. (this is all done via commandline , you will need to ssh into your account)

It can be used to back up a database or to move database information from one server to another.

1. Export A MySQL Database

This example shows you how to export a database. It is a good idea to export your data often as a backup.

# mysqldump -u username -p password database_name > FILE.sql

Replace username, password and database_name with your MySQL username, password and database name.

File FILE.sql now holds a backup of your database, download it to your computer.

2. Import A MySQL Database

Here, we import a database. Use this to restore data from a backup or to import from another MySQL server.

Start by uploading the FILE.sql file to the server where you will be running this command.

# mysql -u username -p password database_name < FILE.sql


This powerful, easy to use command has many uses. Let's say you wanted to switch web hosting providers.

Simply export your data on the old provider's server and import it on your account with the new host.

How do I connect to my Database using MySQLfront?

If you wish to administer your database from your own PC, via a program like MySQLFront, you will need to add an "access host" via the MySQL Database Icon in the cpanel.

For example, if your Internet Service Provider is AOL, enter "%.aol.com". You can also use this to grant access to a database from another server, for example if you have a website on a different server which needs to access this database. To do this, instead of entering %.your-isp.com, enter the IP of the server which needs access, like this: "60.15.10.5".

Also make sure your username matches the username created in the MySQL database section of CPanel, and make sure you add the user to the database you want to access.

Lastly, your IP will need to be entered into many hosting companies whitelist to gain access through the Firewall.

How do I create a MySQL database?

1. Login to your control panel http://yourdomain.com/cpanel
2. Click on MySQL databases
3. Add database
4. Add database users
5. Add users to new database, with appropriate permissions.

Problem with old host and new host, my database still is looking at the old host.

When importing an old database from another host you must go through a multi-step process or it could possibly link back to the old host until it is directed to change by a fast mod available at the PHP Knowledgebase:

Here is the link.
http://www.phpbb.com/kb/article.php?article_id=59

I keep getting CPU Exceeded Errors, What are some things I can do to fix this?

Most systems need to be highly optimized for selects - take a news site which performs millions of queries per day, but where the data arrives in large batches of text files. So for parts of the day, inserts need to be optimal, without noticeably affecting the millions trying to access the data...
Assuming a nicely formatted '|' delimited text file that we want to insert into the table above, take this piece of PHP code:

if (!($fp = fopen("datafile.txt","r"))) {               // open the file for reading

  print "nUnable to open datafile.txt for writing";    // display error

  exit();                                       // end the running of the program

}

while (!feof ($fp)) {                   // loop through the file line by line

  $sline = fgets($fp, 4096);            // put the data into the variable $sline

  $sline = chop($sline);                        // remove the newline

  list($eno,$fname,$sname,$telno,$salary) = split("|",$code);

                        // split the line on "|", populating the ind. variables

  $db->query("insert into employee(employee_number,firstname,surname,

tel_no, salary

   values($eno,'$fname','$sname','$tel_no', $salary)");

}                                               // end while loop

This would work, but would be very slow. The index buffer would be flushed after every insert. Until recently, MyISAM tables (The MySQL default) did not allow data to be inserted at the same time as being read. The new format does, but only if there are no deleted records present (highly unlikely in a heavily used system). So the entire table is locked for the duration of each insert. Fine on a low volume site, but when you're getting hundreds or thousands of queries per second, you'll soon notice the backlog!
There's a solution however - the best way to insert the data is to use MySQL's "LOAD DATA INFILE". This is much faster (20 times according to MySQL), and the only way some systems I've seen are still hanging in there!
The syntax is simple, and the code becomes a lot simpler too:
$db->query("LOAD DATA INFILE 'datafile.txt' INTO TABLE employee (employee_number,firstname,surname,tel_no,salary) FIELDS TERMINATED BY '|'");
LOAD DATA INFILE has defaults of:
FIELDS TERMINATED BY 't' ENCLOSED BY '' ESCAPED BY ''
if you don't specify any of these clauses. And, just as with an ordinary insert, you need to specify a field list if the order of the fields is different, or, as in the example above, you're not inserting data for every field. Always specifying a field list is good practice for all queries anyway - if someone adds a field to the table at a later stage, you don't want to go back and have to fix all your previous INSERT and SELECT * statements.
If you can't get this to work properly, have a look at the format of your text file - every problem I've seen with LOAD DATA has been because of a corrupted text file. Every field in every row must be delimited correctly!
You may not always be inserting from a text file - perhaps your application needs to do many unrelated inserts continually. There are ways to make sure the mass of users selecting are not badly affected... The first is to use INSERT LOW PRIORITY. This waits until there are no more reads waiting to happen, waiting for the gap, and not pushing in as it were. Of course, if your database is a rush hour special, there may never be a gap, and the client performing the INSERT LOW PRIORITY may start to grow cobwebs! An alternative here is INSERT DELAYED. The client is immediately freed, and the insert put into a queue (with all the other INSERT DELAYED's still waiting for the queue to end). This means that there can be no meaningful information passed back to the client, (such as the auto_increment value), as the INSERT has not been processed when the client is freed. Also, be aware that a catastrophe such as an unexpected power failure here will result in the queued INSERT's being lost. For neither of these methods do you have any idea when the data will be inserted, if at all, so I suggest you use with caution.

Conclusion

It's not only getting the data in that needs to be quick - sometimes you need to get it out quickly too. (Say you've accidentally loaded yesterday's classified ads, for example). Don't do a:
DELETE FROM classifieds;
Rather, use:
TRUNCATE TABLE classifieds;
The difference here is that DELETE drops records one by one, and that can be 1 million one by one's too slow! Note that this does not apply before version 4.0 of MySQL. At time of writing, most of you will still be using 3.x versions (if you do a DELETE FROM tablename on a non-empty table, and get 0 records back as a result, you're running an earlier version. To fix this problem, MySQL made DELETE remove records one by one so as to return the number of records deleted, but TRUNCATE still did the quick delete. Also, earlier versions than 3.23.33 used TRUNCATE tablename, not TRUNCATE TABLE tablename)
This has only been a brief introduction to optimizing queries and indexes. Once you've mastered these tips, you'll want to look at your more complex joins that just never seem to perform quickly. The MySQL site has lots of useful information, and there are a few good books out there as well. But don't forget EXPLAIN! Often the best way is to try and rewrite the query in as many different ways as possible, and see which one runs more efficiently. You can learn a lot by trying to figure out why one alternative ran faster than the other. Good luck!
resource list:
www.mysql.com - complete documentation for MySQL