Hi Wagner,
Yes! Analyze solves the situation in a moment.
(14:21:09) [borrame] > alter table creditLine discard tablespace;
Query OK, 0 rows affected (0.41 sec)
(14:21:21) [borrame] > alter table creditLine import tablespace;
Query OK, 0 rows affected (23.48 sec)
(14:24:55) [borrame] > a
Did you check if an ANALYZE TABLE is enough in this case?
--
Wagner Bianchi
Mobile: +55.31.8654.9510
> Em 10/10/2014, às 09:06, Ruben Cardenal escreveu:
>
>
>
> Hi,
>
> I have this problem among several different instaces of 5.6.20. I take
> all the steps as stated in
> http://dev.mysql.com
Nunzio Daveri,
Joerg Bruehe gave you a lot of good tips to try and speed things up.
A few hundred queries per second seem to be a relatively small number to
cause the server to crawl. I don't have the rest of your thread, but can
you publish some of the slow queries (see Slow Query Log) an
Thanks again :-)
Nunzio
From: Joerg Bruehe
To: Nunzio Daveri ; mysQL General List
Sent: Fri, July 30, 2010 1:31:54 PM
Subject: Re: Indexes larger than RAM (was: Do you know who can answer this
question I posted yesterday please?)
Hi!
I am no InnoDB and
Hi!
I am no InnoDB and tuning expert, so I had intended to stay away from
this question. Ok, I'll give some general remarks:
Nunzio Daveri schrieb:
> [[...]]
>
> All, I was running slamdb against one of our QA boxes and noticed that the
> innodb database is 190Gb in size BUT the worrying issu
There is a huge difference !!!
When You Load a Table with a Primary Key, the Primary get built automatically.
Not even ALTER TABLE DISABLE KEYS has an effect since it disables
non-unique indexes. Hence, loading the table is a one-pass operation.
In contrast, loading a table with two non-uniqu
You need do SHOW INDEXES FROM ;
You will see a column called Cardinlaity (another word for a unique count).
This tells you how many distinct values exists at the given Seq_in_index for
the index.
If the Cardinality of an index is low in relation to the number of rows in the
table,
then MySQL m
Hiep Nguyen wrote:
i believe "date" is a reserved word for mysql. don't use reserved word
for the column name. it's confused.
Using reserved words may be confusing, but it is fine as long as you put
them between backticks, as I'm sure the OP is aware of.
On Tue, 20 Mar 2007, Peter wrot
i believe "date" is a reserved word for mysql. don't use reserved word for
the column name. it's confused.
On Tue, 20 Mar 2007, Peter wrote:
Hello,
I have a a large a table which a field called date, type date.
When I select a smaller range e.g 3 months system uses the index 'date'.
That
Sebastian wrote:
> is it ok to add index after the tables and data are already built, or is
> it better to create the index before data gets inserted?
Michael Stassen wrote:
> It's probably better to create the indexes up front (assuming you know in
> advance which ones will be needed), but I t
Hello.
Right, I agree with you for MyISAM tables, however, in my opinion,
for InnoDB indexes should be created before populating the table
(the link below related to LOAD DATA INFILE statement, but I think,
the same applies for INSERT as well):
http://lists.mysql.com/mysql/181445
S
Michael Stassen wrote:
Sebastian wrote:
I read the manual on indexes but i am a little confused how i should
build the type of index i need.
i have this structure:
| id | item | type
|| 3 || 23 || news
|| 4 || 46 || faqs
|| 5 || 23 || news
|| 6 || 23 || news
query:
Some sections of the manual seem to indicate that it's better to
create indexes after data population, rather than before. See section
7.2.14, "Speed of INSERT Statements". The general procedure there is
to load the data using LOAD DATA INFILE, and then use myisamchk. "This
creates the index tree
Sebastian wrote:
i forgot to add another question,
is it ok to add index after the tables and data are already built, or is
it better to create the index before data gets inserted?
It's probably better to create the indexes up front (assuming you know in
advance which ones will be needed),
Sebastian <[EMAIL PROTECTED]> wrote on 08/04/2005 01:31:13 PM:
> i forgot to add another question,
>
> is it ok to add index after the tables and data are already built, or is
> it better to create the index before data gets inserted?
>
> Sebastian wrote:
>
> > I read the manual on indexes bu
Sebastian wrote:
I read the manual on indexes but i am a little confused how i should
build the type of index i need.
i have this structure:
| id | item | type
|| 3 || 23 || news
|| 4 || 46 || faqs
|| 5 || 23 || news
|| 6 || 23 || news
query:
SELECT id, item, type .
i forgot to add another question,
is it ok to add index after the tables and data are already built, or is
it better to create the index before data gets inserted?
Sebastian wrote:
I read the manual on indexes but i am a little confused how i should
build the type of index i need.
i have t
A Z <[EMAIL PROTECTED]> wrote on 02/04/2005 07:21:35 AM:
>
> MySql 4.0.14
>
> I understand that the most effective way of speeding
> up SELECT statements is to have column level indexes.
> Are there any other level indexes exist? I have been
> told that there are but I have no idea, looked thr
OK, thanks. I've reported this as a bug, and another, below. I must
admit, this production release seems flakey:
http://bugs.mysql.com/bug.php?id=6447
On Fri, 2004-11-05 at 04:03, Haitao Jiang wrote:
> Mark
>
> It is a known problem with this feature. So far I am not aware of any
> solution to it
Mark
It is a known problem with this feature. So far I am not aware of any
solution to it. Just want to let you know that you are not alone
having this problem.
Haitao
On Fri, 05 Nov 2004 02:16:49 +, Mark Maunder <[EMAIL PROTECTED]> wrote:
> I keep getting this error when trying to preload
Yes. Those keys are redundant.
-Original Message-
From: Cemal Dalar
To: Group MySQL List
Sent: 8/13/04 7:12 AM
Subject: Indexes
mysql> show index from urun;
+---++--+--+-+--
-+-+--++--+--
Tel: 1 773 975-1115
Fax: 1 773 913-0970
- Original Message -
From: "Dathan Vance Pattishall" <[EMAIL PROTECTED]>
Sent: Monday, May 17, 2004 12:09 PM
Subject: RE: Indexes ignored when using SELECT foo FROM a, b?
> Use force index to force the index lookup on foo_id
>
Use force index to force the index lookup on foo_id
If that doesn't work try analyze table on that table and run the explain
again.
The OR will not allow you to use a compound index but the primary key or 1st
key-foo_id should be used. I just noticed that your table definition foo_id
is not defin
Hmmm... I fixed the problem but I still don't know what caused it. That same query now
produces the below EXPLAIN result and now
runs 70% faster.
I ran OPTIMIZE TABLE on Spells and Classes, that made Spl rows 1, then I forced the
Cls table to use the Object index and it worked.
Now, I can't get
At 5:29 -0400 9/10/03, Andrew Kuebler wrote:
If I:
ADD COLUMN Text (BLOB)
And then
INDEX (Text(10))
Will that index be beneficial if I am search for 10 characters only or
anything 10 characters or less? Thanks in advance!
If those characters occur at the beginning of column values, yes.
--
Paul D
Here's the full query I'm trying to do:
explain select
users.status,
users.oid as oid,
users.type as type,
users.exclusive as exclusive,
userse.o_initials as exclusive_initials,
users.name_processed as name_processed,
users.o_company as o_company,
unix_timestamp(users.created) as c
In the last episode (Jun 01), [EMAIL PROTECTED] said:
> i have one multiple column fulltext index for 3 columns i join in a
> fulltext match statement
Fulltext indexes are different from regular indexes. I don't think it
matters what order the columns are in.
--
Dan Nelson
[EMAI
i have one multiple column fulltext index for 3 columns i join in a
fulltext match statement
> In the last episode (Jun 01), Daniel Rossi said:
>> hi just reading up on optimising indexes, does it matter what order u
>> set your indexes ?
>>
>> The first index part should be the most used column.
In the last episode (Jun 01), Daniel Rossi said:
> hi just reading up on optimising indexes, does it matter what order u
> set your indexes ?
>
> The first index part should be the most used column. If you are
> always using many columns, you should use the column with more
> duplicates first to g
At 4:41 + 3/6/03, Donna Robinson wrote:
Filter-fodder: mysql, query
Hi,
Is this a very hard question or an absurdly simple one? I posted it days ago,
and no-one has responded! Please can someone help?
How can I avoid "Using temporary, "Using filesort"
when creating indices when my selects
On Tuesday 04 March 2003 09:50, Donna Robinson wrote:
> How can I avoid "Using temporary, "Using filesort"
> when creating indices when my selects MUST be ordered?
Please, check chapter "5 MySQL Optimisation" of the manual. You can find cases
when MySQL uses indexes and when it doesn't use them:
At 14:12 -0500 12/20/02, Andrew Kuebler wrote:
I have the following table; I renamed the columns a & b for easy
reading:
ID INT(9) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
aID INT(9),
bID MEDIUMINT(7),
UNIQUE ab (aID, bID)
INDEX b(bID)
As far as I understand the UNIQUE INDEX also provides the index
Hello Egor,
I am not sure how large the table is, since I cannot access it now. I
would guess that it has at least 1 million rows, and maybe more.
It is a MyISAM table type. I probably terminated the create index command
after about 1 hour. Afterwards, when I type 'use database_name' on that
David,
Thursday, October 24, 2002, 11:27:30 AM, you wrote:
DC> I am using mySQL version 3.23.52 and I have tried to create an index on a
DC> large table. This was taking a very long time and so the command was
DC> terminated. Now the database seems to be corrupted, or at least
DC> inaccessible.
David Churches wrote:
> Hi,
>
> I am using mySQL version 3.23.52 and I have tried to create an index on a
> large table. This was taking a very long time and so the command was
> terminated. Now the database seems to be corrupted, or at least
> inaccessible. Is there anything I can do?
>
> I have
On Tue, 2002-06-18 at 13:48, Alexander Burbello wrote:
> I would like to know if mysql have any resources to
> know when I execute any sql (eg. select) and I would
> like to know what index was used, like SQL server.
See EXPLAIN at http://www.mysql.com/doc/E/X/EXPLAIN.html
--
__ ___ _
DELETE FROM mytable WHERE 1;
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]]
Sent: Saturday, February 09, 2002 4:50 PM
To: [EMAIL PROTECTED]
Subject: Re: Re: Indexes on UPDATE/DELETE
sql,query
At 15:35 -0700 2/9/02, Andrei Cojocaru wrote:
>Also, how do I reset an auto-
sql,query
At 15:35 -0700 2/9/02, Andrei Cojocaru wrote:
>Also, how do I reset an auto-increment value back to 1 (with all
>rows being already deleted of course).
ALTER TABLE tbl_name AUTO_INCREMENT = 1;
>
>Thanks for your help in advance.
---
sql,query
At 15:35 -0700 2/9/02, Andrei Cojocaru wrote:
>Also, how do I reset an auto-increment value back to 1 (with all
>rows being already deleted of course).
ALTER TABLE tbl_name AUTO_INCREMENT = 1;
>
>Thanks for your help in advance.
---
Goran Krajacic writes:
> I wonder if mysql uses indexes in temporary tables(when making a join on
> temp tables etc.)?
>
>
MySQL treats temp tables same as normal ones, except when the thread
ends ...
--
Regards,
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevi
On Mon, Sep 10, 2001 at 09:13:06AM -0700, PR wrote:
>
> And are there future plans for indexing on desc order?
This is already fixed in MySQL 4.0.
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
MySQL 3
Scott Chamberlain wrote:
>
> When I try to create an index on any table with greater than about 200,000
> records, it hangs using show processlist shows the process to have died. I
> have even tried moving the data out of the table, creating the index on the
> empty table and moving the data back
Dennis,
Innobase (= InnoDB starting from version 3.23.37) allows you to have
an index on a column which may contain nulls. However, the ANSI SQL
specification requires that the columns in a primary key are declared
as not null. If your main key may can contain nulls, do not define
a primary key a
> "DG" == Dennis Gearon <[EMAIL PROTECTED]> writes:
DG> Is it true no indexing on Nulls In the Innobase database tables and
DG> MyIASM
DG> tables?
Dunnow about Innobase tables, but MyISAM tables let you have indexes
on nullable columns. ISAM did not.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Hi.
You didn't mention your MySQL version. I assume some 3.22.x?
This 30% reatio is based on empirical data, and on your system, it
seems to be not ideal.
On Sat, Mar 31, 2001 at 09:50:57PM -, [EMAIL PROTECTED] wrote:
[...]
> mysql> show index from accounting;
> +++-
For some strange reason, all HEAP table indexes only process WHERE clauses
with = and <=> operators ONLY. There's nothing you can do about it. I'm
pretty sure of that because I remember the MySQL mentions something like
this about HEAP tables.
-Original Message-
From: Jeff S Wheeler [mail
46 matches
Mail list logo
