After spending half the night trying this same query on a
number of different datasets, it looks like sometimes MySQL
/will/ use all parts in certain cases, so I'm satisfied by
that. Thanks for responding!
Eamon Daly
- Ori
27;)
Is this expected behavior? It surprised me that the second
query would take full advantage of the index but not the
third. We're using MySQL 4.1.20.
Eamon Daly
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
n size,
and the MEMORY storage type never converts to on-disk
tables, that's really not a good solution. How does MySQL
determine whether a table is written to memory or to disk,
and is there a way to force the former?
____
Eamon Dal
That's exactly right (the "few writes" part, not the
"knowing what I'm doing" part): this particular machine only
runs reports a few times a day.
________
Eamon Daly
- Original Message -
From: "Jan
would easily overrun the smaller disk.
____
Eamon Daly
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Yep, I forgot to mention that I'm using MySQL 4.1, which
does support ref_or_null, but only for the first column.
Eamon Daly
- Original Message -
From: "Dan Nelson" <[EMAIL PROTECTED]>
To: "Eam
1, b2, b3), but obviously that gets thrown out because of
the OR. Is there a Better Way to either rewrite the WHERE
condition or break out b to allow indexes to be used?
____
Eamon Daly
--
MySQL General Mailing List
For list archives: http:/
Got it. Thanks to all who replied: speed is the primary
concern here, so I'll be going with the first approach,
especially since I could use the unique constraint.
Eamon Daly
- Original Message -
From: "Mart
x27;d guess the latter is the
Right Way, since the index will be significantly smaller,
but then I still need to go to the table to retrieve foo_id.
________
Eamon Daly
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysq
cting from.
________
Eamon Daly
- Original Message -
From: "Ed Reed" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, August 30, 2005 3:08 PM
Subject: Insert Into problem
Can anyone please tell me why this fails, I've done this type of thing
before witho
't be entirely uncommon: I'm pretty
sure I've seen a similar example in the Cookbook.
____
Eamon Daly
- Original Message -
From: <[EMAIL PROTECTED]>
To: "Eamon Daly" <[EMAIL PROTECTED]>
Cc:
locks. Has anyone found
significant speed increases or better concurrency by
switching over to InnoDB for such a table?
________
Eamon Daly
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists
my $sql = sprintf <<'EOF', join(',', @array);
SELECT col2, col3, col4
FROM table1
WHERE col1 IN (%s)
EOF
my $sth = $dbh->prepare($sql);
$sth->execute() or die $sth->errstr();
____
Eamon Daly
- Orig
7;%Y-%m-%d') AS date,
SUM(process),
SUM(pending),
SUM(wip)
FROM tab
GROUP BY date;
SELECT
'Summary',
SUM(process),
SUM(pending),
SUM(wip)
FROM tab;
________
Eamon Daly
- Original Message -
From: "Seena Blace" &
word.
Eamon Daly
- Original Message -
From: "Erik Bukakis" <[EMAIL PROTECTED]>
To: "MySQL Discussion List"
Sent: Monday, May 09, 2005 2:10 PM
Subject: Sorting by relevance?
I just learned a lot stu
+
| [EMAIL PROTECTED] | localhost|
+-+--+
More details in the usual place:
http://dev.mysql.com/doc/mysql/en/information-functions.html
____
Eamon Daly
- Original M
ds and various common abbreviations ('x',
'ext.', and 'EX', for instance). In the future, you should
probably try to clean up your data before it gets into the
database. It'll make your life a lot easier.
__
I don't know if it's possible in MySQL.
That said, in the mysql client, you can type '\s' for
'status'. Look for 'Current user' in the output.
____
Eamon Daly
- Original Message -
From: "
|
+-+--+--+--+
Same concept, but the LOCATE finds the first occurrence of
the casted number. Then add the length of the casted number
et voila.
Eamon Daly
- Original Message -
From: "gerald_
)
Eamon Daly
- Original Message -
From: "dixie" <[EMAIL PROTECTED]>
To: "MySQL"
Sent: Friday, April 22, 2005 6:18 PM
Subject: extract numeric value from a string.
Hi at all, I've this necessity.
In a table I've a field popolated by a string where the
os(b.latitude/57.2958) *
cos(b.longitude/57.2958 - a.longitude/57.2958)
) <= 5 # <-- Your target radius
GROUP BY distance
They both achieve similar results; anyone have a feel for
which is "better"?
____
Eamon Daly
-
de) |
+--+-+
|70443 | 42471 |
+--+-+
Can't take credit for the SQL, by the way; I'm pretty sure I
found it on Google.
Eamon Daly
- Original Message -
From: "
#x27;\n', '');
If you only want to drop trailing carriage returns and
line-feeds, you can use TRIM instead:
SELECT TRIM(TRAILING '\r\n' FROM log) FROM test;
See "String Functions" in the manual:
http://dev.mysql.com/doc/mysql/en/string-functions.html
_
cetera. REGEXP is explained here:
http://dev.mysql.com/doc/mysql/en/regexp.html
Eamon Daly
- Original Message -
From: "Dan Bolser" <[EMAIL PROTECTED]>
To:
Sent: Sunday, April 10, 2005 10:10 AM
Subject: Find valid numeric values in a stri
UE keys. The workaround would be to add a column that
contains the master id and convert your key to multipart,
incorporating that field.
____
Eamon Daly
- Original Message -
From: "Eric Anderson" <[EMAIL PROTECTE
.com/mysql-book/
____
Eamon Daly
- Original Message -
From: "Grant Giddens" <[EMAIL PROTECTED]>
To:
Sent: Monday, March 28, 2005 6:00 PM
Subject: SELECT help
Hi,
I am tring to do a select from 2 tables.
Table1:
sku
title
Table 2:
there a Right Way to move a row from "foo_1" to "foo_2"
short of explicitly locking "foo"? It seems unwieldy to have
to supply the names of any and all MERGE tables to my
routine.
Eamon Daly
--
MySQL General
the MySQL docs:
http://dev.mysql.com/doc/mysql/en/string-functions.html
________
Eamon Daly
- Original Message -
From: "Grant Giddens" <[EMAIL PROTECTED]>
To:
Sent: Monday, March 28, 2005 11:43 AM
Subject: QUERY ordering c
WHERE log like '%tested this%';
As mentioned by others in the thread, this is fine as a
stopgap, but you should change your table ASAP.
____
Eamon Daly
- Original Message -
From: "Keith Ivey" <[EMAIL PROTECTED]>
SELECT
LEFT(SUBSTRING_INDEX(LEFT(log, LOCATE('\n', log, LOCATE('tested this',
log)) - 1), '\n', -1), 8)
FROM test WHERE log LIKE '%tested this%'
Don't try this at home.
____
Eamon Daly
- Or
slave.
The slave server is running MySQL 4.0.20.
________
Eamon Daly
- Original Message -
From: "Keith Ivey" <[EMAIL PROTECTED]>
To:
Sent: Wednesday, March 09, 2005 2:11 PM
Subject: Re: safe way of replication?
At
http://dev.mysql.com/doc/mysql/en/myisam-storage-engine.html
OPTIMIZE TABLE:
http://dev.mysql.com/doc/mysql/en/optimize-table.html
DELETE:
http://dev.mysql.com/doc/mysql/en/delete.html
____
Eamon Daly
- Original Message -
From: <[E
d make sure that each
of your execute parameters have some value prior to the
execute.
Eamon Daly
- Original Message -
From: "Gerald Preston" <[EMAIL PROTECTED]>
To: "'Michael Stassen'" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>;
Sent
.html
Eamon Daly
- Original Message -
From: <[EMAIL PROTECTED]>
To:
Sent: Wednesday, February 16, 2005 3:35 PM
Subject: Generating ticket/file numbers
Hi there is there any way at all that with a special field type mysql can
generate file numbers like so AUD-
| foox |
+--+
____
Eamon Daly
- Original Message -
From: "Marc Michalowski" <[EMAIL PROTECTED]>
To:
Sent: Friday, January 28, 2005 12:27 PM
Subject: Fixed with Fields
I was wondering if there is a way to create fixed wid
27;Oct', 'Nov', 'Dec');
+--+---+
| name | month |
+--+---+
| bar | Jan |
| foo | Mar |
| qaax | Apr |
| baz | May |
| quux | Nov |
| qiix | Dec |
+--+---+
6 rows in set (0.00 sec)
one that, you can do plenty of things to speed up or
scale your system, such as mapping the files to memory with
mod_file_cache, judicious use of a caching proxy, or the
creation of a ramdisk.
____
Eamon Daly
- Original Message -
Fro
t too large" problems, but I couldn't
find confirmation in the docs. If I set max_allowed_packet
to, say, 16M on the master, does it write the data from a
LOAD DATA INFILE command in 16M chunks to the binary log?
____
Eamon Daly
- O
.
Eamon Daly
- Original Message -
From: "Michael Haggerty" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, November 09, 2004 6:06 PM
Subject: Best Practices
I am working on a data warehousing solution involving
mysql and have a question about best practices.
You need to be careful when subtracting dates-- if you treat
them like integers, you'll probably not get the results you
were expecting. Here's more discussion from the archive:
http://lists.mysql.com/mysql/172921
________
5261
date 2 in seconds: 883634399
diff in seconds: -169138
diff in hh:mm:ss: -46:58:58
So you've got that going for you.
Eamon Daly
- Original Message -
From: "listsql listsql" <[EMAIL PROTECTED]>
To: "mysql" <[EMAIL P
WHEN out_method = 'Multiply' THEN @p1 * out_value
-> WHEN out_method = 'Divide' THEN @p1 / out_value
-> END) as phase_2
-> FROM filter
-> GROUP BY name
-> ;
++--+-+-+
| id | name | phase_1 | phase_2 |
++--+-+
/ in_value
END as phase_2
FROM filter
But, of course, that results in "ERROR 1054: Unknown column
'phase_1' in 'field list'". Do I need to create a temporary
table just to hold all the phase_1 values? Ultimately, I
want to group by name, so that seems like an awfully
As a side note, Paul DuBois lists several useful date
calculations such as last day of the month on pages 265-267
of the MySQL Cookbook (O'Reilly). He uses a DATE_SUB routine
for generating the first of the month, so maybe his way is
faster.
_____
$tables{$ARGV[0]}{$table}, $ARGV[0],
$tables{$ARGV[1]}{$table}, $ARGV[1])
if $tables{$ARGV[0]}{$table} != $tables{$ARGV[1]}{$table};
}
Run like so:
$ ./whee.pl production standby
Table `users` differs (6 rows in production, 8 rows in standby)
__
/tmp/count
Creates output like so:
20,21c20,21
< 3 users.txt
< 952 total
---
6 users.txt
955 total
____
Eamon Daly
- Original Message -
From: "Michael J. Pawlowsky" <[EMAIL PROTECTED]>
To: &
aster, assuming you actually want the value of
discount in the result of the SELECT. Otherwise, you're
doing the calculation twice. No idea if that's true, though,
so maybe someone else can give a definitive answer.
____
Eamon Daly
r HAVING.
____
Eamon Daly
- Original Message -
From: <[EMAIL PROTECTED]>
To: "Ed Lazor" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, October 11, 2004 3:51 PM
Subject: Re: Where clause question
Because
I'm interested in this, too. We have a logging table that
sees hundreds of rows per second, and we do a ton of monthly
reports. We just bit the bullet and added an indexed DATE
column. Is there a better strategy?
Eamon
-+
| 42 | 2 |
+--+---+
1 row in set (0.00 sec)
____
Eamon Daly
- Original Message -
From: "Gleb Kozyrev" <[EMAIL PROTECTED]>
To: "Eldo Skaria" <[EMAIL PROTECTED]>
Cc: &q
-+
| 42 | 2 |
+--+---+
1 row in set (0.00 sec)
____
Eamon Daly
- Original Message -
From: "Gleb Kozyrev" <[EMAIL PROTECTED]>
To: "Eldo Skaria" <[EMAIL PROTECTED]>
Cc: &q
11) NOT NULL default '0',
`type` enum('key0','key1','key2','other1','other2') NOT NULL default
'key0',
`data` text NOT NULL,
KEY `id_type` (`id`,`type`)
) TYPE=MyISAM
You'll save a ton of space, and your queries will probably
E TEMPORARY TABLE last_id SELECT id FROM users ORDER BY id DESC LIMIT
1;
> SELECT users.* FROM users JOIN last_id USING (id);
> EOF
id first lastemail
3 testy mctest [EMAIL PROTECTED]
but using @id seems less, well, wasteful.
____
Did anyone ever follow up on this question? I'm looking for
the answer, too.
Eamon Daly
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, August 25, 2004 9:16 AM
Subjec
n the server.
There's more on the subject at mysql.com:
http://dev.mysql.com/doc/mysql/en/Secure_requirements.html
________
Eamon Daly
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesd
And, apparently, my perl solution completely reinvented the
wheel. See pp.527-530 "MySQL Cookbook", 1st ed. for a
better script.
____
Eamon Daly
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTEC
t;write($row, $col, $_->[0]);
$col++;
}
for (@{ $dbh->selectall_arrayref("SELECT * FROM $table") }) {
$col = 0;
$row++;
for (@{ $_ }) {
$worksheet->write($row, $col, $_);
$col++;
}
}
for the composition and
decomposition of comma-separated values. An instance of
the Text::CSV class can combine fields into a CSV string
and parse a CSV string into fields.
____
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975-
E TABLE DSL (
Full_Name varchar(40) NOT NULL default '',
Framed_IP_Address varchar(16) NOT NULL default ''
) TYPE=MyISAM;
et cetera.
____
Eamon Daly
- Original Message -
From: "SciBit MySQL Team
$_->[0] } @{ $dbh->selectall_arrayref($serials) })
) . "\n";
}
Eamon Daly
- Original Message -
From: "Scott Haneda" <[EMAIL PROTECTED]>
To: "MySql" <[EMAIL PROTECTED]>
Sent: Wednesday, August 04, 2004 7:49 PM
Subject: Expor
further limiting treachery.
Most importantly, you should know that no hashing algorithm
is perfect, RAND() isn't entirely random, and security is
Not Easy. Here's some reading material:
http://www.linktionary.com/h/hash_function.html
http://en.wikipedia.org/wiki/SHA-1
http://aspnet.4guysfromrolla.com/articles/112002-1.aspx
Eamon Daly
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
0101235959
GROUP BY c.d_id, c.e_id
This report takes over an hour when looking at just one
month's worth of data. It's brutal. Any and all suggestions
would be appreciated.
____
Eamon Daly
- Original Message -
From: &q
e RAM disk,
make a backup copy somewhere, and start both MySQL servers.
This setup wouldn't require changing any SQL.
____
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975-1115
Fax: 1 773 913-0970
--
MySQL General Mailing List
For list
I would assume it would use reporting_id_t, since the WHERE
clause has both a.a_id and a.timestamp in it.
Eamon Daly
- Original Message -
From: "gerald_clark" <[EMAIL PROTECTED]>
To: "Eamon Daly&qu
TWEEN 2004010100 AND 20040101235959
and it /still/ only uses reporting_t! What the heck am I
missing?
Eamon Daly
- Original Message -
From: "Eamon Daly" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent:
_id`)
But EXPLAIN shows that MySQL isn't even considering the key
on a, and chooses the primary key on c over my index.
Clearly I'm confused about how indexes are used in a
JOIN/WHERE situation: can anyone enlighten me?
____
Eamon Daly
ull_Name | Framed_IP_Address |
++---+---+
| DIALUP | Tom | 127.0.0.1 |
| DSL| Jane | 127.0.0.1 |
++---+-------+
2 rows in set (0.00 sec)
Eamon Daly
ength rows
to fixed, and you'll now be able to index bar properly.
You'll see a dramatic performance improvement.
____
Eamon Daly
- Original Message -
From: "Eric Scuccimarra" <[EMAIL PROTECTED]>
To: <[EMAI
22 quatloos for you!
Yeah, in retrospect, I imagine Dan's on the money: the seek
time is where the money's at. Thanks for the feedback, all!
________
Eamon Daly
- Original Message -
From: "Frank Bax" <[EMAIL
runs each
came out just about equal, which surprised me a little.
Eamon Daly
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FYI, MySQL allows boolean searching as of 4.0.1. See:
http://dev.mysql.com/doc/mysql/en/Fulltext_Boolean.html
Or pp. 256-257 of MySQL, 2nd Ed. by Paul DuBois for several
examples.
Eamon Daly
- Original Message -
From
You probably want SEC_TO_TIME:
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html
SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table
Eamon Daly
- Original Message -
From: "Dirk Bremer
d |1 | Using where; Using index |
+-++--+--+--
---++--+--+
But, of course, those UNIONs still mean a full join. Is this
as good as I'm going to get? Thanks for the previous
replies!
____
Eamon Daly
NextWave Media Group LLC
han using the enum and a
conditional, but that seems really unwieldy and slow (3
selects and then a UNION). Suggestions? I'm on MySQL 4.0.18,
by the way, so subselects aren't an option.
Eamon Daly
NextWave Media Group LLC
Tel: 1
install_Perl_modules
____
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975-1115
Fax: 1 773 913-0970
- Original Message -
From: "Kirti S. Bajwa" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, April 07, 2004 1:5
Perl's Win32::SerialPort (or Device::SerialPort) coupled
with DBI and DBD::mysql would do it in a dozen lines or so.
Docs for both are at http://search.cpan.org/.
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975-1115
Fax: 1 77
Perl's Win32::SerialPort (or Device::SerialPort) coupled
with DBI would do it in a dozen lines or so. Docs for both
are at http://search.cpan.org/.
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975-1115
Fax: 1 773 913
cement mentioned in the Cookbook (pp.647-648) is
a good candidate in this scenario.
____
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975-1115
Fax: 1 773 913-0970
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
You definitely don't want to use timestamping for unique
IDs. You want to use an auto-incrementing column or similar.
See
http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975
SELECT * FROM test ORDER BY RAND() LIMIT 1;
See pp. 694-695 in the MySQL Cookbook.
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975-1115
Fax: 1 773 913-0970
- Original Message -
From: "Jack Lauman" <[EMAIL P
Okay, the lack of locking was driving me crazy. Here's my
version:
#!/usr/bin/sh
MYSQL="mysql -v test"
ethernet_address=$1
cat <
To: "gerald_clark" <[EMAIL PROTECTED]>
Cc: "MySql List" <[EMAIL PROTECTED]>
Sent: Tuesday, February 10, 2004 10:56 AM
Subject: Re: There has to be a way to do this
>
mp2.score;
You can find this method of grouping under section 12.6 of
the MySQL Cookbook: Finding Rows Containing Per-Group
Minimum or Maximum Values.
____
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975-1115
Fax: 1 773 913-0970
- O
book (pp.647-648) is
a good candidate in this scenario, so maybe what I'm doing
is the only way until I get over my fear of deadlocks in
InnoDB.
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975-1115
Fax: 1 773 913-0970
--
dex on the first 8 characters
of a DATETIME column? And if so, how do I reconstruct the
WHERE to use it? Would this work:
WHERE SUBSTRING(entered, 1, 8) = 20040124
? I couldn't find any relevant info in the purple book.
____
Eamon Da
e I'm unaware of?
Would another table type outperform MyISAM under this
scenario?
________
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975-1115
Fax: 1 773 913-0970
--
MySQL General Mailing List
For list archives: http://lists.mysql.co
85 matches
Mail list logo
