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
- Original
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]
,
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 Daly
--
MySQL General
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]
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 Kirchhoff [EMAIL PROTECTED]
To: Eamon
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://lists.mysql.com/mysql
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: Eamon Daly [EMAIL PROTECTED]
Cc
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: Martijn Tonies [EMAIL
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/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL
.
Eamon Daly
- Original Message -
From: Ed Reed [EMAIL PROTECTED]
To: mysql@lists.mysql.com
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 without a problem
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.mysql.com
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: mysql@lists.mysql.com
Sent: Wednesday, August 24
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
- Original Message -
From: Jonathan Mangin
,
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 [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; mysql
.
Eamon Daly
- Original Message -
From: Erik Bukakis [EMAIL PROTECTED]
To: MySQL Discussion List mysql@lists.mysql.com
Sent: Monday, May 09, 2005 2:10 PM
Subject: Sorting by relevance?
I just learned a lot stuff at
http
probably try to clean up your data before it gets into the
database. It'll make your life a lot easier.
Eamon Daly
- Original Message -
From: Chris Ramsay [EMAIL PROTECTED]
To: Jerry Swanson [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
] | localhost|
+-+--+
More details in the usual place:
http://dev.mysql.com/doc/mysql/en/information-functions.html
Eamon Daly
- Original Message -
From: Stanton, Brian
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: Stanton, Brian [EMAIL PROTECTED]
To: mysql
|
+-+--+--+--+
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_clark
)
Eamon Daly
- Original Message -
From: dixie [EMAIL PROTECTED]
To: MySQL mysql@lists.mysql.com
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 first (not
costant
.
Eamon Daly
- Original Message -
From: Scott Haneda [EMAIL PROTECTED]
To: MySql mysql@lists.mysql.com
Sent: Friday, April 15, 2005 5:37 PM
Subject: zip code search within x miles
How are sites doing the search by zip and coming up with results within x
miles
/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
- Original Message -
From: Eamon Daly
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
Eamon Daly
- Original Message -
From: Dan
Eamon Daly
- Original Message -
From: Dan Bolser [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Sunday, April 10, 2005 10:10 AM
Subject: Find valid numeric values in a string field?
I have a column like this my_col varchar(20) null.
The values in the column
a column that
contains the master id and convert your key to multipart,
incorporating that field.
Eamon Daly
- Original Message -
From: Eric Anderson [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, April 05, 2005 4:49 PM
docs:
http://dev.mysql.com/doc/mysql/en/string-functions.html
Eamon Daly
- Original Message -
From: Grant Giddens [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, March 28, 2005 11:43 AM
Subject: QUERY ordering
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 Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com
/
Eamon Daly
- Original Message -
From: Grant Giddens [EMAIL PROTECTED]
To: mysql@lists.mysql.com
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:
sku
feature
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
- Original Message -
From: Ed Reed [EMAIL
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]
To: mysql@lists.mysql.com
Cc: Ed Reed [EMAIL PROTECTED]
Sent
/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: [EMAIL PROTECTED]
To: [EMAIL
.
The slave server is running MySQL 4.0.20.
Eamon Daly
- Original Message -
From: Keith Ivey [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Wednesday, March 09, 2005 2:11 PM
Subject: Re: safe way of replication?
Atle Veka wrote
, $daily, $item, $unit, $qty, $amount, $tax,
$total) or warn Cannot execute FIRST Statement!!\n$DBI::errstr;
If this succeeds, review your code and make sure that each
of your execute parameters have some value prior to the
execute.
Eamon
Eamon Daly
- Original Message -
From: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
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
|
+--+
Eamon Daly
- Original Message -
From: Marc Michalowski [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Friday, January 28, 2005 12:27 PM
Subject: Fixed with Fields
I was wondering if there is a way to create fixed width fields. Example
|
| qaax | Apr |
| baz | May |
| quux | Nov |
| qiix | Dec |
+--+---+
6 rows in set (0.00 sec)
Eamon Daly
- Original Message -
From: N. Kavithashree [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday
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 -
From: [EMAIL PROTECTED]
Cc
.
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. We are
standardized on mysql
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
- Original Message
.
Eamon Daly
- Original Message -
From: listsql listsql [EMAIL PROTECTED]
To: mysql [EMAIL PROTECTED]
Sent: Tuesday, November 09, 2004 7:11 AM
Subject: Emulating timediff in 4.0 ?
TIMEDIFF(expr,expr2)
TIMEDIFF() returns the time between the start
|
++--+-+-+
| 4 | bar | 42.00 | 54.625 |
| 1 | foo |9.00 | 47 |
++--+-+-+
2 rows in set (0.00 sec)
So now I'm completely confused. Anyone have an explanation?
Eamon Daly
- Original Message -
From
'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
wasteful step. Am I missing something?
Eamon Daly
--
MySQL General Mailing
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.
Eamon Daly
- Original Message -
From
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: [EMAIL PROTECTED]
Sent: Thursday, October 14
[1]}{$table};
}
Run like so:
$ ./whee.pl production standby
Table `users` differs (6 rows in production, 8 rows in standby)
Eamon Daly
- Original Message -
From: Michael J. Pawlowsky [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent
.
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 discount isn't one of:
1) a field on one
, 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
- Original
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 Daly
|
+--+---+
1 row in set (0.00 sec)
Eamon Daly
- Original Message -
From: Gleb Kozyrev [EMAIL PROTECTED]
To: Eldo Skaria [EMAIL PROTECTED]
Cc: MySQL List [EMAIL PROTECTED]
Sent: Tuesday, September 21, 2004 2:31 PM
Subject: Re
|
+--+---+
1 row in set (0.00 sec)
Eamon Daly
- Original Message -
From: Gleb Kozyrev [EMAIL PROTECTED]
To: Eldo Skaria [EMAIL PROTECTED]
Cc: MySQL List [EMAIL PROTECTED]
Sent: Tuesday, September 21, 2004 2:31 PM
Subject: Re
be easier to write and run faster than using multiple
passes with UNION. Plus, you'll be able to add more keys
later without having to completely rebuild the table.
Eamon Daly
- Original Message -
From: Jeremy McEntire [EMAIL
.
Eamon Daly
- Original Message -
From: Diana Soares [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, September 02, 2004 9:38 AM
Subject: Re: Suppression of result in SELECT @temp := column?
I didn't understand the problem very well, but maybe disabling the pager
before
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
Subject: Suppression
on 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: Tuesday, August 24, 2004 11:12
) }) {
$col = 0;
$row++;
for (@{ $_ }) {
$worksheet-write($row, $col, $_);
$col++;
}
}
Eamon Daly
- Original Message -
From: Scott Hamm [EMAIL PROTECTED]
To: 'Mysql ' (E-mail) [EMAIL PROTECTED
.
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 PROTECTED]; [EMAIL PROTECTED];
[EMAIL
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-1115
Fax: 1 773 913-0970
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 [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday
$serials = SELECT serial FROM registered_serials WHERE id = $id;
print
join(\t,
@user_data,
join(',',
map { $_-[0] } @{ $dbh-selectall_arrayref($serials) })
) . \n;
}
Eamon Daly
/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]
,
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 archives: http
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: gerald_clark [EMAIL
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 [EMAIL PROTECTED]
Cc: [EMAIL
`)
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
--
MySQL General
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: Wednesday, July 28, 2004 10:58 AM
| Jane | 127.0.0.1 |
++---+---+
2 rows in set (0.00 sec)
Eamon Daly
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, July 06, 2004 7:39 AM
Subject
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 PROTECTED]
To: [EMAIL
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: [EMAIL PROTECTED]
Sent: Thursday, June 24
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 (NISC) [EMAIL
). 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 773 975-1115
Fax: 1 773 913-0970
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
|
+-++--+--+--
---++--+--+
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
Tel: 1 773 975-1115
Fax: 1 773 913-0970
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:57 PM
Subject: Perl Modelues
Hello List:
I
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-0970
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 773 913
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-1115
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 PROTECTED
.
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975-1115
Fax: 1 773 913-0970
- Original Message -
From: Mike Tuller [EMAIL PROTECTED]
To: gerald_clark [EMAIL PROTECTED]
Cc: MySql List [EMAIL PROTECTED]
Sent: Tuesday, February 10, 2004 10:56 AM
Subject: Re: There has
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http
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
- Original Message
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 Daly
NextWave Media
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.com/mysql
84 matches
Mail list logo
