Case sensitive indexes

[gord barq]

I have the following index:

CREATE UNIQUE INDEX SongTopic_idx ON SongTopic(songTitle, artist, album);

Where songtitle, artist and album are varchar() columns.

It appears that this index is not case sensitive. Is that correct? If so, 
how can I have it so that it is indeed case sensitive because I'm importing 
lots of data from Oracle which is case sensitive.

Thanks.

_
Get MSN 8 and enjoy automatic e-mail virus protection.
http://join.msn.com/?page=features/virus

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Case sensitive indexes

[gord barq]

I'm using MySQL 4.1 so how do I define case sensitive collation? Is it on 
the columns or on the indexes?

Thanks.


From: Victoria Reznichenko [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: Case sensitive indexes
Date: Tue, 26 Aug 2003 13:18:19 +0300
gord barq [EMAIL PROTECTED] wrote:
 I have the following index:

 CREATE UNIQUE INDEX SongTopic_idx ON SongTopic(songTitle, artist, 
album);

 Where songtitle, artist and album are varchar() columns.

 It appears that this index is not case sensitive. Is that correct? If 
so,
 how can I have it so that it is indeed case sensitive because I'm 
importing
 lots of data from Oracle which is case sensitive.


VARCHAR column is compared in case-insensitive fashion. You can define 
column as BINARY if you use 3.23 or 4.0 MySQL server. If you use 4.1 MySQL 
server you can define case sensitive collation.

--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
_
Help protect your PC: Get a free online virus scan at McAfee.com. 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Finding warnings/skips from mysqlimport

[gord barq]

Hi,

I'm importing ~2000 rows using mysqlimport with the following syntax:

mysqlimport -d  -v --ignore  -u root -p[rootpw] [database] [tablename].txt

Connecting to localhost
Selecting database [database]
Deleting the old data from table [tablename]
Loading data from SERVER file: /[path]/[tablename] into [tablename]
[database].[tablename]: Records: 1780  Deleted: 0  Skipped: 39  Warnings: 
1178
Disconnecting from localhost

But even with verbose on it doesn't tell me what the warnings are, or what 
rows were skipped. Where can I find that information?

Thanks.

_
MSN 8: Get 6 months for $9.95/month. http://join.msn.com/?page=dept/dialup
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Optimizing a query

[gord barq]

I have this query which does a left outer join and it takes forever (like 
half a day). Here are the results of an explain analysis.

mysql explain SELECT count(searchresult.title) AS number, 
campaigntrack.title, tracknum, trackid FROM campaigntrack LEFT OUTER JOIN 
searchresult ON searchresult.title = campaigntrack.title WHERE 
campaigntrack.albumid = 1 GROUP BY title ORDER BY tracknum;
+---+--+---+--+-+--+--+-+
| table | type | possible_keys | key  | key_len | ref  | rows | 
Extra   |
+---+--+---+--+-+--+--+-+
| campaigntrack | ALL  | NULL  | NULL |NULL | NULL |   62 | 
where used; Using temporary; Using filesort |
| searchresult  | ALL  | NULL  | NULL |NULL | NULL | 19875751 |  
   |
+---+--+---+--+-+--+--+-+

Here are the schemas for the tables:

campaignalbum:

| trackid  | int(10) unsigned |  | PRI | NULL| auto_increment |
| tracknum | int(11)  | YES  | | NULL||
| artistid | int(11)  | YES  | | NULL||
| albumid  | int(11)  | YES  | | NULL||
| title| varchar(255) | YES  | | NULL||
searchresult:

| title | varchar(255)  | YES  | | NULL|   |

Any suggestions on how to optimize this would be greatly appreciated.

Thanks.

_
bGet MSN 8/b and enjoy automatic e-mail virus protection.
http://join.msn.com/?page=features/virus

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Help with count(*)

[gord barq]

I have a table I'm using for logging purposes with a schema like:

create table results (
   user varchar(255)
   
);
Where user is not a unique field and I want to find out how many unique 
users there are in the table.

I want to do something like:

select count(count(*)) from results group by user;

But that doesn't work..

Any ideas?

Thanks

_
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*  
http://join.msn.com/?page=features/junkmail

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]