I remember seeing this same question a few months ago, so you might try the
archives. I'm pretty sure the answer was that mysql continues incrementing
to 4 digits. If you have a dev box, you could create a .999 binlog and put
it in your bin-index file -- then start mysql, issue a flush logs
Hi Roger,
Quick rundown.
Table:
Assignments
CaseID auto_increment
FileNumber char(18)
FirstName char(20)
LastName char(20)
and on and on and on
Indexes on
CaseID (Primary)
FileNumber
Name (LastName, FirstName)
What I would like to do is something along the lines of
SELECT CaseID
Another problem with load data and replication? I've identified 2 confirmed
bugs in the last 2 releases. Honestly, I've pretty much given up on using
load data on my master server for the time being. I'm now using scripts that
generate extended insert statements instead.
--jeff
- Original
- Original Message -
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Jeff Kilbride [EMAIL PROTECTED]
Cc: Lopez David E-r9374c [EMAIL PROTECTED]; 'DeepBlue'
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, March 28, 2003 10:05 PM
Subject: Re: Memory Leak
On Fri, Mar 28, 2003 at 10:46:52AM
I've heard some bad things about turning off swap on Linux. I think it was
on Jeremy Z.'s Blogger page. If he sees this maybe he can comment.
DeepBlue, take a look in the support-files directory under your MySQL
install directory. There are 4 example my.cnf files for various memory/box
Make sure that all the files inside your database directories are owned by
mysql:
chown -R mysql. database dir
The dot at the end of mysql above sets the group to mysql also. The -R
means set the permissions recursively. Each time I install a binary
distribution and run the
Replication doesn't seem to be replicating LOAD DATA INFILE correctly in
3.23.56. Starting with a master and slave that were in sync, I imported a
file:
---
MASTER
mysql select count(*) from list where sourceID=0;
+--+
| count(*) |
+--+
|0 |
- Original Message -
From: Jeremy Zawodny [EMAIL PROTECTED]
To: hemanth [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, March 25, 2003 11:45 PM
Subject: Re: Why Replication stops ?
On Wed, Mar 26, 2003 at 01:00:33PM +0530, hemanth wrote:
Hi Jeremy,
Many Thanks for your
It seems to be this way on all the lists -- the java.mysql.com List-ID
header is missing, too.
--jeff
- Original Message -
From: Joseph Bueno [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: mysql-list [EMAIL PROTECTED]
Sent: Sunday, March 23, 2003 1:43 AM
Subject: List-ID Header
Hello,
What's the error you're getting? What OS are you running on? If it's *nix,
make sure the file you are trying to load, and the full path to that file,
is readable by the user mysql runs as. I once had some files in a user's
home directory I was trying to load and although the file was
- Original Message -
From: Daevid Vincent [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, March 18, 2003 4:59 PM
Subject: RE: Please tell me why to use KEY (a_id, b_id) rather than KEY
(a_id), KEY (b_id)
Ah ha!
So if I had:
CREATE TABLE `rep_table` (
`rep_id` smallint(5)
I'm assuming you're IDs are getting bigger, so how about ordering them in
descending order?
SELECT ID, title, article FROM news ORDER BY ID DESC LIMIT 2
Also, if you're starting from 0, you don't need to include that in the LIMIT
clause.
--jeff
- Original Message -
From: Todd W [EMAIL
How about:
my $sql = INSERT INTO apidbusers VALUES (?, password(?));
--jeff
- Original Message -
From: Jianping Zhu [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, February 28, 2003 2:48 PM
Subject: Re: mysql encripted password from perl
sth-execute($username,
: BUG? 3.23.55 not replicating LOAD DATA INFILE
From: Egor Egorov
Date: Thu, 20 Feb 2003 14:07:02 +0200
On Thursday 20 February 2003 01:04, Jeff Kilbride wrote:
I have one master and two slaves all running 3.23.55-max on RedHat 7.3.
I've had replication up and running smoothly for several
of this
problem?
Thanks,
--jeff
- Original Message -
From: Guilhem Bichot [EMAIL PROTECTED]
To: Jeff Kilbride [EMAIL PROTECTED]
Cc: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thursday, February 20, 2003 1:06 PM
Subject: Re: BUG? 3.23.55 not replicating LOAD DATA INFILE
Hi
I have one master and two slaves all running 3.23.55-max on RedHat 7.3. I've
had replication up and running smoothly for several days. Today, I decided
to try out LOAD DATA INFILE on the master. After successfully loading on the
master, I checked both slaves and neither one had the new data. There
- Original Message -
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Jeff Kilbride [EMAIL PROTECTED]
Cc: MySQL [EMAIL PROTECTED]
Sent: Wednesday, January 15, 2003 10:41 AM
Subject: Re: Optimizing Ext3 for MySQL
On Tue, Jan 14, 2003 at 11:33:54PM -0800, Jeff Kilbride wrote:
Are there any
Roussey [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: 'Jeff Kilbride' [EMAIL PROTECTED]
Sent: Wednesday, January 15, 2003 4:04 PM
Subject: Re: Optimizing Ext3 for MySQL
I use ext3 and have a qps of anywhere from 2800-8000 and use the
defaults with no problems. Have you tried:
iostat -k 1
to look
What about just issuing a PURGE MASTER LOGS TO master-bin.007 on the master
and then starting the slave without a master.info file? I think that will
recreate it for you -- but you should research this before trying...
--jeff
- Original Message -
From: James Fidell [EMAIL PROTECTED]
To:
Are there any general guidelines for optimizing ext3 for MySQL? I have a
perl script that runs 200K + updates into my database once a day and I see
pretty wildly fluctuating query/sec numbers using Jeremy Z's mytop program.
I've seen in excess of 2000 qps and then seen that number drop to 40 qps.
What version of MySQL are you using? The order by and limit clauses should
work the way you want them to, not the way you are describing -- i.e. it
should order first and then return the top 100 rows. I use this kind of sql
statement all the time in the reports I write. I'm not sure if any older
Try this:
select sec_to_time(unix_timestamp(column_two) - unix_timestamp(column_one))
Should give you the elapsed time in hh:mm:ss format.
--jeff
- Original Message -
From: Peter Abilla [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, December 03, 2002 8:45 AM
Subject: DateTime
select if(captain = 1, 'C', '')
from stats
where captain 0
and number = $number
group by number
This returns 'C' whenever the captain field is 1 and '' (empty string)
otherwise.
--jeff
- Original Message -
From: Alex Behrens [EMAIL PROTECTED]
To: DL Neil [EMAIL PROTECTED]; MYSQL
[EMAIL
RESET MASTER
RESET SLAVE
This is a handy page to bookmark, if you're doing replication:
http://www.mysql.com/doc/en/Replication_SQL.html
--jeff
- Original Message -
From: walt [EMAIL PROTECTED]
To: Lewis Watson [EMAIL PROTECTED]
Cc: mysql [EMAIL PROTECTED]
Sent: Tuesday, October 22,
Commas in the FROM clause are the same as INNER JOIN. To make it clearer and
easier to understand, I'd write it something like this:
SELECT psh.StatID,
pt1.TeamName AS Home,
pt2.TeamName AS Visitor,
psh.GameDate
FROM PH_TEAMS pt1, PH_TEAMS pt2, PH_SCORE_HEADER psh
WHERE psh.Home = pt1.TeamID
AND
- Original Message -
From: Paul DuBois [EMAIL PROTECTED]
To: Jeff Kilbride [EMAIL PROTECTED]; Michael J. Mitchell
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, October 15, 2002 6:46 PM
Subject: Re: Query using the same table twice
At 18:03 -0700 10/15/02, Jeff Kilbride wrote
You can relocate an entire database with a symbolic link:
ln -s /path/to/database/files /var/lib/mysql/database_name
--jeff
- Original Message -
From: Niranjan Patel [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, October 14, 2002 10:50 AM
Subject: Default data dir
Hello
In
Not on a per database basis, that I know of.
Anybody else?
--jeff
- Original Message -
From: Niranjan Patel [EMAIL PROTECTED]
To: Jeff Kilbride [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Monday, October 14, 2002 11:19 AM
Subject: Re: Default data dir
I get that point
Hi Frank,
You can use the concat() function:
select concat(numer, ',', text) from Table.
The online docs for MySQL contain a great reference for functions:
http://www.mysql.com/doc/en/Functions.html
--jeff
Hi all,
I'm a DBA in the Oracle World.
I want to make a sql query in mysql,
Not really a MySQL question, but...
The way you have it now definitely won't work. Your select statement for
getting the number of referrals for level one will always return a count of
one -- because id is the primary key of your table. One way to do this,
without listing all the referrals in a
Michael, what compiler/version did you use?
Thanks,
--jeff
- Original Message -
From: Michael Bacarella [EMAIL PROTECTED]
To: John Stanforth [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Saturday, August 31, 2002 6:58 AM
Subject: Re: 3.23.52 hitting system loads of 25+?
We
HEAP tables can have more than one index, but indexes only match = and
!= -- so you can't use , , etc... Also, comparisons only match
the entire index, not the left-most prefix like MyISAM tables. There are a
few other quirks, also:
http://www.mysql.com/doc/en/HEAP.html
--jeff
On Sunday 18
Your /tmp directory is only accessible by root? What distribution are you
using? /tmp is normally world readable/writeable. I would think you'd have
problems with other software, too, if this wasn't setup correctly.
--jeff
- Original Message -
From: Rob Lambden [EMAIL PROTECTED]
To:
You can't subtract dates like that. You need to use the built in date/time
functions. See the following:
http://www.mysql.com/doc/D/a/Date_calculations.html
http://www.mysql.com/doc/D/a/Date_and_time_functions.html
Your query would be something like this:
SELECT TO_DAYS(completion_date) -
Yeah, I'm getting these errors all the time on 3.23.51, also. I agree that
the slave_net_timeout variable is definitely not being paid any attention.
However, I'm not comfortable just upping the net_read_timeout without
knowing what other effects it might have.
--jeff
- Original Message
Is there a web-based interface for bug reporting? Or is there a way to use
the mysqlbug program when my database machine isn't connected to the
internet?
Thanks,
--jeff
- Original Message -
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Mark Hughes [EMAIL PROTECTED]
Cc: Nilesh Shah [EMAIL
I have replication running successfully between two Linux boxes running
3.23.51. However, I'm getting the following error very frequently (every
30 - 60 seconds) in my slave error log:
-
020703 15:41:09 Error reading packet from server: (server_errno=1159)
020703
What about using the skip-name-resolve option rather than skip-grant-tables?
I'm using the 3.23.51 binaries, also, and would appreciate knowing if this
makes a difference. Debian potato 2.95.2 is a known good compiler, too,
isn't it Jeremy? I may have to recompile my own anyway to change
I haven't tried it, yet, but this looks like an interesting alternative for
a try-before-you-buy method of selecting books:
http://safari.informit.com/mainhom.asp?home
You can subscribe to a list of books monthly and have full access to them
online. If you take a look at this, I would recommend
Does mounting ext3 MySQL data directories with the noatime option improve
performance? I ran across an article that said it really helped with ext2
and was wondering if the same benefits applied with ext3.
Thanks,
--jeff
-
I've got replication working between my two machines, however the err log
gets a lot of these errors:
---
020618 20:41:18 Error reading packet from server: (server_errno=1159)
020618 20:42:18 Slave: Failed reading log event, reconnecting to retry, log
'db1-bin.002'
both InnoDB and MyISAM keep also dynamically statistics about tables. In
most cases running ANALYZE TABLE does not bring any benefit in query
optimization.
Personally, I'm pretty shocked to hear you say this. I was under the
impression that running ANALYZE TABLE was a good thing and helped
php's pconnect opens a connection for every process that accesses the
database. The webserver processes don't share the connections, but later
accesses on the same process re-use the previous connection. It's very
likely that when you hit your php script, you're going to get a different
webserver
Try here:
http://www.mysql.com/doc/A/d/Adding_functions.html
--jeff
- Original Message -
From: a a [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, May 23, 2002 6:14 AM
Subject: new functions in MySQL ?
Hi,
Can we create and add new functions in MySQL with
windows NT. This
You should also get rid of the quotes, so MySQL doesn't have to convert from
string to integer:
SELECT team_id,name FROM team WHERE deleted != 1 ORDER BY 'name'
--jeff
- Original Message -
From: Steve Buehler [EMAIL PROTECTED]
To: Keith C. Ivey [EMAIL PROTECTED]; [EMAIL PROTECTED]
is why
I finally chose MySQL.
Thanks again,
--jeff
- Original Message -
From: mos [EMAIL PROTECTED]
To: Jeff Kilbride [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, May 15, 2002 8:14 AM
Subject: Re: Best book on MySQL
At 04:34 PM 5/14/2002, you wrote:
I'd be interested in how
: Jeff Kilbride [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 14, 2002 4:33 PM
To: Michael Grover; [EMAIL PROTECTED]
Subject: Re: Best book on MySQL
Hi Mike,
What did you think of Firebird vs. MySQL? I haven't seen any comparisons
by people who have used both.
Thanks,
--jeff
I don't think quoted identifiers are supported in 3.22.x.
--jeff
- Original Message -
From: Steve Edberg [EMAIL PROTECTED]
To: Elliot L. Tobin [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Monday, May 13, 2002 3:42 PM
Subject: Re: Problem with DDL
'when' is a reserved word:
ported several applications from Firebird 1.0 to MySQL 4.
The main things I ran into was little SQL Syntax differences, Stored
procedures, and
Triggers...
mike
Paul DuBois wrote:
At 13:40 -0700 5/11/02, Jeff Kilbride wrote:
MySQL
Paul DuBois
New Riders
Plus, he's here
The first timestamp field in any table is always updated when the row
changes:
http://www.mysql.com/doc/D/A/DATETIME.html
If you don't want this behavior, use a date or datetime field instead.
--jeff
- Original Message -
From: Damnish [EMAIL PROTECTED]
To: Edilson Vasconcelos de Melo
WHEN is listed as a reserved word:
http://www.mysql.com/doc/R/e/Reserved_words.html
--jeff
- Original Message -
From: Elliot L. Tobin [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, May 13, 2002 1:22 PM
Subject: Problem with DDL
I pulled this DDL from a MySQL server and am
MySQL
Paul DuBois
New Riders
Plus, he's here on the list answering questions... :)
--jeff
- Original Message -
From: Todd Cary [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, May 11, 2002 8:43 AM
Subject: Best book on MySQL
I need to convert a PHP app from using Interbase
MySQL has a column type for that, so you don't have to use an INT or BIGINT:
http://www.mysql.com/doc/S/E/SET.html
--jeff
- Original Message -
From: Harald Fuchs [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, May 03, 2002 4:19 AM
Subject: Re: Strategies for maintaining tables
Just make sure you call the last_insert_id() function before returning the
connection to the pool. If you're using the mm.mysql driver in Java, you can
cast the statement object to an org.gjt.mm.mysql.Statement object and use
it's getLastInsertID() method:
long lastInsertID =
That would give the correct result, but it would still physically count
all
the rows in the table which takes too long. This code will execute every
time a web page opens that has a grid. Some of the grid pages are quite
large, 1 million rows. The person who designed the web page originally
Fulltext search is based on relevance. If the words you're searching for
appear in over 50% of the rows, MySQL assumes they aren't relevant because
they occur too often. Try inserting more rows with different info in the
fields that have the fulltext index -- then try your search again.
--jeff
Try:
SELECT SUM(goals) AS leaders
FROM stats
GROUP BY [player_field]
ORDER BY leaders DESC
--jeff
- Original Message -
From: Alex Behrens [EMAIL PROTECTED]
To: MYSQL [EMAIL PROTECTED]
Sent: Tuesday, April 30, 2002 6:55 PM
Subject: select by total goals
hey guys,
I'm working on a
First, I would recommend upgrading to the latest release of mm.mysql --
which I think is 2.0.12. You can get the latest from sourceforge:
http://mmmysql.sourceforge.net/
2.0.4 is pretty old now.
--jeff
- Original Message -
From: Alan Jones [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent:
The other way to do this is with the DATE_ADD function:
select [columns]
where [date column] DATE_ADD(NOW(), INTERVAL -7 DAY)
--jeff
- Original Message -
From: Chuck PUP Payne [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Sunday, April 07, 2002 10:29 AM
Subject: Re:
Use a combination of the DATE_ADD function and the NOW function:
SELECT DATE_ADD(NOW(), INTERVAL 20 MINUTE)
--jeff
- Original Message -
From: Edilson Vasconcelos de Melo Junior [EMAIL PROTECTED]
To: MYSQL [EMAIL PROTECTED]
Sent: Monday, April 01, 2002 12:04 PM
Subject: DATE_ADD ?
http://www.php.net/manual/en/function.addslashes.php
http://www.php.net/manual/en/function.stripslashes.php
--jeff
- Original Message -
From: Alex Behrens [EMAIL PROTECTED]
To: Son Nguyen [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Sunday, March 31, 2002 11:35 AM
Subject: Re: problem
Do the tables on the slave machine have to be *exactly* the same as the
tables on the master? Is it possible to have different indexes on the slave?
I want to use my master as my transaction server and my slave as my
reporting server. In that respect, I'd like to use very few indexes on my
master
Just an aside, the like comparison is case insensitive -- so you don't
need the UCASE in this example.
like '%A%' will match both A and a.
--jeff
- Original Message -
From: Walter D. Funk [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, March 27, 2002 10:05 AM
Subject: How can
Actually, the column you're referring to is a TIMESTAMP. As the manual
states, the first TIMESTAMP field in any table is automatically updated
whenever a change occurs. If you don't want this value to change, you should
use a DATETIME field instead. However, keep in mind that you'll have to
Interesting comment in the manual, though, where someone says they tried
both methods and found the multiple statements to be faster than
SQL_CALC_FOUND_ROWS.
--jeff
- Original Message -
From: Roger Baklund [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Fletcher Sandbeck [EMAIL PROTECTED]
UPDATE sequence_table
SET sequence = LAST_INSERT_ID(sequence + 1)
Further calls to LAST_INSERT_ID on the same connection will return the value
sequence + 1. It's connection specific, so multiple connections can update
your sequence table without interfering with each other.
There's a great
Hi Theresa,
Your MySQL installation is most likely on the same box. Have you tried
localhost as the hostname in the connection string?
If that doesn't work, do you have shell access to this machine? If so, you
can also look inside the phpMyAdmin directory for a file called
config.inc.php. This
I have a table with 3 fields:
initDate datetime not null
id int unsigned not null
ipAddress int unsigned not null
I'm trying to find the number of distinct ipAddresses associated with a
particular id over a specified time frame -- for simplicity, let's say the
time frame is the last 60 minutes.
,
--jeff
- Original Message -
From: Jeff Kilbride [EMAIL PROTECTED]
To: MySQL [EMAIL PROTECTED]
Sent: Saturday, March 09, 2002 1:16 PM
Subject: SQL question -- can this be done?
I have a table with 3 fields:
initDate datetime not null
id int unsigned not null
ipAddress int unsigned
Thanks, Rob. Yeah, I just figured it out myself. I think I convinced myself
that it couldn't be that easy!
--jeff
- Original Message -
From: Rob [EMAIL PROTECTED]
To: Jeff Kilbride [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED]
Sent: Saturday, March 09, 2002 1:26 PM
Subject: Re: SQL
The list has an automatic filter that blocks any message that doesn't
contain either sql or query in it. Pain in the butt sometimes -- that's
why you'll see sql query at the bottom of a lot of people's emails, or in
their signature.
--jeff
- Original Message -
From: Chuck PUP Payne
Ok, now *that's* funny!! :o)
Guess Blue World's dev team haven't read the White Paper...
--jeff
- Original Message -
From: James Cox [EMAIL PROTECTED]
To: Mysql [EMAIL PROTECTED]
Sent: Thursday, March 07, 2002 3:45 PM
Subject: FW: Re: [ANN] Blue World Announces Lasso vs. PHP White
You need to add the 'IN BOOLEAN MODE' modifier. Try:
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+database
+tutorial' IN BOOLEAN MODE);
and
SELECT * FROM articles WHERE MATCH (title,body) AGAINST
('+database -tutorial' IN BOOLEAN MODE);
--jeff
- Original Message -
From:
If your condition that the status field only contains two values holds true,
then there should be no difference in the two queries you have below -- that
I can see, unless someone can prove me blind...
Are you *absolutely* sure none of the columns contain extra spaces, nulls,
etc...? You might
It's probably easier to resync the whole database to the laptop, than try to
do two-way updates -- unless it's very hard to copy the datafiles from the
central database.
You can use the binary logging facility of MySQL to keep track of changes on
the laptop and then update those changes to the
First of all, are you sure MySQL is running? The mysql.sock file only
appears when the server is running. Run the following command on the command
line to make sure:
ps awx | grep mysqld
You should see one line with safe_mysqld in it, and at least a few lines
with mysqld in them. If not, you
...
I may have posted the same question here, earlier. I'd appreciate any input.
Thanks,
--jeff
- Original Message -
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Jeff Kilbride [EMAIL PROTECTED]
Cc: MySQL [EMAIL PROTECTED]
Sent: Saturday, March 02, 2002 7:11 PM
Subject: Re: InnoDB
Actually, I've been looking pretty closely at a couple of other tables I
wanted to implement as HEAP tables -- and I didn't even think about moving
my little lookup tables into HEAP. :)
Thanks!
--jeff
- Original Message -
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Jeff Kilbride [EMAIL
Have you tried running these from the command line mysql client, to see what
may be up? Maybe you have a variable in your code that's not getting
re-initialized properly.
If you get the same results from the mysql client, it might be time to run
myisamchk on your table. (or just dump the data,
If your server's not running, there won't be a mysql.sock file. It's created
when the server starts and disappears when the server stops. It's not really
a file in the regular sense -- it's a unix socket. So, the server does not
need it to start, it creates it and all the other command line
it anywhere. It seems like this combination would be the best of both
worlds. Am I missing something?
Thanks,
--jeff
- Original Message -
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Jeff Kilbride [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Saturday, March 02, 2002 8:29 PM
Subject: Re: Re
PROTECTED]
To: Jeff Kilbride [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Saturday, March 02, 2002 10:03 PM
Subject: Re: Re-baselining replication slaves?
On Sat, Mar 02, 2002 at 09:51:12PM -0800, Jeff Kilbride wrote:
If you cover stuff like this in your book, I'll definitely buy it.
:)
Now
Hi Jeremy,
Just out of curiosity, what kind of tables are you using on your production
system? MyISAM, InnoDB, a mix of the two, etc...
Thanks,
--jeff
- Original Message -
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Guy Davis [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Friday, March
signature, so I'm picking your brain! :)
- Original Message -
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Jeff Kilbride [EMAIL PROTECTED]
Cc: MySQL [EMAIL PROTECTED]
Sent: Friday, March 01, 2002 2:15 PM
Subject: Re: Re-baselining replication slaves?
On Fri, Mar 01, 2002 at 01:21:52PM
Yep. Check out the last_insert_id function that takes an argument:
create table sequence (id int not null);
insert into sequence values (0);
update sequence set id=last_insert_id(id+1);
select last_insert_id();
Repeat the last 2 lines a few times and you'll see that last_insert_id()
returns the
Hi Egor,
Thanks for the reply. Does it actually hurt to index them on lower volumes
of traffic? Or is it neglible?
--jeff
- Original Message -
From: Egor Egorov [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, February 28, 2002 8:40 AM
Subject: Lookup tables and indexing
that are currently valid.
On Wednesday, February 27, 2002, at 07:31 PM, Jeff Kilbride wrote:
Why not put a flag variable (tinyint or enum) in your ABA table and
instead
of deleting the records, just mark them as no longer valid?
--jeff
- Original Message -
From: David Felio [EMAIL
InnoDB doesn't support the CASCADE functionality of foreign keys, so it's
possible deleting the key from the parent table won't have any effect on
existing transactions in the child. It may only prevent new records from
being inserted with that key -- which is essentially what you want. Most DBs
27, 2002, at 01:37 PM, Jeff Kilbride wrote:
InnoDB doesn't support the CASCADE functionality of foreign keys, so
it's
possible deleting the key from the parent table won't have any effect on
existing transactions in the child. It may only prevent new records from
being inserted
Is there a rule of thumb for small tables and whether they should be
indexed? I have several small, two column lookup tables with few rows
(100-300) and some very small tables ( 10 rows). I don't plan on indexing
the very small ones, but what about the others?
Thanks,
--jeff
sql,query
Why don't my replies go to the list, instead of the person posting the
message? Is this the way it's supposed to be on this list? Here's one I sent
earlier that didn't make it to the list...
-
I'm going to be implementing a keyword search pretty soon myself, so
Sure. Use ALTER TABLE to change the structure of your table and add the
datetime field:
http://www.mysql.com/doc/A/L/ALTER_TABLE.html
Then, use something like:
UPDATE myTable SET [datetime field] = [timestamp field];
Keep in mind that this will automatically update all the timestamp fields to
I'm about to try a full text index in a very similar situation, which has
the potential to grow fairly big. I'd also be interested in hearing how
MySQL's full text index works for your large dataset.
Thanks,
--jeff
- Original Message -
From: Luke Muszkiewicz [EMAIL PROTECTED]
To: [EMAIL
What does this quote from the manual mean:
If you do not have any GRANT statements done, MySQL will optimise the
permission checking somewhat. So if you have a very high volume it may be
worth the time to avoid grants. Otherwise more permission check results in a
larger overhead.
By not having
Hi All,
Let's say I'm creating an affiliate program to track surfer clicks to sales.
I have a question on indexing that's always bothered me. I'm hoping an index
guru can help me out. Here's an example click-recording table:
CREATE TABLE clicks (
clickID int unsigned NOT NULL auto_increment,
Can I have different indexes on the tables in my master and slave? I
want to use the slave strictly for reporting and the master for inserts, so
I'd like to index the crap out of the slave and leave the master relatively
index free.
Will that work?
Thanks,
--jeff
P.S. -- how 'bout adding some
mysql your_script.sql
or
cat your_script.sql | mysql
This assumes you have your username and password set up in your my.cnf or
.my.cnf (home directory). If not, add the -u[username] -p[password]
switches.
--jeff
- Original Message -
From: Chetan Lavti [EMAIL PROTECTED]
To: [EMAIL
Are there any open source ERD programs for MySQL for doing basic data
modelling? I tried myERD at sourceforge and it couldn't parse the sql dump
from my database. It died with a parse exception on the unsigned attribute
for my int columns.
Any other choices out there?
Thanks,
--jeff
For reporting purposes, I usually use a wide index across all the fields
that are relevant to creating the reports. So, for example, my sales table
has this type of data:
sale_id
sale_date
salesperson_id
product_id
referral_id
[other sales data]
sale_id is an auto_incrementing primary key.
or
not?
Thanks,
--jeff
- Original Message -
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Jeff Kilbride [EMAIL PROTECTED]
Cc: MySQL [EMAIL PROTECTED]
Sent: Thursday, February 21, 2002 11:19 PM
Subject: Re: Wide Indexes
On Thu, Feb 21, 2002 at 08:13:51PM -0800, Jeff Kilbride wrote:
For reporting
100 matches
Mail list logo
