updating table but afterwards there are duplicate entries that violate a key..
Hi all, I have a table like this TABLE -- tagid taggerid objectid There is a primary key on (tagid, taggerid, objectid). First I remove that key, then I am changing the object id (because I've changed my objects), but what happens now is that I suddenly have duplicate (tagid, taggerid, objectid) rows. Is there a way to remove duplicate rows like this? Thanks for any pointers!! Peter -- Find 1s of videoblogs and podcasts at http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where url = 'x' with url a TEXT field
Thanks.. the problem is I'm running MySQL 4.1.16 I might try FULLTEXT... On 9/27/06, Dan Buettner [EMAIL PROTECTED] wrote: You can create FULLTEXT indexes on text fields - but that may not be the best solution for your situation since you are querying for an exact match. In MySQL 5.0.3 and later, you can create VARCHAR columns of up to 65,535 character lengths: http://dev.mysql.com/doc/refman/5.0/en/char.html I seem to recall you were asking about storing URLs up to the practical limit, 2083 characters. I've never used a varchar column that long before; I'm not sure what the performance implications might be, or how effective a database index on a field that length would be. Still, worth a try. Could be that it will work smashingly. HTH, Dan On 9/27/06, Peter Van Dijck [EMAIL PROTECTED] wrote: Hi, since urls can be longer than 255 chars, I made the url field a TEXT field. The problem is, I can't make an index on it, so doing 'WHERE url='xxx'' becomes a very sloow query. Any ideas for solutions? Am I mistaken in the idea that I can't make an index on a TEXT field? Thanks! Peter -- Find 1s of videoblogs and podcasts at http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Find 1s of videoblogs and podcasts at http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update old id to new id query
Hi all, I have a pretty complex query going on... In 'transfertable' we have oldid and newid. The old id's are mapped to new id's. In table2 we have the id. To make things more interesting, the id isn't a primary key in table2, the primary key consists of 3 fields... We need to adjust table2 so that every id (which is the old id) is replaced with the newid according to 'transfertable'. I've been trying to do this, but I'm hitting the limits of my mysql wizardry.. any suggestions? I don't think we can just do update table2 set id = select newid from transfertable where oldid = id (that wouldn't work anyways?) I'm stuck, any help is very welcome! Thanks! Peter -- Find 1s of videoblogs and podcasts at http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
where url = 'x' with url a TEXT field
Hi, since urls can be longer than 255 chars, I made the url field a TEXT field. The problem is, I can't make an index on it, so doing 'WHERE url='xxx'' becomes a very sloow query. Any ideas for solutions? Am I mistaken in the idea that I can't make an index on a TEXT field? Thanks! Peter -- Find 1s of videoblogs and podcasts at http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about LOTS of indexes on a table
Thanks for the tips. So it seems that: 1) I should index the most often used ones. I am not sure what OLTP/OLAP means? Peter On 9/20/06, Peter Brawley [EMAIL PROTECTED] wrote: Peter It doesn't seem like it would make sense to make an index for every possible combination... but there must be a way to do this intelligently? It does not make sense for inserts and updates, but it sure makes sense for reproting, so have you considered separating your functionality into OLTP and OLAP dbs? PB - Peter Van Dijck wrote: Hi, I've been trying to figure this out for a while.. I have a table ITEMS with about 15 fields that can be used in any combination in where queries, let me call these fields f1 to f15. There are also 3 fields used for ordering, let's call them o1 to o3. So the table is: tablename (id, title, f1, f2, f3, f4, ..., f15, o1, o2, o3) f1 to f15 are all int(11). The table contains, let's say, 50,000 rows. The queries sent against this table can combine up to 4 f-fields, and 1 ordering field. So it could be: - SELECT * FROM table WHERE f3=x AND f7=x AND f12=x ORDER BY o3 DESC and so on.. you get the idea. The question is: we need indexes to make this go fast. How many indexes do we need? It doesn't seem like it would make sense to make an index for every possible combination... but there must be a way to do this intelligently? Any input is very welcome! I hope I explained the problem clearly? Thanks, Peter No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.12.5/451 - Release Date: 9/19/2006 -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about LOTS of indexes on a table
Hi, I've been trying to figure this out for a while.. I have a table ITEMS with about 15 fields that can be used in any combination in where queries, let me call these fields f1 to f15. There are also 3 fields used for ordering, let's call them o1 to o3. So the table is: tablename (id, title, f1, f2, f3, f4, ..., f15, o1, o2, o3) f1 to f15 are all int(11). The table contains, let's say, 50,000 rows. The queries sent against this table can combine up to 4 f-fields, and 1 ordering field. So it could be: - SELECT * FROM table WHERE f3=x AND f7=x AND f12=x ORDER BY o3 DESC and so on.. you get the idea. The question is: we need indexes to make this go fast. How many indexes do we need? It doesn't seem like it would make sense to make an index for every possible combination... but there must be a way to do this intelligently? Any input is very welcome! I hope I explained the problem clearly? Thanks, Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query question: updating between 2 tables
Hi all, trying to figure out if there is a query I can use for this, or if I have to write a php script to loop tru each row... table1: entryid int(11) itemid int(11) table2: object_id int(11) The situation is: table2.objectid is populated with the values of table1.itemid, but they have to be replaced with the corresponding table1.entryid. I could do in PHP: - select * from table2 - for each row, update table2 with select from table1 but I am wondering if there is 1 query that could take care of this? Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
storing URL 2083 characters
Hi, URL's have a practical limit of 2083 characters it seems. To store these in a space efficient way (I have 1,000,000s of url rows), what's the best approach? varchar has a 255 maximum, right? Should I just use TEXT? I'm not searching *in* the urls, I am selecting like this: where url = 'xxx'. Thanks, Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IN ANY subqueries
Or perhaps add a TEXT index to the table?
Peter
On 9/6/06, Philippe Poelvoorde [EMAIL PROTECTED] wrote:
2006/9/5, Ben Lachman [EMAIL PROTECTED]:
So possibly a better solution would be to create a temporary table
and then do a subquery on that table?
yes. Or do a :
SELECT id FROM t1 WHERE name LIKE '%s1%' OR name LIKE %'s2%' OR name
LIKE '%s3%'
but depending on your data, a table may be the way to go.
-Ben
On Sep 4, 2006, at 7:37 AM, Visolve DB Team wrote:
Hi
The ANY keyword, which must follow a comparison operator, means
return TRUE if the comparison is TRUE for ANY of the values in the
column that the subquery returns
In has 2 forms:
1. IN (subquery). [The word IN is an alias for = ANY (subquery)].
2. IN (list of values seperated by comma)
Hence the exact syntax to use is:
Select fields from table where fieldname = ANY ( select
fieldname from table);
Ref: http://dev.mysql.com/doc/refman/5.0/en/any-in-some-
subqueries.html
Thanks
ViSolve DB Team
- Original Message -
From: Ben Lachman [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Sunday, September 03, 2006 10:33 AM
Subject: IN ANY subqueries
I have a string comparison that I would like to do against a short
list of constant strings.
at the moment I am using the syntax
SELECT id FROM t1 WHERE name IN('s1', 's2', 's3', ...);
However, this limits me to exact matches and I'd like to move to a
caparison expersion that lets me match names that contain any of the
list. The MySQL docs state that 'IN()' is an alias to '= ANY()'
however when I substitute' = ANY' for IN I get a parse error. What
I'd like to do is write something like (although I figure there may
be a better way to do the comparison that I am not thinking of):
SELECT id FROM t1 WHERE name LIKE ANY('%s1%', '%s2%', '%s3%', ...);
Does anyone know a way to do this?
Thanks,
-Ben
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
http://www.myspace.com/sakuradrop : forget the rest
http://www.w-fenec.org/ Webzine rock/metal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
find videoblogs: http://mefeedia.com
my blog: http://poorbuthappy.com/ease/
my job: http://petervandijck.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing URL 2083 characters
the urls never repeat. and it's a very active table so I don't wanna compress right now. And you're right, most URLs are 255chars, but some are bigger, so can't use varchar. I guess I'll just use TEXT :) Thanks! Peter On 9/12/06, Mike Wexler [EMAIL PROTECTED] wrote: Peter Van Dijck wrote: Hi, URL's have a practical limit of 2083 characters it seems. To store these in a space efficient way (I have 1,000,000s of url rows), what's the best approach? varchar has a 255 maximum, right? Should I just use TEXT? I'm not searching *in* the urls, I am selecting like this: where url = 'xxx'. Do the URLs occur multiple times? If so, I would create a URL table, that had the URL and a primary auto_increment key. Then I would just reference the key for each instance. The other thing you could do is use the compress function. Note, that while URLs can be 2083 characters, they generally aren't. So if you use a TEXT field and had 1,000,000 URLs and the average URL was x characters long you would need (x + overhead) * 1,000,000 bytes. I would guess for most situations (x + overhead) is less than 200, so that is only about 200 MB. not particularly huge. You can probably save a factor of 2 or 3 with compress. If the URLs repeat a lot, you can probably save a lot more than that with the sepearate URL table. Thanks, Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query to find duplicate rows
Hi all, a though query problem for me... I have a table with 2 rows that matter: url and id If url and id are the same in 2 rows, then that's no good (bad data). I need to find all the rows that are duplicates. I can't think of how to approach the sql for this.. any pointers? Thanks! Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query question: most active user
I have a table with userid and text. Users write text. I want to find the top 5 users who have the most rows in this table. I can't seem to figure out the query.. is there a query possible to do this? Thanks! Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question: most active user
brilliant, that works! Thanks! On 8/20/06, Chris W [EMAIL PROTECTED] wrote: Peter Van Dijck wrote: I have a table with userid and text. Users write text. I want to find the top 5 users who have the most rows in this table. I can't seem to figure out the query.. is there a query possible to do this? Thanks! Peter SELECT Count(*) as Count, UserID FROM table GROUP BY UserID ORDER BY Count DESC LIMIT 5 -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql - multimedia database
Creating a huge media database and storing the media *inside* of mysql is a big mistake. Store the media in a filesystem. Store metadata about the media in mysql. Good luck. Peter On 7/10/06, Shain Lee [EMAIL PROTECTED] wrote: Hi , I'm just behind a mulimedia database , that wanted to be online with WAP contents. So , i decided to go ahead with mysql , and i installed mysql latest 5.0 in fedora core 5 with innoDB .upto now every thing success. But , my worries is , how can i created huge mulimedia database with mysql ? There are lots of subcatagories , in each and every main catagories , for example , Movie stars --- actors - hollywood---abc---ddd bollywood--aaasss actress - hollywoodaaa bollywood---qqq like that , there should be alot of main catagories and sub catagotries. All contents types are available , like wallpaper , greetings, ring tones ..., movies clips .etc. Those contents are supposed to stored as BLOB in mysql database itself. So , can somebody , help me to created most reliable mysql - multimedia database ? any ideas and comments are welcome , Thanx in advance, Shaine. - All new Yahoo! Mail The new Interface is stunning in its simplicity and ease of use. - PC Magazine -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need way to see all relations?
That is, as far as I know, impossible. Mysql does not know which tables are related to which ones. Peter On 6/30/06, Daevid Vincent [EMAIL PROTECTED] wrote: Is there a way with InnoDB tables to see all related tables/columns? Basically what I want to do is make a script that somehow will dynamically create a backup of a single user record. But I don't want to have to manually add a new table or column everytime to the script. So for example, I have a 'users' table. And there are all kinds of related tables like 'logs', 'preferences', etc. that all have a FK reference back to this user ID. I want to make a query or use some PHP to build a list of all tables in my DB that are references. Then I can just dump out that user ID (via this script) and it will backup that user and all related table data across all tables. ÐÆ5ÏÐ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need way to see all relations?
Oh, InnoDB tables? I was assuming MyISAM tables.. :) And I'm not a total expert, but that's my understanding. Peter On 6/30/06, Daevid Vincent [EMAIL PROTECTED] wrote: That doesn't seem right. I know that MYISAM tables don't understand relations, but INNODB tables most certainly understand foreign key constraints and in fact cause me a lot of grief sometimes when trying to insert a record or create a new table that violates said constraints ;-) DÆVID -Original Message- From: Peter Van Dijck [mailto:[EMAIL PROTECTED] Sent: Friday, June 30, 2006 6:32 PM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: Re: Need way to see all relations? That is, as far as I know, impossible. Mysql does not know which tables are related to which ones. Peter On 6/30/06, Daevid Vincent [EMAIL PROTECTED] wrote: Is there a way with InnoDB tables to see all related tables/columns? Basically what I want to do is make a script that somehow will dynamically create a backup of a single user record. But I don't want to have to manually add a new table or column everytime to the script. So for example, I have a 'users' table. And there are all kinds of related tables like 'logs', 'preferences', etc. that all have a FK reference back to this user ID. I want to make a query or use some PHP to build a list of all tables in my DB that are references. Then I can just dump out that user ID (via this script) and it will backup that user and all related table data across all tables. ÐÆ5ÏÐ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is there a way to optimize like '%..%' searches ?
Also, perhaps this is good enough for your situation: like 'some%thing%' as opposed to like '%some%thing%' in this case, mysql can use an index on that column and filter out everything that doesn't start with some. It's a start at least. Peter On 6/28/06, Dan Buettner [EMAIL PROTECTED] wrote: Martin, currently there is not a way to optimize that particular type of query. You might consider changing to fulltext indexes and searches instead, as it could be faster, but it is a slightly different animal from LIKE so may not fit your needs. http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html If there is any way you can limit the search set using another column that could be indexed, it could speed things up significantly. Date, type of document, author, etc. Even if it were an optional criteria for people it might speed up at least some of the searches. Dan On 6/28/06, Martin Jespersen [EMAIL PROTECTED] wrote: Hey all i am running mysql 4.1.20. I have a table with about 2.5 million records and i have to do queries on it that looks something like: select * from table where field1 like '%some%thing%' order by field2 This is ofcourse very slow since it refuses to use indexes... i have fairly large read_rnd_buffer (32M) and sort_buffer (32M) but this query is still slow as hell... Any hints as to how i can optimize the query? or tune my server settings to produce faster results? Regards Martin Jespersen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show full processlist question
Hi, I want to get a full list of all queries being run at a specific moment... If I look at mytop output, I'm hitting 500 queries per second. If I do show full processlist though, I only see 1 query as output - it's 'show full processlist' itself! And a few sleeping queries sometimes. How does this work? How can I get a full list of queries running within, say, a particular second? Thanks! Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query question: select * from table where id in (1,2,3) order by date uses FILESORT
Hi all, this is a problem I'm running into: A table has like 400,000 rows, with a primary key index id. I use this query: SELECT * FROM table WHERE id IN (58, 25, 75, ...) ORDER BY post_date DESC LIMIT 0, 40 The problem is, it uses the index id and then a FILESORT. Questions: 1) Am I correct in thinking that a filesort on this large table is bad? Or is mysql filesorting only the 40-row result set, and itt's not so bad? 2) Any solutions? Is this a common problem? Thanks, Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
finding the slow query
Hi, I have some troubles finding the slowest queries. My server has regular high loads when a lot of queries that hit the same tables slow down. The question is, which query is slowing it down? The others are probably just slow because the whole thing is slow. And a related question: what's a good strategy to figure out if table locking is causing queries to be slow? Thanks for any pointers, Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
