Re: How to delete all rows....
If its one time .. i will suggest to take a mysqldump of table structure only and drop the database import the backup . [EMAIL PROTECTED] wrote: Hi All, How do I delete all the rows of all the tables(but not table) in the database at one shot. Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to delete all rows....
DELETE FROM table -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 20, 2006 10:35 AM To: mysql@lists.mysql.com Subject: How to delete all rows Hi All, How do I delete all the rows of all the tables(but not table) in the database at one shot. Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to delete all rows....
You might also look at TRUNCATE table... http://dev.mysql.com/doc/refman/5.0/en/truncate.html I believe that DELETE will not reclaim the storage space while TRUNCATE does, although I didn't see that in the documentation when I looked just now... ? Douglas Sims [EMAIL PROTECTED] On Sep 20, 2006, at 2:55 AM, Peter Lauri wrote: DELETE FROM table -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 20, 2006 10:35 AM To: mysql@lists.mysql.com Subject: How to delete all rows Hi All, How do I delete all the rows of all the tables(but not table) in the database at one shot. Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difficult query
I´m not sure if i understood clearly. If you want to list the area and all of companies near it, you could do it: select a.AreaName as Area, group_concat(c.CompanyName) as Companys from AreaCompanys a_c, Area a, Company c where a_c.AreaID=a.AreaID and a_c.CompanyID=c.CompanyID group by a.AreaName order by a.AreaName; I hope it works. Neil Tompkins [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Hi I've the following tables (holds a list of companies) TableName:Company CompanyID (int) CompanyName (varchar) (holds a list of areas) TableName:Area AreaID (int) AreaName (varchar) (holds a list of what areas are near to what companies), TableName:AreaCompanys CompanyID (int) AreaID (int) Nearby (y/n) For example company1 is listed under area 1 but is nearby to area 2, company2 is listed under area 2 and is nearby to area 3. Based on this could I obtain a list of areas if I passed area 2 to the query. From the above example I would expect to get back the following : Area1 Name Area2 Name Thanks for any help Neil _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to delete all rows....
FLUSH table ?? Quoting Peter Lauri [EMAIL PROTECTED]: DELETE FROM table -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 20, 2006 10:35 AM To: mysql@lists.mysql.com Subject: How to delete all rows Hi All, How do I delete all the rows of all the tables(but not table) in the database at one shot. Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find all rows with no matching rows in second table
Hi André, you can do it like: SELECT a.* FROM tablea a LEFT JOIN tableb b ON b.a_id = a.a_id AND b.flag = 'y' WHERE b.b_id IS NULL; /Johan André Hänsel skrev: Hello list, I have two tables: Table A a_id name 1a 2b 3c Table B b_id a_id flag name 12yx 22ny 33nz How can I find the rows from table A where there is no matching row (joined using a_id as key) in table B where flag is y? So in this example I want the entries 1/a and 3/c from table A. 2/b should not be selected because there is a row in table B with a_id = 2 and flag=y. Understandable? It seems quite impossible to me, but I cannot figure out a reason why it is impossible, either. Regards, André -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SUM in WHERE
Following is one way of doing what you want. mysql show create table t; +--- +--- -+ | Table | Create Table | +--- +--- -+ | t | CREATE TABLE `t` ( `TransactionDate` datetime default NULL, `amount` float default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +--- +--- -+ 1 row in set (0.00 sec) mysql select * from t; +-++ | TransactionDate | amount | +-++ | 2006-01-02 00:00:00 | 20 | | 2006-01-04 00:00:00 | 178| | 2006-01-07 00:00:00 | 32.43 | | 2006-01-09 00:00:00 | 3 | | 2006-01-11 00:00:00 | -1000 | | 2006-01-15 00:00:00 | 33.9 | +-++ 6 rows in set (0.00 sec) mysql set @total=0; Query OK, 0 rows affected (0.00 sec) mysql select amount as amount1, tot as tot1 from (select amount, @total:[EMAIL PROTECTED] as tot from t order by TransactionDate) AS Tx where Tot100; +-+--+ | amount1 | tot1 | +-+--+ | 178 | 198 | | 32.43 | 230.43305176 | | 3 | 233.43305176 | +-+--+ 3 rows in set (0.00 sec) Good luck! Douglas Sims [EMAIL PROTECTED] On Sep 19, 2006, at 4:02 PM, Quentin Bennett wrote: No, I don't think it is. I think you want to have a query that will return 'n' rows where the sum of Total is = 100 If your table is ID Total 1 10 2 20 3 30 4 40 5 50 it would return 1 10 2 20 3 30 4 40 (sum total = 100) but if your table was ID Total 1 100 2 20 3 30 4 40 5 50 it would return 1 100 only. Have I got it right. Using only SQL, your best bet would be a stored procedure, otherwise its really application logic to select the rows one at a time and keep a running total. HTH Quentin -Original Message- From: Ahmad Al-Twaijiry [mailto:[EMAIL PROTECTED] Sent: Wednesday, 20 September 2006 2:24 a.m. To: Price, Randall Cc: Edward Macnaghten; mysql@lists.mysql.com Subject: Re: SUM in WHERE Actually is this possible with simple SQL command in Mysql ? On 9/19/06, Price, Randall [EMAIL PROTECTED] wrote: I tried it also with 5.0.24-community-nt and it still didn't work! Randall Price Microsoft Implementation Group Secure Enterprise Computing Initiatives Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original Message- From: Ahmad Al-Twaijiry [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 19, 2006 10:06 AM To: Edward Macnaghten Cc: mysql@lists.mysql.com Subject: Re: SUM in WHERE I tried it also with 4.1.21-log and still didn't work ! On 9/19/06, Ahmad Al-Twaijiry [EMAIL PROTECTED] wrote: I tried that before and it also doesn't work, is it because I'm using mysql version 4.1.19 ? On 9/19/06, Edward Macnaghten [EMAIL PROTECTED] wrote: Ahmad Al-Twaijiry wrote: Hi everyone snip SELECT * FROM tbl_name WHERE SUM(Total)=100 ORDER BY ID SELECT ID FROM tbl_name GROUP BY ID HAVING SUM(Total)=100 ORDER BY ID -- Ahmad Fahad AlTwaijiry -- Ahmad Fahad AlTwaijiry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Ahmad Fahad AlTwaijiry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem installing MySQL 5.0.24a from RHEL RPM on Centos Linux VPS box
Hi list, I'm trying to install MySQL 5.0.24a RHEL RPM on a Centos Linux VPS box. uname -a output on this box: Linux my.host.com 2.6.9-022stab078.14-enterprise #1 SMP Wed Jul 19 14:35:02 MSD 2006 i686 athlon i386 GNU/Linux It already has MySQL 4.1 RHEL installed on it - I checked this with rpm -q mysql, which shows: mysql-4.1.20-1.RHEL4.1 The 4.1 install came by with the box, probably (not sure but maybe able to find out from the previous administrator or the hosting provider). I don't want to uninstall the 4.1 if I can help it. Reason: though I'm fairly comfortable with Linux software installations in general, I haven't installed MySQL before, and so not sure of all the files that get installed, and their locations, particularly into other directories than the base installation directory (e.g. there is a file called my.cnf in /etc). So not sure if all the files installed with 4.1 will be properly removed if I uninstall 4.1, and if they don't, it may create conflicts; e.g. some config file of 4.1might get used when I try to run 5.0 after installing it, and this might lead to strange errors. So I want to install MySQL 5.0 into a different location. That is why I used the --prefix option in the install command that I used: rpm --install --test --hash --verbose --prefix /root/mysql5.0rhel4 MySQL-server-standard-5.0.24a-0.rhel4.i386.rpm When I run the above command (as root), I get this error message: warning: MySQL-server-standard-5.0.24a-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 error: package MySQL-server-standard is not relocatable Does this mean that this RPM can only be installed in the standard/default location? Is there any way or any other kind of package (e.g. not an RPM but a tar.gz) that will work - for the binary package, I mean - such that it will allow me to install MySQL into some base location (directory) specified by me? Otherwise I can install MySQL from the source tarball which I've already downloaded, but thought to avoid that route if possible, as I suspect it might take some time (not sure). This is because I just previously installed the GNU C++ compiler (g++) on the box, which I had to do because I had tried to install MySQL from the source tarball earlier and got a message C++ compiler fails sanity check, and Googling for that message, indicated that the C++ compiler was not installed on the system - my guess is that the C++ compiler is not there by default on the box because it is a VPS, so they want to minimize the disk space used by default. So I downloaded and installed the g++ compiler. It worked ok - I wrote a short C++ test program, could compile and run it. So the reason I want to avoid installing MySQL from source, is because the C++ compiler build+install, took a good amount of time, over 3.5 hours, probably because it compiles hundreds of small source files, and so does a lot of file I/O, and my client doesn't want to spend a lot of time on the install (the project is on a tight schedule). Thanks for any help or pointers. Vasudev Ram http://www.dancingbison.com 10.times say Truly rural
multicolumn indexes, yes or no
Hi, I have 4-5 different types of models which can be tagged. So, in the taggings table should I declare a multicolumn index on [taggable_type, taggable_id] or a single column index on [taggable_id]. What are the pros and cons of either, and which one is preferred? If multicolumn, then in what order, i.e., [taggable_type, taggable_id] or should it be [taggable_id, taggable_type]? I have another table for tracking views for different objects, and this time there are 3 columns [viewable_type, viewable_id, user_id], so should this one be a 3 column index? Please suggest something. Thanks in advance. -- Surendra Singhi http://ssinghi.kreeti.com, http://www.kreeti.com Read my blog at: http://cuttingtheredtape.blogspot.com/ , | Great wits are sure to madness near allied, | And thin partitions do their bounds divide. | | (John Dryden, Absalom and Achitophel, 1681) ` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Null !?
Roberto anybody knows how i should interpret the (null) value in a field in order to visual.net can recognize as just null and then make desicions? Compare it to DBNull.Value. PB - Roberto William Aranda-W Roman wrote: hello anybody knows how i should interpret the (null) value in a field in order to visual.net can recognize as just null and then make desicions? tanks a lot No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.12.5/451 - Release Date: 9/19/2006 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedure for list_zip_by_miles
I'm trying to create stored procedure, but after reading mysql's online document, I was not able to comprehend its usage. Here is what I do, put in target zip code and miles range, then find a list of zipcode, city, state and miles from target zip code. How do I get around to it? [code] SET @targetzip='19943';/*Establish the starting zip code.(Domain)*/ SET @rangemiles='20';/*Specify the miles range from that starting zip code.(Range)*/ SELECT C.ZipCode, Zi.City, Zi.State, Round(C.Miles) AS 'Mile(s)' FROM ( SELECT Lat_A, Long_A, Lat_B, Long_B, ZipCode, (degrees(acos((sin(radians(lat_A)) * sin(radians(lat_B)) + cos(radians(lat_A)) * cos(radians(lat_B)) * cos(radians(long_A - long_B)) * 69.09 AS 'Miles' FROM ( SELECT CAST(latitude AS decimal(8,6)) AS Lat_A, CAST(longitude AS decimal(8,6)) AS Long_A FROM zips WHERE `zip [EMAIL PROTECTED] ) AS A, ( SELECT CAST(latitude AS decimal(8,6)) AS Lat_B, CAST(longitude AS decimal(8,6)) AS Long_B, `zip code` AS ZipCode FROM zips ) AS B WHERE (degrees(acos((sin(radians(lat_A)) * sin(radians(lat_B)) + cos(radians(lat_A)) * cos(radians(lat_B)) * cos(radians(long_A - long_B)) * 69.09 = @rangemiles ) AS C, zips Zi WHERE Zi.`zip code`=C.ZipCode ORDER BY Round(C.Miles); /* RESULT +-++--++ | ZipCode | City | State| Round(C.Miles) | +-++--++ | 19943 | Felton | Delaware | 0 | | 19979 | Viola | Delaware | 3 | | 19980 | Woodside | Delaware | 4 | | 19934 | Camden Wyoming | Delaware | 6 | | 19962 | Magnolia | Delaware | 6 | | 19946 | Frederica | Delaware | 7 | | 19952 | Harrington | Delaware | 7 | | 19954 | Houston| Delaware | 8 | | 19964 | Marydel| Delaware | 10 | | 19942 | Farmington | Delaware | 10 | | 19963 | Milford| Delaware | 11 | | 19901 | Dover | Delaware | 11 | | 19902 | Dover Afb | Delaware | 11 | | 19904 | Dover | Delaware | 11 | | 19953 | Hartly | Delaware | 11 | | 21636 | Goldsboro | Maryland | 11 | | 21640 | Henderson | Maryland | 12 | | 21649 | Marydel| Maryland | 12 | | 21639 | Greensboro | Maryland | 12 | | 19950 | Greenwood | Delaware | 13 | | 19955 | Kenton | Delaware | 15 | | 19960 | Lincoln| Delaware | 15 | | 21644 | Ingleside | Maryland | 16 | | 21660 | Ridgely| Maryland | 16 | | 21629 | Denton | Maryland | 16 | | 21607 | Barclay| Maryland | 17 | | 19941 | Ellendale | Delaware | 17 | | 19938 | Clayton| Delaware | 18 | | 19933 | Bridgeville| Delaware | 19 | | 21668 | Sudlersville | Maryland | 19 | | 21641 | Hillsboro | Maryland | 20 | | 19977 | Smyrna | Delaware | 20 | +-++--++ */ [/code]
Re: How to delete all rows....
But how will that interact with the auto increment counter? Will truncate reset the counter. Chris wrote: Peter Lauri wrote: DELETE FROM table Truncate will be a lot better. DELETE FROM table will do it row by row which also means it will have to update any indexes applicable to the table as it goes... Lots of data lots of indexes = very slow. -- Chris Wagner CBTS GE Aircraft Engines [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to delete all rows....
Hi, I prefer using TRUNCATE statement. syntax: TRUNCATE [TABLE] tbl_name This takes lesser time when compared to the delete from table. It takes hardly few seconds. take a backup before executing this if required. Regards, N.Pradeep Chandru. -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 20, 2006 1:26 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: How to delete all rows DELETE FROM table -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 20, 2006 10:35 AM To: mysql@lists.mysql.com Subject: How to delete all rows Hi All, How do I delete all the rows of all the tables(but not table) in the database at one shot. Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to delete all rows....
Peter Lauri wrote: DELETE FROM table Truncate will be a lot better. DELETE FROM table will do it row by row which also means it will have to update any indexes applicable to the table as it goes... Lots of data lots of indexes = very slow. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to delete all rows....
Sorry, did not read carefully. Either you loop thru all tables an do DELETE FROM table Or as someone else suggested, dump the structure, drop database, recreate from dump. /Peter -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 20, 2006 2:56 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: How to delete all rows DELETE FROM table -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 20, 2006 10:35 AM To: mysql@lists.mysql.com Subject: How to delete all rows Hi All, How do I delete all the rows of all the tables(but not table) in the database at one shot. Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Difficult query
Hi I've the following tables (holds a list of companies) TableName:Company CompanyID (int) CompanyName (varchar) (holds a list of areas) TableName:Area AreaID (int) AreaName (varchar) (holds a list of what areas are near to what companies), TableName:AreaCompanys CompanyID (int) AreaID (int) Nearby (y/n) For example company1 is listed under area 1 but is nearby to area 2, company2 is listed under area 2 and is nearby to area 3. Based on this could I obtain a list of areas if I passed area 2 to the query. From the above example I would expect to get back the following : Area1 Name Area2 Name Thanks for any help Neil _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
Re: multicolumn indexes, yes or no
Surendra - The answer is - it depends. A multi-column index can be helpful for performance over single-column - or it can do you no good at all, depending on how you build it and how you use it. MySQL currently uses (at most) one index per instance of a table per query. This means that adding several single-column indexes will not help speed up an existing query. On the other hand, several single-column indexes may be just what you need if you query a number of different ways. Multi-column indexes are built in the order you specify the columns, and can only be used if you are specifying some or all of the columns in the same order you specified when you built the index. In other words, if you have TABLE with columns A B C and D, and you create a multi-column index on (A, B, C), your index will be helpful in these situations (order by most helpful to least helpful): SELECT D FROM TABLE WHERE A = 1 AND B = 2 AND C = 3 SELECT D FROM TABLE WHERE A = 1 AND B = 2 SELECT D FROM TABLE WHERE A = 1 You could also do SELECT D FROM TABLE WHERE A = 1 AND C = 3 but only the very first part of the index, built on A, will be used. The C part will not be searched by index. If you do SELECT D FROM TABLE WHERE B = 2 AND C = 3 SELECT D FROM TABLE WHERE C = 3 the index will not be used at all since the columns are not the first column specified in the index creation. In my experience, multi-column indexes make for wicked fast searches when you have queries that will make use of them. There's nothing stopping you from creating multi-column as well as single-column - there's some overhead in terms of disk space and speed of insert/update/delete but it's technically possible. Hope this helps. Dan On 9/20/06, Surendra Singhi [EMAIL PROTECTED] wrote: Hi, I have 4-5 different types of models which can be tagged. So, in the taggings table should I declare a multicolumn index on [taggable_type, taggable_id] or a single column index on [taggable_id]. What are the pros and cons of either, and which one is preferred? If multicolumn, then in what order, i.e., [taggable_type, taggable_id] or should it be [taggable_id, taggable_type]? I have another table for tracking views for different objects, and this time there are 3 columns [viewable_type, viewable_id, user_id], so should this one be a 3 column index? Please suggest something. Thanks in advance. -- Surendra Singhi http://ssinghi.kreeti.com, http://www.kreeti.com Read my blog at: http://cuttingtheredtape.blogspot.com/ , | Great wits are sure to madness near allied, | And thin partitions do their bounds divide. | | (John Dryden, Absalom and Achitophel, 1681) ` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Questions about using mysqlimport to update a table.
I need to update a table with the contents of a CSV file regularly, I've used mysqlimport to load all the initial data, but I have a problem with using it for updates. The data in the CSV file does not contain all of the data in the table, there is a field that is updated by another application as well. I need to be able to get updates to the data that is from the CSV file without deleting the data that is not present from those rows. If I run it with --ignore, I don't get the updates to rows that are already present, and if I run it with --replace, I lose the data that wasn't contained within the CSV file. I was really hoping to be able to use mysqlmport for this, since I need to schedule these updates fairly regularly and would like to be able to automate that process. Is there something I am missing that will make this work, or do I need to go about it in another way? Thanks
Searching for a MySQL Admin at TiVo
If anyone would be interested in a contract, please contact me! Thanks! Leann Das Recruiter, TiVo http://www.tivo.com/ http://www.tivo.com/ [EMAIL PROTECTED] Phone: 408-519-9134 Get the inside track to great jobs at TiVo http://www.jobster.com/view.html?i=UCWWZBEYWNVG →
Re: Searching for a MySQL Admin at TiVo
This person ought to be in USA? Leann Das [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] If anyone would be interested in a contract, please contact me! Thanks! Leann Das Recruiter, TiVo http://www.tivo.com/ http://www.tivo.com/ [EMAIL PROTECTED] Phone: 408-519-9134 Get the inside track to great jobs at TiVo http://www.jobster.com/view.html?i=UCWWZBEYWNVG $B*(B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find all rows with no matching rows in second table
At 03:16 AM 9/20/2006, Johan Höök wrote: Hi André, you can do it like: SELECT a.* FROM tablea a LEFT JOIN tableb b ON b.a_id = a.a_id AND b.flag = 'y' WHERE b.b_id IS NULL; /Johan Johan, I don't think that is going to work. How is it going to have b.flag='Y' when it can't find the b record, because b.b_id is null? Everything from b will be Null because that's what's what you're looking for in the Where clause. Mike André Hänsel skrev: Hello list, I have two tables: Table A a_id name 1a 2b 3c Table B b_id a_id flag name 12yx 22ny 33nz How can I find the rows from table A where there is no matching row (joined using a_id as key) in table B where flag is y? So in this example I want the entries 1/a and 3/c from table A. 2/b should not be selected because there is a row in table B with a_id = 2 and flag=y. Understandable? It seems quite impossible to me, but I cannot figure out a reason why it is impossible, either. Regards, André -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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]
RE: Question about LOTS of indexes on a table
If the combination of fields that will be subject to 'where' is unknown, and will be unknown forever, then I think you have no choice but to index each one individually and let MySQL make the choice as to which one to use. If, however, you know, or can establish, that certain combinations of 'f' will be used more often then others, then adding indices for those combination will be useful. If the table is read-intensive, then having multiple indices will improve performance (up to a point), but if its write-intensive, then adding indices will slow things down. HTH Quentin -Original Message- From: Peter Van Dijck [mailto:[EMAIL PROTECTED] Sent: Thursday, 21 September 2006 3:14 p.m. To: MYSQL General List Subject: 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] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- 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
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to delete all rows....
Wagner, Chris (GEAE, CBTS) wrote: But how will that interact with the auto increment counter? Will truncate reset the counter. According to the docs it will: http://dev.mysql.com/doc/refman/5.1/en/truncate.html The table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difficult query
Neil Tompkins wrote: Hi I've the following tables (holds a list of companies) TableName:Company CompanyID (int) CompanyName (varchar) (holds a list of areas) TableName:Area AreaID (int) AreaName (varchar) (holds a list of what areas are near to what companies), TableName:AreaCompanys CompanyID (int) AreaID (int) Nearby (y/n) For example company1 is listed under area 1 but is nearby to area 2, company2 is listed under area 2 and is nearby to area 3. Based on this could I obtain a list of areas if I passed area 2 to the query. From the above example I would expect to get back the following : Area1 Name Area2 Name select companyname, areaname from company c, areacompanys ac, area a where c.companyid=ac.companyid and a.areaid=ac.areaid and a.areaid='2'; not really that complicated. the 'nearby' doesn't get included because it doesn't matter (in this case) whether the company is in the area or just 'nearby' - you just care what's applicable to area 2. Or I could be completely misunderstanding ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Null !?
hi, You can use DBNull. DBNull - First it returns true if such a value is null, then converts the value to an empty string if it is null. For instance, If dbval Is DBNull.value then return End If Thanks ViSolve DB Team. - Original Message - From: Roberto William Aranda-W Roman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, September 21, 2006 12:03 AM Subject: Null !? hello anybody knows how i should interpret the (null) value in a field in order to visual.net can recognize as just null and then make desicions? tanks a lot
