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 my...@ruben.cn escreveu:
Hi,
I have this problem among several different instaces of 5.6.20. I take
all the steps as stated in
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]
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 issue
Thanks again :-)
Nunzio
From: Joerg Bruehe joerg.bru...@oracle.com
To: Nunzio Daveri nunziodav...@yahoo.com; mysQL General List
mysql@lists.mysql.com
Sent: Fri, July 30, 2010 1:31:54 PM
Subject: Re: Indexes larger than RAM (was: Do you know who can answer
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)
There is a huge difference !!!
When You Load a Table with a Primary Key, the Primary get built automatically.
Not even ALTER TABLE tbl-name 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
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
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
You need do SHOW INDEXES FROM tbl-name;
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,
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
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
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
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
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 but i am a
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),
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
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
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 through
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 a
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.
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;
+---++--+--+-+--
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
- 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
If that doesn't work try analyze table on that table and run
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
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
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
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 get
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. If you
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
[EMAIL
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
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 tried
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. Is
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
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
--
__ ___ ___
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-increment
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 Milivojevic
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
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 in
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
"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.
--
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
45 matches
Mail list logo
