select id from member

I have a member table which have hundreds of thousands records.

I can use usual select query results and turn the results to the a a comma separated string. such as the ids would be "101, 102, 103 ...." But I was wondering about the performance. (I would try out the performance now).

Is there a simple well performanced query (mysql) that I can select all the id from member, and output result is already a comma separated string.

    There are some MySQL features to get data into CSV (comma separated values)
    There is even a MySQL storage engine CSV

    http://dev.mysql.com/tech-resources/articles/csv-storage-engine.html
    The CSV engine provides four basic benefits to those using MySQL:

    1. It allows flat files to be referenced via SQL and used alongside other data that has been loaded into MySQL.
    2. Editing data stored via the CSV engine can be performed even when the MySQL server is down through standard file editors.
    3. Data from any CSV engine file can easily be imported into any standard spreadsheet program (e.g. Microsoft Excel).
    4. It allows for the instantaneous loading of massive amounts of data into the MySQL server.

    But in your case it isnt really true CSV and further you do not pick out rows.
    You query for column.

    I say, a normal query SELECT id in combination with some PHP Loop to produce a string:
    "101, 102, 103, 104, ..."
    is good enough, if not the only available solution.
    Regarding speed and benchmark performance you can try some different PHP Loops and functions.
    But I think the gain in perfomance will be marginal, unless you have done it very bad right now.

      Thanks!

      I tested it out.

      Regular query like this is very fast. I would just use regular query and use implode turn array into string in php script.

      But the subquery approach is so slow.

      Say if I want to do

      select * from member where id not in (select id from temp_member)

      I would do 3 steps

      1) select id from temp_member
      2) turn the result array into string, $temp_id_comma_separated_string
      3) select id from member where id not in ($temp_id_comma_separated_string)

      For the large table, these 3 steps are much faster than 1 step which use subquery

      select * from member where id not in (select id from temp_member)

        Blackhorse.

        If your server or your hosting has a PHP that is configured with [man]PDO[/man] + mysql
        you can do what you describe with very little code.
        PDO with mysql, sqlite or any other database has many good things to offer.
        You will find methods, functions that normal [man]MySQL[/man] can not do.
        Compared to PDO the php MySQL is a bit old and almost outdated.

        I tested this code at my own Apache SERVER
        so I know it works perfectly well

        <?php

$host = "localhost";
$user = "dbuser";
$pass = "dbpass";
$dbnm = "database name";

// Connect to PDO MySQL
$dsn =  "mysql:dbhost=$host;dbname=$dbnm";
$db  = new PDO($dsn, $user, $pass);

$stmt = $db->prepare("SELECT id FROM member");
$stmt->execute(); // execute the prepared query

// FETCH one COLUMN from ALL rows in result IN one operation into array
// then implode array into one LINE using Comma+space between
$line = implode( ', ', $stmt->fetchAll (PDO::FETCH_COLUMN) );

// Test display the string
echo $line; //"1, 2, 3, 4, 5, 6, 7, 8, 9, 10"

exit('<hr />done!');

?>
          Write a Reply...