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,

Re: where url = 'x' with url a TEXT field

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

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

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 --

Re: Question about LOTS of indexes on a table

... 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

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,

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

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'.

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

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

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

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/

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

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

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

Re: Need way to see all relations?

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

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:

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

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

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