Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10

Displaying posts with tag: select (reset)

learn mysql join
+0 Vote Up -0Vote Down

hiiiiii http://www.atl-service.kiev.ua/ www.stockopt.com.ua/

Building Queries Systematically
Employee +0 Vote Up -0Vote Down

The SQL language is a bit like a toolkit for data. It consists of lots of little fiddly bits of syntax that, taken together, allow you to build complex edifices and return powerful results. For the uninitiated, the many tools can be quite confusing, and it's sometimes difficult to decide how to go about the process of building non-trivial queries, that is, queries that are more than a simple SELECT a, b FROM c;

A System for Building Queries

When you're building queries, you could use a system like the following: 

  • Decide which fields contain the values you want to use in our output, and how you wish to alias those fields
  • Values you want to see in your output
  • Values you want to use in calculations . For example, to calculate margin on a product, you could
  •   [Read more...]
    When kill flag is checked for SELECT? Part II
    +2 Vote Up -0Vote Down
    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...]
    When kill flag is checked for SELECT? Part I
    +2 Vote Up -0Vote Down
    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...]
    When EXPLAIN estimates can go wrong!
    +0 Vote Up -0Vote Down
    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...
    Limited SELECT count(*)
    +3 Vote Up -0Vote Down
    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 [...]
    SELECT INTO DUMPFILE
    +0 Vote Up -1Vote Down

    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)

    Running a case sensitive query in on a case insensitive table
    +0 Vote Up -0Vote Down

    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...]
    PHP - Populate HTML Select Element
    +0 Vote Up -0Vote Down

    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...]
    SQL to Select a random row from a database table
    +0 Vote Up -0Vote Down

    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...]
    Showing entries 1 to 10

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.