Re: Impossible join?
On 7/16/05, Jonathan Mangin [EMAIL PROTECTED] wrote: Hello all, I'm storing data from a series of tests throughout each 24-hour period. I thought to create a table for each test. (There are six tests, lots more cols per test, and many users performing each test.) select test1.date, test1.time, test2.date, test2.time from test1 left join test2 on test2.date=test1.date where test1.date between '2005-07-01' and '2005-07-16' and uid='me'; ++--++--+ | date | time | date | time | ++--++--+ | 2005-07-13 | 6:30 | 2005-07-13 | 7:30 | | 2005-07-14 | 6:32 | 2005-07-14 | 7:45 | | 2005-07-15 | 6:30 | 2005-07-15 | 7:42 | | 2005-07-16 | 6:35 | NULL | NULL | ++--++--+ Is there a join, or some other technique, that would return (nearly) these same results if test1 (or any test) has not been performed? Using 4.1.11. You would need a full outer join, but MySQL doesn't support it... :( You need some way to hack around it. Maybe ditch the JOIN clauses and join tables in application, for instance using hashes? Regards, Dawid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficient select/insert
On 4/26/05, Jonathan Mangin [EMAIL PROTECTED] wrote:
I would like to select several rows from one table
and insert them into another nearly identical table
using Perl/DBI:
my @array = $q-param(); # HTML checkboxes
foreach my $element (@array) {
my $sql = select col2, col3, col4 from table1
where col1 = ?;
my $sth = $dbh-prepare($sql);
$sth-execute($element) or die $sth-errstr();
my @row = $sth-fetchrow_array;
$sql = insert table2 (col1, col2, col3, col4)
values (NULL, ?, ?, ?);
$sth = $dbh-prepare($sql);
$sth-execute($row[0], $row[1], $row[2]) or die $sth-errstr();
}
Is this efficient db interaction, or is there a better way?
This is 3.23 but can upgrade if necessary.
Is there any reason you didn't just simply do:
INSERT INTO table2 (col1,col2,col3,col4) SELECT NULL, col2, col3 col4
FROM table1;
...which is what SQL was made for in a first place. :) If there is a reason,
then I am sorry, I just didn't see it in the mail. I just don't understand
why sometimes people go at lengths to reimplement SQL in other
languages like perl.
Regards,
Dawid
I've seen things that you people wouldn't believe, I watched perl making
SELECT on two tables, then join the results using perl's hash keys.
All these moments will be lost, like tears in the rain... ;)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
On 4/26/05, Jay Blanchard [EMAIL PROTECTED] wrote: [snip] The same is true for any other value... Now that the columns have a NOT NULL constraint the records that previously contained NULL now hold '0'. x y x 0 x z x 0 Now, how do you uniquely identify the 2nd and 4th rows? [/snip] The database would have thrown an error when you tried to create row 4. No. NULL is not a value. It is a lack of value. Kind of a special symbol, like infinity in mathematics. You cannot say that infinity = infinity or infinity infinity. Same as you cannot say that NULL = NULL or NULL NULL. Regards, Dawid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get a Random Row on a HUGE db
On 4/26/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I am wanting to display a random page from my site, But I have over 12,000 articles right now and we add over 150 per day. What I wound up doing was a Virtual DOS attack on my own server because the 40 mb db was being loaded to many times. I have tons of memory and a Dell Dual Xeon 2.8 gig. Can someone think up a better way of doing this? I wish Mysql would just bring me back 1 valid random row It could be used in so many ways it should just be a part of MySql anyway. $stmt = Select * from firebase_content Order By rand() DESC Limit 0, 1; In general what such a statement does is [ except optimizations ] 1) retrieve all rand() values for all rows 2) sort them according to rand value 3) return the one with highest/lowest value. To improve the speed you could add a WHERE clause that limits the number of rows to, for example 1% of the table: SELECT * FROM firebase_content WHERE rand() 0.01 ORDER BY rand() LIMIT 1; ...so instead of returning and sorting 12000 rows you are doing it with about 120 rows which should be way faster. This is a trick I've learned while working with PostgreSQL -- and as I see it is universally useful. :) Regards, Dawid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database languages supported by MySQL
On 4/25/05, Ochungo, Pamela (ILRI) [EMAIL PROTECTED] wrote: Hallo, I would like to know whether there are any other languages supported by MySQL apart from SQL. e.g. MsAccess provides VB for aplications. Does MySQL support any such lower level language? I believe you are asking about so called procedural languages, as seen in most other DBs (Oracle's PLSQL, PostgreSQL PLpgSQL/plperl/pltcl/plpython, DB2's SQL PL, etc.). If you want them you have to use bleeding edge :) MySQL version 5.0 or later (see: http://dev.mysql.com/doc/mysql/en/stored-procedures.html). Haven't used it though. Regards, Dawid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
On 4/26/05, Jigal van Hemert [EMAIL PROTECTED] wrote: And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). Why is this? I actually do need NULL values (they really are not equal to '0', etc.), but also need this column as part of the PRIMARY key in an InnoDB table... It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. Because it is a PRIMARY KEY. I mean phrase 'PRIMARY KEY' means a key with which each row can be explicitly addressed. So if you have 2000 rows in a table, you can write 2000 SELECT statemens which will use columns in primary key and each of these SELECT statements will return exactly one (different) row. Since PRIMARY KEY is a primary key it cannot have NULL values. And there can be only one primary key on the table, for the same reason. If your PRIMARY KEY would allow NULL values, it would not be possible to address these rows with NULL values (*) and therefore it would not be a real primary key, by definiton. It would be a unique key. (*). Supposedly if there could be only one NULL value per column it might be possible, but since NULL means unknown, it should not be mixed with real values. From what you are saying, you need a UNIQUE key, not a PRIMARY KEY. Regards, Dawid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
