alter table and 'something wrong in indexes'?

Hi! I use mysql/innodb tables on aws services in a small table (about 2M records) I changed some columns datatypes from unsigned int to decimal and from float to decimal I didn't change anything about primary key or other indexes after the change (done without troubles), all my queries where

Indexes strangeness

Hi All, Can someone please fill me in as what I am seeing here... I have two identical tables, with identical indexes, having different records. Both tables have +- 15m records in it... mysql> EXPLAIN SELECT ArticleID, DateObtained, DateAccessed, TimesAccessed FROM IDXa ORDER BY DateAcces

Indexes issue importing tablespaces

changes to disk 2014-10-10 13:27:26 1657 [Note] InnoDB: Phase IV - Flush complete 2014-10-10 13:27:26 1657 [Note] InnoDB: borrame.creditLine autoinc value set to 87313435 After this, the indexes look empty: (13:27:26) [borrame] show index from creditLine

Re: Indexes issue importing tablespaces

[Note] InnoDB: borrame.creditLine autoinc value set to 87313435 After this, the indexes look empty: (13:27:26) [borrame] show index from creditLine

Re: Indexes issue importing tablespaces

- Flush complete 2014-10-10 13:27:26 1657 [Note] InnoDB: borrame.creditLine autoinc value set to 87313435 After this, the indexes look empty: (13:27:26) [borrame] show index from creditLine

Troubles with creating indexes on float columns on MyISAM tables in MySQL 5.6.15 and MySQL 5.6.14 running on FreeBSD 8.4

I got an interesting problem with creation of indexes on MyISAM tables in MySQL 5.6.15 and MySQL 5.6.14 running on FreeBSD 8.4 for float columns - I am not able to create indexes on these columns Indexes on all other columns work just fine The problem occur while I was loading data from MySQL

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

On 10/15/2012 7:15 PM, spameden wrote: Thanks a lot for all your comments! I did disable Query cache before testing with set query_cache_type=OFF for the current session. I will report this to the MySQL bugs site later. First. What are all of your logging settings? SHOW GLOBAL

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

Will do. mysql SHOW GLOBAL VARIABLES LIKE '%log%'; +-+-+ | Variable_name | Value | +-+-+ | back_log

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

your now() statement is getting executed for every row on the select. try ptting the phrase up front as in: set @ut= unix_timestamp(now()) and then use that in your statement. On 2012-10-16 8:42 AM, spameden spame...@gmail.com wrote: Will do. mysql SHOW GLOBAL VARIABLES LIKE '%log%';

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

Interesting thought, but I get the same result. # Query_time: 0.001769 Lock_time: 0.001236 Rows_sent: 0 Rows_examined: 0 use kannel; SET timestamp=1350413592; select * from send_sms FORCE INDEX (priority_time) where time=@ut order by priority limit 0,11; the MySQL i'm using is 5.5.28 from

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012/10/16 12:57 -0400, Michael Dykman your now() statement is getting executed for every row on the select. try ptting the phrase up front as in: set @ut= unix_timestamp(now()) and then use that in your statement. Quote: Functions that return the current date or time each are evaluated only

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

That's exactly what I thought when reading Michael's email, but tried anyways, thanks for clarification :) 2012/10/16 h...@tbbs.net 2012/10/16 12:57 -0400, Michael Dykman your now() statement is getting executed for every row on the select. try ptting the phrase up front as in: set @ut=

mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

| ++-+---+---+---+---+-+--+--+-+ And if both indexes created I do not have anymore this query in the slow-log. Of course If I disable log_queries_not_using_indexes I get none of the queries. So is it a bug inside Percona's implementation

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

| time_priority | priority_time | 12 | NULL | *22* | Using where | ++-+---+---+---+---+-+--+--+-+ And if both indexes created I do not have anymore this query in the slow-log. Of course If I disable

RE: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

over-sized. -Original Message- From: spameden [mailto:spame...@gmail.com] Sent: Monday, October 15, 2012 1:42 PM To: mysql@lists.mysql.com Subject: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order Hi, list. Sorry

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

: Monday, October 15, 2012 1:42 PM To: mysql@lists.mysql.com Subject: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order Hi, list. Sorry for the long subject, but I'm really interested in solving this and need a help: I've got

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

show variables like '%indexes%'; +---+---+ | Variable_name | Value | +---+---+ | log_queries_not_using_indexes | ON| +---+---+ 1 row in set (0.00 sec) If I turn it off - it's all fine

RE: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

the ORDER BY matches one of the indexes, it can avoid the sort and stop with the LIMIT. However, if most of the rows failed the WHERE clause, this could be the wrong choice. That is, it is hard for the optimizer to get a query like this right every time. To see what I mean, flip the inequality

RE: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

[mailto:spame...@gmail.com] Sent: Monday, October 15, 2012 3:29 PM To: Rick James Cc: mysql@lists.mysql.com Subject: Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order Sorry, forgot to say: mysql show variables like 'long_query_time

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

...@gmail.com] *Sent:* Monday, October 15, 2012 3:29 PM *To:* Rick James *Cc:* mysql@lists.mysql.com *Subject:* Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order ** ** Sorry, forgot to say: mysql show variables like 'long_query_time

RE: Are Single Column Indexes are sufficient

= '2012-09-07 08:08:34') AND t0.bean_type = 'ActionItems'; Change the `status` check to AND t0.status IN ('SUSPENDED', 'KILLED', 'RUNNING') Other compound indexes might work, but I guess this is the best: INDEX(bean_type, status, last_modified_time) -- in THIS order Note that I put

Are Single Column Indexes are sufficient

Hi all, Currently i am doing performance level tuning of some queries that are running very slow in my slow -query log. Below are the sample of some queries the cardinality of indexes :- --- Below queries take more than 15 minutes to complete on a table scd_table of size 7 GB SELECT t0.id, t0

Re: 回复： 回复： Why is creating indexes faster after inserting massive data rows?

On 05/07/2012 12:30 PM, Zhangzhigang wrote: Thanks, i thought about this answer in the past, and i appreciate your reply. How about the omelet? What's your method? -- RMA. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:

RE: 回复： 回复： Why is creating indexes faster after inserting massive data rows?

10x to 1000x performance improvement.) Should we discuss this? -Original Message- From: Karen Abgarian [mailto:a...@apple.com] Sent: Monday, May 07, 2012 8:37 PM To: mysql@lists.mysql.com Subject: Re: 回复： 回复： Why is creating indexes faster after inserting massive data rows

RE: 回复： Why is creating indexes faster after inserting massive data rows?

rows per second that you can insert – Terrible! Sortmerge is likely to average over 10,000. From: Zhangzhigang [mailto:zzgang_2...@yahoo.com.cn] Sent: Tuesday, May 08, 2012 9:13 PM To: Rick James Cc: mysql@lists.mysql.com Subject: 回复： Why is creating indexes faster after inserting massive data

Re: 回复： Why is creating indexes faster after inserting massive data rows?

: Zhangzhigang [mailto:zzgang_2...@yahoo.com.cn] Sent: Tuesday, May 08, 2012 9:13 PM To: Rick James Cc: mysql@lists.mysql.com Subject: 回复： Why is creating indexes faster after inserting massive data rows? James... * By doing all the indexes after building the table (or at least all the non

RE: 回复： Why is creating indexes faster after inserting massive data rows?

. ** ** *From:* Claudio Nanni [mailto:claudio.na...@gmail.com] *Sent:* Wednesday, May 09, 2012 8:34 AM *To:* Rick James *Cc:* Zhangzhigang; mysql@lists.mysql.com *Subject:* Re: 回复： Why is creating indexes faster after inserting massive data rows? ** ** This thread is going

回复： 回复： Why is creating indexes faster after inserting massive data rows?

： 2012年5月9日, 星期三, 下午 11:21 主题: RE: 回复： Why is creating indexes faster after inserting massive data rows? A BTree that is small enough to be cached in RAM can be quickly maintained.  Even the “block splits” are not too costly without the I/O.   A big file that needs sorting – bigger than can

回复： 回复： 回复： Why is creating indexes faster after inserting massive data rows?

复： 回复： Why is creating indexes faster after inserting massive data rows? Honestly, I did not understand that.  I did not say anything about being complicated.  What does mysql not use, caching?? Judging by experience, creating a unique index on say, a 200G table could be a bitter one

Re: 回复： 回复： Why is creating indexes faster after inserting massive data rows?

- Original Message - From: Zhangzhigang zzgang_2...@yahoo.com.cn The mysql does not use this approach what you said which is complicated. I  agree with ohan De Meersman. Umm... It's not a matter of who you agree with :-) Karen's technical detail is quite correct; I merely presented

Re: 回复： 回复： 回复： Why is creating indexes faster after inserting massive data rows?

- Original Message - From: Zhangzhigang zzgang_2...@yahoo.com.cn As i known, the mysql writes the data to disk directly but does not use the Os cache when the table is updating. If it were to use the OS cache for reading but not writing, then the OS cache would be inconsistent with

回复： 回复： 回复： 回复： Why is creating indexes faster after inserting massive data rows?

Ok, thanks for your help. 发件人： Johan De Meersman vegiv...@tuxera.be 收件人： Zhangzhigang zzgang_2...@yahoo.com.cn 抄送： mysql@lists.mysql.com; Karen Abgarian a...@apple.com 发送日期： 2012年5月8日, 星期二, 下午 6:07 主题: Re: 回复： 回复： 回复： Why is creating indexes faster after

Re: 回复： 回复： 回复： Why is creating indexes faster after inserting massive data rows?

Hi, If MyISAM tables were being written directly to disk, the MyISAM tables would be so slow that nobody would ever use them.That's the cornerstone of their performance, that the writes do not wait for the physical I/O to complete! On May 8, 2012, at 3:07 AM, Johan De Meersman wrote:

回复： 回复： 回复： 回复： Why is creating indexes faster after inserting massive data rows?

 Oh... I thought that it uses it's own buffer cache as same as the InnoDB. I have got a mistake for this,  thanks! 发件人： Karen Abgarian a...@apple.com 收件人： mysql@lists.mysql.com 发送日期： 2012年5月9日, 星期三, 上午 2:51 主题: Re: 回复： 回复： 回复： Why is creating indexes faster

回复： Why is creating indexes faster after inserting massive data rows?

James... * By doing all the indexes after building the table (or at least all the non-UNIQUE indexes), sort merge can be used.  This technique had been highly optimized over the past half-century, and is more efficient. I have a question about sort merge: Why does it do the all sort merge

Re: Why is creating indexes faster after inserting massive data rows?

which version of mysql are you using. Is this secondary index.? On Mon, May 7, 2012 at 12:07 PM, Zhangzhigang zzgang_2...@yahoo.com.cnwrote: hi all: I have a question: Creating indexes after inserting massive data rows is faster than before inserting data rows. Please tell me why.

回复： Why is creating indexes faster after inserting massive data rows?

Version : Mysql 5.1 Engine : MyISAM. The indexes  are normal but neither primary key or unique key. I should describe mysql question clearly. When inserting massive data rows to table which need to be created indexes, i can create indexes before inserting data rows, anther way is that i can

Re: Why is creating indexes faster after inserting massive data rows?

- Original Message - From: Zhangzhigang zzgang_2...@yahoo.com.cn Creating indexes after inserting massive data rows is faster than before inserting data rows. Please tell me why. Plain and simple: the indices get updated after every insert statement, whereas if you only create

回复： Why is creating indexes faster after inserting massive data rows?

in a single operation. But the indexes has to be updating row by row after the data rows has all been inserted. Does it work in this way? So i can not find the different overhead  about two ways. 发件人： Johan De Meersman vegiv...@tuxera.be 收件人： Zhangzhigang zzgang_2

Re: 回复： Why is creating indexes faster after inserting massive data rows?

* the inserts, the index gets created in a single operation. But the indexes has to be updating row by row after the data rows has all been inserted. Does it work in this way? So i can not find the different overhead about two ways. My simplified 2c. When inserting rows with active indexes one

Re: 回复： Why is creating indexes faster after inserting massive data rows?

, the index gets created in a single operation. But the indexes has to be updating row by row after the data rows has all been inserted. Does it work in this way? So i can not find the different overhead about two ways. 发件人： Johan De Meersman vegiv...@tuxera.be

回复： 回复： Why is creating indexes faster after inserting massive data rows?

@lists.mysql.com mysql@lists.mysql.com 发送日期： 2012年5月7日, 星期一, 下午 5:01 主题: Re: 回复： Why is creating indexes faster after inserting massive data rows? Creating the index in one time is one macro-sort operation, updating the index at every row is doing the operation on and on again. If you do not understand

回复： 回复： Why is creating indexes faster after inserting massive data rows?

Thanks, i thought about this answer in the past, and i appreciate your reply. 发件人： Alex Schaft al...@quicksoftware.co.za 收件人： mysql@lists.mysql.com 发送日期： 2012年5月7日, 星期一, 下午 4:59 主题: Re: 回复： Why is creating indexes faster after inserting massive data rows

Re: 回复： 回复： Why is creating indexes faster after inserting massive data rows?

@lists.mysql.com 发送日期： 2012年5月7日, 星期一, 下午 4:59 主题: Re: 回复： Why is creating indexes faster after inserting massive data rows? On 2012/05/07 10:53, Zhangzhigang wrote: johan Plain and simple: the indices get updated after every insert statement, whereas if you only create the index *after

Re: 回复： Why is creating indexes faster after inserting massive data rows?

- Original Message - From: Zhangzhigang zzgang_2...@yahoo.com.cn Ok, Creating the index *after* the inserts, the index gets created in a single operation. But the indexes has to be updating row by row after the data rows has all been inserted. Does it work in this way? No, when you

RE: Why is creating indexes faster after inserting massive data rows?

* Batch INSERTs run faster than one-row-at-a-time, but this is unrelated to INDEX updating speed. * The cache size is quite important to dealing with indexing during INSERT; see http://mysql.rjweb.org/doc.php/memory * Note that mysqldump sets up for an efficient creation of indexes after

RE: 回复： Why is creating indexes faster after inserting massive data rows?

for the index info. InnoDB does something similar, but it is limited to the size of the buffer_pool. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Monday, May 07, 2012 8:06 AM To: Zhangzhigang Cc: mysql@lists.mysql.com Subject: Re: 回复： Why is creating indexes

Re: 回复： Why is creating indexes faster after inserting massive data rows?

and to sort all rows by the index key. The latter process will be the most determining factor in answering the original question, because for the large tables the sort will have to do a lot of disk I/O.The point I am trying to make is there will be situations when creating indexes

Re: 回复： Why is creating indexes faster after inserting massive data rows?

Good point about key buffer. I was only thinking about the table updates for MyISAM, not indexes. The being stuck waiting for buffer flush could also happen. However, for the table blocks this would be the same issue as with load followed by index rebuild, and for the indexes, it will have

回复： 回复： Why is creating indexes faster after inserting massive data rows?

Karen... The mysql does not use this approach what you said which is complicated. I  agree with ohan De Meersman. 发件人： Karen Abgarian a...@apple.com 收件人： mysql@lists.mysql.com 发送日期： 2012年5月8日, 星期二, 上午 1:30 主题: Re: 回复： Why is creating indexes faster after

Re: 回复： 回复： Why is creating indexes faster after inserting massive data rows?

not use this approach what you said which is complicated. I agree with ohan De Meersman. 发件人： Karen Abgarian a...@apple.com 收件人： mysql@lists.mysql.com 发送日期： 2012年5月8日, 星期二, 上午 1:30 主题: Re: 回复： Why is creating indexes faster after inserting massive data

Re: MySQL Indexes

you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.com wrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all

Re: MySQL Indexes

against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.comwrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each

Re: MySQL Indexes

the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.com**wrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query

Re: MySQL Indexes

How heavily a given table is queried does not directly affect the index size, only the number and depth of the indexes. No, it is not that unusual to have the index file bigger. Just make sure that every index you have is justified by the queries you are making against the table. - md On Fri

Re: MySQL Indexes

neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.com**wrote: Maybe that was a bad example. If the query was name = 'Red' what

Re: MySQL Indexes

need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.com**wrote: Maybe that was a bad example. If the query was name = 'Red' what index should I

Re: MySQL Indexes

Em 07-10-2011 00:22, Michael Dykman escreveu: Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins

Re: MySQL Indexes

Do you have any good documentation with regards creating indexes. Also information for explain statement and what would be the desired result of the explain statement? On 7 Oct 2011, at 17:10, Michael Dykman mdyk...@gmail.com wrote: How heavily a given table is queried does not directly

Re: MySQL Indexes

and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.com**wrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query

FW: MySQL Indexes

-Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Friday, October 07, 2011 12:21 PM To: mysql@lists.mysql.com Subject: Re: MySQL Indexes but could this not be called a bug? [JS] No. Think of two telephone books: one is sorted by first name, last name

Re: FW: MySQL Indexes

That cleared it up for me. Thanks! On 10/07/2011 03:06 PM, Jerry Schwartz wrote: -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Friday, October 07, 2011 12:21 PM To: mysql@lists.mysql.com Subject: Re: MySQL Indexes but could this not be called a bug

Re: MySQL Indexes

At 01:58 PM 10/7/2011, you wrote: Do you have any good documentation with regards creating indexes. Also information for explain statement and what would be the desired result of the explain statement? This might help: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html http

MySQL Indexes

Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table

Re: MySQL Indexes

, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table

Re: MySQL Indexes

some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index

Re: MySQL Indexes

Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.comwrote: Maybe

Re: MySQL Indexes

at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.comwrote: Maybe that was a bad

Re: Dropping ALL indexes from a database / not just a table?

Cc: Anirudh Sundar sundar.anir...@gmail.com; mysql@lists.mysql.com Sent: Tue, August 10, 2010 5:03:44 PM Subject: Re: Dropping ALL indexes from a database / not just a table? auto_increment is only allowed on primary-keyed columns. I expect it is not allowing you to drop the primary key because

Re: Dropping ALL indexes from a database / not just a table?

Hello Nunzio, Instead of Dropping a index, you can disable the indexes and get the work done and re-enable them. If you are ok with this then run the below as a shell script :- MUSER=username MPASS=password DATABASE=dbname for db in $DATABASE do echo starting disabling indexes for database

Re: Dropping ALL indexes from a database / not just a table?

At 01:06 AM 8/10/2010, you wrote: Hello Nunzio, Instead of Dropping a index, you can disable the indexes and get the work done and re-enable them. Disabling keys will NOT disable Primary or Unique keys. They will still be active. Mike If you are ok with this then run the below

Re: Dropping ALL indexes from a database / not just a table?

Thanks for the feedback. What I am trying to do is two things: 1. Remove all indexes and make the database smaller to copy and move to another prod box. Currently my indexes are in the double digit GB! Yikes ;-) 2. Remove all indexes so I can find out which ones are needed then tell mysql

Re: Dropping ALL indexes from a database / not just a table?

...@yahoo.com wrote: Thanks for the feedback.  What I am trying to do is two things: 1. Remove all indexes and make the database smaller to copy and move to another prod box. Currently my indexes are in the double digit GB! Yikes ;-) 2. Remove all indexes so I can find out which ones are needed

Re: Dropping ALL indexes from a database / not just a table?

...@yahoo.com Cc: Anirudh Sundar sundar.anir...@gmail.com; mysql@lists.mysql.com Sent: Tue, August 10, 2010 3:17:48 PM Subject: Re: Dropping ALL indexes from a database / not just a table? This should give you a good starting point (not tested): select distinct concat('ALTER TABLE ', TABLE_NAME, ' DROP

Re: Dropping ALL indexes from a database / not just a table?

@lists.mysql.com Sent: Tue, August 10, 2010 3:17:48 PM Subject: Re: Dropping ALL indexes from a database / not just a table? This should give you a good starting point (not tested): select distinct concat('ALTER TABLE ', TABLE_NAME, ' DROP INDEX ', CONSTRAINT_NAME,';') from

Re: Dropping ALL indexes from a database / not just a table?

Hi Micheal and all, ok so I did some digging around and I still can't find why I cant drop the last few indexes. mysql SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = 'db_Market' AND table_name = 'dbt_Fruit' and index_name = 'PRIMARY'; +--+ | COUNT(1

Re: Dropping ALL indexes from a database / not just a table?

nunziodav...@yahoo.com wrote: Hi Micheal and all, ok so I did some digging around and I still can't find why I cant drop the last few indexes. mysql SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = 'db_Market' AND table_name = 'dbt_Fruit' and index_name = 'PRIMARY

Dropping ALL indexes from a database / not just a table?

Hello Gurus, is there a way / script that will let me DROP ALL the indexes in a single database? for example, lets say my database is call db_Animals, and inside db_Animals there are 97 tables, is there a SINGLE command or a perl script of some kind that can read all the MYI files, remove

Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?)

is that the indexes are 30GB in size!!! When I hit this server hard, it tanks on memory but still performs, slower of course ;-) Having indexes which are larger than RAM is (in itself) not critical. IMO, it becomes bad only when accesses to these indexes are spread so wide that even the index

Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?)

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

Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?)

that the innodb database is 190Gb in size BUT the worrying issue is that the indexes are 30GB in size!!! When I hit this server hard, it tanks on memory but still performs, slower of course ;-) Having indexes which are larger than RAM is (in itself) not critical. IMO, it becomes bad only when

Re: combined or single indexes?

not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office

Re: combined or single indexes?

combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from

RE: combined or single indexes?

-Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Thursday, July 22, 2010 3:39 PM To: Shawn Green (MySQL) Cc: mysql@lists.mysql.com Subject: Re: combined or single indexes? Thanks for your reply, and sorry for not verifying in the manual. Another couple

Re: combined or single indexes?

- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Thursday, July 22, 2010 3:39 PM To: Shawn Green (MySQL) Cc: mysql@lists.mysql.com Subject: Re: combined or single indexes? Thanks for your reply, and sorry for not verifying in the manual. Another couple of questions I have

RE: combined or single indexes?

From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Thursday, July 22, 2010 4:50 PM To: Jerry Schwartz Cc: Shawn Green (MySQL); mysql@lists.mysql.com Subject: Re: combined or single indexes? Thanks for the information Jerry. Just to confirm, you mentioned if you only need one

Re: combined or single indexes?

@lists.mysql.com Subject: Re: combined or single indexes? Thanks for the information Jerry. Just to confirm, you mentioned if you only need one key then you only need one key. My question was that this particular query was using SELECT against a primary key and other fields which

Re: combined or single indexes?

for ? Neil, Keep in mind that the more indexes you add to a table, the slower your inserts will become because it needs to maintain the indexes. I would only consider adding indexes to the slow queries. You can use the Explain on your Select statement to see which indexes are being used. See http

Re: combined or single indexes?

At 12:43 AM 7/9/2010, you wrote: On Fri, Jul 9, 2010 at 7:30 AM, Neil Tompkins neil.tompk...@googlemail.comwrote: Should we be looking to create a index for all fields that we might be perform a select condition in a where clause for ? Neil, Keep in mind that the more indexes you add

Re: combined or single indexes?

How many indexes are recommended per table ?? On 7 Jul 2010, at 06:06, Octavian Rasnita octavian.rasn...@ssifbroker.ro wrote: Hi, MySQL can use a single index in a query as you've seen in the result of explain. Of course it is better to have an index made of 2 or more columns because

Re: combined or single indexes?

As many as you need, but no more :-) The right indexes give you a boost in select performance, but every index also needs to be updated when your data changes. On Thu, Jul 8, 2010 at 11:25 PM, Neil Tompkins neil.tompk...@googlemail.com wrote: How many indexes are recommended per table

Re: combined or single indexes?

Should we be looking to create a index for all fields that we might be perform a select condition in a where clause for ? On 9 Jul 2010, at 05:59, Johan De Meersman vegiv...@tuxera.be wrote: As many as you need, but no more :-) The right indexes give you a boost in select performance

Re: combined or single indexes?

On Fri, Jul 9, 2010 at 7:30 AM, Neil Tompkins neil.tompk...@googlemail.comwrote: Should we be looking to create a index for all fields that we might be perform a select condition in a where clause for ? It's a bit of trial and error, you have to weight the number of times you select based on

combined or single indexes?

Is there a benefit to a combined index on a table? Or is multiple single column indexes better? If I have table 'foo' with columns a, b, and c. I will have a query like: select c from foo where a in (1,2,3) and b 12345; Is index on a,b better in any way than an a index and a b index? An explain

Re: combined or single indexes?

Hi Bryan, all! Bryan Cantwell wrote: Is there a benefit to a combined index on a table? Or is multiple single column indexes better? This is a FAQ, but I'm not aware of a place to point you for the answer. If I have table 'foo' with columns a, b, and c. I will have a query like: select

Re: combined or single indexes?

only the index, without getting data from the table. -- Octavian - Original Message - From: Bryan Cantwell bcantw...@firescope.com To: mysql@lists.mysql.com Sent: Tuesday, July 06, 2010 6:41 PM Subject: combined or single indexes? Is there a benefit to a combined index on a table

Re: The query doesn't use the specified indexes

for indexes, so I tried to use columns that contain integers: mysql explain select * from test where id_symbol=2 order by id_market limit 20\G *** 1. row *** id: 1 select_type: SIMPLE table: test type: ref possible_keys: id_symbol key: id_symbol

The query doesn't use the specified indexes

thought that this is because it doesn't like varchar or char columns for indexes, so I tried to use columns that contain integers: mysql explain select * from test where id_symbol=2 order by id_market limit 20\G *** 1. row *** id: 1 select_type: SIMPLE

Re: The query doesn't use the specified indexes

=utf8 So you have a table with 5 columns, one being the primary key, and separate single-column indexes on the other 4 columns. The search query is: mysql explain select * from test where symbol='etc' order by market limit 20\G *** 1. row

Re: Any faster building primary/unique indexes after Load Data Infile?

Hi, On Sun, Feb 21, 2010 at 1:42 PM, mos mo...@fastmail.fm wrote: I am loading 35 million rows of data into an empty MyISAM table. This table has 1 primary key (AutoInc) and 1 unique index and 2 non-unique indexes. Is it going to be any faster if I remove the indexes from the table before

  1   2   3   4   5   6   7   >