In the previous part I've stopped at the moment when we entered JOIN:exec() - most checks for kill flag happen somewhere there, during query execution. We know the list of functions that checks this flag during query execution:

sub_select_cache()
evaluate_join_record()
flush_cached_records()
end_write()
end_update()
end_unique_update()
end_write_group()
remove_dup_with_compare()
remove_dup_with_hash_index()

but we do not know when exactly each of them is called. So, let me try to show what happens inside JOIN::exec (some code paths and checks are not considered for simplicity, we care about SELECT, but not EXPLAIN SELECT etc). I've included











  [Read more...]

Manual describes this briefly:

In SELECT, ORDER BY and GROUP BY loops, the flag is checked after reading a block of rows. If the kill flag is set, the statement is aborted.

Complete, correct and useful answer is more complex though. Here is correct answer, but not very useful. So, kill flag is checked in the following functions related to SELECT statement processing:

make_join_statistics()
best_extension_by_limited_search()
find_best()
sub_select_cache()
evaluate_join_record()
flush_cached_records()
end_write()
end_update()











  [Read more...]

This is the title of my first blog post on MySQL Performance Blog. It deals with a customer case where the customer was facing a peculiar problem where the rows column in the EXPLAIN output of the query was totally off. The actual number of rows was 18 times more than the number of rows reported by MySQL in the output of EXPLAIN. Now this can be a real pain as MySQL uses “the number of rows” estimation to pick and choose indexes and it could really be picking up a wrong index simply because of the wrong estimate. You...

A SELECT statement with COUNT returns the number of rows retrieved by the SELECT statement (see mysql select count). For performance reasons, the desired result is to limit that count. Including a LIMIT clause in the SELECT statement will not work since it only restricts the number of rows returned, which is always one. The [...]

While learning a new ORDER BY syntax recently, as a diligent architect/DBA I reviewed the documentation. What I also found in the SELECT syntax which I did not also know was the keyword DUMPFILE.

The SELECT Syntax from MySQL 5.1 Manual states:

If you use INTO DUMPFILE instead of INTO OUTFILE, MySQL writes only one row into the file, without any column or line termination and without performing any escape processing. This is useful if you want to store a BLOB value in a file.

It’s a shame there is no middle ground, where you get the features of OUTFILE (i.e. all rows), and the features of DUMPFILE (i.e. no heading)

A colleague at work asked me “how can I run a case sensitive select on a case insensitive table?” out of curiosity and for a moment I hesitated, then said, yeah why not :) ….

Below are two different approaches (one of which is quite inefficient) and if anyone has another way, better or worse, please do leave a comment with your suggested approach :).

Cheers,
Darren

Preparation


mysql [localhost] {root} (test) > create table t1(a varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {root} (test) > insert into t1 (a) values ('darren');
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {root} (test) > insert into t1 (a) values ('Darren');
Query OK, 1 row affected (0.00 sec)

mysql

  [Read more...]

There is many ways to populate vales into a HTML select element. Here is one example I’ve come up with.

PHP Code Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14

<select size="1" name="szFooBar[]" multiple="multiple">
< ?php
$i=0;
while($obResults = mysql_fetch_row($saResults))
{
    if ($_POST['szFooBar'] == $obResults[0])
    { $szSelectedValue[$i] = " selected=\"selected\""; }
    else { $szSelectedValue[$i] = ""; }
 
    printf("<option value=\"%s\"%s>%s\n",$obResults[0], $szSelectedValue[$i], $obResults[1]);
    $i++;
}
?>							
</select>

  [Read more...]

There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic, but each database server requires different SQL syntax.

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column FROM table
ORDER BY RAND() 
FETCH FIRST 1 ROWS ONLY

Thanks Tim

Select a random record with Oracle:

SELECT column FROM 
( SELECT

  [Read more...]