Does splitting a large table (20 Million rows) growing at 5 million or more
a month into smaller tables improve performance given that the table can be
split in a logical way such that 95% queries don't need to look at data
spanning across the split tables
Table Description:
20 Million
Hi,
i'm testing 4.1.05, executed the mysql_fix_privilege_tables script but
have to use old clients (3.51.06) to connect to mysql. For this reason
I inserted the old-passwords parameter into my.cnf (I'm calling several
mysql-daemons with different versions using a single script, so I don't
Does application validation constitute all that is
needed for qualifying data prior to an insertion in
the database ?
Translated, If I have checked via my forms validation
things like required fields, character input, etc,
are there still checks through MySQL before the insert
happens ?
Thank
In article [EMAIL PROTECTED],
Stuart Felenstein [EMAIL PROTECTED] writes:
How am I keeping transactions open ? Since I don't
want to do a transaction till the very end. All I'm
doing is bringing the data to last stage. After it's
all been collected.
You don't keep transactions open. You
In article [EMAIL PROTECTED],
Morten Egan [EMAIL PROTECTED] writes:
Well, it might not be SQL standard, but most databases out there allow
you to use the alias in your where clauses. It helps make the sql more
readable, and it shouldn't be that hard to add this feature to the
parser, so it
In article [EMAIL PROTECTED],
Laszlo Thoth [EMAIL PROTECTED] writes:
I'm trying to create a single UPDATE query to deal with the following problem:
==
-- I've got two tables:
CREATE TABLE `banannas` (
`owner` varchar(15) NOT
The way I've found to be the most 100% safe for me is to get my failover
system to completely stop the failed master. The new master will stay master
until I fix the problem on the failed, there's no further interventions from
my failover system. Then I repair, resync etc, then I put my main
I have successfully used this technique on a RedHat server. (I have
also seen it fail on Mac OSX running VirtualPC.)
What are you using to access MySQL? The command line? (Make sure
it's configured to use port 3306 and not looking for a local socket
file.)
What are the errors given? Can you
Harold,
Yes that information is available dynamically as you described. However, I
can think of at least two situations where what he wants to do is not only
useful but an excellent optimization.
First, he could be calculating some kind of static reporting. These are
reports that are
Absolutely!
Smaller tables = smaller indexes. Smaller indexes also mean faster
look-ups and faster record inserts. You could eventually drop indexes on
the older tables, saving disk space (by comparison, you can't index only
part of a table). Once a table becomes so old that no updates will
I want to create a simple three field form for querying my db could someone
point me in the right direction to make a start?
Andrew
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What you have so far looks good, but what I learned from doing my ecomm
project was that it is beneficial to make a separate table for anything
and everything that you might have more than one of... Addresses, phone
numbers, and email addresses are all great candidates for breaking out
into
In article [EMAIL PROTECTED],
[EMAIL PROTECTED] writes:
Harold,
Yes that information is available dynamically as you described. However, I
can think of at least two situations where what he wants to do is not only
useful but an excellent optimization.
I've done denormalizations like that
Is there a way to tell mysqldump to dump all tables in
the specified database except certain specified
tables?
Like maybe: mysqldump dbname !exclude_this_table
data.sql?
I have about 200 tables in my database and I only need
to back-up about 98% of them. The other 2% are HUGE
temporary search
At 09:06 AM 10/12/2004, you wrote:
Absolutely!
Smaller tables = smaller indexes. Smaller indexes also mean faster
look-ups and faster record inserts. You could eventually drop indexes on
the older tables, saving disk space (by comparison, you can't index only
part of a table). Once a table
If you need the table definitions as part of your dump, you could truncate
those tables right before you run mysqldump then restore the data right
after you finish.
If you can get rid of them during the dump, then: drop your temp tables,
do a full database backup, re-create your tables.
If
Justin Smith mailto:[EMAIL PROTECTED]
on Tuesday, October 12, 2004 8:48 AM said:
What you have so far looks good, but what I learned from doing my
ecomm project was that it is beneficial to make a separate table for
anything and everything that you might have more than one of...
Chris W. Parker wrote:
interesting you say that because i was going to do this same thing
except not as completely as i probably should (which i think is what you
are suggesting). what i mean is, my extra table of addresses was going
to be merely shipping addresses for the customer and nothing
Quoting [EMAIL PROTECTED]:
Why would you want to do that? bananacount is something you can
calculate with a LEFT JOIN and a GROUP BY, so storing it in the DB
would break normalization.
This would also the first step in creating your own OLAP cube. For each
statistic, you save yourself a
You were wondering about a separate phone number table?
Most phone numbers (esp. cell phones and home phones) belong to only one
person. If that were your only data, then creating a new table may not
make sense. However, dozens of people can share a common number (like in a
large office. Each
Hi,
I need to messure the time needs of my procedures in MySQL. How can I
reach a messurement of time intervals that include milliseconds, written
in MySQL SQL statements?
Thanks for any ideas,
Thomas
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To
Hi -
I'm seeing these error/warning messages on my replication slave:
[ERROR] Slave: load data infile on table 'DeviceItem' at log position
38844696 in log 'db1-bin.01' produced 6 warning(s). Default database:
'senvidsysdb'
I was wondering if anyone has seen similar messages.
I have
Good, you recognize the need to perform two separate aggregates (GROUP
BYs) and compare the separate results... In fact, you may need two temp
tables so that you can (INNER / LEFT) JOIN one to the other. (Yes, I can
think of a query that works without the second temp table but I think it's
Hello, im new in this I have 2 related
tables, how can I see same data from main table ( simple where clause ) and if
exists data from related table show it.confused??
Main table: Employee: id, name,sex,age
Related table: Contact:id,telephone,employee
I want to see all female
Quoting [EMAIL PROTECTED]:
Good, you recognize the need to perform two separate aggregates (GROUP
BYs) and compare the separate results... In fact, you may need two temp
tables so that you can (INNER / LEFT) JOIN one to the other. (Yes, I can
think of a query that works without the second
I usually prefer to work off of the results of SHOW CREATE TABLE
statements but I think you gave me enough information to make a guess at a
query.
SELECT e.name, c.telephone
From Employee e
INNER JOIN Contact c
on e.id = c.employee
WHERE e.sex = 'F'
I assumed that the employee field on
I'd like to have the data store on a netapp NFS server.
One writer mysql machine, with external-locking, and one (or more)
machines that do read only queries.
Will the read-only machines get consistent data from their queries?
The docs say only Make it easy for yourself: Forget about sharing a
Answers interspersed...
Laszlo Thoth [EMAIL PROTECTED] wrote on 10/12/2004 01:46:08 PM:
Quoting [EMAIL PROTECTED]:
Good, you recognize the need to perform two separate aggregates (GROUP
BYs) and compare the separate results... In fact, you may need two
temp
tables so that you can
If you include an ORDER BY clause, and the contents of the table are not
modified, then yes. Unexpected insertion of a row in the middle of
the table (as ordered by your ORDER BY clause) may cause a row to be
seen twice (if it happens at an index below the point your queries have
reached) as it
I have been trying to explain my question, I'm trying to detail it as
exactly as I can, I can not tell you how much this forum has helped with
my project. I hope I am clear, he's some invaluable SQL i got from a
poster here, it's what I'm currently playing with as my
search/query[w/php]:
$query =
Miguel,
No subquery needed
SELECT e.name, c.telephone
FROM employee e
LEFT JOIN contact c ON c.id = e.id
WHERE e.sex = 'F'
LEFT JOIN means there does not have to be a matching contact row to
find an employee row, but if there is a matching row, the data will be
returned.
I just guessed at what
The query cache is based on a result set size, not table size. A query
returning one row from a 100 million row table can be cached just as
easily as a row returned from a 10 row table. The difference being
modification frequency. Every time a table is modified
(update/detele/insert/replace) the
It means totally read only. File locking in general is sketchy at
best. Over NFS it's a train wreck.
-Eric
On Tue, 12 Oct 2004 11:03:06 -0700, Steve Francis
[EMAIL PROTECTED] wrote:
I'd like to have the data store on a netapp NFS server.
One writer mysql machine, with external-locking, and
My response below
leegold [EMAIL PROTECTED] wrote on 10/12/2004 03:11:05 PM:
I have been trying to explain my question, I'm trying to detail it as
exactly as I can, I can not tell you how much this forum has helped with
my project. I hope I am clear, he's some invaluable SQL i got from a
On Tue, 12 Oct 2004 16:06:59 -0400, [EMAIL PROTECTED] said:
My response below
Thank you for that comprehensive answer below - it helped me a lot.
leegold [EMAIL PROTECTED] wrote on 10/12/2004 03:11:05 PM:
I have been trying to explain my question, I'm trying to detail it as
Hi,
I have MySql running on Redhat 9, and I am trying to create a New
Database.
When I issue the command:
mysqladmin -u root ver
Result:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)'
Command:
[EMAIL PROTECTED]
I have a table that contains a item_id field (non unique index) id field and
a date field.
How would you go about selecting rows from the table (single row for each
item_id with the earliest date field for that item_id).
If I use group by item_id the date field will be whatever the first date
[snip]
I have a table that contains a item_id field (non unique index) id field
and
a date field.
How would you go about selecting rows from the table (single row for
each
item_id with the earliest date field for that item_id).
If I use group by item_id the date field will be whatever the
thank you, this does return the proper date field for the item_id, however
the rest of the fields of the records are still from the first record in the
table.
How would you make it so that the entire row is the one that contains the
earliest data field ?
- Original Message -
From:
[EMAIL PROTECTED] wrote:
I want to create a simple three field form for querying my db could
someone point me in the right direction to make a start?
Andrew
OpenOffice has data aware 'stuff' that you can make a form out of.
If you have MS Access, then it should also be powerful enough for a
There's something I'm not getting about how to put a SELECT restriction on a query
with an outer join. The following query:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual
FROM ApplicantStatus
INNER JOIN Applicants ON Applicants.AppID =
Hello Listfolk,
I have a table with a 'category_fk' column and a 'status' column. 'Status'
has but a tiny handful of known values, kinda like an enum.
I'd like to form a query that would give me results like:
category_fk | status=1 | status=2 | status=3 |
Also International Numbers
Have you seen any address books accomodating 3 digit Country Code??
Martin-
- Original Message -
From: [EMAIL PROTECTED]
To: Chris W. Parker [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, October 12, 2004 1:20 PM
Subject: RE: help with table structure
hello,
continuing my quest to build a better database, i'd like to ask a
question that i haven't been able to find an answer to. here is an
excerpt from an article on evolt
(http://www.evolt.org/article/Beginning_Database_Design_Part_I/18/27137/
): (and where i got the idea as well)
You'll
The answer depends on what version of MySQL you're using. If it's prior
to 4.1 then there is no good way (if any at all) to do it in a single query.
Prior to 4.1:
CREATE TEMPORARY TABLE tmptable SELECT item_id,MAX(date_field) as
date_field FROM table GROUP BY item_id;
SELECT tblone.* FROM
I have this query below than seems to be running really slow. I have
tried indexing key JOIN fields but it seems to have not helped. Any
pointers would be greatly appreciated.
(ver: MySQL 3.23.53) side note: seems to run fairly quick in 4.0.18
SELECT
dl.email as download_email, CONCAT(dl.first,
I've tried to find references to if there are any design flaws with using
multiple databases or not however was unable to locate anything (but I was
told by a previous co-worker that there were performance hits).
Are there any performance hits or design flaws by separating a large
database
I'm struggling with this, and am not sure what I'm doing wrong... There
are two tables in separate databases that have 6 fields in common. In
addition to the six columns in common between table_a and table_b, each
table has some additional fields that are not related. Table_a and table_b
48 matches
Mail list logo
