lxndr

Academic really as I'm not at liberty to change the existing database structures as I pointed out in my original post. I was looking for a way of resolving the issue with them as they are. If it can't be done then so be it.

I already explained that that's the way it is and that I don't have the possibility to change it. Secondly, with 50,000 users and say an average of 3,000 documents viewed each that's a table with 150 million rows!

I'm wondering if anyone can help me with a MySQL problem I have. The first thing to say is that I am limited in the changes I can make to the database so basically have to work with what I've inherited. The problem I have is with a library system written in php that allows searches to be made of the types of documents that are held within the library. The existing search routine works fine but there is now a requirement to allow the search results to consist of ONLY those documents which have not yet been read by the user. There are basically two main tables involved: library document table: indexed by rec_id which is unique and numerical. user table, contains a text field entitled docread which holds all the rec_ids of the documents which have been read by the user, stored in a delimited form, e.g. 1654|7653|543| and so forth. Initially, I was able to get the following to work by adding this to the existing query $sql .= " AND rec_id NOT IN ({$exclude_list}) "; where the $exclude_list variable is a list of all the document ids separated by commas. Unfortunately, further investigation revealed that it was only working for some users and not others. I looked at a number of the user records and discovered that some of them had viewed over 6,000 documents so I guess the above is not the way to do it and likely to produce an enormous SQL query. Given the structure of the rest of the existing software I don't really want to do down the route of solving this via PHP and was wondering whether there was a way of tackling it using MySQL directly. My own experience with MySQL is very limited so I was wondering whether anyone had any constructive suggestions. I'm not in a position to do away with the delimited field though.

Update: Now solved, I needed to create the div to be appended in the parent not the iframe.

The following code is being used to add a div to the parent page from within an iframe and is working in Firefox, Chrome and Opera but not in IE6 or IE7 (the other IEs haven't been tested yet): parent.window.document.body.appendChild(newDiv); Can anyone suggest what the problem might be and whether there's a way round it ? Thanks in advance.

Thanks to everyone who responded. I've been reading up on the whole indexing process and after a bit of trial and error using EXPLAIN have been able to make some big improvements in many of the tables. There are still one or two queries that I'm unsure of in terms of whether I can optimize them, for example, can the following be optimized, right now EXPLAIN shows that it accesses all the rows in the tabke: SELECT * FROM users_table WHERE UPPER(LEFT(user,1)) ='A' AND (status = '' OR status ='B' ) ORDER by user ASC, status DESC user: varchar (20) status: varchar (16) I've tried adding indexes for user, status and user-status in combination but it hasn't made any difference. Explain returns: type: all possible keys: status key: null key_len: null Extra: Using where; Using filesort I assume the problem is being caused by the UPPER(LEFT(user,1)) fragment but not sure what to do about it ?

Thanks for replying. I don't have any specific performance data except that the website host has complained a couple of time about MySQL resource utilisation and has suggested we consider a number of things including the creation of additional indexes. So basically, from the little I know, I'm assuming any of the queries I listed for this particular table will be accessing every row rather than just a a handful each time. As it stands, none of the columns are unique and there is no primary key. If I wanted to create an index on 'story_id' using phpMyAdmin I assume I can call the index name anything I want ... would I be right in thinking I'd need to choose index type of INDEX and then select the 'story_id' where it asks for 'field'? There's also a box for "size" which I'm not sure about .. do I set that to the same size as the 'story_id' field .. i.e. 7 ?

I'm something of a beginner as far as MySQL is concerned so please go easy on me. I have a table with the following structure: story_id INT 7 date INT 10 userc varchar 45 comment text status char 1 None of the columns are indexed and as the table now has over 20,000 rows I'm wanting to create some indexes to improve performance. The table gets around 70 to 100 inserts a day and having gone through the various scripts that access it in one way or another I've found the following queries (I've replaced the variable names with 'value' to try and simplify things): DELETE FROM table WHERE story_id = 'value' AND userc = 'value' AND date = 'value' LIMIT 1 SELECT * FROM table WHERE story_id = 'value' AND userc = 'value' ORDER BY date DESC SELECT * FROM table WHERE story_id = 'value' ORDER BY date DESC UPDATE table SET status = '' WHERE story_id = 'value' AND date = 'value' LIMIT 1 DELETE FROM table WHERE story_id = 'value' AND date = 'value' LIMIT 1 SELECT * FROM table WHERE userc = 'value' SELECT * FROM table, table2 WHERE table.status <> 'R' AND table2.avail ='0' and table.story_id = table2.rec_id ORDER by date DESC SELECT * FROM table WHERE status <> 'R' AND userc = 'value' ORDER by date DESC SELECT * FROM table WHERE status = 'U' ORDER BY date ASC Basically I'm not sure whether to create more than one index and whether to create indexes on multiple columns. If someone who has more experience with creating indexes could take a quick look at the queries above and make some suggestions I'd really appreciate it. Many thanks in advance.

No, they are being stored but I can't then retrieve them and assign them by setting innerHTML. Perhaps there's no way round it and I'll have to find another way of doing it.

I have an html form which features a textarea. The textarea is originally populated by allowing the user to click on a link which executes the following javascript: document.getElementById('styled').innerHTML='$storyline'; where styled is the id of the textarea and $storyline the php variable containing the text as retrieved from a MySQL database table. The user can then make changes to the text within the textarea and then click Update to have it written back to the database and so forth. It all works fine until they use a carriage return within the textaraa. I can't convert to <br /> as the innerHTML won't work then. Is there a way round this? Thanks in advance for any help.

Thanks for responding to my post. What I showed was merely to illustrate the structure of the scripts as I'm sure you realised, there are no such syntax errors in the actual scripts. Short open tags are not used in the actual scripts, the content of the include statement is one single function which is used throughout the main index.php script so is not failing. There are no spelling errors. Yes to both the above. I'm not in a position to share the actual code but thanks for your suggestions anyway. __

I've inherited some php scripts and one of them is generating an error. The scripts are quite large but in essence the problem is being caused by the following in that script1.php which is loaded into index.php via an "include" statement in turn tries to access a function within index.php itself and subsequently generates an error that the function has not been declared. Is there a recommended way to fix this? Simplified scenario: index.php <? include("script1.php") function update_log() { } $var = function2(); ?> script1.php <? function2() { update_log(); } ?>

I have a database item which always has 16 characters (varchar), each of which is set to either "0" or "1". It's used to indicate which of 16 different characteristics (a "1" indicates the existence of a characteristic) are present in a particular item and has values such as: 0000010000000010 0000000000000000 1110110110000001 etc What I'd like to be able to do is produce some statistics to show how often individual characteristics have occurred. I can see that I could easily do that by running 16 SQL queries (one for each of the 16 characters) but was wondering if there was a better or more efficient way of doing it ? Any help greatly appreciated.

Hehe, thanks, that did the trick, your help much appreciated! Best wishes, Ian

Hi Keith, Many thanks for the suggestion but when I try that query I get an error message saying function UNIXTIME does not exist? Best wishes, Ian __