Mysql add multiple index

Mysql, Is there any difference between alter table test add index(id, lastname); alter table test add index(zip5, zip4); and alter table test add index(id, lastname), add index(zip5, zip4); The test table has 90 millions records. Thanks. -- MySQL General Mailing List For list archives:

Re: mysql 4.0 to 4.1 migration and charset problems

the version is 4.1.12. show variables like this, | character_set_client| latin1 | character_set_connection| latin1 | character_set_database | latin1 |

Question about index for group by

Mysql, I have table structure like this: CREATE TABLE `promo_2003_1` ( `k1` char(17) NOT NULL default '', `fname` char(3) default '', `lname` char(6) default '', `sndx_lname` char(6) default '', `suffix` char(5) default '', `zip5` char(5) default '', `zip4` char(4) default '', `firstname`

Re: mysql top 2 rows for each group

I don't have any PK in this table, but If you like one, I can add another column for PK, like, ++---+ | id | seqno | ++---+ | 1 | 00122 | | 2 | 00123 | | 3 | 00123 | | 4 | 00123 | | 5 | 00336 | | 6 | 00346 | | 7 | 00349 | | 8 |

Re: mysql top 2 rows for each group

I did like this, look good, anyone can try it. set @a:=0; set @b:=0; update test set item=if([EMAIL PROTECTED], @a:[EMAIL PROTECTED], if(@b:=seqno, @a:=1, 0)); Vivian Wang wrote: I don't have any PK in this table, but If you like one, I can add another column for PK, like

mysql top 2 rows for each group

I have question about how I can get top 2 rows for each group. like I have table test | seqno | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have

Re: Group By - Is there a way to set which rows values are used for the fields not in the Group By clause?

create table temp select * from viewvisitor order by lastviewtime desc; select app, itemid, ownerid, visitorid, vusername,lastviewtime, sum(viewcount) AS totalcount, itemname from temp where ownerid = 2 GROUP BY concat( app, itemid ) ORDER BY totalcount; or if you only care about

different between index and key when create table

Can anyone tell me what is different between index and key when creating table? like this situation: create table info ( fname char(9), lname char (15), address char(30), index(lname)); or create table info ( fname char(9), lname char(15), address char(30), key(lname)); Thanks.

Fwd: different between index and key when create table

mysql: Can anyone tell me what is different between index and key when creating table? like this situation: create table info ( fname char(9), lname char (15), address char(30), index(lname)); or create table info ( fname char(9), lname char(15), address char(30), key(lname)); Thanks.

simple question about 3 files become one table

I have three files which are .frm, .MYD and .MYI. How I can use those three files become one table again? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysql: change column to row

mysql, Can I change the table one column to one row like this? Table1: key, count A 123 B 456 C 789 to Table2: A B C 123 456 789 Thanks, - Before posting, please check:

mysql:Question about definition and index for performance

mysql, tableA, definition: key1 char(17) not null, acct char(12), other char (5) tableB is same definiton like tableA. I add index for both tableA and tableB like: alter table tableA add index(key1, acct) I will do left join with tableA and

mysql or pathon date question

mysql, The question is: If I use unix date Command like: date -date=20030220 +%j I will get 051 which is Julian date. I like to know how I can get Julian date if I use mysql or Python. Thanks. - Before posting, please check:

Re: load data infile syntax

still need \ I posted my syntax before, but nobody helped me to figure out. So this time I did my test again and found out only need one \ for ESCAPED BY. At 10:43 PM 11/5/2002 -0600, you wrote: At 14:38 -0500 11/5/02, Vivian Wang wrote: The syntac ESCAPED BY of load data infile is wrong

Re: load data infile syntax

This is my statement using python, db.query(load data infile '/data/winshare/experian/test' into table d_adds fields terminated by '' escaped by '\' lines terminated by '\n'); Is python problem? not sure. At 10:01 AM 11/6/2002 -0600, Paul DuBois wrote: At 10:03 -0500 11/6/02, Vivian Wang wrote

Re: load data infile syntax

-0500 11/6/02, Vivian Wang wrote: This is my statement using python, db.query(load data infile '/data/winshare/experian/test' into table d_adds fields terminated by '' escaped by '\' lines terminated by '\n'); Is python problem? not sure. Looks like what's happening is that Python itself

load data infile syntax

The syntac ESCAPED BY of load data infile is wrong in http://www.mysql.com/doc/en/LOAD_DATA.html. the [ESCAPED by '\\'] should be: [ESCAPED BY '\'] - Before posting, please check: http://www.mysql.com/manual.php (the

how to join two tables in different server

MySQL, I like to have a query like this, select * from tableA left join tableB on. But tableA is in hostA, and tableB is in hostB. What I should do? I can use either PHP or python. Any ideas? Thanks - Before posting, please

Re: CREATE/CHECK syntax

From create syntax, you cannot put , after VARCHAR(1). You have another , after AUTO_INCREMENT, maybe it is not correct too. At 01:56 PM 7/31/2002 -0400, Larry Irwin wrote: Using MySQL 4.02 on SuSE Linux 7.3 The MySQL docs at http://www.mysql.com/doc/C/R/CREATE_TABLE.html state that CHECK

Re: removing duplicate records

Walt, Don't trust group by. I am using mysqldump and sort -u on *nix for duplicate. Try both ways, let me know your result. Thanks At 04:11 PM 7/30/2002 -0400, walt wrote: Thanks David! The entire row, not just one or two columns, is a duplicate which makes life fun.. :-) I can key or unique

Fwd: Re: Question about group by or distinct

Hi Benjamin, Arjen I did some testing again. I have 75,000,000 rows table, after using group by I got 62,300,000 rows. If I use *nix -u sort, I got 74800,00 rows. I compared the two results, I feel I trust my *nix result. Some lines just miss in group by' result, look like no reason at all.

Re: Question about group by or distinct

I have 14 fields are chars (like char(8), char(9)), and one field is int(4). All char fields are mixed with upper case letters, numbers(0-9) and sign(like $). Because there is no low case char, I don't have to put -f option for unix sort. Thanks. At 08:23 PM 7/23/2002 +0200, Benjamin Pflugmann

copy question

Can mysql copy from table1 to table2? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL

Re: Question about group by or distinct

as in the Linux sort only entire row duplicates will be removed meaning 'field1field2'. They would return the same number of records only if you selected field1 only and then sorted it via the Linux sort. Regards, Bhavin. - Original Message - From: Vivian Wang [EMAIL PROTECTED