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 __

I have a table that contains amongst other things the following columns: comment: text date: int(10) The date is a unix timestamp and what I'd like to be able to do is run a MySQL query to show the number of comments made each week or each month so that the output might be something like: January 2009: 345 February 2009: 384 etc. etc. I'm sure there's a way to do it with a single SQL query but have so far failed to get it working. Any help much appreciated. ___

OK, well I added the query to my script and it does seem to be working so many thanks for everyone's help and especially to JustLikeIcarus. I'm not sure why phpMyAdmin was screwing up the pagination but as long as the query works and displays correctly from within my script (which it does!) I can live without knowing

I'll go try the query within my own php script and see what happens

That's probably my fault for not explaining clearly enough. When I run the query that we ended up with within phpMyAdmin it executes successfully and says: Showing rows 0 - 29 (264 total, Query took 0.0246 sec) but it then doesn't display just 30 rows as I'd expect but ALL of them, 264 in this case. If I then select the second page of results (there's obviously 9 in total) I then don't get rows 30 - 59 but all of them again and so on for all the 9 pages. Not sure if that explains what's happening any clearer?

Yes, that works OK and lists both the authors and co-authors individually but I changed select coauthor as authors FROM library WHERE coauthor IS NOT NULL to select coauthor as authors FROM library WHERE coauthor != '' as the co-author field doesn't use NULL when empty.

That executes but I still get all the rows shown (255) on each page of the phpMyAdmin output rather than just 0-29. then 30-59 etc?

That's OK, I really appreciate the help. This runs OK and I got rid of the blank author row by changing: select coauthor as authors FROM library WHERE coauthor <> 'Unknown' to select coauthor as authors FROM library WHERE coauthor <> '' So, basically the only problem now is that phpMyAdmin is displaying all 255 results on a page when it thinks it's only showing 30. I'm not sure how this would get handled if I was to try and output the result using while ($row = mysql_fetch_array($result)) from within my php script.

This query gives the following error: Unknown column 'authors' in 'field list' I tried renaming 'authors' to 'author' and that then executes but gives me the same result as previously with the mixed up row count...

LOL. I suck far more than you because I didn't even know where to start OK, well this time the query runs and the co-authors seem to have been included in the totals! However, when I run the query in phpMyAdmin something strange happens. It reports the number of rows as 265 and says 'showing rows 0 to 29' but then it actually shows ALL 265 rows and does the same for each of the 9 pages of results .. it also starts off with a row where the author field is blank and the total number of records in the table is shown against it?

Thanks for the updated query, still giving an error though I'm afraid: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) as total FROM ( select author FROM library WHERE author <> 'Unknown' UNIO' at line 1