MongoDB

Why NoSQL? http://www.couchbase.com/why-nosql/nosql-database

MongoDB Schema Design: How to Think Non-Relational

When to use NoSQL?

Interactive web and mobile application.

Hibernate with MongoDB?

You can't easily do this. The point of Hibernate is to map Java Objects to a relational database. Although Hibernate abstracts a lot of details away you still need to understand how relational databases work with things such as foreign and primary keys, and the performance implications of queries you run. MongoDB requires an entire different way of designing your database focusing on objects instead of columns and tables. while you may be able to create a Hibernate dialect for MongoDB creating a design that would work on both a relational database and a NoSql database will give you a design that works poorly on both.

http://blog.jobbole.com/31329/

Best Programming Language 最佳编程语言

① Python: 28%、② C++: 19%、③ C: 18.9%、④ Java: 8.9%、⑤ Perl: 8.2%

Other: 6.1%、Ruby: 4%、JavaScript: 3.1%、C#: 2.4%、Erlang: .7%、Haskell: .7%

Best Scripting Language 最佳脚本语言

① Python: 36.3%、② Bash: 24.4%、③ Perl: 14.2%、④ PHP: 13.8%、⑤ Ruby: 4.8%、⑥ Awk: 2.2%、⑦ Other: 2.2%、⑧ Lua: 1.6%、⑨ Groovy: 0.4%

Best Java App Server

tomcat: 60.1%、jboss: 21.9%、Other: 10.9%*、glassfish: 7.1%

Best Content Management System 最佳内容管理系统

WordPress: 34.9%、Drupal: 27.8%、Joomla!: 15.3%、Other: 8.1%……

Database Glossary in Database Design

A database glossary at your disposal.

The words used in database design MAY be confusing, at times: That is why I provide this database glossary for you: If you don't find the term you are uncertain about here, use my Contact page for a message, and I will include the term here.

This is a "live" glossary: Whenever there exists relevant information about a term on some other page around here, the term appears as a direct link to that page. Some of the pages may be articles, short or long, others may lead you to a free eBook, and others again may offer you a payable eBook, if the term is a complex and/or lengthy one.

If I do not have a relevant page for a given term, you can blame it on me: Not writing fast enough. I will, however, put the term in bold and give a short explanation.

Alternate (candidate) Key

A column or combination of columns, other than the primary key column(s), which may be used to uniquely identify a row in a table. The primary key is chosen from one of the candidate keys. As you can read from the article behind this term; if you have many candidate keys, you have a problem of selecting the best fit.

Attribute

An attribute is a part of the description of the entity. The entity itself is described by one or more attributes; together, they describe all things of importance about the entity. Example: Typhical attributes for a customer would be name, address, telephone, etc.

Business Rules

Specific business-related information that is associated with database objects. The information can be business restrictions (allowable values), facts, or calculation rules for given business situations, e.g VAT shall be added to all products. Business rules should be applied in the completed database, either as triggers/stored procedures, or (absolutely worst case) implemented in the application code. However, if you do that, you will make your business dependent on a specific application...

Candidate keys

Each entity should have at least one candidate (that is: unique) key defined. This is in order to being able to identify each row in the table at hand. Read the above link about candidate keys: It will teach you the importance of the issue.

Cardinality

The cardinality of an entity indicates the number of instances (zero or many) of an entity.

Many describe the cardinality through relationships: One-to-one, One-to-many, Many-to-many. In addition, each end of a relationship may be optional or mandatory. The cardinality term is not restricted to relationships, however: We also try to distinguish between low and high cardinality on attributes: an attribute for GENDER will have a low cardinality (2); Male or Female. This may inflict on how we design indexes in the database.

Another meaning of cardinality is to describe how many rows you have in a specific table.

Column

The physical equivalence to an attribute: In a database table, the table consists of one or more columns; together they describe all we want to know about each occurrence (record) in the table.

Data Definition Language (DDL)

The language used to define objects in a database: CREATE TABLE, CREATE INDEX, and so on. DDL provides the semantics for administering all the physical objects in your database. It is based on a given standard, but may deviate some from vendor to vendor. All of the objects created together, form the 'schema' of a given application.

Data Manipulation Language (DML)

The language used to manipulate objects in a relational database: We only have four statements in DML: INSERT, SELECT, UPDATE and DELETE. DML provides the semantics for manipulating one or more occurrences (record) in a table in our database. It is based on a given standard, but may deviate some from vendor to vendor, especially through ‘extensions’ found valuable by the given vendor.

Data model

A data model is not the model (conceptual/logical/physical) of your future or existing database: It is a generic model which you base your analysis and design upon. The relational model is one such data model. To my knowledge, there are no other data models that reflect the reality as we have to deal with it. I can not provide you with an OO-oriented data model, or an XML data model; such complete models do not exist, although some vendors are trying to make you think otherwise.

Database model

A Database model is the logical conversion of an Entity Relationship model. While an ER model reflects the business case, the Database model visualizes the logical model, which in turn is the basis for the physical implementation in the form of database tables, indexes and other mechanisms in the database, necessary to construct the database in question.

Database Normalization

Database Normalization is the process of analyzing your database model to ensure that information is stored only in one place in the database, and that there are no derivates of the information within the database. A perhaps more understandable explanation of the 5 Normal Forms can be found in my eBook on Database Normalization

Datatype

Identifies the kind of information that an Attribute/column in an entity/table on a specific database platform represents. These are actual physical representations and are dependent on the actual RDBMS’s. The datatype should be given by the domain definition for that attribute/column.

Domain

A standardized definition which applies to many attributes/columns.

For example, the domain MONEY may be specified as NUMBER, 15 digits long, with 2 decimals, not allowed to have a value of 0.00, and so on. Applying domain definitions to every attribute/column, eases the implementation of changes, and assures that the same kind of information is treated equally throughout the system.

Entity

Any kind of information of importance to the business: Customers, Orders, Products, or whatever information we need in order to perform a certain task.Used in the building of the conseptial (business) model

Foreign Key

Column or combination of columns in a table, whose values are related to a primary key in another table.

Index

An index is a physical mechanism applied to one (or a combination of) column(s). The purpose of the index is for the database system to use the index as a look-up mechanism instead of reading the whole row. Indexes are a prime resource for optimalization (and thereby increasing speed) of searches in the database.

Join Relationship

A join relationship is a collection of information from two or more tables. The join is performed by relating columns which are foreign key columns in one table with equivalent columns which are primary key columns in the other table.

MetaData

'Data about Data'. This is the documentation stored in the database repository, and which holds information about your database objects. In Oracle, for example, the table USER_TABLES holds vital information about your tables.

NULL values

The concept of NULL, which means "unknown value", (not the digit zero) is not a part of the relational model, but has been introduced by the different vendors. It can create unexpected problems when accessing the database (read the linked article).

Occurrence

Each row in a database table. If you have 100 customers in your CUSTOMERS table, the table has 100 occurrences.

Open DataBase Connectivity (ODBC)

A general interface for communication with different vendor-specific Relational Database Systems.

Primary Key

A column (or combination of columns) whose value(s) uniquely identify a row in a table. This has been a central consept in Relational Theory, and crucial to both identification and performance. Lately, the concept has been questioned, saying that a table should have one or more candidate keys only; however, for all practical reasons, a table should never be created before its (unique) primary key has been determined.

Ref cursor

A ref cursor is a pointing feature for use in a procedural language. The use of a ref cursor, explicit, gives you the power to navigate a result set from a SELECT statement one record at a time. It is a basic concept in Oracle, but can also be used from other procedural languages such as Java.

Referential Integrity

Referential integrity deals with the governing of data consistency. We mostly think of it as keeping the relations between tables valid; that is, an order may not have a customer id that does not exist; a transaction can not be posted for an illegal (non-existent) account.

Relationship

A relationship is an association between two entities. For example, the relationship between the ORDERS and CUSTOMERS tables will normally be that an order is placed by ONE AND ONLY ONE customer, while a given customer MAY HAVE placed ONE OR MORE orders. This will create a link between orders and customers, and thereby generating a business rule inside the database to enforce that relationship.

Structured Query Language (SQL)

SQL is the industry standard language for designing and communicating with relational databases.

Stored Function

A stored function is SQL (and procedural code, in most cases), placed in the database itself. It masks the business logic from the programmer. In addition, stored functions return only one value to the calling program, and can be used as part of DML statements and within calculations and conditional statements as a parameter.

Stored Procedure

A stored procedure is SQL (and procedural code, in most cases), placed in the database itself. It masks the business logic from the programmer. In addition, stored procedures represent a powerful tool to let all programmers have a generic interface to different access mechanisms to each table in the database.

Table

The physical implementation of an entity. This is where the actual data is stored as customers, orders, products, or whatever. Each table consists of one or more columns.

Trigger

A trigger is a stored procedure assigned to a given table. It ‘fires’ whenever you do an operation on that table (BEFORE/AFTER INSERT/UPDATE/DELETE etc.) Triggers are powerful, performance-enhancing mechanisms in the database.

Unique Identifier

The equivalent of a primary key. It may also be a candidate for creating unique indexes in addition to the primary key.

View

An imaginary table: A view may be constructed to give the user/programmer access to a limited resultset from one or more tables. It is often used for security reasons; restricting access through views.
However; it may also be a signal of insufficient design: Making a view to solve a problem with a query which is difficult to do in a single SELECT.

http://www.databasedesign-resource.com/database-glossary.html

Query Optimization / SQL Tuning or SQL Optimization

Steps of Query Optimization

For any production database, SQL query performance becomes an issue sooner or later. Having long-running queries not only consumes system resources that makes the server and application run slowly, but also may lead to table locking and data corruption issues. So, query optimization becomes an important task.

First, we offer some guiding principles for query optimization:

1. Understand how your database is executing your query

Nowadays all databases have their own query optimizer, and offers a way for users to understand how a query is executed. For example, which index from which table is being used to execute the query? The first step to query optimization is understanding what the database is doing. Different databases have different commands for this. For example, in MySQL, one can use "EXPLAIN [SQL Query]" keyword to see the query plan. In Oracle, one can use "EXPLAIN PLAN FOR [SQL Query]" to see the query plan.

2. Retrieve as little data as possible

The more data returned from the query, the more resources the database needs to expand to process and store these data. So for example, if you only need to retrieve one column from a table, do not use 'SELECT *'.

3. Store intermediate results

Sometimes logic for a query can be quite complex. Often, it is possible to achieve the desired result through the use of subqueries, inline views, and UNION-type statements. For those cases, the intermediate results are not stored in the database, but are immediately used within the query. This can lead to performance issues, especially when the intermediate results have a large number of rows.

The way to increase query performance in those cases is to store the intermediate results in a temporary table, and break up the initial SQL statement into several SQL statements. In many cases, you can even build an index on the temporary table to speed up the query performance even more. Granted, this adds a little complexity in query management (i.e., the need to manage temporary tables), but the speedup in query performance is often worth the trouble.

Below are several specific query optimization strategies.

• Use Index
  Using an index is the first strategy one should use to speed up a query. In fact, this strategy is so important that index optimization is also discussed.


• Aggregate Table
  Pre-populating tables at higher levels so less amount of data need to be parsed.


• Vertical Partitioning
  Partition the table by columns. This strategy decreases the amount of data a SQL query needs to process.


• Horizontal Partitioning
  Partition the table by data value, most often time. This strategy decreases the amount of data a SQL query needs to process.


• Denormalization
  The process of denormalization combines multiple tables into a single table. This speeds up query performance because fewer table joins are needed.


• Server Tuning
  Each server has its own parameters, and often tuning server parameters so that it can fully take advantage of the hardware resources can significantly speed up query performance.

http://www.1keydata.com/datawarehousing/query-optimization.html

Optimizing MySQL: Queries and Indexes

http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm

By Ian Gilfillan

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 MySQL 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.

A MySQL 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!

Related Articles

• Optimizing the mysqld variables
• Optimizing MySQL: Hardware and the Mysqld Variables

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?

EX:

EXPLAIN SELECT `user_id` , `mag_id` 
FROM `order` , `order_magazineinfo` 
WHERE `order`.`order_id` = `order_magazineinfo`.`order_id`

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	order	index	PRIMARY	user_id	4	NULL	2	Using index
1	SIMPLE	order_magazineinfo	ref	order_id	order_id	4	aietimedb.order.order_id	1	Using index

• table shows us which table the output is about (for when you join many tables in the query)
• type (Join type) is an important one - it tells us which type of join is being used. From best to worst the types are: 

1. system
2. const
3. eq_ref
4. ref
5. range
6. index
7. 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.


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!

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 OPTIMIZEoften.


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!