RE: index collate

Got it; Thanks! Zhigang -Original Message- From: Hartmut Holzgraefe [mailto:hart...@skysql.com] Sent: Thursday, September 11, 2014 1:44 PM To: mysql@lists.mysql.com Subject: Re: index collate On 09/11/2014 05:55 AM, Zhigang Zhang wrote: > The “CREATE INDEX Syntax” can not assig

Re: index collate

On 09/11/2014 05:55 AM, Zhigang Zhang wrote: > The “CREATE INDEX Syntax” can not assign a collation. > > What collation to use to create a index on a column? The collation used for an index on a textual column is the columns collation itself. You can't choose a different collation for the index

Re: Index Query Tunning

Hi Anupam, We are keep on getting deadlock due to index locking, there is index on FLAG, we can allow phantom read in session 1, we tried with READ COMMITTED but still same, I think issue with next-key locking. Did you try setting binlog-format=ROW as well? I have a brief explanation of thi

Re: *.index files in MySQL Data directory

above. > > -Original Message- > From: Reindl Harald [mailto:h.rei...@thelounge.net] > Sent: Wednesday, March 27, 2013 3:14 PM > To: mysql@lists.mysql.com > Subject: Re: *.index files in MySQL Data directory > > > > Am 27.03.2013 19:53, schrieb Arnold: >

RE: *.index files in MySQL Data directory

3:14 PM To: mysql@lists.mysql.com Subject: Re: *.index files in MySQL Data directory Am 27.03.2013 19:53, schrieb Arnold: > What are the *.index files in the MySQL Data directory? I notice a new one > is created every time that MySQL is stopped and restarted. My Data directory > is n

Re: *.index files in MySQL Data directory

Am 27.03.2013 19:53, schrieb Arnold: > What are the *.index files in the MySQL Data directory? I notice a new one > is created every time that MySQL is stopped and restarted. My Data directory > is not in the default location so does that have anything to do with it? maybe a "ls -lha" of a s

RE: index & innodb

1. Secondary indexes (but not the PRIMARY KEY) requires additional disk space. A secondary index implicitly includes copies the field(s) of the PRIMARY KEY; this is how it can get to the actual data row. Finding a row via a secondary key involves two BTree lookups -- one in the secondary inde

Re: index

Innodb stores it's indexes internally in the datafile; they do consume storage, but that space is shared with the data. innodb_per_file will, in most cases, enhance performance in a high-concurrency environment. It also can simplify administration, and help limit your liabilities in the event of

Re: Index question

- Original Message - > From: "Tompkins Neil" > Thanks for the information. One final question in what ways should we > use EXPLAIN EXTENDED statement to help improve our query > performance. Explain is your friend. You should listen to it :-) It gives a nice idea of how the database i

Re: Index question

Thanks for the information. One final question in what ways should we use EXPLAIN EXTENDED statement to help improve our query performance. On Tue, Oct 11, 2011 at 2:51 PM, Johan De Meersman wrote: > - Original Message - > > From: "Rik Wasmus" > > > > Depends on the data and usage, but

Re: Index question

- Original Message - > From: "Rik Wasmus" > > Depends on the data and usage, but probably I'd go for a index(a,b) & > index(b,a) if reads heavily outnumber writes. As index(a) is covered > by index(a,b), and index(b) by index(b,a), we don't need to add those, > which saves time on modifi

Re: Index question

> In this instance would you create four indexes key(a) key(b) key(a,b) key > (b,a) ? Or is the decision based on the query response time ? Depends on the data and usage, but probably I'd go for a index(a,b) & index(b,a) if reads heavily outnumber writes. As index(a) is covered by index(a,b), a

Re: Index question

In this instance would you create four indexes key(a) key(b) key(a,b) key (b,a) ? Or is the decision based on the query response time ? On 11 Oct 2011, at 13:40, Rik Wasmus wrote: >> Next question. If you have the two separate indexes and then do two >> queries, one for a and one for b. If you

Re: Index question

> Next question. If you have the two separate indexes and then do two > queries, one for a and one for b. If you then get a list of unique id's > of both, would it be faster to create an intersection yourself rather > than have the server do the legwork? If you only have 2 unrelated indexes on a &

Re: Index question

On 2011/10/11 02:30 PM, Alex Schaft wrote: On 2011/10/11 02:22 PM, Rik Wasmus wrote: Just to clarify having key indexes of (a,b) or (b,a) have no difference ? They DO. See it as lookup table which starts with 'a' in the first case, and 'b' in the second one. Looking for anything that matches

Re: Index question

On 2011/10/11 02:22 PM, Rik Wasmus wrote: Just to clarify having key indexes of (a,b) or (b,a) have no difference ? They DO. See it as lookup table which starts with 'a' in the first case, and 'b' in the second one. Looking for anything that matches 'b' for an index (a,b) requires a full scan

Re: Index question

> Just to clarify having key indexes of (a,b) or (b,a) have no difference ? They DO. See it as lookup table which starts with 'a' in the first case, and 'b' in the second one. Looking for anything that matches 'b' for an index (a,b) requires a full scan as you don't know 'a', likewise searchin

Re: Index question

The difference is based on the relative frequency of queries where a is important vs. where b is important. Either way, what will happen is that the index scan will isolate the first item mentioned, then scan the result set to isolate the second term. e.g. SELECT * FROM someTable WERE a = someVal

Re: Index question

Just to clarify having key indexes of (a,b) or (b,a) have no difference ? On 11 Oct 2011, at 09:36, Johan De Meersman wrote: > - Original Message - >> From: "Alex Schaft" >> >> If you have a table with columns A & B, and might do a where on A or >> B, or an order by A, B, would single

Re: Index question

- Original Message - > From: "Alex Schaft" > > If you have a table with columns A & B, and might do a where on A or > B, or an order by A, B, would single column indexes on A and B suffice > or would performance on the order by query be improved by an index on > A,B? Depends on usage :-)

Re: index problem

thanks . i dropped the primary key , and it still didn't use the index . when i dropped the index `range`, and add two indexes `start` and `end` . it picks up the index , but it still used more seconds than using no index with `start` and `end` indexed as one --`range`. 2011/7/28 Johan De Meersman

Re: index problem

[Note to self, reply to mailinglist, not to author ;) ] > i found a strange problem . when i using index for 'select' , i > got a slower result than without index . > i have a tabe : > create table geo_query ( > `id` int(10) unsigned not null auto_increment , >

Re: index problem

The optimizer is right, you are wrong, as simple as that :-) between [field1] and [field2] cannot use indices, as your primary reference is a constant, not a field. Rewrite that to "start >= 1988778880 and end <= 1988778880" and the optimizer should pick up the index. Index hints are rarely ev

Re: Index creation

Hi Alex, with MySQL 4.1.22 there is not much you can do. MySQL alter tables recreating a new temporary one and swapping then afterwards. In my opinion the effort to speed up the process is not worth for just this alter table. Cheers Claudio 2011/6/21 Alex Schaft > Hi, > > I'm busy creating

Re: Index not being used

Hi all! Ananda Kumar wrote: > you should change the column order for the index sitefieldsort. > It should be (sortorder,siteid`,`fieldid`). NO! NEVER! (sorry for shouting) For any DBMS (this is not specific to MySQL), it is not possible to use a multi-column index unless values are given for t

Re: Index not being used

you should change the column order for the index sitefieldsort. It should be (sortorder,siteid`,`fieldid`). regards anandkl On Fri, Mar 5, 2010 at 11:52 PM, Johnny Withers wrote: > I don't have your data, so I do not really know if my results are > "correct". > > I created this table on my syste

Re: Index not being used

I don't have your data, so I do not really know if my results are "correct". I created this table on my system and added two records: INSERT INTO form_fields_items(siteid,fieldid,`value`,sortorder) VALUES (1234,5678,'test1',0) ; INSERT INTO form_fields_items(siteid,fieldid,`value`,sortorder) VALU

Re: Index analyser

Bryan, On Tue, Feb 23, 2010 at 6:09 PM, Cantwell, Bryan wrote: > > Ya, that one is helpful... just trying to land on a solution like I've seen > in other DB's that have index-advisor that listens and creates what it thinks > is the perfect indexes ... but thx... I know exactly what you are thi

RE: Index analyser

February 2010 17:44 To: Cantwell, Bryan; mysql@lists.mysql.com Subject: RE: Index analyser There's also the Query Analyser http://www.mysql.com/products/enterprise/query.html which is part of MySQL Enterprise - I've never used it and it is very expensive but I believe it will advise on optima

RE: Index analyser

lto:bcantw...@firescope.com] Sent: 23 February 2010 23:09 To: mysql@lists.mysql.com Subject: RE: Index analyser Ya, that one is helpful... just trying to land on a solution like I've seen in other DB's that have index-advisor that listens and creates what it thinks is the perfect

RE: Index analyser

ebruary 23, 2010 4:33 PM To: mysql@lists.mysql.com Subject: Re: Index analyser At 03:28 PM 2/23/2010, you wrote: >Is there still no such thing anywhere for Mysql as an index analyser? >Many others have such a thing that will sit and monitor db activity over a >poeriod of time and suggest the ex

Re: Index analyser

At 03:28 PM 2/23/2010, you wrote: Is there still no such thing anywhere for Mysql as an index analyser? Many others have such a thing that will sit and monitor db activity over a poeriod of time and suggest the exact indexes on each table based on what it has seen to improve performance An

Re: Index analyser

On Tue, February 23, 2010 1:28 pm, Cantwell, Bryan wrote: > Is there still no such thing anywhere for Mysql as an index analyser? > Many others have such a thing that will sit and monitor db activity over a > poeriod of time and suggest the exact indexes on each table based on what > it has seen to

Re: Index selection problem

Maybe I'm wrong :) On Tuesday, July 21, 2009, John Daisley wrote: > > > On Tue, 2009-07-21 at 19:42 +0200, Morten Primdahl wrote: > >> On Jul 21, 2009, at 3:27 PM, Johnny Withers wrote: >> >> > MySQL is unable to use your index when you use IN and/or OR on yoru >> > column. >> >> Is this really

Re: Index selection problem

On Tue, 2009-07-21 at 19:42 +0200, Morten Primdahl wrote: > On Jul 21, 2009, at 3:27 PM, Johnny Withers wrote: > > > MySQL is unable to use your index when you use IN and/or OR on yoru > > column. > > Is this really true? No its not true! Try running OPTIMIZE TABLE on the affected table, t

Re: Index selection problem

The other index does have a way higher cardinality, but the query is for 3 columns all of which are in the first index. I guess this is just one of the situations where MySQL makes a wrong assessment. On Jul 21, 2009, at 3:54 PM, Brent Baisley wrote: Try doing a "SHOW INDEX FROM orders"

Re: Index selection problem

On Jul 21, 2009, at 3:27 PM, Johnny Withers wrote: MySQL is unable to use your index when you use IN and/or OR on yoru column. Is this really true? I'm reading "High Performance MySQL 2nd ed." these days and specifically got the impression that using IN will allow usage of the index. Th

Re: Index selection problem

Try doing a "SHOW INDEX FROM orders" and look at the cardinality column. These are the stats MySQL uses to determine which index to use. Sometimes they aren't always update properly and you may need to run ANALYZE on the table. But, you can also tell MySQL to use the index you want. SELECT * FROM

Re: Index selection problem

MySQL is unable to use your index when you use IN and/or OR on yoru column. If the query is slow, you should switch to a union: SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531 AND status_id =1 UNION SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531 AND status_id =

RE: Index time columns?

Hi, If you have that date column in your where clause for example: SELECT .. FROM . WHERE tstamp > NOW() - INTERVAL 1 WEEK; Then it's essential to index that column to speed up a table with lots of data. On a table with many rows, an index on a timestamp column is invaluable. However,

Re: Index/Range Problem?

Hi, Thanks for the help; that makes sense I think you guys are right. Is it worth tuning such a thing? It seems to me like it would be much faster to use the index? Thanks again Dave > Dave schrieb: >> Hi all, >> I've been trying to optimize some of our queries against a large >> database >> an

Re: Index/Range Problem?

Dave schrieb: Hi all, I've been trying to optimize some of our queries against a large database and come up against an index problem I haven't been able to find any documentation on. I've cut the query down to the bare minimum, and found the following -- explain Select iname,domain,serv,time fr

Re: Index/Range Problem?

what is the total no. of records in the table. Index will be used , if the query selects between 5 to 10% of the total records in the table. If its more than that then, optimizer will doing ALL scan, as it assumes doing ALL scan is faster than an INDEX SCAN. On 6/9/08, Dave <[EMAIL PROTECTED]> wr

Re: index creation taking too much time

Change the following parameter: myisam_sort_buffer_size=300MB Larger than here. myisam_max_sort_file_size=10GB Reduce this value to 30% of your real memory. On Tue, May 13, 2008 at 7:10 PM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > Hi Krishna, > how do i make my index to get more key blocks > > O

Re: index creation taking too much time

Hi Krishna, how do i make my index to get more key blocks On 5/13/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> wrote: > > Hi anand, > > PRIMARY KEY (`id`), > KEY `KI_IDX_0805090456` (`words`,`id`), > KEY `CI_IDX_0805090456` (`lf_id`) > > Since id is a primary key. Then why again indexing o

Re: index creation taking too much time

Hi anand, PRIMARY KEY (`id`), KEY `KI_IDX_0805090456` (`words`,`id`), KEY `CI_IDX_0805090456` (`lf_id`) Since id is a primary key. Then why again indexing on id is being created (`words`,`id`). It will be a duplicate index on id. words is a varchar type. So instead of creating fulltext index

Re: index creation taking too much time

| Handler_commit| 25802690 | | Handler_delete| 100 | | Handler_discover | 0| | Handler_prepare | 10370014 | | Handler_read_first| 88920| | Handler_read_key

Re: index creation taking too much time

At 08:18 AM 5/12/2008, you wrote: Hi All, We have a table which is around 100 Million rows. Its a myisam table, but the db default is innodb. CREATE TABLE `dc_data` ( `id` decimal(22,0) NOT NULL, `words` varchar(255) NOT NULL, `lf_id` decimal(22,0) NOT NULL, `occurence` bigint(20) NOT NUL

Re: index creation taking too much time

myisam_max_sort_file_size=10GB You have alloted 10GB memory to myisam_max_sort_file_size, The system has 8GB of memory. send show global status; On Mon, May 12, 2008 at 6:48 PM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > Hi All, > We have a table which is around 100 Million rows. Its a myisam ta

how to re-index a table?

. Now I need to make one index with these to columns. Using MySQL Browser I did it no problem. I think I have to re-index them now, right? How? thanks for any help. -a Looking for last minute

Re: Index help

On Nov 12, 2007 9:22 AM, Afan Pasalic <[EMAIL PROTECTED]> wrote: > If you have to deal with it again consider using a bunch of unions > instead of the 'IN'. Not prettiest thing, but it should fix your > performance issue. > Could you please give me more details about your statement that mysql dea

Re: Index help

On Nov 12, 2007 7:57 AM, Stut <[EMAIL PROTECTED]> wrote: > Hi Rob, > > Thanks for your reply. > > > Rob Wultsch wrote: > > On Nov 8, 2007 4:08 PM, Stut <[EMAIL PROTECTED]> wrote: > >> Hi all, > >> > >> I've inherited a PHP app that uses a MySQL database. The following query > >> is extremely slow a

Re: Index help

Hi Rob, Thanks for your reply. Rob Wultsch wrote: On Nov 8, 2007 4:08 PM, Stut <[EMAIL PROTECTED]> wrote: Hi all, I've inherited a PHP app that uses a MySQL database. The following query is extremely slow and I've been battling for a couple of days on an off to try and get a combination of in

RE: Index help

On Nov 8, 2007 4:08 PM, Stut <[EMAIL PROTECTED]> wrote: > Hi all, > > I've inherited a PHP app that uses a MySQL database. The following query > is extremely slow and I've been battling for a couple of days on an off > to try and get a combination of indexes to optimise it. Any help would > be grea

Re: Index usage for simple query

Hi, Joerg Bruehe wrote: Hi all, Baron Schwartz wrote: Hi, Colin Martin wrote: Baron Schwartz wrote: Hi, Colin Martin wrote: Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and w

Re: Index usage for simple query

Hi all, Baron Schwartz wrote: Hi, Colin Martin wrote: Baron Schwartz wrote: Hi, Colin Martin wrote: Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and why in the second query whe

Re: Index usage for simple query

Hi, Colin Martin wrote: Baron Schwartz wrote: Hi, Colin Martin wrote: Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and why in the second query where there are two constants, it d

Re: Index usage for simple query

Baron Schwartz wrote: Hi, Colin Martin wrote: Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and why in the second query where there are two constants, it decides not to? Is there

Re: Index usage for simple query

Hi, Colin Martin wrote: Hi there, Can someone please explain why when the query below uses one constant in the WHERE clause, MySQL decides to use the index on the 'source' column, and why in the second query where there are two constants, it decides not to? Is there a way to get MySQL to u

Re: Index usage - MyISAM vs InnoDB

Hi! Comments inline. Edoardo Serra wrote: SELECT sum(usercost) FROM cdr WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59' If I run it on the MyISAM table, MySQL choose the right index (the one on the calldate column) and the query is fast enough If I run it on the Inno

Re: index, unique index question

artijn/ > Database development questions? Check the forum! > http://www.databasedevelopmentforum.com > > -Noah > > > -Original Message- > From: Kristian Myllymäki [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 14, 2007 3:50 AM > To: Ananda Kumar > Cc: Jame

Re: index, unique index question

ijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -Noah -Original Message- From: Kristian Myllymäki [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 14, 2007 3:50 AM To: Ananda Kumar Cc: James Tu; MySQL List Subject: Re: index, unique index questi

RE: index, unique index question

3:50 AM To: Ananda Kumar Cc: James Tu; MySQL List Subject: Re: index, unique index question A composite index on both columns may be used by queries involving either both columns, or the first column in the index. http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html So, an index

Re: index, unique index question

A composite index on both columns may be used by queries involving either both columns, or the first column in the index. http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html So, an index on (receiver_id, sender_id) may be used by predicates on both columns or receiver_id al

Re: index, unique index question

Hi James, Since your queries have both receiver_id and sender_id in the where condition and u want this to be unique, just create one combined unique index on both these columns. Do this at db level will give you much better options, performance, rather than doing at code level, which might involv

Re: index question

Run explain select * from A,B where A.col1=B.col1; The explain plan for your query will tell you what indexes are chosen. If your explain plan says what you do not like, definitely add an index on col1 in B. Make sure you run OPTIMIZE TABLE on both tables. The, run explain select * from A,B wh

Re: index question

John Mancuso wrote: If I have 2 large tables A and B and I need to join them: select * from A,B where A.col1=B.col1 If A.col1 is an index and B.col1 is not, if I create an index on B.col1 will this index be used? In other words how are indexes used on table joins? Thanks John Mancuso Li

Re: index not used

Hi Bernd, Bernd Jagla wrote: Hi, I have a problem with a very slow query that should be VERY fast. > select * from tfbs_ecr t [snip] where ecr_id in (301,302,316,323,345,346,351,362,363,364,380,411,413,451,464,466,470,471,481 ,498,506,507,538,541,543); => returns 1167 rows within a few mil

Re: index number of a specific cell

sam rumaizan schrieb: > Is there a function in mysql to view the index number of a specific > cell/field? "index number" = primary key? i guess no > For example if I want the index number for info4 or info7. Is it possible? "index number" = row number? no - without an ORDER BY there is no

Re: index overhead

As for question 1: the overhead will be exactly the same in both index layout scenarios. The only deciding factor is how your application uses this data.. the choice of structure has only to do with finding what accelerates application quiries most effectively. Question 2: by all means, add the

Re: Index not used sometimes -- why?

M5 a écrit : > Sometimes, when I run a query on a table, the index will be used. But if > I run the SAME query, but on a larger set of rows (e.g., where id > BETWEEN 1 AND 10 vs WHERE id BETWEEN 1 AND 500), the index is not > used. (EXPLAIN tells me this.) And as a result, the query runs slow.

Re: Index and Order By

It's possible mysql optimizes queries incorrectly. I've had to "correct it" on a few occasions. If you look at the results of the explain for Q1, you'll notice that in both queries the key_len is 1. Meaning Mysql thinks it only needs to look at the first "character" of the index. Based on that "o

Re: Index and Order By

Hi Kelvin, all! Kelvin Wu wrote: I am looking for help and suggestion. Mysql Ver 12.18 Distrib 4.0.12, for pc-linux (i686) The table structure: [[...]] I created multiple parts of Index for this table: [[...]] And here are the questions: Q1: the query is supposed to use index bynone but ...

Re: Index and Order By

BTW, I have force to use index key by using USE INDEX (bysource) for example, in the query, but it didn't look good to me... -- Sent from my BlackBerry. Ignore the typos unless they're funny.

RE: Index effectivity for a 2-valued field (was Re: Should I use an index in this case?)

>From what I know, an index on a field that can only have two values will never be used, except to make INSERTs slower. By the way, I think I remember that you have a VARCHAR in those records. Doesn't that force the CHAR to become a VARCHAR under the hood? That might be less efficient that using s

Re: Index effectivity for a 2-valued field (was Re: Should I use an index in this case?)

Hi, I'm not a wizard on the MySQL optimizer, but here's my go... > > Two questions for you: > > 1) Why store so many repeatable data in the same table? > > I mean you needn't to store "purchases"/"previews" for all records. > > You can choose MySql SET datatype, or you can choose another table to

Re: Index on MERGE table

Oops! I think I've missed it... Thanks a lot! :) C.R.Vegelin пишет: Hi Eugene, I suppose you have read: http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html Especially the paragraph starting with: The order of indexes in the MERGE table and its underlying tables should be the same.

Re: Index on MERGE table

Hi Eugene, I suppose you have read: http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html Especially the paragraph starting with: The order of indexes in the MERGE table and its underlying tables should be the same. HTH, Cor - Original Message - From: "Eugene Kosov" <[EMAI

Re: Index merge optimization (with OR) and table joins

Thanks for the reply, it's much appreciated. I'll have another look at the tables, although my criteria were such that the query should have only returned a tiny fraction (<0.1%) of the rows (which is why I was confused). If I get any closer to a solution I'll try and post with a bit more info. An

Re: Index merge optimization (with OR) and table joins

On 5/4/06, sheeri kritzer <[EMAIL PROTECTED]> wrote: (again, apologies for the lateness...) MySQL has a cost-based optimizer. If it's deciding that a full-table scan is appropriate, there's a reason. If more than 30% (approx) of the table would be returned in a range query, the optimizer reaso

Re: Index merge optimization (with OR) and table joins

(again, apologies for the lateness...) MySQL has a cost-based optimizer. If it's deciding that a full-table scan is appropriate, there's a reason. If more than 30% (approx) of the table would be returned in a range query, the optimizer reasons that it's LESS expensive to just do a full table sc

Re: Index and multiple fields

Markus Fischer wrote: Basically, this means if I've a table like this id1 id2 id3 id4 id5 and I've two different select statements: select * from ... where id1 = .. and id2 = .. and the other being select * from ... where id3 = .. and id4 = .. I would create two indexes, one for id1/id2 and

Re: Index and multiple fields

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, thanks, somehow I wasn't able to find those pages. Basically, this means if I've a table like this id1 id2 id3 id4 id5 and I've two different select statements: select * from ... where id1 = .. and id2 = .. and the other being select * from

Re: Index and multiple fields

Hi, Mark! Of course, it depends on queries you are running. I beleive you can find all anwers here: http://dev.mysql.com/doc/refman/5.0/en/indexes.html http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html Markus Fischer п

Re: Index merge optimization with joined tables?

In the last episode (Dec 01), Kayra Otaner said: > Is MySQL 5.0's Index merge optimization is also available to multiple > table joins? When I do explain on 3 table joins, only one key per > table is used : > > SELECT R.* FROM L, M, R > WHERE > L.key1 = 1 > ANDL.key2 = M.key2 > ANDM.key1

Re: Index of JOINed table?

Jonathan Mangin wrote: I have a simple query, (part of a union). calendar is a table of only dates, `date` as PK. sales has a multi-col index on (date, uid). (select calendar.date as date, time_format(time(sales.date),'%H:%i') as time, from calendar left join sales on date(

Re: Index of JOINed table?

- Original Message - From: "Jonathan Mangin" <[EMAIL PROTECTED]> To: Sent: Monday, November 21, 2005 8:48 AM Subject: Index of JOINed table? I have a simple query, (part of a union). calendar is a table of only dates, `date` as PK. sales has a multi-col index on (date, uid). (selec

Re: INDEX

Scott Hamm <[EMAIL PROTECTED]> wrote on 09/26/2005 02:21:38 PM: > > On 9/26/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > > > > Scott Hamm <[EMAIL PROTECTED]> wrote on 09/26/2005 01:59:52 PM: > > > > > How do I set Index to enforce that ONLY 1 QAID can own that order number, > > > but

Re: INDEX

Scott Hamm <[EMAIL PROTECTED]> wrote on 09/26/2005 01:59:52 PM: > How do I set Index to enforce that ONLY 1 QAID can own that order number, > but nothing else? > > For example: > > QAID [order] ErrorType > 11223 123456789 19 'ALLOWED TO HAVE different ErrorTypes as long as one QAID > number us

Re: Index update process 20+ hrs

It does not seem fine to me, mysql seems to really get slow once you break about the 3 gig myd size, or 1.5 gig myi size not sure why, I've changed every setting, but nothing seems to help Matt Clyde Lewis wrote: Matt, Looking at how the database if currently configured, do you have any su

Re: Index update process 20+ hrs

On Monday 29 August 2005 04:55 pm, Alexey Polyakov wrote: > I'd try setting > innodb_buffer_pool_size=8G > in my.cnf Then it will run out of memory. You only get 2gig for bufferpools no matter how much more you have. -- === Jab

Re: Index update process 20+ hrs

I'd try setting innodb_buffer_pool_size=8G in my.cnf -- Alexey Polyakov

Re: Index update process 20+ hrs

If you are doing big updates on indexes, like during an import, you want to adjust your variables accordingly. The variables that would speed inserts and index updates are key_buffer_size bulk_insert_buffer_size if using MYISAM table types: myisam_max_sort_file_size myisam_sort_buffer_size R

Re: Index update process 20+ hrs

Get used to it, I've got similar tables, mine have less columns, but more records, my biggest takes a week to restore a mysqldump Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Index update process 20+ hrs

This is a test server with nothing else hitting the box. I imported a dump of a production database and is trying to find a way to reduce the time it takes to rebuild the index on the mentioned table Hard Drive spec: Clarion EMC Hard drive 100 GB internal 3.5" Fibre Channel 1 rpm buffer

Re: Index update process 20+ hrs

Clyde Lewis <[EMAIL PROTECTED]> wrote on 08/29/2005 01:47:11 PM: > Guys, > > I have a huge table to which I'm attempting to update the foreign key > and index. It is taking me more than 20 hrs to complete the process and > would like to know if someone can point me in the right direction. > Pl

Re: Index - max key length is 1024 bytes

javabuddy wrote: But still I can't get the part where my column size totals to 560, but MySql complaining that I have exceeded 1024. Did I went anywhere wrong??? Are you sure you've thought your index through correctly and considered how MySQL will use it? What sort of query would such an in

Re: Index - max key length is 1024 bytes

javabuddy wrote: Thanks But still I can't get the part where my column size totals to 560, but MySql complaining that I have exceeded 1024. Did I went anywhere wrong??? - Gana. People are con

RE: Index - max key length is 1024 bytes

If you are on a version prior to 4.1.2 the max index size is 500 bytes {not sure why the error mentions 1024} >From section 14.1 of documention The maximum key length is 1000 bytes (500 before MySQL 4.1.2). This can be changed by recompiling. For the case of a key longer than 250 bytes, a larger

Re: Index in the desc order

Gana wrote: I am not able to create index with the DESC flag on it. - javabuddy. Hassan Schroeder answered this exact question just yesterday . Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:ht

  1   2   3   >