Re: Best table structure
Hello Tom, Wednesday, June 30, 2004, 1:26:52 PM, you wrote: TC My question is this: TC Would I be better keeping this format and inserting multiple rows on each TC submit, or to have one row for all 81 variables ($name, 20 x Position, 20 x TC Artists, 20 x Titles, 20 x Labels.) TC I know that the latter will be easier to query. Table design aside - you need to ask yourself this: How often will this multiple insert be occurring? Sure, you are performing 20 inserts one after the other, but if you only do this once an hour then it really isn't as much of an issue as you think I don't believe. Try and balance out the admin / code headache that would be an 81 column table vs. exactly how often this is even an issue anyway. Best regards, Richard Davey -- http://www.launchcode.co.uk - PHP Development Services I am not young enough to know everything. - Oscar Wilde -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update if insert fails
Hello Andy, Friday, April 23, 2004, 12:59:28 PM, you wrote: AF Is there such a statement where, if the insert fails (due to a duplicate AF record) an update will happen No, but you might want to look at REPLACE INTO to simulate this effect. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Question
Hello, Friday, April 16, 2004, 12:50:21 AM, you wrote: M Is it possible to use a query to select all rows from the table M where Column_Count is greater than Column_TotalCount? M Like this: M SELECT * FROM mytable WHERE Column_Count Column_TotalCount; Perhaps you ought to try it before posting? ;) -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Query Question
Hello, Friday, April 16, 2004, 12:56:32 AM, you wrote: M I did try it, and it doesn't work, I was looking for Ideas that will work. Obviously not, because that's exactly how you do it. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using 1 statement to delete from 2 tables
Hello B., Wednesday, April 14, 2004, 7:02:06 PM, you wrote: BF I'm using an older version of Mysql, and I know it doesn't support BF trigger. What I'm trying to do, is to delete records from 2 tables BF (Sessions Cart). In a nutshell - you cannot do it in a single SQL Query on MySQL 3.x MySQL 4 supports a cascaded delete, but 3 does not. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT search in form of MATCH...AGAINST
Hello Robb, Thursday, April 8, 2004, 4:30:46 PM, you wrote: RK I've got several tables with FULLTEXT indexes and on none of them can I get RK this syntax to work. What's up? Your version of MySQL perhaps? -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where clause query
Hello joe,
Monday, April 5, 2004, 3:17:27 PM, you wrote:
jc does MySQL have an equilivent for the WHERE Unique_id IN ('1',' 2', '3') as
jc used in oracle.
Yes.
--
Best regards,
Richard Davey
http://www.phpcommunity.org/wiki/296.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search question: words with numbers (ie DB2)
Hello Shane,
Wednesday, March 31, 2004, 5:43:10 AM, you wrote:
SA using MATCH(description) AGAINST('+db2' IN BOOLEAN MODE) returns no results.
SA Is this expected behaviour? If so, is there a way to circumvent it?
By default, the full text indexing engine doesn't include words with
less than 4 characters in. If you are using MySQL 4 you can change the
minimum length via the ft_min_word_len variable. On MySQL 3.x
there's nothing you can do short of changing the actual source code
and recompiling.
--
Best regards,
Richard Davey
http://www.phpcommunity.org/wiki/296.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating index on very large table
Hello Jeffrey, Monday, March 29, 2004, 7:05:27 PM, you wrote: JH I've got a problem creating an index on a MYISAM table with 302,000,000 lines, JH roughly 58 GB on disk. Attached you will find the table definition, output JH of mysqladmin variables, and mysql -e 'show status'. JH and it's still running! That's roughly 11 days and it's not complete yet! JH Here's the current file sizes under the mysql root directory... Index creation CAN take a long time, but I've never known it last 11 days :) but then I've never tried it on a table with 302 million records (although I'm sure others here have). My suggestion would have been - why not take say 10,000 records and just test the index creation on that small sub-set of the data? If it works without error then you know it will (should?) eventually finish on the final massive set. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Index not functioning
Hello Jack, Wednesday, March 24, 2004, 6:50:45 PM, you wrote: JC I tried using 'USE INDEX' and 'BETWEEN' but it didn't change anything. JC EXPLAIN SELECT counter, UNIX_TIMESTAMP(dtime) FROM ifInOctets_137 JC USE INDEX (dtime) WHERE id=2809 AND dtime BETWEEN FROM_UNIXTIME(107397) JC AND FROM_UNIXTIME(1076734799) ORDER BY dtime; Does your index include both id and dtime in a single index? If not, it probably ought to if the above is a typical query you need to run on this table. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: PHP and using mysql_last_id()
Hello Lorderon, Wednesday, March 17, 2004, 3:39:35 AM, you wrote: L It is better run a query with: L SELECT LAST_INSERT_ID(); L immediately after making the insert query. that way you'll get the exact L ID.. Why? What difference does that give between using the PHP function that returns the last ID - other than the fact it takes another query on the database? -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General Help - ERROR 1045: Access denied for user: 'ackerley@localhost' Using password: YES
Hello Rob, Friday, March 12, 2004, 7:39:47 PM, you wrote: RA ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' Using password: YES Does that user actually exist within MySQL? I have you used the mysqladmin program and created a user account called ackerley? If not, that's why you can't get in. Try using root and giving nothing as the password. If that lets you in, create yourself the ackerley user. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing Queries
Hello Chris, Tuesday, March 9, 2004, 4:38:00 PM, you wrote: CF I'm trying to determine the best way to optimize the query below. Right now CF it is taking around 9mins and we need it to take no more than 30 seconds (we CF can get it under 30s on MS SQL): CF | 1 | SIMPLE | speedlink | ref | CF idx_state,idx_county,idx_phone,idx_homeowner,idx_hpm,idx_ph,idx_first,idx_la CF st | idx_state | 3 | const | 2840162 | Using where | Some things that have been recommended to me which may be useful for you: Is the table in question Fixed or Dynamic? Do anything you can, including separating out dynamic length fields to joined tables, to make your main table fixed in length. Your fields appear to be indexed individually rather than collectively, so out of the 8 possible indexes in use, it's only using one of them (state). Try combining common/grouped indexes together based on the type of query you run most often. If MySQL is using the wrong index (or one that isn't as efficient as it could be) force it to use another. I don't know about your table definition, but check to see if you really do need to use the datatype you've currently selected. You can shave MBs from the total table size just by optimising your use of data types. Just some thoughts anyway. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update field conditionally
Hello Terry, Tuesday, March 9, 2004, 5:11:00 PM, you wrote: I know you have some solutions to the original problem already, but I just wanted to make one small observation: TR The fields are to be CounterCode (Varchar 10), CounterValue (Int 10) and TR CounterStartDateTime (DateTime). Using a varchar(10) for the CounterCode will give you a Dynamically sized table. If you changed this to char(10) you will have the speed benefits of a Fixed size table which MySQL will be able to process significantly faster. This could be especially useful if this counter is to be hit a lot of times (i.e. it's a popular site). -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Optimizing Queries
Hello Chris, Tuesday, March 9, 2004, 6:15:56 PM, you wrote: CF Why does it only use the one index? It will evaluate the best index to use for the query and if all you have are single-field indexes, it can only select one of those. From the MySQL manual: If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer tries to find the most restrictive index by deciding which index will find fewer rows and using that index to fetch the rows. Might be worth checking over this page: http://www.mysql.com/doc/en/MySQL_indexes.html -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Update field conditionally
Hello Terry, Tuesday, March 9, 2004, 6:25:00 PM, you wrote: TR Good point, Richard. I was perhaps in a little bit too much of a hurry TR putting that together, and didn't even consider that! No worries. One other thought that occurred to me that might help with the original problem is as follows: Instead of having the date when the counter started as a date-time field, you could construct your table as so: counter_code char(10) :) counter_value int(10) counter_last_modified timestamp counter_started timestamp By replacing the single started date with 2 time stamps you won't ever have to actually worry about the date again because on the very first INSERT both time stamps will be set and on any future UPDATE you can simply do counter_value = counter_value + 1 and the modified field will change automatically, leaving the original started field intact. This also presents the option of showing to the client/visitor the last time a page was visited (and you just know that might be the next request on the list :) -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Problem
Hello Eric, Tuesday, March 9, 2004, 11:46:23 PM, you wrote: ES We have a table with a Date Time field and we need to update only the date ES portion of that field. ES We have table a with field Foo with value '2004-01-01 12:15:00' and a date ES '2004-03-01' and we need to change the date portion of Foo to the date and ES leave the time part alone. ES So Foo would change from: ES '2004-01-01 12:15:00' ES to: ES '2004-03-01 12:15:00' I would have thought that: UPDATE x SET y = DATE_ADD(y, INTERVAL 2 DAY) would work and not alter the time, but only a test will tell. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Date Problem
Hello Peter,
Wednesday, March 10, 2004, 12:16:51 AM, you wrote:
PB But that is adding two days, the original query was to add two
PB months, so presumably it should be INTERVAL 2 MONTH - but beware
PB that MySQL does some
It should, sorry, I'm too used to the UK date format (even though I
know MySQL doesn't use it) but you get the idea anyway.
PB seriously bizarre things with dates - for example adding 2 months
PB to 31st Dec takes you to 31st Feb which probably isn't what you
PB want.
Doesn't for me:
SELECT DATE_ADD('2004-12-31 00:00:00', INTERVAL 2 MONTH)
Gives me 2005-02-28 00:00:00, which is what I would expect. That's
on MySQL 3.23.58, so I doubt if they broke it in any version since.
MySQLs date handling has never caused any problems for me (when I
remember the correct y-m-d format :)
--
Best regards,
Richard Davey
http://www.phpcommunity.org/wiki/296.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Optimizing Queries
Hello Chris, Wednesday, March 10, 2004, 12:48:02 AM, you wrote: CF Thanks for the email. I created a multi-field index using the fields that CF are in the query and the query only took 0.91 seconds. That's better than 9 CF minutes and definitely under 30 seconds. Glad to hear it. Farewell MSSQL :) -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Duplicates returns in query?
Hello Erich, Thursday, March 4, 2004, 3:23:41 PM, you wrote: EB I have a problem with a select returning duplicates, even though there EB aren't any duplicates in the database. EB select EB e.EventID, EB date_format(e.EventDate, '%c/%d/%y') as EventDate, EB e.EventTime, EB v.VenueName, EB v.VenueID EB from EB events e, EB venues v; EB +-+---+---+-+-+ EB | EventID | EventDate | EventTime | VenueName | VenueID | EB +-+---+---+-+-+ EB | 2 | 3/06/04 | 09:00:00 | The Bombshelter | 1 | EB | 3 | 3/12/04 | 09:00:00 | The Bombshelter | 1 | EB | 2 | 3/06/04 | 09:00:00 | Goodtimes | 2 | EB | 3 | 3/12/04 | 09:00:00 | Goodtimes | 2 | EB +-+---+---+-+-+ EB I tried adding a distinct to the above select, which had no effect. I EB also ran an explain on the query: EB Which tells me nothing. What have I done wrong here? First of all, I would be quite concerned that your EXPLAIN query showed that you were not using a single key/index in your query. But, this is not the place to get into a discussion about that yet - just something to think about. You are getting duplicates because of the SELECT part of your query - you're asking for 5 values and while you have duplicates no-where are ALL of the values duplicated. DISTINCT probably isn't what you need here, instead look at using a GROUP BY on perhaps the VenueName (if that is what you need grouped). -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: plz help
Hello CurlyBraces, Wednesday, March 3, 2004, 1:22:51 PM, you wrote: CTPL SO i want to add colors for this status. CTPL up = green CTPL down = red CTPL how can i do that ? can some body help me ..plz You asked this question a few days ago, didn't you read the reply you got last time? -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing the primary key
Hello Michael, Wednesday, March 3, 2004, 1:40:00 PM, you wrote: RM I have a table, that has a primary key with two columns and want to add a RM third column to this primary key. RM Is this possible and when yes: Do I have to delete all tables, that RM reference to this table? No, you don't have to delete all tables that reference this table. You can drop the key: ALTER TABLE tablename DROP PRIMARY KEY and then re-create it: ALTER TABLE tablename ADD PRIMARY KEY (a,b,c) Please note that if you have a field with a property such as auto-increment then dropping the primary key will fail because it will leave an invalid table definition. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Remove a RPM Installation
Hello Rafael, Tuesday, March 2, 2004, 2:09:13 PM, you wrote: RDV Please how can I remove a RPM installation. I used RDV MySQL-server-4.1.1-1.i386.rpm to install MySQL, but how can I RDV delete it. So far I have received 11 copies of this message - each has a unique created date so either my mail client is playing silly buggers, or you're sending far too many copies of this to the list Rafael. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimising LIMITs
Hi all, I have what is probably a quite standard question and would love to know how you would all approach this scenario: I have a table in a database that has approx. 190,000 records in it. The table is currently 128MB in size and I'm happy that it is well constructed with no data duplication and sensible indexes. I'm using MySQL 3.28.58. and my question is about querying this volume of data efficiently. The table holds forum threads (several years worth) so a common query running on the table is to bring back the top 50 or 100 threads from a board within the forum. To do this I'm using a LIMIT on my query and for the paging through the data (i.e. the first 100 threads, the next 100, etc) I use the LIMIT n,x syntax. Threads are sorted by date (most recent to the top). This is fine and it works well but I'm concerned it's not the most efficient way to do this because the use of LIMIT is causing the whole table to be scanned each time. Here is a typical (simplified) query: SELECT * FROM thread WHERE thread.status='L' AND thread.boardid=1 ORDER BY created DESC LIMIT 100,50 This takes over 1.02 seconds to process. Running an EXPLAIN on my query shows that it's using one key (boardid), but in the Extra field it shows it is having to use a filesort on the data. 5701 rows were used in order to bring back the final 50 - that's every single thread for this board. What I'm trying to figure out is a more efficient way of selecting a block of 50 or 100 records from any point in my table without MySQL needing to sort/check them all first. One thought I did have was that the Primary Key on my table is called threadid - and I thought that instead of bring back the data in my original query, I could collect nothing but the thread IDs and then use a separate query that does something like: SELECT * FROM thread WHERE threadid IN (...) (where ... = all of the IDs previously selected). Would the fact that threadid is my primary key make the original LIMIT/sort faster? Any thoughts appreciated. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Type TEXT
Hello Jim, Monday, March 1, 2004, 5:44:22 PM, you wrote: JM Are the TEXT column types padded out to their max length by spaces? No, they're variable length and at a maximum of 65KB per row, be thankful of this! A 30 character string will take up 32 bytes for example. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Type TEXT
Hello Jim, Monday, March 1, 2004, 8:58:51 PM, you wrote: JM Thanks. What (if any) difference is there then be between a varchar(255) and JM a tinytext column? On the surface nothing I believe. Varchar too is a variable length data type. There may well be internal differences in the way MySQL handles them however, but I couldn't tell you what. JM And what disadvantages are there be in using columns of type text, rather JM than varchar(n)? Are there performance penalties? If your data will always fit into 255 characters (or less) then use a varchar, if you always know the exact length then use a char. Use the text range of data types if you need more storage space but still want to be able to run fulltext indexes etc. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
