On Fri, June 17, 2011 07:11, Jerry Schwartz wrote:
>>-Original Message-
>
>
>>What I was really trying to figure out was why it takes me 4
hours to
>>add a new column to my 22-million-row table, and
whether a different
>>table design can avoid that problem.
That reply in the forum says
>-Original Message-
>What I was really trying to figure out was why it takes me 4 hours to
>add a new column to my 22-million-row table, and whether a different
>table design can avoid that problem. That reply in the forum says,
>"ALTER TABLE ... ADD COLUMN will always copy the entire ta
At 10:47 PM 6/16/2011, Johan De Meersman wrote:
- Original Message -
> From: "Bennett Haselton"
>
> Do you happen to know the answer to my other problem -- if I have
> TEXT and BLOB columns but all my other columns are fixed-length, can
> I still get the benefit of faster lookups resul
- Original Message -
> From: "Bennett Haselton"
>
> Do you happen to know the answer to my other problem -- if I have
> TEXT and BLOB columns but all my other columns are fixed-length, can
> I still get the benefit of faster lookups resulting from fixed-length
> rows, if each row just c
At 11:45 AM 6/14/2011, Johan De Meersman wrote:
- Original Message -
> From: "Bennett Haselton"
>
> modifications. (For example, the question I asked earlier about
> whether you can declare extra space at the end of each row that is
> "reserved for future columns".)
That question I c
- Original Message -
> From: "Bennett Haselton"
>
> modifications. (For example, the question I asked earlier about
> whether you can declare extra space at the end of each row that is
> "reserved for future columns".)
That question I can answer: you can't "reserve" space, but if you
At 05:46 AM 6/14/2011, Carlos Eduardo Caldi wrote:
Hello Bennett
On the Mysql developer site have a grate documentation, try the
links above.
http://dev.mysql.com/doc/refman/5.0/en/optimizing-database-structure.html
http://dev.mysql.com/doc/refman/5.0/en/data-size.html
Thanks, this gets me
les, would I?
>>
>> - Original Message - From: "Keith Murphy"
>> To:
>> Sent: Tuesday, January 26, 2010 11:06 AM
>> Subject: Re: optimization
>>
>>
>>
>> ♫
>> I would recommend the same to you about reading High Perf.
convert all of the spamassassin,
> drupal, and mediawiki tables to innodb too? Or just my own database? What
> about the mysql database itself? I wouldn't convert those tables, would I?
>
> - Original Message - From: "Keith Murphy"
> To:
> Sent: Tuesday
To:
Sent: Tuesday, January 26, 2010 11:06 AM
Subject: Re: optimization
♫
I would recommend the same to you about reading High Perf. MySQL as Baron,
et al wrote a great book about performance on MySQL. That being said, it has
been my experience that in 99% of client cases they don't really
♫
I would recommend the same to you about reading High Perf. MySQL as Baron,
et al wrote a great book about performance on MySQL. That being said, it has
been my experience that in 99% of client cases they don't really need to run
two different types of tables. If I were you, I would use InnoDB exc
Get yourself a copy of the book High Performance MySQL 2nd Edition
Publisher: O'Reilly Media; 2 edition (June 18, 2008) Language: English
ISBN-10: 0596101716 ISBN-13: 978-0596101718
Here is a brief preview of the first edition:
http://books.google.ca/books?id=iaCCQ13_zMIC&printsec=frontcover&d
From: "Jaime Crespo Rincón"
Sent: Monday, January 25, 2010 5:30 PM
2010/1/25 John G. Heim :
I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is
running the latest mysql-server from debian lenny (5.0.1). I have
databases
for drupal, moodle, spamassassin, horde3, and a small
2010/1/25 John G. Heim :
> I have a server with 16Gb of RAM and a dual-core 2Ghz processor. It is
> running the latest mysql-server from debian lenny (5.0.1). I have databases
> for drupal, moodle, spamassassin, horde3, and a small database for
> departmental stuff.
>
> The problem is that insert
.
Baroda - 390007
Gujarat, India
Personally I'm always ready to learn, although I do not always like being taught
From: Gavin Towey
To: Sudhir N ; Mysql
Sent: Tue, 15 December, 2009 2:16:53 AM
Subject: RE: Optimization suggestions
Id should probably
Id should probably be an auto_incrementing INT, if you still need a unique text
identifier, then I would make a separate field. Though my opinion isn't the
only way; there is much debate on natural vs. surrogate keys.
I would normalize "folderid" into a lookup in another table, and make folderi
After one very quick look, the index on folderid alone is unnecessary since
you have another index in which that field is the high-order field.
On Mon, Dec 14, 2009 at 12:31 PM, Sudhir N wrote:
> I have following table structure, I have to use merge storage engine.
> Please have a look, and prov
That's quite a query. You may not be able to optimize it well with
those nested selects. You may want to think about changing your query
around a little, perhaps joining pieces of data using whatever
programming language you're using on the front end. You have MySQL
doing a lot of work and
On 6/27/07, Dave G <[EMAIL PROTECTED]> wrote:
select payload_time,HEX(processed_data) from data__ProcessedDataFrames
where test_id=18 AND top_level_product_name="DataProduct" AND payload_time
> 11808.74704 AND payload_time < 1180564096.24967;
What I'm concerned about is with how much data I
I do, but I don't see any way around that with the data I have.
Dave G.
> Good Afternoon David
>
> sounds as if you have a number of non-unique indices (even possibly FTS!)
> slowing down queries..this should help you concentrate on the slower
> indices
> mysql>
> select TABLE_NAME,COLUMN_NAME,IN
I think I can do that:
I don't have any other indexes, just the keys.
mysql> show create table data__ProcessedDataFrames;
+---+--
Good Afternoon David
sounds as if you have a number of non-unique indices (even possibly FTS!)
slowing down queries..this should help you concentrate on the slower indices
mysql>
select TABLE_NAME,COLUMN_NAME,INDEX_NAME from INFORMATION_SCHEMA.STATISTICS
where NON_UNIQUE=1;
Anyone else?
Mart
On 6/27/07, Dave G <[EMAIL PROTECTED]> wrote:
Queries on this table when it gets large is slow as molasses. I'm
thinking about making a new table for anything with a different test_id
any opinions as to whether this is good or bad?
Hi Dave G.,
We need to know how:
a)How large the tabl
At 02:37 PM 8/1/2006, Cesar David Rodas Maldonado wrote:
Hello to all!
How can I optimize this query
select * from table where col1 = 5 && col2 = 15;
Put both fields in the same index index. The first index should be the
column with the least unique values (col1 by your example).
If I k
Eli,
>Example: I want to search on all the directories under 'd4' that
contain the word "music".
>I got several solutions, but not satisfying:
>A) Loop from 'd4' to sub-dirs in first level, and use buffer list for
next iterations
>when going deeper into levels. [not good: there can be many su
Please see my comments below :
On Thu, 2005-12-01 at 14:06 -0600, Ross Anderson wrote:
> I have read the suggestions for optimization for innodb however I'm
> curious if anyone can help me understand which buffers are common (thus
> used by innodb action) and which are specific to myiasm. The s
Hi!
Am Fr, den 15.04.2005 schrieb Fan, Wellington um 18:50:
> Hello all,
>
> I have a link table that relates 'Profiles' with 'Categories':
>
> CREATE TABLE lnk_profile_category (
> profile_fk int(11) NOT NULL default '0',
> category_fk int(11) NOT NULL default '0',
> PRIMARY KEY (catego
"Fan, Wellington" <[EMAIL PROTECTED]> wrote on 04/15/2005 12:50:07 PM:
> Hello all,
>
> I have a link table that relates 'Profiles' with 'Categories':
>
> CREATE TABLE lnk_profile_category (
> profile_fk int(11) NOT NULL default '0',
> category_fk int(11) NOT NULL default '0',
> PRIMARY KE
"Abdul Aziz" <[EMAIL PROTECTED]> wrote:
>
> How can we optimize MySQL queries, plz define easy method
> comparing as well as better performance for data retrieval.
>
The following chapter of the manual will be helful to you:
http://dev.mysql.com/doc/mysql/en/Query_Speed.html
--
For t
try using explain
>
>
> Hi All,
>
> How can we optimize MySQL queries, plz define easy method
> comparing as well as better performance for data retrieval.
>
> Regards:
> aaziz
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://l
On Wed, May 05, 2004 at 01:06:45PM -0400, Brent Baisley wrote:
> Basically, you can't, it's a limitation of the InnoDB format. If you
> change the table type to MyISAM, that query would be almost
> instantaneous. But you are probably using InnoDB for a reason, so you
> may be stuck if you want a
Brent Baisley wrote:
Basically, you can't, it's a limitation of the InnoDB format. If you
change the table type to MyISAM, that query would be almost
instantaneous. But you are probably using InnoDB for a reason, so you
may be stuck if you want a record count.
As Brent said, there is no way to o
Basically, you can't, it's a limitation of the InnoDB format. If you
change the table type to MyISAM, that query would be almost
instantaneous. But you are probably using InnoDB for a reason, so you
may be stuck if you want a record count.
On May 5, 2004, at 10:01 AM, Vladimir Romanovski wrote:
-2287
M: (713) 252-4688
-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
Sent: Monday, April 26, 2004 4:23 PM
To: Boyd E. Hemphill
Cc: [EMAIL PROTECTED]
Subject: Re: Optimization and the use of indexes
On Mon, Apr 26, 2004 at 05:17:45PM -0500, Boyd E. Hemphill wrote:
>
On Mon, Apr 26, 2004 at 05:17:45PM -0500, Boyd E. Hemphill wrote:
> I thought I heard at the conference that the optimizer only uses a one
> index per table in a query regardless of the number of indexes on the
> table.
>
> Is this true?
Yes.
The only exception is in a UNION, but that's best th
On Jan 12, 2004, at 08:09 AM, Douglas Sims wrote:
I think... you don't have an index on the "Incident" field itself,
just on (Date, Incident, Type, Task) which means that it concatenates
those fields and orders the result - thus this may be virtually
useless if you're looking for a specific inc
I think... you don't have an index on the "Incident" field itself, just
on (Date, Incident, Type, Task) which means that it concatenates those
fields and orders the result - thus this may be virtually useless if
you're looking for a specific incident within a large date range. Since
your query
Hello,
On Fri, Dec 05, 2003 at 12:02:05PM +0100, Martin Gillstr?m wrote:
>
> The table can look something like this:
> row_id INT PRIMARY KEY
> where_column SET('a','b','c')
> groupby_column VARCHAR(255)
> orderby_column DATE
> .. more rows that I need to fetch with the select.
> This is what I
D]>
To: "'Rodrigo Pérez'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, May 03, 2002 2:23 PM
Subject: RE: Optimization
> [snip]
> I want to optimize one of my php scripts to acess mysql only once. Is
there
> anyway to put these two queries together:
>
[snip]
I want to optimize one of my php scripts to acess mysql only once. Is there
anyway to put these two queries together:
SELECT COUNT(*) from TABLE where name="JOHN"
SELECT * from TABLE where name="JOHN" limit 10
[/snip]
try
select *,
sum(if(name = 'JOHN', 1, 0)) AS COUNT
from table
where n
, 2002 9:29 AM
Subject: Re: Optimization And Memory Size
>
> You have written the following:
>
> I have a mysql database table that is currently 1.5G in size with
well over
> a
> million records. It is running on a twin pentium 3 1G processor
machine with
> SuSE Linux version
You have written the following:
I have a mysql database table that is currently 1.5G in size with well over
a
million records. It is running on a twin pentium 3 1G processor machine with
SuSE Linux version 1.4.
Recently inserts have become VERY slow (several seconds). As I am adding
around
On Mon, Feb 18, 2002 at 01:52:41PM -0600, Melvin wrote:
> Hi:
> I'm trying to create an image database of about 130,000 records. Right
> now I have only the filenames in a CHAR Field (the records are of fixed
> size). I read those images a lot. Is it better to leave it this way or to
> move
Did you get an answer to this problem. I'm experiencing the same behavior.
Mike Wexler wrote:
> When I do
>
> EXPLAIN
> SELECT status.itemKey, status.auctionUrl,
> status.hideItem, status.auctionId, status.action,
> status.auctionHouse
> FROM auction.status, inventory.thisItem
> WHERE s
Hi!
I just forwarder your email about query caching
(Yes, I know that the query cache in MySQL 4.0 has solved this for you but...)
>> Hello all,
>>
>> I'm having real trouble trying to optimize MySQL cause I can't believe
>> that
>> MSSQL is faster.
>> My configurations are as follows:
>> MSSQ
ndeed caching DOES work.. Thanks a lot guys
-Original Message-
From: Fournier Jocelyn [Presence-PC] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 17, 2002 7:58 PM
To: Ioakim Spyros; [EMAIL PROTECTED]
Subject: Re: Optimization
Hi,
It's already done since MySQL 4.0.1.
Take a
The first thing that comes to mind is that you're running the databases on
different hardware and operating systems. I know the Linux kernel had some
SMP performance issues not too long ago, prompting IBM to rewrite portions
of the kernel and improving performance by (I think) 16-20%. Also, you're
Hi,
It's already done since MySQL 4.0.1.
Take a look here :
http://www.mysql.com/doc/Q/u/Query_Cache.html
Regards,
Jocelyn
- Original Message -
From: "Ioakim Spyros" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, January 17, 2002 6:52 PM
Subject: FW: Optimization
Hello all
MySQL uses only one index for a select, so it can't use an index
when there is an OR in the where clause.
Ask Bjoern Hansen wrote:
> Hi,
>
> I have a table with about 1.5M rows.
>
> 9 of the colums are varchar(9)'s.
>
> when I just select on one of them it goes fine, like:
>
>
>> explain se
Stefan Pinkert wrote:
> Examine that query with explain select... show that the indexes are not
> used. The keyfields are unsigned tiny int where every bit has a different
> meaning. Any clue how i can rewrite this query in a way where the indexes
> are
> used?
If MySQL thinks it will be faster
On Wed, May 02, 2001 at 08:40:10AM +0200, Warren van der Merwe wrote:
> Hi there
>
> Is there anything similar to this that will run on a Windows platform?
As luck would have it, yes. I managed to get mytop working on Windows
today. There are a few rough edges, but with about 20 minutes more
wor
nal Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
> ]On Behalf
> Of Jeremy Zawodny
> Sent: 02 May 2001 07:30
> To: Bryan Coon
> Cc: '[EMAIL PROTECTED]'
> Subject: Re: Optimization of MySQL
>
>
> On Tue, May 01, 2001 at 04:46:39PM -0700, Br
On Tue, May 01, 2001 at 04:46:39PM -0700, Bryan Coon wrote:
>
> Our database is large, and getting larger, with several tables
> approaching the 1gig mark. In addition, the database will be moving
> to a dedicated node on a beowulf cluster.
Cool... :-)
> For our users, we are not particulary i
53 matches
Mail list logo
