Hi, all
I have a mysql server that has been suffereing performance problems due to
disk fragmentation problems.
The server is on a red hat 7.3 box, and the tables in question are MyIsam.
The problem seems to be occuring because there are millions of inserts and
millions of deletes each week.
It seems odd to me that the data type Year, should only allow the limited
range of 1901-2155.
i understand that this is so that it will store in 1 byte, but whats the
point?
i wanted to use a year field for historical data, such as the year famous
people were born, such
as abe lincoln - 1809, but
Hi all, I have recently encountered some strange (to me) behaviour with an
auto
increment column. It resulted because of an oversight in table creation.
Here is some SQL statements that will illustrate my issue:
CREATE TABLE test (
ID INT AUTO_INCREMENT,
Name CHAR(30),
KEY (Name, ID)
I've bene trying to find info on how efficient queries are for the SET
datatype, specifically, how does
MySQL use indexes for SET column types.
I have looked in the O'Reilly MySQL Reference Manual, and at mysql.com and
have found effectively nothing on this subject.
For example, if i have a table
I'm working on a design for a database that is leaving me with some
normalization issues that I havent had to deal with before. For example, if
im trying to store
information about car dealerships I end up with the following situation:
I want to store the cars in stock at each physical
I think i have a damaged table in one of my databases.
I can do a SELECT count(*) from table
on every table in the db with no problem, except for 1
of them, the query just sits for a while.
So i assume this table is damaged. does anyone have any suggestions as to
what i can do about this?
tests
If i use GRANT and REVOKE to change user privileges, it is my understanding
that i don't have
to issue a FLUSH PRIVILEGES statement.
Is this correct?
Our sysadmin rebooted the MySQL server this morning, and there were some
privilege problems
afterward. I had to reset the privileges for some
How about
SELECT fieldname FROM table ORDER BY CAST(fieldname as SIGNED)
UNSIGNED could replace SIGNED, depending on your needs.
this works for me on Mysql 4.02 alpha on solaris
regards,
sean peters
[EMAIL PROTECTED]
- Original Message -
From: John Almberg [EMAIL PROTECTED]
To:
I've read a bit about using LAST_INSERT_ID(expr)
to set the next auto_increment value.
my question is, if i have 2 tables, like follows
CREATE TABLE A (
id int not null auto_increment primary key,
...
)type=MyISAM;
CREATE TABLE B (
id int not null auto_increment primary key,
...
)type=MyISAM;
If i need to create / drop indexes from MyISAM tables how does the blocking
work?
I need to mess with some indexes on a production database, and dont want
downtime.
Should i do this in the middle of the night, when hardly anyone is using the
system, or will the blocking be negligable?
thanks
Is there a way to see what temporary tables exist for the connection.
SHOW TABLES
doesnt list the temp tables for the connection. Is there some other command
i can issue?
thanks
sean peters
[EMAIL PROTECTED]
sql, query
-
If i use a replace query, and there isn't an error, then either
a new row was inserted, or an existing row was updated.
The primary key in the table i'm replacing to is defined as
mprid int not null auto_increment primary key
whether an insert or update actually occurs, i want to get the value
Does anyone have or know of any good books that discuss or teach normal
forms in great detail. I'd like to say that
im pretty good with 2NF, i'm looking for info on more advanced
normalizations.
A specific normalization question:
if i have three tables, call them A, B, C is it ever proper to
A problem I have with InnoDB tables, which forced me to use MyISAM tables in
a recent project, is as follows:
(I am running MySQL on Solaris, not that it matters much for this problem.)
With MyISAM tables, I can easily get different databases to reside on
separate physical drives.
The base
I was wondering about the Enum and Set column types in InnoDB tables.
I was able to create an InnoDB table with an enum column, but i
get errors when trying to insert values into that column.
I'm thinking that InnoDB doesnt support those data types, but the MySQL
interface to InnoDB may have
I am performing a query along the lines of the following:
SELECT DISTINCT property.Internal_ID
FROM property, owner_names
WHERE property.Internal_ID = owner_names.Internal_ID
AND [ other conditions ]
ORDER BY owner_names.Name
Without the order by clause this is a pretty quick query, but with
After many headaches, rantings, and emails, i have FINALLY figured out how
to get 64 indexes out
of MySQL tables.
There are 3 changes you need to make to the source code.
in file mysql_priv.h
change the line:typedef ulong key_map
to:typedef ulonglong key_map
in
Is there an upper limit on the number of indexes in InnoDB tables in MySQL?
I can't find anything about it in their documentation at www.innodb.com
I am designing a database where one table has around 70 keys so far.
yes, it's normal, and all of those keys are commonly searched on.
thanks
sean
I've read that it is possible to use 64 indexes in MyISAM tables in MySQL.
I haven't been able to find any documentation on how to actually do this
though.
any references to appropriate documentation would be greatly appreciated.
regards
Sean Peters
[EMAIL PROTECTED]
if you set it up so that the slaves occasionally update the master, you can
get around the
problem as follows.
For the *primary* table(s), when you update the master, get the data for 1
row to update from the
slave.
When you insert this row into the master, remove the auto_increment field
from
20 matches
Mail list logo