The WHERE clause, if given, indicates the
condition or conditions that rows must satisfy to be selected.
where_condition is an expression
that evaluates to true for each row to be selected. The
statement selects all rows if there is no
WHERE clause.

SELECT can also be used to retrieve
rows computed without reference to any table.

For example:

mysql> SELECT 1 + 1;
-> 2

You are permitted to specify DUAL as a dummy
table name in situations where no tables are referenced:

mysql> SELECT 1 + 1 FROM DUAL;
-> 2

DUAL is purely for the convenience of people
who require that all SELECT
statements should have FROM and possibly other
clauses. MySQL may ignore the clauses. MySQL does not require
FROM DUAL if no tables are referenced.

In general, clauses used must be given in exactly the order shown
in the syntax description. For example, a
HAVING clause must come after any
GROUP BY clause and before any ORDER
BY clause. The exception is that the
INTO clause can appear either as shown in the
syntax description or immediately following the
select_expr list. For more information
about INTO, see Section 13.2.8.1, “SELECT ... INTO Syntax”.

The list of select_expr terms comprises
the select list that indicates which columns to retrieve. Terms
specify a column or expression or can use
*-shorthand:

A select list consisting only of a single unqualified
* can be used as shorthand to select all
columns from all tables:

SELECT * FROM t1 INNER JOIN t2 ...

tbl_name.* can
be used as a qualified shorthand to select all columns from
the named table:

SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...

Use of an unqualified * with other items in
the select list may produce a parse error. To avoid this
problem, use a qualified
tbl_name.*
reference

SELECT AVG(score), t1.* FROM t1 ...

The following list provides additional information about other
SELECT clauses:

A select_expr can be given an alias
using AS
alias_name. The alias is
used as the expression's column name and can be used in
GROUP BY, ORDER BY, or
HAVING clauses. For example:

SELECT CONCAT(last_name,', ',first_name) AS full_name
FROM mytable ORDER BY full_name;

The AS keyword is optional when aliasing a
select_expr with an identifier. The
preceding example could have been written like this:

SELECT CONCAT(last_name,', ',first_name) full_name
FROM mytable ORDER BY full_name;

However, because the AS is optional, a
subtle problem can occur if you forget the comma between two
select_expr expressions: MySQL
interprets the second as an alias name. For example, in the
following statement, columnb is treated as
an alias name:

SELECT columna columnb FROM mytable;

For this reason, it is good practice to be in the habit of
using AS explicitly when specifying column
aliases.

The FROM
table_references clause
indicates the table or tables from which to retrieve rows. If
you name more than one table, you are performing a join. For
information on join syntax, see Section 13.2.8.2, “JOIN Syntax”. For
each table specified, you can optionally specify an alias.

tbl_name [[AS] alias] [index_hint]

The use of index hints provides the optimizer with information
about how to choose indexes during query processing. For a
description of the syntax for specifying these hints, see
Section 8.9.3, “Index Hints”.

You can refer to a table within the default database as
tbl_name, or as
db_name.tbl_name
to specify a database explicitly. You can refer to a column as
col_name,
tbl_name.col_name,
or
db_name.tbl_name.col_name.
You need not specify a tbl_name or
db_name.tbl_name
prefix for a column reference unless the reference would be
ambiguous. See Section 9.2.1, “Identifier Qualifiers”, for
examples of ambiguity that require the more explicit column
reference forms.

A table reference can be aliased using
tbl_name AS
alias_name or
tbl_name alias_name:

Columns selected for output can be referred to in
ORDER BY and GROUP BY
clauses using column names, column aliases, or column
positions. Column positions are integers and begin with 1:

SELECT college, region, seed FROM tournament
ORDER BY region, seed;
SELECT college, region AS r, seed AS s FROM tournament
ORDER BY r, s;
SELECT college, region, seed FROM tournament
ORDER BY 2, 3;

To sort in reverse order, add the DESC
(descending) keyword to the name of the column in the
ORDER BY clause that you are sorting by.
The default is ascending order; this can be specified
explicitly using the ASC keyword.

If ORDER BY occurs within a subquery and
also is applied in the outer query, the outermost
ORDER BY takes precedence. For example,
results for the following statement are sorted in descending
order, not ascending order:

(SELECT ... ORDER BY a) ORDER BY a DESC;

Use of column positions is deprecated because the syntax has
been removed from the SQL standard.

If you use GROUP BY, output rows are sorted
according to the GROUP BY columns as if you
had an ORDER BY for the same columns. To
avoid the overhead of sorting that GROUP BY
produces, add ORDER BY NULL:

SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;

MySQL extends the GROUP BY clause so that
you can also specify ASC and
DESC after columns named in the clause:

The HAVING clause is applied nearly last,
just before items are sent to the client, with no
optimization. (LIMIT is applied after
HAVING.)

The SQL standard requires that HAVING must
reference only columns in the GROUP BY
clause or columns used in aggregate functions. However, MySQL
supports an extension to this behavior, and permits
HAVING to refer to columns in the
SELECT list and columns in
outer subqueries as well.

If the HAVING clause refers to a column
that is ambiguous, a warning occurs. In the following
statement, col2 is ambiguous because it is
used as both an alias and a column name:

SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

Preference is given to standard SQL behavior, so if a
HAVING column name is used both in
GROUP BY and as an aliased column in the
output column list, preference is given to the column in the
GROUP BY column.

Do not use HAVING for items that should be
in the WHERE clause. For example, do not
write the following:

SELECT col_name FROM tbl_name HAVING col_name > 0;

Write this instead:

SELECT col_name FROM tbl_name WHERE col_name > 0;

The HAVING clause can refer to aggregate
functions, which the WHERE clause cannot:

SELECT user, MAX(salary) FROM users
GROUP BY user HAVING MAX(salary) > 10;

(This did not work in some older versions of MySQL.)

MySQL permits duplicate column names. That is, there can be
more than one select_expr with the
same name. This is an extension to standard SQL. Because MySQL
also permits GROUP BY and
HAVING to refer to
select_expr values, this can result
in an ambiguity:

SELECT 12 AS a, a FROM t GROUP BY a;

In that statement, both columns have the name
a. To ensure that the correct column is
used for grouping, use different names for each
select_expr.

MySQL resolves unqualified column or alias references in
ORDER BY clauses by searching in the
select_expr values, then in the
columns of the tables in the FROM clause.
For GROUP BY or HAVING
clauses, it searches the FROM clause before
searching in the select_expr
values. (For GROUP BY and
HAVING, this differs from the pre-MySQL 5.0
behavior that used the same rules as for ORDER
BY.)

The LIMIT clause can be used to constrain
the number of rows returned by the
SELECT statement.
LIMIT takes one or two numeric arguments,
which must both be nonnegative integer constants (except when
using prepared statements).

With two arguments, the first argument specifies the offset of
the first row to return, and the second specifies the maximum
number of rows to return. The offset of the initial row is 0
(not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

To retrieve all rows from a certain offset up to the end of
the result set, you can use some large number for the second
parameter. This statement retrieves all rows from the 96th row
to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

With one argument, the value specifies the number of rows to
return from the beginning of the result set:

SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

In other words, LIMIT
row_count is equivalent
to LIMIT 0,
row_count.

For prepared statements, you can use placeholders. The
following statements will return one row from the
tbl table:

For compatibility with PostgreSQL, MySQL also supports the
LIMIT row_count OFFSET
offset syntax.

If LIMIT occurs within a subquery and also
is applied in the outer query, the outermost
LIMIT takes precedence. For example, the
following statement produces two rows, not one:

(SELECT ... LIMIT 1) LIMIT 2;

A PROCEDURE clause names a procedure that
should process the data in the result set. For an example, see
Section 8.4.2.4, “Using PROCEDURE ANALYSE”, which describes
ANALYSE, a procedure that can be used to
obtain suggestions for optimal column data types that may help
reduce table sizes.

Following the SELECT keyword, you
can use a number of options that affect the operation of the
statement. HIGH_PRIORITY,
STRAIGHT_JOIN, and options beginning with
SQL_ are MySQL extensions to standard SQL.

The ALL and DISTINCT
options specify whether duplicate rows should be returned.
ALL (the default) specifies that all
matching rows should be returned, including duplicates.
DISTINCT specifies removal of duplicate
rows from the result set. It is an error to specify both
options. DISTINCTROW is a synonym for
DISTINCT.

HIGH_PRIORITY gives the
SELECT higher priority than a
statement that updates a table. You should use this only for
queries that are very fast and must be done at once. A
SELECT HIGH_PRIORITY query that is issued
while the table is locked for reading runs even if there is an
update statement waiting for the table to be free. This
affects only storage engines that use only table-level locking
(such as MyISAM, MEMORY,
and MERGE).

HIGH_PRIORITY cannot be used with
SELECT statements that are part
of a UNION.

STRAIGHT_JOIN forces the optimizer to join
the tables in the order in which they are listed in the
FROM clause. You can use this to speed up a
query if the optimizer joins the tables in nonoptimal order.
STRAIGHT_JOIN also can be used in the
table_references list. See
Section 13.2.8.2, “JOIN Syntax”.

STRAIGHT_JOIN does not apply to any table
that the optimizer treats as a
const or
system table. Such a table
produces a single row, is read during the optimization phase
of query execution, and references to its columns are replaced
with the appropriate column values before query execution
proceeds. These tables will appear first in the query plan
displayed by EXPLAIN. See
Section 8.8.1, “Optimizing Queries with EXPLAIN”. This exception may not apply
to const or
system tables that are used
on the NULL-complemented side of an outer
join (that is, the right-side table of a LEFT
JOIN or the left-side table of a RIGHT
JOIN.

SQL_BIG_RESULT or
SQL_SMALL_RESULT can be used with
GROUP BY or DISTINCT to
tell the optimizer that the result set has many rows or is
small, respectively. For SQL_BIG_RESULT,
MySQL directly uses disk-based temporary tables if needed, and
prefers sorting to using a temporary table with a key on the
GROUP BY elements. For
SQL_SMALL_RESULT, MySQL uses fast temporary
tables to store the resulting table instead of using sorting.
This should not normally be needed.

SQL_BUFFER_RESULT forces the result to be
put into a temporary table. This helps MySQL free the table
locks early and helps in cases where it takes a long time to
send the result set to the client. This option can be used
only for top-level SELECT
statements, not for subqueries or following
UNION.

SQL_CALC_FOUND_ROWS tells MySQL to
calculate how many rows there would be in the result set,
disregarding any LIMIT clause. The number
of rows can then be retrieved with SELECT
FOUND_ROWS(). See
Section 12.14, “Information Functions”.

The SQL_CACHE and
SQL_NO_CACHE options affect caching of
query results in the query cache (see
Section 8.10.3, “The MySQL Query Cache”). SQL_CACHE
tells MySQL to store the result in the query cache if it is
cacheable and the value of the
query_cache_type system
variable is 2 or DEMAND.
With SQL_NO_CACHE, the server does not use
the query cache. It neither checks the query cache to see
whether the result is already cached, nor does it cache the
query result. (Due to a limitation in the parser, a space
character must precede and follow the
SQL_NO_CACHE keyword; a nonspace such as a
newline causes the server to check the query cache to see
whether the result is already cached.)

For a query that uses UNION,
subqueries, or views, the following rules apply:

For a cacheable query, SQL_CACHE
applies if it appears in the first
SELECT of the query, or in
the first SELECT of a view
referred to by the query.

A SELECT from a partitioned table using a
storage engine such as MyISAM that
employs table-level locks locks all partitions of the table. This
does not occur with tables using storage engines such as
InnoDB that employ row-level locking.
This issue is resolved in MySQL 5.6. See
Section 18.5.4, “Partitioning and Table-Level Locking”, for more
information.

when selecting a single random row you have to use a query like this: SELECT ... FROM my_table ORDER BY RAND() LIMIT 1. as explain shows, mysql optimizes this VERY badly (or may be better said, doens't optimize it at all): it uses an temporary table and an extra filesort. couldn't this be optimized?! if not, may be add a syntax like SELECT RANDOM_ROW .... FROM my_table ...

Sometimes it is nice to use the SELECT query options like SQL_CALC_FOUND_ROWS or SQL_CACHE, but to maintain compatibility across different databases or even older versions of MySQL which do not support those options, it is possible to enclose them in a comment block, e.g.:

SELECT /*! 40000 SQL_CALC_FOUND_ROWS */ foo,bar FROM some_table;

The /* construct will stop DBMS's other than MySQL from parsing the comment contents, while /*! will tell ALL MySQL versions to parse the "comment" (which is actually a non-comment to MySQL). The /*!40000 construct will tell MySQL servers starting from 4.0.0 (which is the first version to support SQL_CALC_FOUND_ROWS) to parse the comment, while earlier versions will ignore it.

In a database with personal information (name, surname, etc..) with an auto_increment index I wanted to retrieve all the entries with same name and surname field (duplicate names), which by accident were inserted to the base.

I used this syntax

SELECT name,surname,COUNT(name) AS cnt_n, COUNT(surname) AS cnt_s FROM the_table GROUP BY name HAVING cnt_n>1 AND cnt_s>1;

I hope this might be of help to anyone that wants to do some extended maintenance on the database

Will allow you to sort by categories listed in a seperate table IF the category column in this primary table contains ID values from your ID column in your second reference table.

So your first "authors" table looks like:

id name category1 Henry Miller 23 June Day 13 Thomas Wolf 2

and your second reference table looks like:

id category1 Modern2 Classics

Now when the order of categories is changed in the second table the order of categories will be reflected in the primary table.

Then just select the categories from the reference table and putthe list into a numbered array. Then in your script when you runacross a category number from the first recordset just reference the value from the index in the second array to obtain the value.In php in the above example it might look like:

reply to Fahed Bizzari's post, based on Havilland-Fortesque-Smedley's comment (above) the equivalent of select * while doing DISTINCT is:

select *, count(FIELD) from TABLE group by FIELD having count(FIELD)=1 into outfile 'foobar.txt';

then you can check the output. note that there are twice as many rows as records, because each unique row is followed by its count (in this case count=1). so just toss the .txt file into something and sort on the field containing the count and throw out all the rows =1. this is the same result as a select * distinct FIELD (as far as I can tell).

In regards to:_______________________________________________******************************************I found a nifty way of influencing the ORDER of rows returned by a query that helps in displaying a list with frequently accessed items at the top.

An example is a name/address form where the country is a selectable list. If most of your users are from the UK and US you may want to do something like:

_______________________________________________******************************************If found that if you also add in another 'iso_code' column in the order by statment after the first one containing the IN() statment, it will sort the remaining records:SELECT * FROM countries ORDER by iso_code IN ('UK', 'US') desc, iso_code

This will add the text headers Fiscal Year, Location and Sales to your fields. Only caveat is with an ORDER BY statement, if you don't want your headers sorted along with your data you need to enclose it in parenthesis:

As a newbie to MySQL and to dealing with BLOBs, I had a difficult time trying to determine how to extract a BLOB field from the database back to a file. It turns out to be quite simple by doing the following SQL:

In response to Heywood's tip about adding column headers to OUTFILEs...

Make sure that the format of the columns that match up with your headers doesn't limit the display of the headers. For instance, I was using the UNION tip to add a header to a column defined as char(2) (for storing a two-letter state code). The resulting CSV file only displayed the first two letters of my column header. The fix is simple, just use CAST() on the column in the second SELECT to convert it to the appropriate type. In my case, doing something like this:

If you want to use ORDER BY before GROUP BY, the only way I've found to achieve it is with a subquery.

For example, if you want to get a list of users from a table UserActions sorted according to the most recent action (based on a field called Time) the query would be:

SELECT * FROM (SELECT * FROM UserActions ORDER BY Time DESC) AS Actions GROUP BY UserID ORDER BY Time DESC;

Without the subquery, the group is performed first, and so the first record that appears in the database (which is not necessarily in the order you want) will be used to determine the sort order. This caused me huge problems as my data was in a jumbled order within the table.

--Edit--This same result can be achieved with the use of MAX(Time), so the query would be:

SELECT *, MAX(Time) AS LatestAction GROUP BY UserID ORDER BY LatestAction DESC;

As far as I can see, the subquery model still holds up if you need more complex sorting before performing the GROUP.

Be careful about the "SELECT...INTO OUTFILE" options. They are similar to, but not exactly the same as, the mysqldump options.

Two things:

1) The options in mysqldump can be in any order, because they are true command-line options (that is, they are conceptually used together, but syntactically separate on the mysqldump command line). The options in the SELECT...INTO OUTFILE need to be in the exact order as specified in the documentation above.

2) The options MUST have dashes between the words (e.g., fields-enclosed-by) when use as options with the mysqldump utility, but MUST NOT have dashes when used as options with the SELECT...INTO OUTFILE. This may not be clear in the documentation above.

If you want to keep field names, consider using mysqldump instead of SELECT INTO OUTFILE.

I use this method to transfer small amounts of data from our live database to our test database, for example when investigating a reported problem in our program code. (We cannot guarantee the field order across all our databases.)

Now use RIGHT JOIN to list all class descriptions along with signups if any,SELECT cs.s_ClassID, cs.s_PersonID, cd.ClassName, cd.ClassID, cd.ClassType, cd.ClassDate, cd.ClassMax from class_signups cs RIGHT JOIN classdescription cd on (cs.s_ClassID = cd.ClassID )in itself, not too useful, but you can see classeshaving no one signed up as a NULL.

To count the number of signups for each class:SELECT cs.s_ClassID, COUNT(s_ClassID) AS ClassTotal, cd.ClassName, cd.ClassID, cd.ClassType, cd.ClassDate, cd.ClassMax from class_signups cs RIGHT JOIN classdescription cd on (cs.s_ClassID = cd.ClassID )GROUP BY cd.ClassIDThe COUNT/GROUP BY options show a row per unique ClassID, and the COUNT is adding upnon-null occurances of field s_ClassID. If we had used COUNT(*) then the class withno signups would have counted 1 record, rather than the desired 0/NULL for nosignups.

Now we show only classes where the count of signups is less than ClassMax, meaning theclass has openings!SELECT cs.s_ClassID, COUNT(s_ClassID) AS ClassTotal, cd.ClassName, cd.ClassID, cd.ClassType, cd.ClassDate, cd.ClassMax from class_signups cs RIGHT JOIN classdescription cd on (cs.s_ClassID = cd.ClassID )GROUP BY cd.ClassIDHAVING ClassTotal < cd.ClassMaxThe HAVING clause limits the after-JOIN output rows to ones matching its criteria, discarding others!

We may want to look only at the firstaid ClassType, so add a WHERE clause tothe JOIN,SELECT cs.s_ClassID, COUNT(s_ClassID) AS ClassTotal, cd.ClassName, cd.ClassID, cd.ClassType, cd.ClassDate, cd.ClassMax from class_signups cs RIGHT JOIN classdescription cd on (cs.s_ClassID = cd.ClassID ) WHERE cd.ClassType='firstaid' GROUP BY cd.ClassIDHAVING ClassTotal < cd.ClassMaxNow there are no outputs as firstaid is full, but suppose we are looking in this list with respectto a certain student PersonID==12. That is, we want to see classes this person can signupfor, including the ones they are already in!In the case we need to disregard signups by PersonID==12 for e.g.,

SELECT cs.s_ClassID, COUNT(s_ClassID) AS ClassTotal, cd.ClassName, cd.ClassID, cd.ClassType, cd.ClassDate, cd.ClassMax from class_signups cs RIGHT JOIN classdescription cd on (cs.s_ClassID = cd.ClassID AND cs.s_PersonID <> 12) WHERE cd.ClassType='firstaid' GROUP BY cd.ClassIDHAVING ClassTotal < cd.ClassMaxIn the join we drop out signups of PersonID 12, so they don't get counted.

If you cancel a long-time running query by Ctrl-C, you might find the CPU load of mysqld remains at 99%. That's because the query is still running on mysqld, and Ctrl-C only closes the client.Now, you can enter mysql again and use command SHOW PROCESSLIST to check the thread of the query, and kill the query by command KILL thread_id.I'm using mysql 5.0.21.

Since the LIMIT clause of a SELECT statement doesn't allow user variables you can use a prepared statement as in the example above in the manual. An alternative is to load all record ids of yourTable into a temporary table as shown below. This also has the benefit of getting all data necessary for pagination of your result set:

I was trying to figure out how to sort a varchar field which contained both number string and alphanumeric string. I wanted to sort it so that the numbers would be in order and then the alphanumeric entries would be in order. Here is the query that helped me accomplish that:

Just my little contribution when it comes to random row selection used with mysql & php. Based on the solution that consists of returning the count(*) of a table, then using that value to select a random row.

The problem with that solution from the MySQL standpoint is that there still remains the possibility of duplicate selections when we want more than one row, especially if the table is not that large (e.g. what are the chances of getting at least 2 duplicate rows while selecting 5 randomly, 1 at a time, out of a set of 10).

My approach is to rather generate unique random numbers from php, then fetch the corresponding table rows:

1- Use the appropriate php methods to fetch the table count from MySQL as done before:SELECT COUNT(*) FROM foo;

2- Use php to generate some unique random numbers based on the count.

This is the php function that i use. It takes 3 arguments: the minimum and maximum range values, and the amount of unique random numbers to be returned. It returns these numbers as an array.

3- Once you receive your set of randoms from the above function, perform a query for each random:<?phpforeach($randoms as $random_row){$query="SELECT * FROM foo LIMIT $random_row, 1;"//perform query, retrieve values and move on to the next random row...}?>

That's it-----

On a side note regarding the php random number generation function that I have here, I'm sure it's not the best solution all the time. For example, the closer the amount of random numbers gets to the range of numbers available the less efficient the function gets, i.e. if you have a range of 300 numbers and you want 280 of them unique and random, the function could spend quite some time trying to get the last 10 numbers into the array. Some probabilities get involved here, but I suspect that it would be faster to insert the 300 numbers directly into an array, shuffle that array, then finally select the 280 first entries and return them.

Also, as pointed earlier in the thread, keep in mind that if your table isn't that large, just performing the following works very well (e.g. selecting 5 random rows on a moderately large table):SELECT * FROM foo ORDER BY RAND() LIMIT 5;

If you want use multilanguage queryies you cat use this:Table 1 --------------langid langname--------------1 rus2 eng3 den---------------Table 2 (catalog)-----------------------catid url-----------------------1 www.google.com2 www.yandex.ru3 www.mysql.com------------------------table 3 (titles of sites from Table 3)-------------------------------------langid catid title------------------------------------- 1 1 Poiskovaya sistema 2 1 Search system 1 2 Portal 2 2 Portal 3 2 Portal 1 3 Sayt razrabotchikov MySQL 2 3 Site of MySQL's team 3 3 Bla bla bla------------------------------------And you need select sites from table2 on any language (for example Denmark), but site google.com have not title by Denmark. Ok if you can't select title by current language, you should select title by default language (here russian). You can make in one querySELECT *, ( SELECT title FROM table3 WHERE table3.catid = table2.catid AND langid = 3 UNION SELECT title FROM table3 WHERE table3.catid = table2.catid AND langid = 1 LIMIT 1 ) as title FROM table2

It very easy, but i think it query very big for MySQL if table2 contain around 1000-5000 rows, and site have 5000-6000 people per second.

You can make it another:SELECT *, (SELECT title FROM table3 ORDER BY IF(langid='1',0,1) ASC LIMIT 1) as title FROM `table2`i couldn't compare this queries, if anybody can compary spped of this method please write r.valiev@uzinfocom.uz (by russian (:^) .

Now my task more complexed, i need select any site from table2 : 1 - On current language 2 - If site have not title, Select title by default language 3 - If site have not title on default, Select title by any language.I think if will make it by thats method - it will very big for MySQL.

In regards to:Posted by Count Henry De Havilland-Fortesque-Smedley on January 13 2004 5:59am--------------START QUOTE---------------------Sometimes you want to retrieve the records that DONT match a select statement.

This finds all the CarIndex values in the Dealer's catalog that are in the bigger distributor catalog.

How do I then find the dealer CarIndex values that ARE NOT in the bigger catalog?

The answer is to use LEFT JOIN - anything that doesn't join is given a NULL value , so we look for that:

SELECT CarIndex FROM DealerCatalog LEFT JOIN BigCatalog ON DealerCatalog.CarIndex=BigCatalog.CarIndex WHERE BigCatalog.CarIndex IS NULL------------------END QUOTE--------------------------

I have found that the Left Join is quite expensive when doing this type of SQL Query. It is great if you have less than 1000 records in each table that you want to compare. But the real hardship is realized when you have 100,000 records in each table. Trying to do this type of join takes forever because each and every record in 1 table has to be compared to each and every record in the other table. In the case of 100,000 records, MySQL will do 10 BILLION comparisons (from what I have read, I may be mistaken).

So I tried the sql query above to see which rows in 1 table do not have a corresponding value in the other table. (Note that each table had close to 100,000 rows) I waited for 10 minutes and the Query was still going. I have since came up with a better way that works for me and I hope it will work for someone else. Here goes....

1: You must create another field in your base table. Let's call the new field `linked` (For the example above, we would perform this query ---ONLY ONCE--- to create the linked field in the DealerCatalog table.)

ALTER TABLE `DealerCatalog` ADD `linked` TINYINT NOT NULL ;

2: Now to get your results, simply execute the following queries instead of the left join query stated above

I know it is 3 queries instead of 1 but I am able to achieve the same result with 100K rows in each table in about 3 seconds instead of 10 minutes (That is just how long I waited until I gave up. Who knows how long it actually takes) using the LEFT JOIN method.

I would like to see if anyone else has a better way of dealing with this type of situation. I have been looking for a better solution for a few years now. I haven't tried MySQL 5 yet to see if there is a way to maybe create a view to deal with this situation but I suspect MySQL developers know about the expensive LEFT JOIN....IS NULL situation on large tables and are doing something about it.

SELECT INTO OUTFILE creates world-writable files. To avoid this security risk, you can create new subdirectory with +x rights for mysql and your user only (e.g. using chown me:mysql restricted_dir, chmod 770 restricted_dir), and then save the file into this directory. This way only you and mysql process can modify the file.

If you want to copy a file from the server in other location you can use select load_file('source_file') into OUTFILE 'target_file' Security issue on windows ... you can copy any file from any folder even if you don't have access to that file to an convenient folder where you have access !!

If you need names from second table for more then 1 columns in first table.Select table1.id,table1.konto,table2.name as name1,table1.konto1,table2_2.name as name2,table1.konto3,table2_3.naziv as name3,from table1left join table2 on (table1.konto=table2.id)left join table2 as table2_2 on (table1.konto2=table2_2.id)left join table2 as table2_3 on (table1.konto3=table2_3.id)

As a variant on the random row selection question, I had the goal of reading out a limited set of rows, always in the same order, but starting at a different point in the sequence each time (like Facebook short list of members):e.g. given records a, b, c, d, e, fI want random selections of triplets such as:b, c, dc, d, ef, a, b --> note I want wraparound!

The prior postings on random rows selections have shown: SELECT * FROM foo ORDER BY count*RAND() LIMIT 5;This will yield the 5 random rows, but not in the same record ordering.

To preserve order, including a wraparound, we must UNION a pair of queries.For e.g. to get 3 rows from a table of $counted rows,where we have selected $start, which happens to be within3 of the end, we wrap as:

(SELECT * FROM `Ordering` ORDER BY something LIMIT $start, 1) UNION(SELECT * FROM `Ordering` ORDER BY something LIMIT 0, 2)

suppose the table has 6 rows, and we deciderandomly to start with row 6, then concretely:(SELECT * FROM `Ordering` ORDER BY something LIMIT 5, 1) UNION(SELECT * FROM `Ordering` ORDER BY something LIMIT 0, 2)

As a variant on the random row selection question, I had the goal of reading out a limited set of rows, always in the same order, but starting at a different point in the sequence each time (like Facebook short list of members):e.g. given records a, b, c, d, e, fI want random selections of triplets such as:b, c, dc, d, ef, a, b --> note I want wraparound!

The prior postings on random rows selections have shown:SELECT * FROM foo ORDER BY count*RAND() LIMIT 5;This will yield the 5 random rows, but not in the same record ordering.

To preserve order, including a wraparound, we must UNION a pair of queries.For e.g. to get 3 rows from a table of $counted rows,where we have selected $start, which happens to be within3 of the end, we wrap as:

(SELECT * FROM `Ordering` ORDER BY something LIMIT $start, 1) UNION(SELECT * FROM `Ordering` ORDER BY something LIMIT 0, 2)

suppose the table has 6 rows, and we deciderandomly to start with row 6, then concretely:(SELECT * FROM `Ordering` ORDER BY something LIMIT 5, 1) UNION(SELECT * FROM `Ordering` ORDER BY something LIMIT 0, 2)

The common approach for selecting the attributes of each object into a single result-row per object is to join Objects with Attributes multiple times. However, not only such SELECT can grow very big and ugly, with large tables it becomes very slow.This could be dealt with using group-by functions, so to select all the objects of type T1, use the following SQL:

However fields with carriage returns may break the CSV as MySQL will automatically close a field when the \r\n line break is found. To work around this, replace all \r\n breaks with \n. The field does not close on \n breaks and it will be read into a single cell in Excel. You can do this in the same SQL statement, for example:

Note: this replaces NULL values with an empty string which is technically not the same thing but it will give you an empty cell in Excel instead of breaking the CSV structure and shifting the following cells to the left.

It essentially returns a two column result set. The first column contains the word 'cheap', 'moderate' or 'expensive' depending on the price of the product. The second column is the product name. This query can easily be modified to return a count of number of products categorized by the price range:

A really good speedup for those using 'Group By'. This is reported to MySql who are looking atr it, but can halve the speed of your query.

If you have a query that looks like:

Select col1, col2, col3 From tab1, tab2 ... Group by col1, col2

You can add the following: Group By col1, col2 WITH ROLLUP Having (col1 IS NOT NULL) and (Col2 is not NUll)

This totals the 'groupings' but then removes those rows from the query. At the moment it is believed that an optimisation was performed for the 'WITH ROLLUP' that didn't make it into the main optimisation...

In order to select random rows from a table don't use the ORDER BY RAND() clause.You can get a much better performing query if you use the RAND() function at the WHERE clause only. This query will not result in file sort and will stop as soon as it get to the limit.See http://www.rndblog.com/how-to-select-random-rows-in-mysql/

1) Let `task` be a MySql table containing at least 2 columns: id (primary key), pid (parent id - may be NULL) so that the rows form a classic tree structure.

2) Suppose you want to extract the tree relative to a particular actual id (constituted by itself and all its spawns) so that you need a recursive select which is unfortunately not implemented in MySql.

This will add the text headers Fiscal Year, Location and Sales to your fields. Only caveat is with an ORDER BY statement, if you don't want your headers sorted along with your data you need to enclose it in parenthesis: