RE: How many colums should a index contain?
John, Things to consider are that only one index can be used in a query, and it's what's in your where clause that's important. Therefore, your search (where bid = ...) will only use an index that has bid as the first column in it. Therefore your multicolumn index wouldn't be used, as id is the first column in the index, but id isn't in the where clause of your query. Sometimes you need to make lots of multicolumn indexes on a table to optimise all of your queries. Sometimes this makes the indexes much larger than the data itself. Hope this helps, Andy -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: 03 November 2006 16:04 To: John.H; mysql Subject: Re: How many colums should a index contain? I think you want to create separate indexes. Indexes are basically a sorted list. So a single index on all those fields would sort the data first by id, then bid, then title,... If id is unique, then there is absolutely no reason to add other fields to the index. Think of a compound index as a field that combines all the fields specified connected in the order specified. So in your example, searching on title wouldn't use the index because the index is first on id+bid then title. Create indexes so the database can quickly narrow down the number of records it needs to search on. If you do a SHOW INDEX ON tablename, you'll see a column called cardinality. This is the uniqueness of the data in the index. Higher numbers indicate more uniqueness. A cardinality of 2 is bad, since that indicates there are only 2 unique values. Using that index means it would still have to search half the database, might as well search the whole thing. Create separate indexes on the fields you mostly search on. - Original Message - From: John.H [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Thursday, November 02, 2006 3:25 AM Subject: How many colums should a index contain? I have two tables and I must do : select `id`,`bid`,`title`,`link`,`bname` from table1 where `bid` in ( ...this is a subquery in table2 ) should I create a index (`id`,`bid`,`title`,`link`,`bname`) so that my query will take less time or should a index contain so many colums? -- 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: Reply / Return Address of this List
Yes this battle has been fought before. But this is still a pain in the ass. Whilst the list is unmoderated, surely someone at MySQL has the capacity to make a change to the server configuration of whatever hosts the list? How many times has someone had their problem solved by someone who accidentally emailed them direct, rather than via the list. So the solution was never seen by anyone else and never made the archives. Hence the same question gets asked again... and again... I know I've accidentally emailed suggestions to people directly a number of times because of this (I, like many, many others pragmatically use MS outlook), and invariably the only reason I even find this out is when the person thanks me directly rather than through the list. Come on, let's move into the nineties and sort this out. Andy -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: 18 April 2006 13:05 To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: Reply / Return Address of this List [snip] 1. Please always reply to the List. Who runs this list? Could it please be configured to send replies back to the list rather than the individual? It's really annoying to keep ending up with a personal address - it would make things so much easier, and is, to my knowledge, standard practice for mailing lists to have replies automatically go to the list itself. [/snip] This is an un-moderated list and this little battle has been fought. Just hit Reply-all or whatever your e-mail client allows. Many lists (many, many older lists especially) are set up just like this one. -- 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: Reply / Return Address of this List
Ok, As expected, multiple flames were sent in my direction for suggesting that this list should behave in the way that a logical person would expect it to. I accept that this was probably done for a reason, and that other lists work this way (though I've never seen one), so maybe I'd feel better if someone could actually tell me a reason why it is better this way? Thanks, Andy -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: 18 April 2006 13:44 To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: Reply / Return Address of this List [snip] However, if the subject has been addressed and the decision made, then there's just no point in this topic. I guess those of us that don't like it, or don't like people inadvertently posting personal replies thanks to that decision, should simply find another list. [/snip] Either that or learn how to move their cursor 1 to the right before they click. Have you STFA for this? Again, Alec points out that there was probably good reason for setting it up like this. Again, I will point out that several are like this. -- 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: query help?
Richard, If you mean with _both_ the same id _and_ vendor id, try this: Select id, vendor_id, count(*) from tablename group by id, vendor_id; If you just want separate counts for id and vendor_id, use: Select id, count(*) from tablename group by id; Select vendor_id, count(*) from tablename group by vendor_id; Andy -Original Message- From: Richard Reina [mailto:[EMAIL PROTECTED] Sent: 23 February 2006 16:48 To: mysql@lists.mysql.com Subject: query help? I am a novice when it come to queries such as this and was hoping someone could help me write a query that tells me how many records have the same ID and vendor number. |ID | vendor_no | date| |2354 | 578 | 2005-12-23| |2355 | 334 | 2005-12-24| |2356 | 339 | 2005-12-26| |2357 | 339 | 2005-12-26| |2358 | 339 | 2005-12-26| |2359 | 445 | 2005-12-26| |2354 | 522 | 2005-12-27| |2355 | 522 | 2005-12-27| Would I use select count? Any help would be greatly appreciated. A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Geographical advice
James is right. I use this method on a table with a combined index on 50 million rows and it's almost instantaneous. Performance was vastly improved after I did an alter table order by x Andy -Original Message- From: James Harvard [mailto:[EMAIL PROTECTED] Sent: 10 January 2006 14:27 To: Ben Clewett Cc: mysql@lists.mysql.com Subject: Re: Geographical advice If you visualise your search area as a circle around your 'target' coordinates, then you can eliminate many of the irrelevant rows by search for coordinates that fall within a square surrounding that circle. So, imagine a simple grid with target coordinates of 6,8 and a search radius of 3. Therefore you search WHERE x BETWEEN 3 AND 9 AND y BETWEEN 5 AND 11. I'm not certain but I think MySQL should be able to used a combined index of (x,y) for that. As you probably know you can use EXPLAIN SELECT to check whether MySQL is using an index. HTH, James Harvard At 12:01 pm + 10/1/06, Ben Clewett wrote: I have a need to locate (x,y) coordinates from mysql where they are close to another coordinate. For instance, all pizza bars near my car. Example: Searching for points closer than z to (i,j) using Pythagoras: SET i = 10; SET j = 10; SET z = 30; SELECT x, y FROM coordinates WHERE POW(x - @i, 2) + POW(y - @i, 2) POW(@z, 2) Big problem! Must searches every row. Linear indexing not able to help here. -- 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: working w/UK postcodes
Hi, There is a database available which maps post codes to grid references. This is controlled by the Royal Mail. See http://www.royalmail.com/portal/rm/jump2?mediaId=400088catId=400084 This may also be interesting: http://www.jibble.org/ukpostcodes/ In case you don't know, UK post codes consist of between 5 and seven characters which are in four parts with increasing resolution: City/Area Code (one or two letters) District (one or two digits) Sector (one digit) Unit (two letters) See http://www.equibrand.co.uk/postcodes.html They are usually written with a space between the District and Sector. Eg: W1 2AA MK6 3AB SN25 1DD This structure means you can easily give a central location for a partial postcode, ie right truncated to the sector, district or just the city. Hope this helps, Andy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 09 January 2006 13:12 To: [EMAIL PROTECTED] Cc: MySQL List Subject: Re: working w/UK postcodes I need to do a search query on the columns called Name of business the name of the business Town Local town or city name Postcode this is the same as your zip code Categorythis is type of business ie dress shop bakers XY this is the table of geo address maped by postcode I need the search to give me acurate results by the above fields. Example: a user is searching Heywood postcode ol10 1jb and category hotel, We need to see the nearest hotel to that post code and then the next nearest and so on up to 24 hotels Is there way to query this in the same manner one would do it with US zipcodes? The only effective way to do this is with geographic coordinates (your XY, I guess). While US zip codes _generally_ increase east to west and _usually_ have a numeric difference somewhat related to their geographic distance, this isn't always true. I know of two island zip codes (unrelated to those that surround it) in New York State. -- 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: Performance problem
Marco, Traurig - ich habe das googleübersetzungshilfsmittel benutzt, um zu versuchen, Ihnen zu helfen. Bevor Sie Benutzerprioritäten betrachten, schlage ich Sie Blick an vor, wie Ihre Daten registriert werden. Mysql verwendet nur einen Index pro Frage, also muß Ihre Tabelle einen Index auf jedem Feld in haben WHERE Klausel, plus das Datumfeld. Ich vermute, daß dieses nicht der Fall ist, da die Datenausgabe für eine mysqldatenbank ziemlich klein ist. Blick auf den EXPLAIN SELECT, zu sehen, was Indizes für eine Frage verwendet werden. Andy -Original Message- From: Nuno Pereira [mailto:[EMAIL PROTECTED] Sent: 29 September 2005 11:24 To: Marco Baar Cc: mysql@lists.mysql.com Subject: Re: Performance problem What? Please post in english. Marco Baar wrote: Hallo, Ich benutze mysql seit 2 Jahren. Inzwischen haben sich 50MB Nutzdaten angesammeln und hab ein Problem mit der Performance während der Abfrage meiner Haupttabelle. Die Tabelle hat ca 200.000 Datensätze und ich mache im verhältnis zu anderen Abfragen doch ein eher simplen select. Ich selektiere mit Prüfung auf 4 Attribute (mit = ) und es dauert 0,2- 0,4 sekunden. Damit kann ich leben. Diese Abfrage musste ich abwandeln und will mir nur die ersten 50 Ergebnisse anzeigen, die nach Datum sortiert sind. Diese allerdings dauert ~3,5 sekunden und legt meinen Rechner lahm. Sogar der Sound wird unterbrochen. 2. Frage: Ich würde dies gerne als Daimon oder Server im Hintergrund laufen lassen, so dass mich die Datenbankabfragen nicht im Laufenden Betrieb stören. Trotzdem sollen doch relativ complexe Abfragen gemacht werden. Ist es möglich, bestimmte Benutzer mit sehr geringer Priorität auszustatten, dass deren Abfrage nicht über einen bestimmten %-Wert der Cpu-last geht und somit den laufenden Betrieb nicht stören? Vielen Dank für die Bearbeitung meiner Anfrage. Marco Baar -- Nuno Pereira -- 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: Fulltext behavior in 3.23.58
Nitzan,
In the unlikely event that you can recompile but not upgrade, you could add
your common words to the full text stopword list in myisam/ft_static.c and
rebuild mysql.
Otherwise, this might be helpful
http://lists.mysql.com/mysql/132649
Andy
-Original Message-
From: nitzan shaked [mailto:[EMAIL PROTECTED]
Sent: 22 September 2005 14:57
To: mysql@lists.mysql.com
Subject: Fulltext behavior in 3.23.58
... this one is peculiar, and I *do* hope there's a workaround:
Using MySQL 3.23.58 and cannot upgrade. When running SELECT * FROM
tblname WHERE match(a) against('very_common_word') LIMIT 1 the whole
table is scanned just to give me no results at all. The very common word
*does* appear, and appears in more than 50% of the lines.
However, I would think that there is no need to scan the whole table just
for that. In MySQL4 the result comes out much quicker, but still very
slowly.
To contrast, if I use a_non_existent_word instead of
a_very_common_word I get 0 rows immeidately. If I use
an_existing_but_not_common_word I get 1 row quickly: not immeidately as
a non-existing row, but not so slowly as a very common word.
Questions:
1) Why the different behavior between MySQL 3 and 4 ?
2) How to circumnavigate in MySQL 3?
tia,
Nitzan
--
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: Startup Error
Richard, In my instructions, the chowns are as follows: shell chown -R root /usr/local/mysql/. shell chown -R mysql /usr/local/mysql/data shell chgrp -R mysql /usr/local/mysql/. Note no slash after data. However, you have world read and write on the data folder anyway The mysql folder is a link into /root/mysql-max-4.1.14-pc-linux-gnu-i686 My guess is that the tomcat user can't create a file in /root/ mysql-max-4.1.14-pc-linux-gnu-i686/data What do you get if you do: ls -l /root/mysql-max-4.1.14-pc-linux-gnu-i686 Can tomcat write into this? Andy _ From: Johnson, Richard (NY Int) [mailto:[EMAIL PROTECTED] Sent: 20 September 2005 12:00 To: 'Andy Eastham' Subject: RE: Startup Error [EMAIL PROTECTED] mysql]# ls -l /usr/local/mysql lrwxrwxrwx1 root root 40 Sep 19 09:00 /usr/local/mysql - /root/mysql-max-4.1.14-pc-linux-gnu-i686 Richard Johnson 212-589-6503 [EMAIL PROTECTED] -Original Message- From: Andy Eastham [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 20, 2005 4:58 AM To: Johnson, Richard (NY Int) Subject: RE: Startup Error Richard, What output do you get from: ls -l /usr/local/mysql Andy -Original Message- From: Johnson, Richard (NY Int) [mailto:[EMAIL PROTECTED] Sent: 19 September 2005 19:57 To: 'mysql@lists.mysql.com' Subject: RE: Startup Error Still getting the following errors after running the chown's below... Here is what the ls -l result window shows... mysql]# ls -l /usr/local/mysql/data total 20536 -rw-rw1 mysqlmysql10485760 Sep 19 10:05 ibdata1 -rw-rw1 mysqlmysql 5242880 Sep 19 10:05 ib_logfile0 -rw-rw1 mysqlmysql 5242880 Sep 19 09:33 ib_logfile1 drwxr-x---2 mysqlmysql4096 Sep 19 09:30 mysql The error log shows.. 050919 14:44:36 mysqld started 050919 14:44:36 [ERROR] bdb: /usr/local/mysql/data: Permission denied 050919 14:44:36 [ERROR] bdb: /usr/local/mysql/data/log.01: Permission denied 050919 14:44:36 [ERROR] bdb: PANIC: Permission denied 050919 14:44:36 [ERROR] bdb: PANIC: DB_RUNRECOVERY: Fatal error, run database recovery 050919 14:44:36 [ERROR] bdb: fatal region error detected; run recovery 050919 14:44:36 [ERROR] bdb: /usr/local/mysql/data: Permission denied 050919 14:44:36 InnoDB: Started; log sequence number 0 43634 050919 14:44:36 [ERROR] Can't init databases 050919 14:44:36 [ERROR] Aborting 050919 14:44:36 InnoDB: Starting shutdown... 050919 14:44:38 InnoDB: Shutdown completed; log sequence number 0 43634 050919 14:44:38 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 050919 14:44:38 mysqld ended Richard Johnson 212-589-6503 [EMAIL PROTECTED] -Original Message- From: J.R. Bullington [mailto:[EMAIL PROTECTED] Sent: Monday, September 19, 2005 11:49 AM To: mysql@lists.mysql.com Subject: RE: Startup Error Well, as you can see in the first few lines, you have a Permission Denied problem. This means that your /data/ folder doesn't have the write permissions to it via the mysql user. Try running the permission scripts again, but this time use the full shell names. shell chown -R root /usr/local/mysql/. shell chown -R mysql /usr/local/mysql/data/. shell chgrp -R mysql /usr/local/mysql/. Then check to make sure that user has write permission to the folders: shell ls -l /usr/local/mysql/data It should say something similar to: shell ls -l /usr/local/mysql/data total 63868 drwx--2mysqlmysqlsizedatedirname -rw-rw1mysqlmysqlsizedatefilename- bin.000 . . -rw-rw1mysqlmysqlsizedatehostname.err J.R. _ From: Johnson, Richard (NY Int) [mailto:[EMAIL PROTECTED] Sent: Monday, September 19, 2005 11:35 AM To: 'J.R. Bullington' Subject: RE: Startup Error Here are the contents of the file 050919 09:33:52 mysqld started 050919 9:33:52 [ERROR] bdb: /usr/local/mysql/data: Permission denied 050919 9:33:52 [ERROR] bdb: /usr/local/mysql/data/log.01: Permission denied 050919 9:33:52 [ERROR] bdb: PANIC: Permission denied 050919 9:33:52 [ERROR] bdb: PANIC: DB_RUNRECOVERY: Fatal error, run database recovery 050919 9:33:52 [ERROR] bdb: fatal region error detected; run recovery 050919 9:33:52 [ERROR] bdb: /usr/local/mysql/data: Permission denied InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 050919 9:33:52 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 050919 9:33:53 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait
RE: SATA vs SCSI
Brent, I'd disagree with your felling that today's disk drives are more reliable than dive years ago. I used to think of disk failures as a rare event, but now that they are producing such high capacity parts for next to nothing, I think quality has suffered. I've heard of a lot more people suffering drive failures (in PCs, laptops and servers) recently. Also, I believe that Fujitsu produced an enormous batch of disks which had a very high failure rate. Whatever, I'd say make sure you've always got hot standby disks in your raid arrays, and keep decent backups :-) Andy -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: 12 May 2005 17:47 To: Scott M. Grim Cc: mysql@lists.mysql.com Subject: Re: SATA vs SCSI I'd be curious what you tested. Did the SATA drives support tagged command queueing (TCQ)? That can make a huge difference in a multi-user environment, detrimental in a single user. How many drives were in the SATA array and how many were in the SCSI array? You could probably put 2-3x the numbers of drives in the SATA array, boosting performance, for the same price as a much smaller SCSI array. One on one I think an SATA is slower than SCSI, but bang for the buck I think goes to SATA. Here's a link to a review comparing SATA and SCSI. It shows equal setups (meaning number of drives) of SCSI and SATA have similar performance, but the SATA setup costs 40% less. Reliability is of course a major consideration, but the SATA drives of today are probably just as reliable as SCSI drives of 5 years ago. Kind of like the worst cars of today are more reliable than the best cars of 10 years ago. http://www.storagereview.com/articles/200406/20040625TCQ_1.html On May 12, 2005, at 11:42 AM, Scott M. Grim wrote: I've fairly extensively (although not necessarily scientifically) tested SATA 150 vs. SCSI U320 and find that if you're doing a lot of random reads and writes (such as with a database server), SCSI provides nearly 5x the performance as SATA so, for us, it's well worth the additional expense. It's also my experience that even the best SATA drives seem to be disposable. There's a huge difference in reliability and life expectancy between SATA and SCSI drives because they put a bit more quality into SCSI drives as they are expected to perform in an enterprise environment. With RAID arrays and hotswap bays, it's easy enough to deal with SATA's unreliability, but it's always best to not have to swap and rebuild because every failure has the potential to cause some cascade that can become devestating. - Original Message - From: Kevin Burton [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, May 11, 2005 3:29 PM Subject: SATA vs SCSI Were kicking around using SATA drives in software RAID0 config. The price diff is significant. You can also get SATA drives in 10k RPM form now., Kevin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- 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] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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: Remove 1st 3 Chars
Andrew,
I think you'll get what you want if you add order by fieldname desc on the
end of your query, but that's only because the order you have specified
happens to be in reverse ascii order.
Andy
-Original Message-
From: Andrew Braithwaite [mailto:[EMAIL PROTECTED]
Sent: 11 May 2005 12:53
To: mysql@lists.mysql.com
Subject: RE: Remove 1st 3 Chars
Hello,
Is there any way to get MySQL to return the results of this query with
the 'fieldname' in the order listed in the in() bit?
select fieldname from tablename where fieldname in
('B4079','B4076','B4069','B4041','A4710','58282','58220','56751','56728'
,'45003','09234','04200','04035','04026');
i.e. I want row 1 to have fieldname=' B4079', row 2 to have
fieldname='B4076', etc.
Or is there any other way to list the results by the order as defined in
a list specified in the query.
Please don't reply saying that I should do this in the application layer
as that isn't an option.
Thanks for any help at all on this...
Cheers,
Andrew
MySQL, Query
--
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: EXPLAIN: Select tables optimized away
Gabriel, I think it means that this count can be done from an index, so there's no need to access the actual table at all. Andy -Original Message- From: Gabriel PREDA [mailto:[EMAIL PROTECTED] Sent: 17 February 2005 11:16 To: mysql@lists.mysql.com Subject: EXPLAIN: Select tables optimized away MySQL 4.1.10 What does Select tables optimized away mean ? mysql explain SELECT COUNT(*) AS total FROM members_twining_main; | 1 | SIMPLE | NULL | NULL | NULL | NULL |NULL | NULL | NULL | Select tables optimized away | Gabriel PREDA -- 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: Undo function?
Backups are good too :-) Andy -Original Message- From: Artem Koltsov [mailto:[EMAIL PROTECTED] Sent: 20 January 2005 15:06 To: mysql@lists.mysql.com Subject: RE: Undo function? Hello, If you define table type as InnoDB, you can use transactions (see the link below). You will need set AUTOCOMMIT=0, and after you can issue COMMIT or ROLLBACK at the end of query or session to submit or cancel a transaction. I don't think you can use transactions for mysql system tables because they have to be MyISAM type, so the best solution for them will be backups. http://dev.mysql.com/doc/mysql/en/InnoDB_transaction_model.html -Original Message- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 8:17 AM To: mysql@lists.mysql.com Subject: Undo function? Hi, I get very nervous when I log onto my database via SSH and type in queries manually. Take the following query for example: Delete FROM Users WHERE User_ID = 5; If I hit return before I start typing WHERE then things would be disastorous! Is there any type of undo function with MySQL? Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. -- 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: sub query is extermely slow
I think it might also be better to remove the function date(tt.date) if possible ie change date(tt.date) = 2005-01-31 to tt.date = correct date format This will remove the function on the tt table field which I believe will force a full table scan on what is probably the largest table? Andy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 19 January 2005 15:04 To: sam wun Cc: mysql@lists.mysql.com Subject: Re: sub query is extermely slow sam wun [EMAIL PROTECTED] wrote on 01/19/2005 07:02:37 AM: Hi list, The following sql statement takes 3 mintues to complete the query. How can I improve its speed? select DISTINCT i.basename from inventory i, transaction t, customer c where i.prodcode = t.prodcode and c.custcode = t.custcode and i.basename is not NULL and i.prodname is not NULL and ((date(t.date) = 2004-01-01 and date(t.date) = 2004-01-31) and i.basename IN (select DISTINCT ii.basename from inventory ii, transaction tt, customer cc where ii.prodcode = tt.prodcode and cc.custcode = tt.custcode and ii.basename is not NULL and ii.prodname is not NULL and(date(tt.date) = 2005-01-01 and date(tt.date) = 2005-01-31))) order by i.basename Thanks Sam I would simplify it by converting everything to us explicit (not implicit) JOIN statements,skipping unnecessary type conversions, and logically merging your conditions. Here is your original query, slightly reformatted. SELECT DISTINCT i.basename FROM inventory i, transaction t, customer c WHERE i.prodcode = t.prodcode and c.custcode = t.custcode and i.basename is not NULL and i.prodname is not NULL and ( (date(t.date) = 2004-01-01 and date(t.date) = 2004-01-31 ) and i.basename IN ( select DISTINCT ii.basename from inventory ii, transaction tt, customer cc where ii.prodcode = tt.prodcode and cc.custcode = tt.custcode and ii.basename is not NULL and ii.prodname is not NULL and( date(tt.date) = 2005-01-01 and date(tt.date) = 2005-01-31) ) ) order by i.basename Here is my proposal: SELECT DISTINCT i.basename FROM inventory i INNER JOIN transaction t ON i.prodcode = t.prodcode AND t.date = '2004-01-01' AND t.date = '2004-01-31' INNER JOIN transaction tt ON i.prodcode = tt.prodcode AND tt.date = '2005-01-01' AND tt.date = '2005-01-31' INNER JOIN customer c ON c.custcode = t.custcode AND c.custcode = tt.custcode WHERE i.basename is not NULL and i.prodname is not NULL order by i.basename This should give you a list of inventory basenames for all current customers (their names are still in the customer table) that had transactions (ordered products?) during both JAN 2004 and JAN 2005. This list will show only the products that were ordered during BOTH time periods BY THE SAME CUSTOMER at least once (at least one repeat sale, year to year, in JAN). Is this what you were after or was there a different question you were trying to answer? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Retrieving partial field values
Hi, Full text search should be fine with 8 million records. These are short records too, so there shouldn't be any problem at all. Also, you can configure the minimum word length in the my.cnf file, so if you want it to find short words, you can. I've got it set to two in one of my applications. Note you have to delete the index and rebuild it if you change this value. Best regards, Andy -Original Message- From: Kentucky Families [mailto:[EMAIL PROTECTED] Sent: 06 January 2005 14:05 To: Jigal van Hemert; mysql@lists.mysql.com Subject: Re: Retrieving partial field values A full-text search won't work. Too many records -- at least 8 million. From what I've read, full-text won't pick up the individual initials or very short names, like Vu, Lee, Doe, etc. Jigal van Hemert [EMAIL PROTECTED] wrote: Example a (GivenNames): Mary Elizabeth or Marg Elizabeth Example b (GivenNames): J. W. or I. W. Example c (Surname): Stotts, Statts or Stutts I need to be able to retrieve the following based upon the examples: For Example a: Return Mary Elizabeth where GivenNames begins with M; Return Marg Elizabeth where GivenNames begins with M; Return Mary Elizabeth where GivenNames contains the whole word Mary; Return Marg Elizabeth where GivenNames contains the whole word Marg; Return Mary Elizabeth where GivenNames=Mary Elizabeth Return Marg Elizabeth where GivenNames=Marg Elizabeth I'm beginning to think that a form of FULL TEXT search is what you need; take a look at these articles: http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html http://dev.mysql.com/doc/mysql/en/Fulltext_Boolean.html and even query expansion might be usefull in your case (though it might be a bit slow): http://dev.mysql.com/doc/mysql/en/Fulltext_Query_Expansion.html Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? All your favorites on one personal page Try My Yahoo! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Restart of Mysql and tomcat error
Johanne, There are numerous questions about connection methods, pooling etc that would be better asked in the tomcat list and would require work in your web application. However, putting on my pragmatic system integrator hat, could you get round this by simply doing a request to your application using wget at the end of your MySQL backup script? Worth considering, Andy -Original Message- From: Duhaime Johanne [mailto:[EMAIL PROTECTED] Sent: 15 December 2004 19:22 To: [EMAIL PROTECTED] Subject: Restart of Mysql and tomcat error Hello Since that list is wonderfull to solve my problem, I will try again. But this might not be the best place since the problem concerns mysql access througt tomcat (jakarta-tomcat-5.0.28). The java application we have, when start after a mysql restart (night backup) , will give an error (reset of the connection) for the first person that log in. Then all subsequent logging will be fine until a mysql restart. How can I prevent that? Johanne Duhaime IRCM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Excel 2 mysql
David, In the load data infile command you can specify the delimiter character (see manual). It is a comma in CSV files, but I think it defaults to the TAB character in load data infile. You can specify the delimiter to be comma in the load command. However, I prefer to export from Excel as TAB separated, as you don't have to mess with the load data command. Also, more importantly, data fields often contain commas which screws everything up, whereas TABS are much less common in Excell data fields. Hope this helps, Andy -Original Message- From: David Ziggy Lubowa [mailto:[EMAIL PROTECTED] Sent: 08 December 2004 23:55 To: Eric Bergen Cc: [EMAIL PROTECTED] Subject: Re: Excel 2 mysql On Tuesday 07 December 2004 20:02, Eric Bergen wrote: The easiest way to turn excel into MySQL is to have excel save the file as a .csv (comma separated values) file and use the mysqlimport utility or a load data infile query to insert it into a table in cheers guys, i have managed to change my excel file into a .csv and done a LOAD DATA INFILE and dumped the data in the mysql db the only problem though that all the data is going into one field in the table yet it is comma separated with the exact columns. How can i solve this . All help is highly appreciated. -Z MySQL. -Eric On Tue, 7 Dec 2004 16:02:23 -0800, David Ziggy Lubowa [EMAIL PROTECTED] wrote: Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Queries taking 60 seconds+
John, Have you got a single multi-column index on countyid, price and old, or do you have individual indexes on each of these fields? The former would be much better. Andy -Original Message- From: John Smith [mailto:[EMAIL PROTECTED] Sent: 11 November 2004 14:15 To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: Queries taking 60 seconds+ On Thu, 2004-11-11 at 13:58, Victor Pendleton wrote: What does the explain plan look like? id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE properties ref old,price,countyid countyid 3 const 9233 Using where; Using filesort The filesort I know is a problem but I thought I read sorting it using myisamchk by the price key helps this. Also if I remove ORDER BY and the price =1 AND old = 0 the query still takes 46 seconds which is too long for a dynamic site that I am trying to build. John -- 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: Referring to columns by ordinal
The programming solution is work out the column name in your script, ie do describe tablename in your script, look for the column name marked as PRI in the key column, then insert this column name in the select statement. Andy -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: 07 October 2004 14:08 To: Paul Hanlon; [EMAIL PROTECTED] Subject: Re: Referring to columns by ordinal - Original Message - From: Paul Hanlon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 07, 2004 4:55 AM Subject: Referring to columns by ordinal Hi all, I've searched all over the place for a solution to this, and I hope you can help. I'm trying to write a very simple database handling script. The SQL statement I'm trying to do is SELECT * FROM tablename WHERE the_first_column = a_number; All my tables have the primary key in the first column, but they are called different names. What can I use to replace the_first_column. Sorry, I don't think there is any way to use ordinals in a WHERE clause. You will have to use the actual name of the first column. According to the manual, http://dev.mysql.com/doc/mysql/en/Problems_with_alias.html, you can use an alias to refer to a column in GROUP BY, ORDER BY, or HAVING but *not* in WHERE. The reason: This is because when the WHERE code is executed, the column value may not yet be determined. Now, this refers to an alias which you have defined in a SELECT clause, like 'Select count(*) as num, not an ordinal. I don't see anything that explicitly says that you can't have an ordinal in a WHERE so there is always the possibility that an ordinal is valid in a WHERE. However, I think you can disprove that possibility very quickly by trying a query like: select * from mytable where 1 = 'Jones'; This assumes that the first column of your table contains surnames. I think you'll see that this doesn't work. There's at least one very practical reason why it would be very confusing if ordinals were allowed in WHERE clauses. Suppose you had a table that contained integers in the some columns and you used an ordinal to represent the column position instead of using its name. Consider this query: select * from mytable where 2 = 7; Is this query trying to find all the rows where the value in the second column is 7 or all the rows where the value in the 7th column is 2? The only way to prevent a misinterpretation here is to insist that the integer to the left of the equal sign is always a column ordinal and the integer to the right of the equal sign is always a literal. The only solution I can see for your problem would be to re-create all of your tables and this time name the first column of each table something like 'key' or 'primary key'. Then, all your queries could say something like: select * from mytable where key = 7; Rhino -- 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: Working with 160M entries table
Ricardo, The best performance solution is to create another column to contain (time_utc-1004127737) div 86400 Update the table to set this value correctly for every row, then calculate the value for this column every time you insert more data. Create an index on prefix and the new column and this should become quick, especially if you tune sort buffers and cache sizes. This solution is obviously at the expense of more storage space, but it will speed things up. Andy -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: 23 September 2004 13:48 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Working with 160M entries table I don't think indexes have anything to do with it, unless you have an index on the prefix field, in which case it might use it for the grouping. But regardless, you are selecting all 160M records (no index used) AND doing 160M calculations (no index used) and then grouping 160M records (maybe an index used). That's a lot of stuff going on for which you should have lot of memory, fast I/O and the proper my.cnf settings. For instance, if you sort buffer setting in the config file is set pretty high, this may take quite a while. On Sep 23, 2004, at 3:35 AM, Ricardo Oliveira wrote: Hi, I'm doing a query on the following table: +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | time_utc | int(11) | | PRI | 0 | | | prefix | varchar(18) | | PRI | | | +--+-+--+-+-+---+ The query is: - select prefix, ((time_utc-1004127737) div 86400),count(*)from t129_250_0_11 group by prefix,((time_utc-1004127737) div 86400); - I'm doing it on a dual processor 2Mhz Athlon 64-bit machine. The only problem is that the table has about 160,000,000 (160M) entries and i'm afraid that disk i/o looking for indexes is taking most of time. The query has been running for almost 10 hours now. Do you have any sugestions that might speed up the query? Do you think removing the indexes will speed up things? Thanks in advance for your help! --Ricardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- 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 can I avoid warm-up time?
Tamar, The only way to fill the caches up is to execute queries. If there is a delay between your server coming up and the application being used, try executing the queries that your application will use from a start-up script (you'll need to work these out carefully. This way the caches will be full of the correct data when the application makes it's first queries. If the application starts straight away at boot time, you're stuck. As for, the system gong slow after being inactive, this sounds like your system could be swapping. This could be caused by your caches taking up all or most of the available memory, leaving none for the operating system and other things running. When your application goes idle, the operating system will copy its cache memory into swap space on the disk, and read its own and other applications memory back from the swap space into memory. The solutions to this are 1) Add more memory 2) Reduce the size of your caches so there is enough memory left for the rest of the system. Of course, the initial delay could be related to swapping too. Andy -Original Message- From: Tamar Rosen [mailto:[EMAIL PROTECTED] Sent: 10 September 2004 12:56 To: [EMAIL PROTECTED] Subject: How can I avoid warm-up time? Hi, We are now in the last phases of testing a new system on Mysql. We are using all MyISAM tables, on Linux. We found that after the mysql server is restarted, our application is very slow for a couple of minutes. We understand that this is because the caches have to fill up first - the mysql key cache (we made it big enough to hold all the indexes) and the OS cache. My question: is there any way we can preload the caches so that we don't experience this warm-up time? Also, we found that if the server is not active for some time, say overnight, then again we experience the warm-up time, even though the mysql server was not restarted. This leads to totally unpredictable performance. At very low loads, the times are the worse!!! If anyone had a similar experience and/or possible suggestions on how to solve this problem, it will be greatly appreciated. Thanks, Tamar Rosen www.gurunet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problems counting the number of returned rows
Arthur, Is it faster if you do: select SQL_CALC_FOUND_ROWS category use index(category) from books where category=1 limit 0,10 ie change * to category (which can be read from the index)? Andy -Original Message- From: Arthur Radulescu [mailto:[EMAIL PROTECTED] Sent: 07 September 2004 14:23 To: [EMAIL PROTECTED] Subject: problems counting the number of returned rows Hello! I am having a problem retrieving the number of records matching a certain condition from the database. I have a large table of about 3 millions records A simple query like the one below returns me the results select * use index(category) from books where category=1 limit 0,10 This query takes about 0.01 seconds since I have an index on the category column When I try to retrieve the number of rows matching this condition I am using one of the following 2 queries 1. select SQL_CALC_FOUND_ROWS * use index(category) from books where category=1 limit 0,10 and then I retrieve the needed result using FOUND_ROWS()... This query where I make use of SQL_CALC_FOUND_ROWS takes about 15 seconds 2. select count(*) use index(category) from books where category=1 which returns me the needed result... This query takes about 3 seconds using explain on both queries I notice that the first query is not using anymore the index and I cannot figure out exactly why... However the main problem is that each query is way to slowly and I cannot figure out any other better method to retrieve this result... I am missing anything here? Is there any other better method to return the number of results with a certain condition for a large database? Any help would be really appreciated Regards, Arthur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problems counting the number of returned rows
Arthur, What about select count(category) use index(category) from books where category=1 (don't think this will be faster, but try it) Then you could try to run OPTIMIZE TABLE books (read the manual first if it's a live system) Andy -Original Message- From: Arthur Radulescu [mailto:[EMAIL PROTECTED] Sent: 07 September 2004 15:03 To: Andy Eastham; Mysql List Subject: Re: problems counting the number of returned rows Thanks for the tip! It is much faster now... But it still takes about 3 seconds which makes about the same thing like using count() so this still does not solves the problem Regards, Arthur Arthur, Is it faster if you do: select SQL_CALC_FOUND_ROWS category use index(category) from books where category=1 limit 0,10 ie change * to category (which can be read from the index)? Andy -Original Message- From: Arthur Radulescu [mailto:[EMAIL PROTECTED] Sent: 07 September 2004 14:23 To: [EMAIL PROTECTED] Subject: problems counting the number of returned rows Hello! I am having a problem retrieving the number of records matching a certain condition from the database. I have a large table of about 3 millions records A simple query like the one below returns me the results select * use index(category) from books where category=1 limit 0,10 This query takes about 0.01 seconds since I have an index on the category column When I try to retrieve the number of rows matching this condition I am using one of the following 2 queries 1. select SQL_CALC_FOUND_ROWS * use index(category) from books where category=1 limit 0,10 and then I retrieve the needed result using FOUND_ROWS()... This query where I make use of SQL_CALC_FOUND_ROWS takes about 15 seconds 2. select count(*) use index(category) from books where category=1 which returns me the needed result... This query takes about 3 seconds using explain on both queries I notice that the first query is not using anymore the index and I cannot figure out exactly why... However the main problem is that each query is way to slowly and I cannot figure out any other better method to retrieve this result... I am missing anything here? Is there any other better method to return the number of results with a certain condition for a large database? Any help would be really appreciated Regards, Arthur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT SELECT
Shaun, You need two columns for the insert, but you're only selecting one. Try this: INSERT INTO Letter_Templates (Work_Type_ID, Project_ID) SELECT Work_Types.Work_Type_ID, Work_Types.Project_ID FROM Work_Types WHERE Work_Types.Project_ID = 'x'; Andy -Original Message- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: 07 July 2004 13:37 To: [EMAIL PROTECTED] Subject: INSERT SELECT Hi, Using the following query I am attampting to insert the Work_Type_IDs from Work_Types WHERE Project_ID = 'x'. However I also wan to insert the Project_ID into the table, how would this syntax work - apperently the column count is incorrect... INSERT INTO Letter_Templates (Work_Type_ID, Project_ID) SELECT Work_Types.Work_Type_ID FROM Work_Types WHERE Work_Types.Project_ID = 'x'; Thanks for your help _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- 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: RE - Order By Problem
Look at using the Reverse() function, then take the substring up to the first space, then reverse the result. Andy -Original Message- From: Paul McNeil [mailto:[EMAIL PROTECTED] Sent: 08 June 2004 14:04 To: [EMAIL PROTECTED] Subject: RE - Order By Problem I have never done anything like this but after looking at the spec's I have a possible direction for you In String functions there is LOCATE(substr,str,pos) The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str. I think that if you create a function that uses this to strip the string to the left of the last found space and that returns the string to the right you could call this in your query and use it in the order by statement. -- 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: RE - Order By Problem
Andy, Just: select substring_index(surname,' ',-1) as r from advisers order by r; works. Andy -Original Message- From: andy thomas [mailto:[EMAIL PROTECTED] Sent: 08 June 2004 15:57 To: Andy Eastham Cc: Mysql List Subject: RE: RE - Order By Problem On Tue, 8 Jun 2004, Andy Eastham wrote: Look at using the Reverse() function, then take the substring up to the first space, then reverse the result. Well, 'select substring_index(surname,' ',-1) from advisers' does the trick as far as extracting the wanted parts of surnames at the end of the surname filed but I'm not sure how to use this as an argument to ORDER BY? Shouldn't something like: select substring_index(surname,' ',-1) as r from advisers, select * from advisers order by r work? Thanks for your help, Andy -Original Message- From: Paul McNeil [mailto:[EMAIL PROTECTED] Sent: 08 June 2004 14:04 To: [EMAIL PROTECTED] Subject: RE - Order By Problem I have never done anything like this but after looking at the spec's I have a possible direction for you In String functions there is LOCATE(substr,str,pos) The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str. I think that if you create a function that uses this to strip the string to the left of the last found space and that returns the string to the right you could call this in your query and use it in the order by statement. -- 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] -- 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: BETWEEN
Max, You can measure the elapsed time by writing a linux shell script to do the inserts, then use the linux time command to run it. However, the user and system times displayed will not include the amount of cpu time used by the db server. Do it a few times and vary the number of inserts to build an accurate picture. Alternatively, you may be able to do this easier in version 4.1, where you can use %f in time_format to get milliseconds, so hopefully now() retrieves milliseconds too(?): select time_format(now(), '%H:%i:%s.%f'); to get timestamps to the nearest millisecond. Of course, getting the timestamp takes a finite amount of time, which you may want to measure. Andy -Original Message- From: Boyd E. Hemphill [mailto:[EMAIL PROTECTED] Sent: 20 April 2004 14:29 To: 'Max Michaels'; 'mysql' Subject: RE: BETWEEN Max: Thanks for the tip. Unfortunately I am not using a FreeBSD environment. My options are to either run a WinXP client remotely or to run something Linux based in a terminal emulator (Putty). Any suggestions would be appreciated. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Max Michaels [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 7:07 AM To: 'Boyd E. Hemphill'; 'mysql' Subject: RE: BETWEEN Hello: I am trying to measure the difference between a single insert statement of 10,000 rows and 10,000 insert statements. It is easy for me to see the single statement takes about 2 seconds. However I can come up with no good way to get the total time for individual statements. Can anyone provide a suggestion? Thanks in advance. Try super-smack. It works great for this type of testing. http://jeremy.zawodny.com/mysql/super-smack/ Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- 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] -- 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 do I determine the row number or key when table has no key fields
Ross, You'll need to do an order by on both columns (so you'll need to index both columns in a compound index), then use the LIMIT keyword which is designed for exactly this job. Alternatively, unload the data using mysqldump, then edit the table definition to have an autoincrement column, then reload the data. Find more information in the manual at http://www.mysql.com/doc/en/index.html Andy -Original Message- From: Ross Honniball [mailto:[EMAIL PROTECTED] Sent: 02 April 2004 13:48 To: [EMAIL PROTECTED] Subject: How do I determine the row number or key when table has no key fields eg. say a table is created using: create table fred (f1 char(10), f2 int) Then it has neither keys nor an AUTO_INCREMENT field. Let's say 1000,000 records are then inserted into table fred. I then say 'select * from fred' and loop through results writing to a web page. I stop writing to the web page after say 20 records. The user hits 'next page'. I want to say 'select * from fred where ?field? ?value? Where ?field? and ?value? are what I want to know. Surely there is some kind of 'record number' or something available in mySQL for me to : 1. Retrieve and save 2. Query against I'm new to this mailing list. Apologies if I am asking this question of an inappropriate email address. Regards ... Ross . Ross Honniball JCU Bookshop Cairns Supervisor . James Cook Uni, McGreggor Rd, Smithfield, Qld. 4878, Australia . Ph:07.4042.1157 Fx:07.4042.1158 Em:[EMAIL PROTECTED] . There are no problems. Only solutions. -- 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 do I determine the row number or key when table has no keyfields
The LIMIT clause can be used to constrain the number of rows returned by the
SELECT statement. LIMIT takes one or two numeric arguments, which must be
integer constants. With two arguments, the first argument specifies the
offset of the first row to return, and the second specifies the maximum
number of rows to return. The offset of the initial row is 0 (not 1):
mysql SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15
-Original Message-
From: Andy Ford [mailto:[EMAIL PROTECTED]
Sent: 01 April 2004 05:53
To: [EMAIL PROTECTED]
Subject: RE: How do I determine the row number or key when table has no
keyfields
I thought LIMIT limited you to N number of CONCURRENT record. ie. limit
10 or limit 20
I believe Ross would like to select select 1000 records and then do a
sub select of records 1-20 and then 21-40 on this record set
Or am I barking up the wrong tree?
Andy
On Fri, 2004-04-02 at 14:05, Andy Eastham wrote:
Ross,
You'll need to do an order by on both columns (so you'll need to index
both
columns in a compound index), then use the LIMIT keyword which is designed
for exactly this job.
Alternatively, unload the data using mysqldump, then edit the table
definition to have an autoincrement column, then reload the data.
Find more information in the manual at
http://www.mysql.com/doc/en/index.html
Andy
-Original Message-
From: Ross Honniball [mailto:[EMAIL PROTECTED]
Sent: 02 April 2004 13:48
To: [EMAIL PROTECTED]
Subject: How do I determine the row number or key when table has no key
fields
eg. say a table is created using:
create table fred (f1 char(10), f2 int)
Then it has neither keys nor an AUTO_INCREMENT field.
Let's say 1000,000 records are then inserted into table fred.
I then say 'select * from fred' and loop through results writing to a web
page.
I stop writing to the web page after say 20 records.
The user hits 'next page'.
I want to say 'select * from fred where ?field? ?value?
Where ?field? and ?value? are what I want to know.
Surely there is some kind of 'record number' or something available in
mySQL for me to :
1. Retrieve and save
2. Query against
I'm new to this mailing list. Apologies if I am asking this question of an
inappropriate email address.
Regards ... Ross
. Ross Honniball JCU Bookshop Cairns Supervisor
. James Cook Uni, McGreggor Rd, Smithfield, Qld. 4878, Australia
. Ph:07.4042.1157 Fx:07.4042.1158 Em:[EMAIL PROTECTED]
. There are no problems. Only solutions.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
perl -e 'print qq^;@) [###]^^qq^z\.MY{eLQ9^'
in:control developer, Telindus, RG27 9HY
DDI: +44 1256 709211, GSM: +44 7810 636652
--
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: Searching for matching zipcode in a list of (ranges of) zipcodes
Tom, Change your database so that you have an engineer table and an engineer_zipcodes table. Each engineer can have multiple entries in the engineer_zipcodes table. Engineer Engineer_id integer auto_increment primary key Name Address Etc Create index engineer1 on engineer(engineer_id) Engineer_zipcodes Engineer_id Min_zipcode Max_zipcode Create index zipcodes1 on engineer_zipcodes(engineer_id, min_zipcode, max_zipcode) To handle ranges of zipcodes, have two columns min_zipcode and max_zipcode for every entry. For single zip codes both columns are identical, for ranges they are different Then to locate an engineer: Select * from engineer e, engineer_zipcodes z where e.engineer_id = z.engineer_id and min_zipcode = zzz and max_zipcode = zzz zzz is the zipcode you're searching for. Hope this helps, Andy -Original Message- From: Tom Hesp [mailto:[EMAIL PROTECTED] Sent: 14 January 2004 12:56 To: [EMAIL PROTECTED] Subject: Searching for matching zipcode in a list of (ranges of) zipcodes Hi all, I am looking for a simple solution to find a zipcode in lists of zipcodes. I have a table with customer data including the customer's zipcode and a table containing information about service engineers. The service engineers can define a list of zipcode (ranges) of areas they want to (or are able to) service. An example of such a list is: 3528,3529,3612-3621,3828. This list is stored in one field in the service engineer table. What I would like to do is by using the customer's zipcode select all engineers that have that zipcode in their list. E.g. 3529 3615 would match while 3530 would not in the above example. I can of course code this in perl, for example, but that would mean that for every search I need to do I would have to retrieve the entire service engineer table and go through it to find a match. I was hoping MySQL would have function to this. I searched the documentation but have not been able to find any. Thanks for your time. Kind regards, Tom Hesp -- 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: MySQL as document storage?
Steve, I know you've been pointed at an interesting resource, and others are saying they've done this successfully, but I think you should get some balance. I've worked in document management for years, and I think it's a very bad idea. Have you ever had to restore a system from loads of incremental backups? I'd recommend you look at storing the files on the files system in a proper storage array that can be expanded when needed. Backing up and restoring files is much easier in this scenario, as when someone deletes 1 document by mistake or a file gets corrupted, you only have to restore 1 file. How do you do this in your database system? The storage array will give much more flexible backup options. It's also much easier to use a full text indexer such as Lucene on the raw files. In the database, you store all of the document metadata and a unique id. On the file system, name the files as the same as the id. That way, it's easy to get between the file system and the database record, or the full text index and the database record. I have to say that reading the PHP article made me feel very uneasy - it all seemed fundamentally flawed to me. However, good luck if you decide to go that way. I'd be interested to hear if you genuinely feel it has been a success, especially after you've recovered from your first major data loss :-) (This is not meant to be sarcastic) Best regards, Andy -Original Message- From: Steve Folly [mailto:[EMAIL PROTECTED] Sent: 07 January 2004 20:56 To: MySQL MySQL Subject: MySQL as document storage? Hi, (disclaimer - this thread could easily go off topic; I'm interested only in the MySQL aspects of what follows...) At work we are currently investigating ways of filing all our electronic documents. There is commercial software that will do this I know, but I was wondering whether MySQL would be suitable for this type of thing. The 'documents' could be literally any binary file. My idea would be to create a table with a blob column for the document itself, and document title, reference number, keywords, other meta-data. And a web-based front-end to search and serve documents. Although the documents could be any file, the majority would be textual documents (Word documents, PDF, etc). How would one go about indexing such data, since full text searches operate on textual columns? How to cope with columns exceeding the max packet length? Why is there a max_packet_length setting; surely this is low-level stuff that shouldn't affect query and result sizes? Is storing the actual documents in the database such a good idea anyway? Perhaps store the file in a file system somwhere and just store the filename? If anyone has experience in doing (or been dissuaded from doing) this kind of application your thoughts and comments would be appreciated. (If only to tell me don't be so stupid, it'll never work :) Thanks. -- Regards, Steve. -- 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: Security Question
Thomas, It would be more secure if you has the DB on another server that was locked down and only allowed access to the web server on the MySql port, (plus probably ssh access for admin). If you're going to the expense of audits, this must be fairly important, so the cost of the other server would not be too significant? Best regards, Andy -Original Message- From: Curley, Thomas [mailto:[EMAIL PROTECTED] Sent: 26 November 2003 13:22 To: [EMAIL PROTECTED] Subject: RE: Security Question Importance: High thanks for reply - the requirement comes from a security audit - so try to think in terms of a hacker Obviously and (I had assumed) 1.- the files would have tight unix security file permissions applied 2.- indeed the key would be stored on an internal tightly managed box (or device) Another Assumption -- Encrypting / decrypting all data on the fly would be too expensive and grind the app to a halt So the question again :- Any ideas on how to avoid having data files stored with absolutely no protection against copying If there is no solution to this then MySql should not be used on internet accessible boxes for dynamic web sites Thomas -Original Message- From: Fagyal, Csongor [mailto:[EMAIL PROTECTED] Sent: 26 November 2003 12:51 To: Curley, Thomas Cc: [EMAIL PROTECTED] Subject: Re: Security Question Thomas, I am trying to find a solution to the following security issue with MySql DB on linux - Someone copies the DB files to another box, starts a mysql instance, loads the DB and presto - views the 'private' data !!! Well, someone should not have access rights to the DB files on the first hand. Ideally I would like to know if there is any option in MySql to store the DB files in a secure format and one that needs a key or similiar to open the DB If someone was able to access your DB files, he would probably also be able to access that key (that you must store _somewhere_), wouldn't he? - Csongor ** *** This email and any attachments are confidential and intended for the sole use of the intended recipient(s).If you receive this email in error please notify [EMAIL PROTECTED] and delete it from your system. Any unauthorized dissemination, retransmission, or copying of this email and any attachments is prohibited. Euroconex does not accept any responsibility for any breach of confidence, which may arise from the use of email. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the Company. This message has been scanned for known computer viruses. ** *** -- 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: Index before or after inserts?
Mirza, Definitely, index after insert. Andy -Original Message- From: Mirza [mailto:[EMAIL PROTECTED] Sent: 27 November 2003 15:33 To: [EMAIL PROTECTED] Subject: Index before or after inserts? I need to insert hundreds of milions of records to a table and make several indicies on it. Now, is it faster to make tables with indicies and then fill tables or fill tables first, then make indicies? Any experiancies? regards, mirza -- 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: DB design question
Julian, Your design is sound in my opinion. An area you probably need to consider is when you need to search across a day boundary. You will need to make the application aware that it needs to search across a day boundary, so that it searches two tables with a union where necessary. It will also need to know what the oldest table is, so that it doesn't try to do a union with a table that doesn't exist. Alternatively, you could always search across three tables - so that you always union with the one before and one after the required time window. Of course, you again need to check that you're not searching the earliest or latest available table, and if so, modify the union so that you don't try to search a non-existent table. Hope this helps, Andy -Original Message- From: Julian Zottl [mailto:[EMAIL PROTECTED] Sent: 21 November 2003 12:03 To: [EMAIL PROTECTED] Subject: DB design question Hello all, I am designing a database right now that will have between 300-400k inserts per day. I need to keep this information for approximately 3 months and will probably do 5-10 reads on the data set per day. I've been storing it in one table up to now (only col.), but the searches are becoming more and more of a problem. I'd like to break it up so that I have one table for every day, and then I'll just delete the trailing days when I create a new day. So I would have 90 tables of roughly 350k records instead of a single table with 6+ million records. What do you al think of this design? I'm making an assumption that it will make my searches a lot faster for a single day (I doubt I would ever need to search on more than one day). Thanks! Julian Zottl Unix Systems Administrator NASA HQ - 202-358-1682 -- 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: SQL query question
Pael, Try this: SELECT firmal.beskrivelse as Businessline, lokasjon.navn as Location, count(person.[uniqueid]) FROM firmal INNER JOIN ( person INNER JOIN lokasjon ON person.lokid = lokasjon.lokid) ON firmal.firmalid = person.firmalid GROUP BY firmal.beskrivelse, lokasjon.navn Replace [uniqueid] with the primary key of the person table. Andy -Original Message- From: Paal Eriksen [mailto:[EMAIL PROTECTED] Sent: 11 November 2003 12:11 To: [EMAIL PROTECTED] Subject: SQL query question Hi, i have the following query: SELECT person.name as Name, firmal.beskrivelse as Businessline, lokasjon.navn as Location FROM firmal INNER JOIN ( person INNER JOIN lokasjon ON person.lokid = lokasjon.lokid) ON firmal.firmalid = person.firmalid which will give me a list of Name, Businessline, Location. What i'm trying to do is to get a list which is grouped on Businessline and Location. Then i want to list a count of name at each location and businessline. How can i achieve this, if it's possible? So it should be like this: Businessline, Location, Sum people A AA 10 A AB 30 B AA 5 B AB 27 B AC 90 Paal Ny versjon av Yahoo! Messenger Nye ikoner og bakgrunner, webkamera med superkvalitet og dobbelt så morsom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need ur help ..........
Renuka, Put the file into a location such as /usr/local Gunzip it: gunzip mysqlgui-linux-semi-static-1.7.5.tar.gz Unpack it: tar -xvf mysqlgui-linux-semi-static-1.7.5.tar create a symbolic link from mysql to mysqlgui-linux-semi-static-1.7.5 under /usr/local/ ln -s /usr/local/mysqlgui-linux-semi-static-1.7.5 /usr/local/mysql read the installation instructions in the INSTALL-BINARY file in /usr/local/mysql Hope this helps, Andy -Original Message- From: Renuka Prasad [mailto:[EMAIL PROTECTED] Sent: 07 November 2003 08:00 To: [EMAIL PROTECTED] Subject: Need ur help .. Hi, I downloaded Linux semi static binary of mySQL,mysqlgui-linux-semi-static-1.7.5.tar.gz. Could you please help me,how to install in my system.My OS is Redhat Linux9.0. Warm regards, Renuka Prasad.N -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL query question
Chris,
You're almost there!
select * from temp where col2 like concat('%',col1, '%');
Andy
-Original Message-
From: Chris A. Mattingly [mailto:[EMAIL PROTECTED]
Sent: 07 November 2003 17:01
To: [EMAIL PROTECTED]
Subject: MySQL query question
I've searched around on the lists archives and even did some
googling, but I'm
having trouble finding the answer to this question.
Given that I have a table with 2 columns (say col1, col2) I want
to be able to
search for the value of col1 in the value of col2. Let's say that in one
instance col1 = foo and col2 = foobar, I want to know, but if col1
= temp and col2 = foobar, I do not want anything returned.
A query something like: SELECT col2 FROM table WHERE col2 LIKE '%col1%';
Any help would be greatly appreciated.
-Chris
--
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: Faster Query Method?
Scott, First, you don't mention indexes, - generally you need to make sure you've got the right indexes. Each table should have an index that contains every field in the where clause. Second, searching for %x% is always slow as this search can't use indexes (search for x% can though). As it looks like you may be searching like %foo% on text fields. If this is the case, you should consider using full text search on these as it can use indexes and could therefore be quicker. Hope this helps, Andy -Original Message- From: Scott Turnbull [mailto:[EMAIL PROTECTED] Sent: 22 October 2003 15:02 To: [EMAIL PROTECTED] Subject: Faster Query Method? I'm having tremendous problems with a query and I was hoping someone could point out where (if) I'm going wrong. Using MySQL 4.0.15 on a LINUX box. Basically I have a database that lists a large number of web links cataloged by subject. The first table (records) contains all the link information (link id, prelink text, link text, postlink test, and url) The second table (record_cats) contains the subjects for each link (link id, mwsubject). Browsing by subject seems to work ok (it's a little slow sometimes) with the following select query SELECT DISTINCT r.prelink, r.link, r.postlink, r.url FROM records AS r JOIN record_cats AS rc ON r.lid = rc.lid WHERE rc.mwsubject LIKE 'foo' ORDER BY link LIMIT 0,30; My real problem is when I try to build a search function with user input. In a search like this I need to have the text entered searched for across all the relavant rows that contain text, including the subject. My problem is this query runs well over 10 min. I have about 16 thousand records in the records table and about 93 thousand in the record_cats table and I'm using queries like: SELECT DISTINCT r.prelink, r.link, r.postlink, r.url FROM records AS r LEFT JOIN record_cats AS rc ON r.lid = rc.lid WHERE (r.prelink LIKE '%foo%' OR r.link LIKE '%foo%' OR r.postlink LIKE '%foo%' OR r.url LIKE '%foo%' OR rc.mwsubject LIKE '%foo%') ORDER BY link LIMIT 0,30 My question is, am I out of my gord with the search above? Is there a faster and better way to do this in MySQL? Thanks in advance for any insight. Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: SELECT 9 BETWEEN 1 AND 0
This went direct and not to the list. Andy -Original Message- From: Andy Eastham [mailto:[EMAIL PROTECTED] Sent: 21 October 2003 08:19 To: Steven Ducat Subject: RE: SELECT 9 BETWEEN 1 AND 0 Steve, I'd add an extra column with modified code in it, where I subtracted 1 from the number bit in the second field, So 1 would become 0, and 0 would become 9. I'd search against that field ( modifying my search terms accordingly), and retrieve the proper value from the original column. Being from the UK and knowing how these codes work, I think I would also break the code up into four columns (eg from RH6 9XJ, ie 1 - first two letters of first field eg RH, 2 - one or two numbers of first field eg 6, 3 - one number of second field (this is - the one I'd modify as above) eg 9 4 - two letters from second field eg XZ Then add the extra modified field 3. I'd index all of these fields. Because you wouldn't have to perform substring searches on them, this should be a lot faster. Hope this helps, Andy -Original Message- From: Steven Ducat [mailto:[EMAIL PROTECTED] Sent: 21 October 2003 07:13 To: [EMAIL PROTECTED] Subject: SELECT 9 BETWEEN 1 AND 0 I am trying to create a select query to find the post town of a users post code. I am using Royal Mails (UK) post town gazetteer. UK Postcode (eg. RH6 9XJ). The first column contains the first half of the post code (eg. RH6) and the next column holds the range of the second half (eg. 2AA-6PP). I have some code as follows: ?php $pc = explode( ,RH6 9XJ); SELECT postTown, postCode, SUBSTRING(postSector,1,3) as a, SUBSTRING(postSector,5,3) as b, postCounty FROM postCode WHERE postCode = \.$pc[0].\ AND \.$pc[1].\ BETWEEN SUBSTRING(postSector,1,3) AND SUBSTRING(postSector,5,3); ? This will return 1 row for example if the first column was RH6 and the second column was 8ZZ-9ZZ. But in the post town list the range goes from 1 - 0 (eg. 1AA-0ZZ) so what I find is if I use the postcode RH6 9XJ and a first column of RH6 and a second column of 7AA-0BW it will not return the row as it does not count from 1 - 0. The question is how can I use some sort of statement using mysql and php to select all rows between 1 and 0 NOT 0 and 9 like the above statement performs. What I really need is some sort of function where I can set the range that the between option sorts from. I have been trying to solve this for some weeks now. Thank You. Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [mysql] MYSQL
Here are some hard examples to help: It is certainly usable on an old PC eg 200MHz Pentium 2 with linux and 32Mb RAM. Obviously the performance will be proportional to the hardware, but MySql is designed to be able to run on low level hardware. If you just want to play around with the database, or run a moderate database with only a few tens of thousands of rows in a few tables, this would be fine. You can get excellent good performance from a 500MHz Pentium 3, if you're prepared to spend a small amount of money on extra memory. I've got a database with 55 million rows in one table and it runs fine on my P3 833MHz laptop with 512Mb RAM when I'm doing development. If you were buying new hardware for a commercial deployment, I'd recommend linux on a server style machine with more than one disk, 3GHz Pentium 4/Xeon and 1Gb RAM. I've just bought such a machine for 1200 UKP from Dell, which is a serious machine for the money, such that it's hardly worth buying anything less for most commercial deployments. Move down the list according to your budget! Hope this helps, Andy -Original Message- From: Bill Kendrick [mailto:[EMAIL PROTECTED] Sent: 10 October 2003 00:25 To: [EMAIL PROTECTED] Subject: Re: [mysql] MYSQL On Thu, Oct 09, 2003 at 12:00:51PM -0300, [EMAIL PROTECTED] wrote: PLEASE, I'M A STUDENT AND I?D LIKE TO KNOW WHAT IS THE RECOMMENDED HARDWARE CONFIGURATION TO RUN MYSQL SERVER. THANKS. I think this depends on how big the database will be, how much it will be hit, etc!!! I've heard of MySQL being run on the 200MHz StrongARM based Sharp Zaurus with only 32MB of RAM and 32MB of 'disk' space. :^) I doubt that could handle a popular e-commerce site, though. Let us know what you think the strain might be on the database itself, and folks here will no doubt have some suggestions of the kind of iron that can handle it. Good luck! -bill! -- [EMAIL PROTECTED] Got kids? Get Tux Paint! http://newbreedsoftware.com/bill/ http://newbreedsoftware.com/tuxpaint/ -- 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: printing reports
Please take this flame war off list. -Original Message- From: Wang Feng [mailto:[EMAIL PROTECTED] Sent: 08 October 2003 11:31 To: Michael Haunzwickl; 'Director General: NEFACOMP'; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: printing reports Importance: Low So listen guy, this is a big miss understanding: NO 1.) If you want to have an easy way tool for creating your reports - use MS Access Hey, man, inserting a record is also ealier in Access. = You suggest me to do everything in Access. You'r bad bad man!!! -- Tools are for dummy users, Clever users create tools. -- 2.) All this things do not have anything to do with the base of your datas (either you use Oracle, MySQL, Access or whatever) - so that means: if you are using oracle, you can still use ACCESS to create some reports. Unbelievable, haven't seen any smart guy doing so. 3.) a dummy users is not a fool user but is a user how is like a user is ... normal, not trained, unexperienced user ... That doesnt mean that he has just shit in his head! yeah? a 'dummy user' is 'normal' = not dummy users on this list are NOT normal? See, you're laughing at me, I can hear that. 4.) I didnt talk of you as a dummy user, because as I understand your email, you were asking for tool to create reports easily and fast. If you understand my email, then Access is the tool you suggest? liar. 5.) I think the only guy in this list, who falls into this missunderstanding is you, because nobody contacted me to tell me, not to use words like dummy user on the list. Maybe thats because they did understand what was mentioned about it. Of course they do understand the word since you're telling them that. Actually, you can use the 'dummy user' word, but you should send the mail to me secretly :-) , now people know who I am are laughing at me. 6.) This shouldnt start a discussion of what things are good or bad in IT science. So if you like Access, choose access, if you like MySQL choose that one, and if you have a lot of money, than choose oracle, which is definitly (sorry list) the best and completest database product on the market. See, now people know that you prefer Oracle to MySQL. Because you're poor and that's why you HAVE TO pick MySQL. I'm different, I like MySQL. :-) Although I have $ to spend on Oracle, I still insist on MySQL. :-) So guy, before I come up and start giving you words, which i maybe see later as a little bit to unfriendly ... I will stop this email now. I was just joking, no worries. :-) My tip for you: TAKE MS ACCESS TO CREATE YOUR REPORTS ... Or ... IF YOU WANT TO HAVE A REAL GOOD REPORTING TOOL ... TAKE CRYSTAL REPORT. Now seriously, thanks for the advice, but I won't adopt this since I really want to use something completely free. Access is good for printing, but it costs $. Michael i'll have to go to the gym. email you later. BTW, I don't mind people call me dummy user Hope you didn't read my words seriously, they're just jokes. :-) see ya. cheers, feng -- 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 export data from multiple tables
Nitin, Create a temporary table, then select each table into it in turn, then output that to the file? Andy -Original Message- From: Nitin [mailto:[EMAIL PROTECTED] Sent: 07 October 2003 11:40 To: [EMAIL PROTECTED] Subject: how to export data from multiple tables Hi all, I've got a small problem (hope it's very small). Could any of you suggest me how to select my data into a simple text file from multiple tables (selected with union)! I've tried: select field-list into outfile filename from table1 union select field-list into outfile filename from table2 union select field-list into outfile filename from table3 It says check your version manualblah blah Any idea, how to do it? Thanx in advance Nitin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbye speed question - which setup to use for indexing
Peer, How big are the table and index files? Can your OS handle files bigger than 2/4Gb? I've got a table with 55 million rows with just 3 columns all floats. I've got three indexes with all the fields in various orders. My data file is 700Mb but my index file is over 4Gb, so yours could easily be so (as could your data file). Indexing my db takes under 2 hours on a sloow 400MHz Sun E250. I don't think the index process will use two processors, but I don't think your problem is related to processor speed. Andy -Original Message- From: Peer Reiser [mailto:[EMAIL PROTECTED] Sent: 03 October 2003 10:28 To: [EMAIL PROTECTED] Subject: Re: Newbye speed question - which setup to use for indexing Next week I will have access to a new PomerMac G5 with Dual 2GHZ processors, and i want to do some indexing. Does anyone know if MySQL will take advantage of dual processors if the only process running is the indexing process?? Is disk I/O more important ? The bad temper of my boss seems to increase exponentially with time and he thinks that 2 weeks for importing the 27 million rows and indexing is too slow (he doesnt know anything about informatics, but as i am missing experience i cannot say if he is right or not). anyone tried indexing a large? database? thanks a lot -- 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: GRANT update query: Updating host access entry for users but retaining existing passwords
Shin, I've never tried this, so it's pure speculation, but I believe all of the grant information is contained in a regular table called user. You should be able to copy this information into a temporary table using select into, then perform regular updates to change the host information to match your new subnets. Then simply copy it back to the user table, which will add these records to the existing records, crucially with the same passwords. You might have to repeat this a few times to get all the subnets in. I'd certainly perform lots of testing away from the live system before I tried this for real. Also, hopefully someone else will comment on whether this will actually work, or whether their is a fatal flaw in the idea. Best regards, Andy -Original Message- From: Shin [mailto:[EMAIL PROTECTED] Sent: 30 September 2003 11:02 To: [EMAIL PROTECTED] Subject: GRANT update query: Updating host access entry for users but retaining existing passwords Hi, I've got a MYSQL 3.23.x setup that has approx 4000 database and 4000 user accounts. 1 database per user. I created each userid from a script of the form CREATE DATABASE mdb_userid; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX,RELOAD ON mdb_userid.* TO [EMAIL PROTECTED] IDENTIFIED BY 'passwd'; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX,RELOAD ON mdb_userid.* TO [EMAIL PROTECTED] IDENTIFIED BY 'passwd'; FLUSH PRIVILEGES; (this is part of a much longer script that I pass thru to mysql and I'm using dummy/example userid,passwd and host entries in the above). I now need to grant additional access from a number of subnets for each of the users in the database to their own database. I wanted to use a statement of the form GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER,INDEX,RELOAD ON mdb_userid.* TO userid@'192.168.10.0/255.255.255.0' IDENTIFIED BY 'passwd'; But the issue is that I do not have the plain text versions of each of the users password; as many have changed over the years. So I can't use the latter format with IDENTIFIED BY as I want to retain existing passwds. If I just do the above without having an IDENTIFIED BY entry then the users get a blank passwd which is definetly a no-no in my environment. Ideally what I'd like is a quick and simple way to grant users access from the new subnets but to retain their existing passwords - and without me having to extract all their existing encrypted passwords and then inserts them in afterwards in the appropriate tables - I prefer using GRANT. Is there anyway I can what I'm after? many thanks Shin -- 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: join not using first primay key, per explain
Jeff, Try creating a new index on Question containing just the question_key field, and try it again. Andy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 22 September 2003 13:23 To: [EMAIL PROTECTED] Subject: join not using first primay key, per explain I have four tables that i'm trying to join together most are pretty small(100-200 rows tops) and one, the Response table is 127,000 rows. The query i'm currently executing is SELECT Business_Unit.Business_Unit, Question.Text_Long, AVG(Response) from Question INNER JOIN Response on Question.Question_Key = Response.Question_Key INNER JOIN Survey_Response on Survey_Response.Survey_Key = Response.Survey_Key INNER JOIN Business_Unit on Survey_Response.BUKey = Business_Unit.BUKey WHERE Question.SurveyID = 1 Group by Business_Unit.BUKey I'll actually need to join in two more tables, but to this point MySQL contol center won't execute the query, telling me that it would have to examine to many records. The Query above returns in about 12 seconds adn i'd really like to cut that down, if possible. When looking at EXPLAIN it doesn't seem to be using the first KEY from the Question table...which i'd have to imaging is slowing it down considerably..or is it, there seems to be very little information based on the rest of the data. +++---++-++--- -++ | table | type | possible_keys | key| key_len | ref| rows | Extra | +++---++-++--- -++ | Question| ALL| PRIMARY | [NULL] | [NULL] | [NULL] | 49 | where used; Using temporary| | Response| ref| PRIMARY | PRIMARY| 4 | Question.Question_Key| 1267 || | Survey_Response| eq_ref | PRIMARY | PRIMARY| 4 | Response.Survey_Key| 1 || | Business_Unit| eq_ref | PRIMARY | PRIMARY| 4 | Survey_Response.BUKey| 1 || +++---++-++--- -++ Any Thoughts/Suggestions are apprecitated. Jeff Question -- +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | Question_Number | int(11) | | | 0 | | | Text_Long | varchar(255) | YES | | [NULL] | | | Text_Short | varchar(255) | YES | | [NULL] | | | Category_ID | int(11) | YES | | [NULL] | | | SurveyID| int(11) | | PRI | 0 | | | End_Date| datetime | YES | | [NULL] | | | Question_Key| int(11) | | PRI | 0 | | +-+--+--+-+-+---+ **This has the Primary key at the end of the table...would this matter to MySQL??** The layout of the three tables are as follows Response (127,000) +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | Question_Key | int(11)| | PRI | 0 | | | Survey_Key | int(11)| | PRI | 0 | | | Response | tinyint(4) | | MUL | 0 | | +--++--+-+-+---+ Survey_Response +---+-+--+-+-+ + | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+ + | Survey_Key| int(11) | | PRI | [NULL] | auto_increment | | Sex | varchar(5) | YES | | [NULL] | | | Age | varchar(5) | YES | | [NULL] | | | Ethnicity | varchar(5) | YES | | [NULL] | | | Title | varchar(5) | YES | | [NULL] | | | Functional_Area | varchar(5) | | | | | | Years_of_Service | varchar(5) | YES | | [NULL] | | | Employment_Source | varchar(20) | | | | | | BUKey | int(11) | YES | | [NULL] | | +---+-+--+-+-+ + Business_Unit +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | BUKey | int(11) | | PRI | 0 | | | BU_Number | int(11) | | | 0 | | | Business_Unit | varchar(55) | YES | | [NULL] | | | End_Date | datetime| YES | | [NULL] | | | RegionKey | int(11) | YES | | [NULL] |
RE: Do I use Except?
Matt, On most platforms, you would generally do a sub select of the form select playerid from players p where not exists ( select * from myplayers m where m.player_id = p.player_id ) However, as sub selects are only supported in mysql 4.1, you'll need to see section 1.7.4.1 Sub queries in the manual on how to change this into a join supported in mysql prior to 4.1 Andy -Original Message- From: Matt MacLeod [mailto:[EMAIL PROTECTED] Sent: 19 September 2003 16:38 To: [EMAIL PROTECTED] Subject: Do I use Except? Hi, I'm building an online fantasy sports game. I want to present a list of players available to purchase. HOwever I need to filter out the players the user already has. I have a table which includes all of the players' information - name, position, price, etc I have a table which includes all of my transactions - managerid, playerid, dateofpurchase, dateofsale I need to select all players in the players table except those that occur in the transactions table which match the 'managerid'. I'm stuck! Any help would be greatfully received! Matt -- 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: Platform vs. Performance
Jeremy, I don't think there's a huge difference in WinTel performance and Linux, given the same hardware. Anyway, your application is so lightweight, it's not really going matter very much. Ease of administration for you and your client will be far more important. Andy -Original Message- From: Jeremy Proffitt [mailto:[EMAIL PROTECTED] Sent: 17 September 2003 23:18 To: [EMAIL PROTECTED] Subject: Platform vs. Performance We are currently using an access database going through an odbc connection in vb.net for our application (which is still in development). The plan was to use MySQL in the end and we are needing to cross that bridge. I was wondering the Performance differences between running MySQL on a Linux box vs. a WinTel Platform. The WinTel looks inviting because we can put in an easy to administrate Windows 98 or XP Pro box in the corner of our clients office. I would rather not tackle Linux as I would need file sharing and the ability to backup and remote administration. Pros? Cons? I know the windows box would need a good reboot now and then and what is the performance hit on a WinTel 98/XP Pro platform vs Linux? Looking at a 40K record database with maybe 3 users at a time running 5-10 Large (length of the select statment is over 500 characters normally) queries at a time. Any help is greatly appriciated! Thanks! Jeremy Proffitt Computer Programmer, Pearson Appraisal Services, Inc. -- 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: Performance Problems
Matthias, Can you send us your table index definitions and the output of an EXPLAIN command on your query? ie DESCRIBE pool; SHOW INDEX FROM pool; EXPLAIN SELECT sendnr FROM pool where sendnr = 111073101180; I'm pretty sure we can improve this - I've got a table with 55 million records (though only 3 columns) and a search like the one you've got takes 0.07 seconds on a box similar to your dev box. Andy -Original Message- From: Schonder, Matthias [mailto:[EMAIL PROTECTED] Sent: 18 September 2003 10:25 To: '[EMAIL PROTECTED]' Subject: Performance Problems Hei :) I have an extreme performance problem with a MySQL-DB. The database consists of 21 tables where all except three are storing only a few records. Two have about 150.000 records but they are only used temporary. The main table is rather huge, it has 90 columns and now after three month it has 500.000 records... but in the end it has to store data of 36 month. But since the table has grown to over 350.000 records I ran into massive performance problems. Querying for one record (Example: SELECT sendnr FROM pool where sendnr = 111073101180) takes 8 seconds via command line! The table is indexed and i put the my-huge.cnf into /etc/ as my.cnf How can I get more performance out of the mysql? The server which currently only hosts this database and is running apache1.3 with php4 for providing results via intranet. The OS is FreeBSD 5.1. We are running two servers with the same enviroment One is a DualXeon 2.4ghz (with HT), 3GB RAM, 105GB HD (RAID5) the other one is a P4 with 2.4ghz, 1GB RAM and 120GB HD The Dual is the Productionserver, the P4 the developement- and test server. The querey takes that long on BOTH machines so it seems clear the DB itself is causing the performance problem. So anyone can help? This is really urgend and will save my life :) Big thanks in advance. Pacem, Matthias Schonder -- 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: Platform vs. Performance
I don't see anything in there that is relevant to the original posting. Andy -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Harald Fuchs Sent: 18 September 2003 14:04 To: [EMAIL PROTECTED] Subject: Re: Platform vs. Performance In article [EMAIL PROTECTED], Andy Eastham [EMAIL PROTECTED] writes: Jeremy, I don't think there's a huge difference in WinTel performance and Linux, given the same hardware. The following URL tells you that there's a big difference between Windoze and Linux: http://www.mysql.com/information/presentations/presentation-oscon2 000-2719/index.html -- 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: Platform vs. Performance
Alec, My point was that in a 40,000 row database, server speed is irrelevant - it's going to be sub second on anything more powerful than my mobile phone. Cost of ownership is much more important for this application, and that depends on the particular circumstances. Andy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 18 September 2003 14:53 To: [EMAIL PROTECTED] Cc: Mysql List Subject: RE: Platform vs. Performance I don't see anything in there that is relevant to the original posting. The tables headed Speed difference between different SQL servers (times in seconds) The top row of each table shows times for the same operation on Linux and Windows, showing that for both operations tested, Linux achieves 50% better throughput (takes 2/3 of the time). Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Platform vs. Performance
Harald, How often do you read 200 rows by key in a daya to day application? How often do you insert 350768 rows in an application that you're considering Win98 as the platform? I've never deployed MySql on Wintel, except for developing on my laptop. I always use Solaris or Redhat for serious deployments. However, Wintel was the best platform for the deployment we were talking about because that was where the experience lay. It doesn't need *nix - it's only replacing a tiny little access dastabase... Don't get so hung up on your platform preaching that you forget your sense of reality... Andy -Original Message- From: news [mailto:[EMAIL PROTECTED] Behalf Of Harald Fuchs Sent: 18 September 2003 17:00 To: [EMAIL PROTECTED] Subject: Re: Platform vs. Performance In article [EMAIL PROTECTED], Andy Eastham [EMAIL PROTECTED] writes: I don't see anything in there that is relevant to the original posting. Andy The following URL tells you that there's a big difference between Windoze and Linux: http://www.mysql.com/information/presentations/presentation-oscon2 000-2719/index.html Speed difference between different SQL servers (times in seconds) Reading 200 rows by key:NT Linux mysql 367 249 Inserting (350768) rows:NT Linux mysql 381 206 -- 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: Blasted #$%$^$^ host has v3.23 when i need UNION
Ryan, As you probably found out, union is only available in version 4 of mysql. As you're using PHP anyway, why don't you just break it up into 5 separate selects and combine the results in PHP? Andy -Original Message- From: Ryan A [mailto:[EMAIL PROTECTED] Sent: 15 September 2003 10:41 To: Subject: Blasted #$%$^$^ host has v3.23 when i need UNION Hi guys, First of all let me warn you, i am very much a newbie to mysql, i am pretty good with the basics like selecting,deleting,updateing etc but after that i get a dazed look in my eyes with complex sql :-D Now that you have been warned let me explain, on my local machine (win2k) i have php and mysql 4.0 installed for testing purposes, I have created a site locally and then uploaded it only to find out that because of UNION it wont run as my host is on version 3.23. I then searched google as i was pretty sure that i am not the only person who ran into this problem and found 2 places with some kind of explanation of which this seems to be the best: (the other one is the online manual) http://jinxidoru.com/tutorials/union.html but being a newbie and never having used join in my life, (at least not knowingly) this is @$#$%^$ confusing. Below is my union select statement, can somebody please show me how to convert it so it will work on 3x please? (This one is the actual php code i am using but if you dont understand it there is the normal sql below this one) $tt = SELECT COUNT(*), 'C1' FROM shared WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C2' FROM dedicated where user='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C3' FROM reseller WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C4' FROM colocated WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C5' FROM freehosting WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno; // Normal version SELECT COUNT(*), 'C1' FROM shared WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C2' FROM dedicated where user='testing' and ccno=1 UNION SELECT COUNT(*), 'C3' FROM reseller WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C4' FROM colocated WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C5' FROM freehosting WHERE user ='testing' and ccno=1; Thanks in advance and have a fantastic day, even though its monday... :-D Cheers, -Ryan -- 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: Blasted #$%$^$^ host has v3.23 when i need UNION
Ryan, If this query worked, it would return you 5 rows, one for each separate count. If you execute 5 separate counts in PHP, you'll get 5 separate values with the same numbers as above. Not radically different? Andy -Original Message- From: Ryan A [mailto:[EMAIL PROTECTED] Sent: 15 September 2003 11:25 To: [EMAIL PROTECTED] Cc: Subject: Re: Blasted #$%$^$^ host has v3.23 when i need UNION Hey, Thanks for replying. Nope, the whole reason for selecting the data is the count, i need to display to the client how many records of each category he has...if there is any other way to do that (me being a newbie to mysql) I would happy to know of it. Cheers, -Ryan Without 'count' function, you could have: SELECT user.C1, dedicated.C2, reseller.C3, colocated.C4, freehosting.C5 FROM user, dedicated, reseller, colocated , freehosting WHERE (user.user ='testing' and user.ccno=1) OR (dedicated.user ='testing' and dedicated.ccno=1) OR (reseller.user ='testing' and reseller.ccno=1) OR (colocated.user ='testing' and ccolocated.cno=1) OR (freehosting.user ='testing' and freehosting.ccno=1) OR Ryan A wrote: Hi guys, First of all let me warn you, i am very much a newbie to mysql, i am pretty good with the basics like selecting,deleting,updateing etc but after that i get a dazed look in my eyes with complex sql :-D Now that you have been warned let me explain, on my local machine (win2k) i have php and mysql 4.0 installed for testing purposes, I have created a site locally and then uploaded it only to find out that because of UNION it wont run as my host is on version 3.23. I then searched google as i was pretty sure that i am not the only person who ran into this problem and found 2 places with some kind of explanation of which this seems to be the best: (the other one is the online manual) http://jinxidoru.com/tutorials/union.html but being a newbie and never having used join in my life, (at least not knowingly) this is @$#$%^$ confusing. Below is my union select statement, can somebody please show me how to convert it so it will work on 3x please? (This one is the actual php code i am using but if you dont understand it there is the normal sql below this one) $tt = SELECT COUNT(*), 'C1' FROM shared WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C2' FROM dedicated where user='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C3' FROM reseller WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C4' FROM colocated WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno. UNION SELECT COUNT(*), 'C5' FROM freehosting WHERE user ='.$mmmy_user.' and ccno=.$mmmy_ccno; // Normal version SELECT COUNT(*), 'C1' FROM shared WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C2' FROM dedicated where user='testing' and ccno=1 UNION SELECT COUNT(*), 'C3' FROM reseller WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C4' FROM colocated WHERE user ='testing' and ccno=1 UNION SELECT COUNT(*), 'C5' FROM freehosting WHERE user ='testing' and ccno=1; Thanks in advance and have a fantastic day, even though its monday... :-D Cheers, -Ryan -- 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: Blasted #$%$^$^ host has v3.23 when i need UNION
Ryan, You might well find that the 5 separate counts are quicker than the join approach. Mysql is pretty efficient at counts on indexed columns from a single table. My instincts suggest that the four table join you are proposing could be slower than the 5 separate counts, especially if the tables have thousands of rows. As long as you use the same database connection, there's shouldn't be much extra network overhead either. I may be wrong, but I suspect you're worrying unnecessarily, unless the database server is connected to the web server via a particularly slow network. Make sure you've got the right indexes on all of the tables though (ie put an index on each table that matches the where clause against that table). All the best, Andy -Original Message- From: Ryan A [mailto:[EMAIL PROTECTED] Sent: 15 September 2003 13:31 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Blasted #$%$^$^ host has v3.23 when i need UNION Hey Andy, True, but thats using 5 selects instead of just one, and since we are expecting quite a bit of traffic to the site that can add up pretty fast, expecially since we cant afford to have a dedicated server but are on a shared hosting package. If we have no other alternative we will be going with the 5 selects but since there seems to be a join alternative was hoping someone could help me out. Thanks anyway. Cheers, -Ryan Ryan, If this query worked, it would return you 5 rows, one for each separate count. If you execute 5 separate counts in PHP, you'll get 5 separate values with the same numbers as above. Not radically different? Andy -- 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: random record
39? -Original Message- From: tuncay bas [mailto:[EMAIL PROTECTED] Sent: 15 September 2003 13:32 To: mysql Subject: random record hi, why its mysql database over random record use? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to generate sql scripts in Mysql?
Florence, Section 3.6 of the manual explains... 3.6 Using mysql in Batch Mode In the previous sections, you used mysql interactively to enter queries and view the results. You can also run mysql in batch mode. To do this, put the commands you want to run in a file, then tell mysql to read its input from the file: shell mysql batch-file If you are running mysql under windows and have some special characters in the file that causes problems, you can do: dos mysql -e source batch-file If you need to specify connection parameters on the command-line, the command might look like this: shell mysql -h host -u user -p batch-file Enter password: When you use mysql this way, you are creating a script file, then executing the script. If you want the script to continue even if you have errors, you should use the --force command-line option. Why use a script? Here are a few reasons: If you run a query repeatedly (say, every day or every week), making it a script allows you to avoid retyping it each time you execute it. You can generate new queries from existing ones that are similar by copying and editing script files. Batch mode can also be useful while you're developing a query, particularly for multiple-line commands or multiple-statement sequences of commands. If you make a mistake, you don't have to retype everything. Just edit your script to correct the error, then tell mysql to execute it again. If you have a query that produces a lot of output, you can run the output through a pager rather than watching it scroll off the top of your screen: shell mysql batch-file | more You can catch the output in a file for further processing: shell mysql batch-file mysql.out You can distribute your script to other people so they can run the commands, too. Some situations do not allow for interactive use, for example, when you run a query from a cron job. In this case, you must use batch mode. The default output format is different (more concise) when you run mysql in batch mode than when you use it interactively. For example, the output of SELECT DISTINCT species FROM pet looks like this when run interactively: +-+ | species | +-+ | bird| | cat | | dog | | hamster | | snake | +-+ But like this when run in batch mode: species bird cat dog hamster snake If you want to get the interactive output format in batch mode, use mysql -t. To echo to the output the commands that are executed, use mysql -vvv. You can also use scripts in the mysql command-line prompt by using the source command: mysql source filename; Andy -Original Message- From: florence florence [mailto:[EMAIL PROTECTED] Sent: 12 September 2003 09:17 To: [EMAIL PROTECTED] Subject: How to generate sql scripts in Mysql? Hi, Hope someone can guide me how to generate sql scripts in Mysql?Thanks. regards, florence Yahoo! Games - Who Wants to Be A Millionaire? Play now! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query won't use index
Ken, The problem is that you've got a compound index on files which type_id isn't the first item. If you create a new index on files, just on type_id, all will be fine. Mysql would only be able to use a compound index for this query if type_id was the first column in it. Andy -Original Message- From: Ken [mailto:[EMAIL PROTECTED] Sent: 09 September 2003 18:19 To: [EMAIL PROTECTED] Subject: Query won't use index I have a query that won't seem to use an index. See below for the EXPLAIN, the tables and the indexes (relevant fields only, so no need to ask me why I'm bothering to do a query with nothing else in it). Note that if I change select t.Desc to select t.type_id, then MySQL correctly uses the index. What am I missing? - explain select t.Desc from files f, types t where t.type_id = f.type_id | t| ALL | PRIMARY,type_id | NULL|NULL | NULL |3 | | | f| ref | type_id | type_id | 4 | t.type_id | 2322 | Using index | mysql describe types; | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | type_id| int(11) | | PRI | NULL| auto_increment | | Desc | char(6) | YES | | NULL|| mysql describe files; --++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+ | id | int(11) | | PRI | NULL | auto_increment | | type_id| int(11) | | MUL | 1 mysql show index from files; | Table | Non_unique | Key_name | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Comment | --+---+-+--++-+ | files | 0 | PRIMARY|1 | id | A |6965 | NULL | NULL | | | files | 1 | id |1 | id | A |6965 | NULL | NULL | | | files | 1 | type_id|1 | type_id | A | 2 | NULL | NULL | | mysql show index from types; | Table | Non_unique | Key_name| Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Comment | +---+-+--++-+ | types | 0 | PRIMARY |1 | type_id | A | 3 | NULL | NULL | | | types | 1 | type_id |1 | type_id | A |NULL | NULL | NULL | | Thanks in advance! - Ken -- 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: STUMPED: How Can I Pull Related Info Using Subqueries/Joins?
Patrick, You need outer joins to do this. Try searching for outer join sql tutorial on Google. Hope this helps, Andy -Original Message- From: Patrick Crowley [mailto:[EMAIL PROTECTED] Sent: 07 August 2003 16:51 To: [EMAIL PROTECTED] Subject: STUMPED: How Can I Pull Related Info Using Subqueries/Joins? I'm creating a tool to browse a database of movie listings. The browser pulls up 25 results at a time, and you can page through them using 'Next' and 'Prev' tools. Pretty basic stuff. Here are my tables: movies directors comments movies_directors movies_comments etc... (primary key is movies.id) But here's where I'm stuck: for each film, I need to pull the movie info in 'movies', plus any related data from other tables, like this: The Lord of the Rings | Peter Jackson | 3 comments Episode II| George Lucas | 0 comments Indiana Jones | Steven Spielberg | 15 comments I seem to run into problems when I try to join info from all of these related tables. If there's a match, great. But, if not (like a movie with no comments), the movie is excluded from the result set. I've tried all sorts of SQL queries to make this work, but nothing seems to do the trick. SO, HERE'S MY QUESTION, IS THERE ANY WAY TO DO THIS QUERY WITHOUT SUBQUERIES/MYSQL 4.0? Or would the best approach be to use PHP to do all the subquery lookups? Thanks for your help! Patrick -- 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: Problems with spatial extensions
Enrique, Your English is great so don't apologise! Looking on the web site at http://www.mysql.com/doc/en/Spatial_extensions_in_MySQL.html , it appears that this feature was introduced in server version 4.1, so I'm afraid you'll have to upgrade your server. Best regards, Andy -Original Message- From: Enrique Andreu [mailto:[EMAIL PROTECTED] Sent: 05 August 2003 08:08 To: [EMAIL PROTECTED] Subject: Problems with spatial extensions Hi, I would like to have a column of a table of the type GeometryCollection, but when I try to type a simple example like the ones in the manual, I get this: mysql CREATE TABLE geom (g GEOMETRY); ERROR 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 'GEOMETRY)' at line 1 I'm running server mysqld-nt version 4.0.13 on a windows NT, the mysql client is the same version. Have I to install some plug-in or change some option in the configuration file? I need help. Thanks and excuse me by the english (I'm spanish). Enrique ___ Yahoo! Messenger - Nueva versión GRATIS Super Webcam, voz, caritas animadas, y más... http://messenger.yahoo.es -- 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: Problem reading my.cnf
Ganbold Because the bug that did not recognise the comment character in the password line has been fixed? Andy -Original Message- From: Ganbold [mailto:[EMAIL PROTECTED] Sent: 04 August 2003 09:51 To: Primaria Falticeni Cc: [EMAIL PROTECTED] Subject: Re: Problem reading my.cnf Hi, It was Ok in previous version of mysql. Why they changed ? Thanks anyway. Ganbold At 11:21 AM 8/4/2003 +0300, you wrote: Try to not use # if you want the password to be in my.cnf file. The # is for comments if my.cnf file. - Original Message - From: Ganbold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, August 04, 2003 11:08 AM Subject: Problem reading my.cnf Hi, I have just upgraded mysql server version from 4.0.13 to 4.0.14, from FreeBSD 5.1 ports collection. Before upgrade mysql command line client could read password from /etc/my.cnf file and I could use mysql without supplying -p option. But after upgrade it could read password only up to # sign. For instance in /etc/my.cnf file: [client] password= test#istest$ port= 3306 socket= /tmp/mysql.sock When I issue command mysql --print-defaults it prints: mysql would have been started with the following arguments: --password=test --port=3306 --socket=/tmp/mysql.sock --no-auto-rehash Is this a bug of mysql command line client or it is something different? Is there anybody who solved this already? thanks in advance, Ganbold -- 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] -- 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: my.cnf is not available under windows 2000
Morten , The file used is my.cnf on unix and my.ini on windows. You should only have one file. Andy -Original Message- From: Morten Gulbrandsen [mailto:[EMAIL PROTECTED] Sent: 01 August 2003 14:23 To: [EMAIL PROTECTED] Subject: my.cnf is not available under windows 2000 Hi programmers, according to the manual, There are two option files with the same function: `C:\my.cnf', and the `my.ini' file in the Windows directory. Is it sufficient with only one of the files ? I have only my.ini For which purpose is my.cnf , please? Yours Sincerely Morten Gulbrandsen -- 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: Can someone help me??
Try using only single quotes rather than a mixture of single and double quotes? Andy -Original Message- From: Sbandy [mailto:[EMAIL PROTECTED] Sent: 16 July 2003 10:32 To: Rudy Metzger; [EMAIL PROTECTED] Subject: RE: Can someone help me?? I use phpmyadmin At 11.26 16/07/2003 +0200, Rudy Metzger wrote: Did you do that on the mysql command line? Or did you use a different client and/or API? Cheers /rudy -Original Message- From: Sbandy [mailto:[EMAIL PROTECTED] Sent: woensdag 16 juli 2003 11:21 To: [EMAIL PROTECTED] Subject: Can someone help me?? I am new in mysql I wrote this query: INSERT INTO testo (id_lingua, id_categoria, id_campo, riga, sezione, ordine, codice) VALUES (10 ,6 ,5 ,10 - 99: ,12 ,50 ,'S12IT3' ) and server reply to me: Query was empty Can someone tell me where i do mistake? Thanks a lot.. Sbandy [EMAIL PROTECTED] http://www.motormaniaci.com il portale per gli appassionati di motore [EMAIL PROTECTED] http://www.motormaniaci.com il portale per gli appassionati di motore -- 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: Another Newbie Question
George, Try in the folder with the same name as your database, under the data folder. Andy -Original Message- From: Degan, George E, JR, MGSVC [mailto:[EMAIL PROTECTED] Sent: 15 July 2003 13:30 To: [EMAIL PROTECTED] Subject: Another Newbie Question I am finally able to enter data and am going through the Tutorial in section 3 of the mySQL manual. It suggests that I create a .txt file from which to load date into a table. Where does mySQL look for data to load in the default installation? I thought it would be in the data folder under mysql, but it can't find it. Please advise. Thanks, George -- 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: MySQL vs. PostgreSql -- speed test
When I benchmarked PostgreSql against MySql for my application, MySql was 15 times faster, so 18% wouldn't make much difference for me! Andy -Original Message- From: Robson Oliveira [mailto:[EMAIL PROTECTED] Sent: 14 July 2003 15:35 To: [EMAIL PROTECTED] Subject: Re: MySQL vs. PostgreSQL -- speed test I agree with your opinion in 100%, but in my case I need DBMS with features like subselectes/utf-8/stored procedures but the speed is also very important issue. You might have to spend money! You are saying that there is DBMS with all this features and it is as fast as MySQL ? I don't know, but if there is, it is one you will have to pay for. In any case, speed is as much a matter of application design as a DBMS characteristic. As a minor side issue, we did some _very limited_ testing with MS SQLServer 2000 using unicode v ascii queries. Using unicode, queries tended to run at about half the speed compare to using ascii. This was client server, so it is likely that the increased network traffic is to blame, but bear it it mind. IPv6 is coming soon and PostgreSQL 7.4 will be the 1st database IPv6-ready! This oportunity give to us the choice to move on to IPv6 network environment. I do believe (tested in my network 6to4 connection) this move can improve the environment performance until 18% in all! Some moves can Checkmate! Robson Oliveira -- 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: Can mysql handle this load?
Adam, Mysql will easily handle this. This certainly doesn't constitute a large database. Correctly indexing the database should see you doing speedy queries on years worth of data. Sounds like you've used access in the past :-) Andy -Original Message- From: Adam Gerson [mailto:[EMAIL PROTECTED] Sent: 09 July 2003 14:47 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Can mysql handle this load? I am writing an attendance system in php for my school. We have a little less then 1000 students. For every day of the school year one record will be entered into a table for each student representing their attendance status (present, absent, late, etc...). I also have several other supporting tables for relationships. When it comes to reporting and querying this DB I am worried that it will very quickly become very large and slow. Can mysql handle this? Are there any techniques to speed it up? I will trying indexing major columns. I have also considered keeping all previous days attendance in a separate table from the current days attendance and moving things over in the middle of the night. This way any operations on the current days data will go quickly, but reports on long term things will still be slow. Good idea? Thanks, Adam --- Adam Gerson Systems Administrator / Computer Teacher Columbia Grammar and Prep School 212-749-6200 [EMAIL PROTECTED] www.cgps.org -- 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: concat() differences between mssql and mysql
Note that trailing spaces are not removed when you insert data into a TEXT (or BLOB for that matter) column. This may be of use to you, but TEXT does have limitations. Andy -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: 08 July 2003 09:31 To: [EMAIL PROTECTED] Subject: Re: concat() differences between mssql and mysql Ooks Server [EMAIL PROTECTED] wrote: I've run into a problem with the behavior of concat(). If I have two fields, char(10), and I do this: concat(field1,fields) With MSSQL I get both fields including trailing spaces. With MYSql, I get the two fields with the trailing spaces trimmed. Example: Field1 = abc Field2 = qwerty MSSQL - concat( field1, fields) - abc qwerty MYSQL - concat( field1, fields) - abcqwerty How do I get Mysql to behave like MSSQL does? I need it to concatenate the fields without stripping the trailing spaces. It's a known behaviour of MySQL. MySQL removes trailing spaces at the end of VARCHAR and CHAR columns: http://www.mysql.com/doc/en/Open_bugs.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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: [HELP] Newbie experiences problems AND receives no help for the moment
Matthias, I, like everyone else on the list it appears, have no idea how to help you as you have provided no example of what you are trying to do, no output and no error messages. Remember everyone gives help here for free, so people tend to help people who make it clear what the problem is. I know you originally tried to attach something, but there was no attachment to your message. I looked for it, saw it wasn't there and thought I'll ignore that - he's bound to repost when he sees that his attachment didn't get through... Andy -Original Message- From: Matthias Fischer [mailto:[EMAIL PROTECTED] Sent: 07 July 2003 14:11 To: [LIST] MySQL Subject: [HELP] Newbie experiences problems AND receives no help for the moment Hi, I was trying to install, via an sql file (not enclosed, since 4MB big), a MySQL db, but I experience some problems: - when installing from shell, I get the output mysql.out (originally enclosed, not sure whether the server tolerated the enclosure); I don't think anything useful is achieved by my command, as I cannot see any confirmation of creating db, tables, datasets etc. are contained in the file. - when installing via MySQL Front, I cannot run the entire sql code in one go, I have to do it section-wise, e.g. tyble by table; even then, it appears that not all of the sql command are carried out properly. Please help: - what do I have to do to install file.sql properly from the DOS shell? I am operating under Win-32 (Win98 to be precise), with Apache 2.0.46 and MySQL 4.0.13. Any hints would be very much appreciated. Matthias Fischer -- 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: issue with 'count'
Paul, Try SELECT c.id, count(cug2.id_curso) as num_profe_curso FROM nuke_elearning_curso as c, LEFT JOIN nuke_elearning_curso_usuario_grupo as cug2 ON c.id = cug2.id_curso group by c.id HAVING num_profe_curso 0 Andy -Original Message- From: Paul [mailto:[EMAIL PROTECTED] Sent: 07 July 2003 19:56 To: [EMAIL PROTECTED] Subject: issue with 'count' hi to all, and thank for your valuable help my problem SELECT c.id, count(cug2.id_curso) as num_profe_curso FROM nuke_elearning_curso as c, LEFT JOIN nuke_elearning_curso_usuario_grupo as cug2 ON c.id = cug2.id_curso WHERE num_profe_curso 0 group by c.id esta sentencia sql busca la cantidad de profes para cada uno de los cursos me da error en que es desconocida la columna num_profe_curso, hay alguna manera de accesar a esa columna? this sql statement search the number of teachers to each of the courses in the 'c' table. the server mysql give me error because the column 'num_profe_curso' is unknown, is there a way to access a this column?? -- 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: Counting question
Try this: select delivery, count(*) as ticketcount from ticketsales where delivery=post or delivery=pickup group by delivery Andy -Original Message- From: Ville Mattila [mailto:[EMAIL PROTECTED] Sent: 03 July 2003 11:28 To: [EMAIL PROTECTED] Subject: Counting question Hi there, We are currently having a large festival here in Ikaalinen, Finland (www.satahamesoi.fi) and we have our booking system based on MySQL. I should find out how many tickets are sold in each concert with different delivery methods. How could I make a count like this: SELECT COUNT(delivery='post') AS post, COUNT(delivery='pickup') AS pickup... Or should I just make two separated queries? Thanks, Ville -- 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: CSV Formated output
Jeff, Use SELECT INTO OUTFILE and FIELDS TERMINATED BY ',' See the manual for more info. Andy -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: 03 July 2003 12:38 To: [EMAIL PROTECTED] Subject: CSV Formated output Is there a way to output the results of a select query into a CSV or Comma Deliminated format? Thanks, Jeff McKeon IT Manager Telaurus Communications LLC [EMAIL PROTECTED] (973) 889-8990 ex 209 ***The information contained in this communication is confidential. It is intended only for the sole use of the recipient named above and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication, or any of its contents or attachments, is expressly prohibited. If you have received this communication in error, please re-send it to the sender and delete the original message, and any copy of it, from your computer system. Thank You.*** -- 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: CSV Formated output
Jeff, By manual, I mean http://www.mysql.com/documentation/index.html It's worth downloading the all in one HTML file version so you can search through the whole document for things like SELECT INTO OUTFILE or FIELDS SEPARATED BY All the best, Andy -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: 03 July 2003 13:50 To: Andy Eastham; Mysql List Subject: RE: CSV Formated output Thanks Andy, that will do! I don't have a manual, using the open source MySQL. I do have a book (New Riders MySQL) but was looking for deliminated not terminated. Found it now. Thanks again for the help. Jeff McKeon IT Manager Telaurus Communications LLC [EMAIL PROTECTED] (973) 889-8990 ex 209 ***The information contained in this communication is confidential. It is intended only for the sole use of the recipient named above and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication, or any of its contents or attachments, is expressly prohibited. If you have received this communication in error, please re-send it to the sender and delete the original message, and any copy of it, from your computer system. Thank You.*** -Original Message- From: Andy Eastham [mailto:[EMAIL PROTECTED] Sent: Thursday, July 03, 2003 8:06 AM To: Mysql List Subject: RE: CSV Formated output Jeff, Use SELECT INTO OUTFILE and FIELDS TERMINATED BY ',' See the manual for more info. Andy -Original Message- From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: 03 July 2003 12:38 To: [EMAIL PROTECTED] Subject: CSV Formated output Is there a way to output the results of a select query into a CSV or Comma Deliminated format? Thanks, Jeff McKeon IT Manager Telaurus Communications LLC [EMAIL PROTECTED] (973) 889-8990 ex 209 ***The information contained in this communication is confidential. It is intended only for the sole use of the recipient named above and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication, or any of its contents or attachments, is expressly prohibited. If you have received this communication in error, please re-send it to the sender and delete the original message, and any copy of it, from your computer system. Thank You.*** -- 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] -- 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: sum() problems
Pat, I think it might be caused by the fact that you are grouping by a column that isn't being selected - ordini.numordine is not in the select part. Andy -Original Message- From: PaT! [mailto:[EMAIL PROTECTED] Sent: 26 June 2003 12:50 To: [EMAIL PROTECTED] Subject: sum() problems Dear All, I have this two queries The first one gives me the right result for the sum(): SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y'), SUM(ordini.totale), SUM(ordini.quantita), ordini.codcliente, ordini.fromprev FROM ordini WHERE ordini.numordine = 2302 GROUP BY ordini.numordine; this other one gives me problems with the sum(), wrong result. SELECT DATE_FORMAT(ordini.dataord,'%d-%m-%Y'), SUM(ordini.totale), SUM(ordini.quantita), ordini.codcliente, ordini.fromprev, carello.numprev, DATE_FORMAT(carello.dataordine,'%d-%m-%Y') FROM ordini, carello WHERE ordini.numordine = 2302 AND carello.numprev = ordini.fromprev GROUP BY ordini.numordine, carello.numprev; Help is appreciated I'm using mysql 4.0.13 Patrizio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: search and replace.
Try something like this: UPDATE tablename SET url = concat(programs, substring(url,12)) where url like (disciplines/%); Andy -Original Message- From: Craig Harding [mailto:[EMAIL PROTECTED] Sent: 20 June 2003 17:31 To: [EMAIL PROTECTED] Subject: search and replace. Is there is a way to do a search and replace on the mysql cmd prompt? I want to replace text in about 20 rows that has the same pattern with a replacement. I have a table that has a column 'name' and a column 'url' which holds menu items for a website. Both columns are type varchar. 'url' has about 20 rows starting with 'disciplines/someurl' and I want to replace 'disciplines' with 'programs'. Is there a way to do this in mysql? I know I can do it in php, but it would be cool to do it, (and probably faster) to do it in mysql. I know how do use the regexp for SEARCHING, but can I do a replace on the same cmd with an UPDATE? thanks in advance, Craig. -- 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: probably a stupid question
Jonas, After the insert, execute SELECT LAST_INSERT_ID(); This always gives the last auto increment value generated by your database connection. Andy -Original Message- From: Jonas Geiregat [mailto:[EMAIL PROTECTED] Sent: 18 June 2003 19:26 To: 'Mysql' Subject: probably a stupid question Hey, Here is what I want to do I have 2tables let's say table A and B for simplicity. in table A I have column id and in table B I have column A_id I insert a new value into table A insert into a values(NULL) Since id is auto_incremenet and the primary key it will have an auto value. Now I want that the column A_id contains that id nr. I could query for the biggest id in column A and insert that into B. But are there better way's of doing something like this ? -- 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: question?
Jerry, Try this link: http://www.phpbuilder.com/columns/moon19990716.php3 Andy -Original Message- From: JeRRy [mailto:[EMAIL PROTECTED] Sent: 16 June 2003 14:35 To: [EMAIL PROTECTED] Subject: question? Hi, I want to run my php scripts, mysql from home. Now after being pointed to download apache for Windows as well as mysql for windows I have installed them both. Apache loads fine on localhost as I have tested it. But I can't get my php pages to load correctly via apache. It shows html correctly but not php. Is there something I need to add to apache to get php to work with it? If so where can I download it from and any instructions on installing it please. Thanks for your time. Jerry http://mobile.yahoo.com.au - Yahoo! Mobile - Check compose your email via SMS on your Telstra or Vodafone mobile. -- 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: Getting the last entered row from a relational table
Try: select * from comments where (place_holder id) = (id in main table) order by id desc LIMIT 1 Andy -Original Message- From: Petre Agenbag [mailto:[EMAIL PROTECTED] Sent: 05 June 2003 11:05 To: [EMAIL PROTECTED] Subject: Getting the last entered row from a relational table Hi List. I think I'm having a very off day and need some confirmations on how MySQL works with it's result sets. I have a couple of relational tables , the first holding the person's name and address for instance, and the other tables holds comments and complaints respectively. Each table has it's own id field, as well as a master_id that equals the id of the main table. The app that I'm writing (in PHP), lists the users with a simple select * from main , and this returns all the users currently on the system ( I have names as varchar and unique). My problem is now with the following: When the user clicks on one of the names, I want to do a couple of things: a) The user details be displayed along with all the comments and complaints that correspond to that users id located in the other tables. pseudo SQL - select * from comments where (place_holder id in comments) = (id in main table); b) Being able to list the comments and complaints in reverse order ie, older ones first: pseudo SQL - select * from comments where (place_holder id) = (id in main table) order by id desc; c) List ONLY the last (newest) comments/complaints THIS IS WHERE I have problems: If I do a select MAX(id), comment from comments where (place_holder id) = (id in main table) will MySQL automagically grab the comment from the row that has the maximum ID? If so, is there a shorter way of doing this query? For my example here, it's not a big deal, but with larger tables with more collumns, having to specify the collumns in the query ( when I want ALL to be returned) becomes a bit of a hassle.. I basically want to say: return ONLY the last comment added where the id matches the supplied id from main_table. So, I need to Translate this to SQL... Can I do this with SQL, or must I first establish the id with the select MAX(id) from comments where id = provided_id, and then do a new query select * from comments where id = MAX(id) Thanks -- 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: full text searching question
Chris,
You're nearly there - the way to do it is:
SELECT first_name,last_name,MATCH(first_name,last_name) AGAINST
('+wilkinson* chris*' IN BOOLEAN MODE) AS score FROM names_table WHERE
MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN
BOOLEAN MODE) ORDER BY score DESC;
The db engine won't execute the full text query more than once so this is
not inefficient.
Andy
-Original Message-
From: Chris Wilkinson [mailto:[EMAIL PROTECTED]
Sent: 03 June 2003 13:12
To: [EMAIL PROTECTED]
Subject: full text searching question
can anybody explain this to me please! I search am searching
through a database
with first name and last names. BTW I use mysql 4.0.13 on RedHat
Linux 9.0 in
case that matters. I created a fulltext search on both fields
together so I can
search them like this:
mysql SELECT first_name,last_name FROM names_table WHERE
MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN
BOOLEAN MODE);
The problem is that for some reason the record with
first_name=fran is displayed
first even though the score is lower than the record where
first_name=christopher
mysql SELECT first_name,last_name,MATCH(first_name,last_name) AGAINST
('+wilkinson* chris*' IN BOOLEAN MODE) AS score FROM names_table WHERE
MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN
BOOLEAN MODE);
+-+---+-+
| first_name | last_name | score |
+-+---+-+
| Fran| Wilkinson | 1 |
| Christopher | Wilkinson | 1.333730698 |
+-+---+-+
2 rows in set (0.01 sec)
Can somebody please explain why this is and how I can get
Christopher to show up
before Fran! Thanks!
--
Chris Wilkinson
[EMAIL PROTECTED]
--
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: full text searching question
Chris,
I should have added that the explanation is that the full text query does
not automatically sort on the score when boolean mode is selected.
Andy
-Original Message-
From: Chris Wilkinson [mailto:[EMAIL PROTECTED]
Sent: 03 June 2003 13:12
To: [EMAIL PROTECTED]
Subject: full text searching question
can anybody explain this to me please! I search am searching
through a database
with first name and last names. BTW I use mysql 4.0.13 on RedHat
Linux 9.0 in
case that matters. I created a fulltext search on both fields
together so I can
search them like this:
mysql SELECT first_name,last_name FROM names_table WHERE
MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN
BOOLEAN MODE);
The problem is that for some reason the record with
first_name=fran is displayed
first even though the score is lower than the record where
first_name=christopher
mysql SELECT first_name,last_name,MATCH(first_name,last_name) AGAINST
('+wilkinson* chris*' IN BOOLEAN MODE) AS score FROM names_table WHERE
MATCH(first_name,last_name) AGAINST ('+wilkinson* chris*' IN
BOOLEAN MODE);
+-+---+-+
| first_name | last_name | score |
+-+---+-+
| Fran| Wilkinson | 1 |
| Christopher | Wilkinson | 1.333730698 |
+-+---+-+
2 rows in set (0.01 sec)
Can somebody please explain why this is and how I can get
Christopher to show up
before Fran! Thanks!
--
Chris Wilkinson
[EMAIL PROTECTED]
--
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: password not working from command line
Eldon, Make sure you don't enter a space between -u and the username and -p and the password ie mysql -uuser -ppassword Andy -Original Message- From: Eldon Ziegler [mailto:[EMAIL PROTECTED] Sent: 01 April 2003 16:15 To: [EMAIL PROTECTED] Subject: password not working from command line The password I entered in a GRANT statement isn't being accepted from the command line after entering mysql -u username -p and then entering the password from the GRANT statement. Is there something else I need to do? -- 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: Opposite of DISTINCT()
Bob, You have to do a self join - try this off the top of my head... - Select p1.email FROM tblperson p1, tblperson p2 WHERE p1.email = p2.email GROUP BY p1.email HAVING count(p1.email) 1 Andy -Original Message- From: Bob Sawyer [mailto:[EMAIL PROTECTED] Sent: 01 April 2003 21:04 To: MySQL List Subject: Opposite of DISTINCT() I know that using SELECT DISTINCT(colname) will result in output that does not contain any duplicates from that column. But how would I output JUST the duplicates? If I have as part of a table a column containing email addresses, and I want to list just the duplicate addresses rather than the distinct addresses, what's the syntax there? Thanks, Bob -- 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: UPDATE syntax help
Paul, You have to use the results of one select to generate lots of update statements. If you execute all these from your program, make sure you use a different database connection for the updates, if you're keeping a results set open. Alternatively, if it's a one off, generate a script file with your code (or directly from SQL if you're a martyr) and then run it. I've used both methods successfully, Andy -Original Message- From: JJ [mailto:[EMAIL PROTECTED] Sent: 12 March 2003 23:45 To: MySQL Cc: Paul DuBois Subject: Re: UPDATE syntax help That explains it then D'OH Is there a workaround? Thanks :-) - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 9:02 AM Subject: Re: UPDATE syntax help At 8:33 +1000 3/13/03, MySQL wrote: Hi all, I am having a little UPDATE syntax issue. According to the manual According to the manual, this won't work until MySQL 4.x UPDATE EBA_USERS, IMPORT_USERS SET EBA_USERS.HUB_ID = IMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPORT_USERS.REP_LOCATION_ID, EBA_USERS.REP_FIRST_NAME = IMPORT_USERS.REP_FIRST_NAME, EBA_USERS.REP_LAST_NAME = IMPORT_USERS.REP_LAST_NAME, EBA_USERS.REP_DISABLED = IMPORT_USERS.REP_DISABLED WHERE EBA_USERS.REP_ID = IMPORT_USERS.REP_ID should work (as I understand it ;-) but I get the error ERROR 1064: You have an error in your SQL syntax near ' IMPORT_USERS SET EBA_USERS.HUB_ID = IIMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPO' at line 1 I did note in the comments section at the bottom someone else with same/similar problem, but have been unable to find a thread in the mail archive. My apologies if this has been dealt with already, or a workaround suggested... MySQL 3.23.51-nt on a Win2K box MyODBC 3.51 Thanks Jeff Creed Throbware (0417) 797 592 http://www.throbware.com.au - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: What to Download?
Jeremy, If you just want to use the server and connect to it to perform queries and maintain it, just the server and clients should suffice. If you want to link your own C programs against mysql or measure the exact performance on your hardware, you may want the others. By the fact that you're asking this question (no offence intended), I think you'll probably just want the server and client programs. Andy mysql query -Original Message- From: Jeremy Whitlock [mailto:[EMAIL PROTECTED] Sent: 03 March 2003 16:39 To: 'MySQL' Subject: What to Download? MySQL List, On the http://www.mysql.com/downloads/mysql-3.23.html page, under Linux x86 RPM downloads, there are 5 available downloads. Do I need all of them? I would like to have all capabilities. Can someone advise me as to what each download is for? Thanks, Jeremy Whitlock --- MCP/MCSA IT Manager for Star Precision, Inc. Phone: (970) 535-4795 Metro: (303) 926-0559 Fax: (970) 535-0780 Metro Fax: (303) 926-0559 http://www.starprecision.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Recursion
Rob, This is a common problem in document management, where I have a reasonable amount of experience. Unfortunately, the short answer is, that to be completely generic, efficient and elegant, it's a bit of an impossible problem. What we have always done in this situation is to maintain an additional denormalised column called FullPath so, expanding your sample data a bit: ID | Name | ParentID | FullPath 1 | Bob | 0| 1 2 | John | 1| 1/2 3 | Elm | 1| 3/1 4 | Sue | 2| 1/2/4 5 | Dave | 4| 1/2/4/5 6 | Fred | 5| 1/2/4/5/6 etc. This initially seems like a horrible solution, raddled with problems. However it's actually quite efficient. The application has to manage the Full Path on updates (although it's easy to rebuild it and check integrity if you screw it up). It's also easy to find anything at any level under an object using string comparisons. If you move a folder (parent) to [new path], you have to do an update such as UPDATE table set FullPath = [new path] + substring(oldpath, [new Path Length]) WHERE fullpath like '[old path]%' Again this is indexed and pretty efficient. If you like, you can remove the objects own id from the fullpath and make it effectively parent path Hope this helps. All the best, Andy -Original Message- From: Rob [mailto:[EMAIL PROTECTED]] Sent: 12 February 2003 07:18 To: [EMAIL PROTECTED] Subject: Recursion Hi all, I need some help with recursion in mySql. I have the following table: ID | Name | ParentID 1 | Bob | 0 2 | John | 1 3 | Elm | 1 etc. For a given ID, I need to recurse up the tree and get all the parents. I've already read about Joe Celko's nested set approach, but it's not a good solution as apparently updates are a real pain and this table will be modified heavily. Does anyone have any good suggestions?? Maybe store procs (although, by all accounts store proc functionality doesn't come standard with mySql)?? Thanks --- Rob ** Rob Cherry mailto:[EMAIL PROTECTED] +27 21 447 7440 Jam Warehouse RSA Smart Business Innovation http://www.jamwarehouse.com ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Recursion
Amer, It's still worth storing the parentId, because you can easily recreate the fullpath if (when!) your code screws up a set of full paths. You can also write a reliable sanity checker that checks the full path of all the nodes in the table based on the parentids. Also, to locate multiple children of a node, it's a more efficient query to use where parentid = x, rather than where fullpath like '1/2/3/4/%' All the best, Andy Yes, excellent idea. It's the classic 'linked list' from my old Pascal days. While playing with it I realized that you only have to save the ID, Name and the FullPath (parents) data. For example, using Andy's data: ID | Name | FullPath 1 | Bob | 0 2 | John | 0/1 3 | Elm | 0/1 4 | Sue | 0/1/2 5 | Dave | 0/1/2/4 6 | Fred | 0/1/2/4/5 The FullPath doesn't need the the 'leaf' or bottom node - it can be derived (it's the ID). Using a 'split' function on the FullPath data you can pull out the individual parents. -- /* All outgoing email scanned by Norton Antivirus 2002 */ Amer Neely, Softouch Information Services W: www.softouch.on.ca E: [EMAIL PROTECTED] V: 519.438.5887 Perl | PHP | MySQL | CGI programming for all data entry forms. We make web sites work! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Very slow request with many ORs in where parts
Artem, Have you considered using a full text index? I don't really understand exactly what you are trying to do, but consider it if you haven't already. Andy -Original Message- From: Artem Koutchine [mailto:[EMAIL PROTECTED]] Sent: 11 February 2003 14:23 To: [EMAIL PROTECTED] Subject: Very slow request with many ORs in where parts Hi! I have the following table: create table law_words ( l_id int unsigned not null, w_id int unsigned not null, primary key (w_id, l_id) ); The request is: SELECT DISTINCT w0.l_id FROM law_words as w0 inner join law_words as w1 on w0.l_id=w1.l_id where w0.w_id in (258,282,... about 30 ids) and w1.w_id in (405, 2017, and so on about 50 ids); The basic idea is thart law_words hold index of words (w_id) for each law (l_id), so law can be found by words, which are specified by user and the their ids are looked up in vocabular. Now law_words has 288000 records and that request takes about 1 second on a pc with 1GB of RAM and dual Pentium III XEON 550Mhz, which is TOO MUCH! Explain show thart mysql is using ' range' and primary index, and about 400 records for each table. For three specified words request takes about forever, so no search is possible for three words. The request is using INNER JOIN to get the words in the 'AND' manner (laws which contain ALL specified words). I don't understand what i am doing wrong, since i thought it is a basic technology behind any search engine. Please, help, if you can. Regards, Artem - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: [ gamma file ]
It's the next step after beta - ie it's release quality, but hasn't been in release that long. Therefore it's probably not been deployed in production by that many people. Bear in mind that even alpha versions have undergone and completely passed the full set of regression tests. After a period of time in gamma, where bug reports have tailed off, it is finally called Stable. As a rough comparison, check the following table Microsoft Label MySQL label version 1.0 alpha Version 2.1a beta Version 3.5c gamma Version XP Pro .NET SP19stable Andy mysql query -Original Message- From: Elby Vaz [mailto:[EMAIL PROTECTED]] Sent: 16 January 2003 13:46 To: [EMAIL PROTECTED] Subject: [ gamma file ] --Im brazilian. Sorry by my english hello! I installed mysql-4.0.9-gamma-win on my machine. What means gamma ? Thanks, Elby. _ MSN Hotmail, o maior webmail do Brasil. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Need help with UNION
Garry, You are using mysql version 4? Unions are only supported in version 4. If so, the only difference I can see from your example to the manual is that each select is in brackets in the manual. Try the query: (select cnumber from spouse where fd_status = A) union (select cnumber from members where fd_status = A); Andy -Original Message- From: Garry Rothert [mailto:[EMAIL PROTECTED]] Sent: 16 January 2003 14:11 To: [EMAIL PROTECTED] Subject: Need help with UNION Hi I am attempting a simple union. select cnumber from spouse where fd_status = A union select cnumber from members where fd_status = A When I run this SQL statement I get the following error. ERROR 1064 : You have are an error in your SQL syntax near 'union select cnumber from members where fd_status = A' at line 2. CNUMBER is smallint in both tables. I know this seems like a silly union to try but I've simplified a more complex statement to troubleshoot, I can't get any union to work. Thanks This email and any files transmitted with it are privileged, confidential, subject to copyright and intended solely for the use of the individual or entity to whom they are addressed. Views expressed are those of the user and not necessarily those of DPH Engineering Inc.. Any unauthorized use, copying, review or disclosure is prohibited. Please notify the sender immediately if you have received this communication in error. Thank you for your assistance and co- operation. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Solaris Performance
Jeremy, Thanks for replying. It's CPU bound. The 64 bit version has improved things a little - about 5-10%. I just expected an enterprise server to be faster than a laptop running win2k with an IDE, Tomcat, Outlook, Office all at the same time... I now know better. I'm thinking of putting a fast Intel Linux box behind the sun box as a database server, and leaving the sun to run apache and tomcat. Hopefully this will give me the best of both worlds. Thanks again, Andy -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: 14 January 2003 18:50 To: Andy Eastham Cc: [EMAIL PROTECTED] Subject: Re: Solaris Performance On Mon, Jan 13, 2003 at 03:34:29PM -, Andy Eastham wrote: I've got a mysql application that was developed on win32 and linux that is now going to be deployed on a Sun E250 Solaris 9 box with 3 36Gb non raided SCSI disks. No problem I thought - the performance is fine on my PIII 850MHz laptop, so it will rock on an E250... I'm not sure why you thought that, but... Not the case - the laptop seems to be performing at twice the speed of the E250 for a simple select from a table of 55 million simple rows (just 3 floating point numbers per row - the .MYD file is 700Mb and the .MYI index file is 2.7Gb). It takes 1.2 seconds on the laptop and 2.5 seconds on the sun. This is a big problem, as I have to do three of these queries plus processing and return in under 10 seconds. The laptop takes 6 seconds, the Sun takes 12. I've got a Linux PIII 933MHz Dell Server which at least half as much again faster than the laptop. Is it disk or I/O bound? I've increased the key cache to 250Mb on the Sun (it's at the default on the laptop), but no radical difference was apparent. Then it probably wasn't the bottleneck. Have you looked at system activity to get an idea of what it is (or it not) doing? Disk I/O? Paging? CPU? Memory pressure? I'm running the 32 bit variants of MySQL. Would the 64 bit be any different (Solaris 9 is installed with 32 bit and 64 bit support)? Maybe. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 30 days, processed 1,007,094,133 queries (381/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Solaris Performance
Hi, I've got a mysql application that was developed on win32 and linux that is now going to be deployed on a Sun E250 Solaris 9 box with 3 36Gb non raided SCSI disks. No problem I thought - the performance is fine on my PIII 850MHz laptop, so it will rock on an E250... Not the case - the laptop seems to be performing at twice the speed of the E250 for a simple select from a table of 55 million simple rows (just 3 floating point numbers per row - the .MYD file is 700Mb and the .MYI index file is 2.7Gb). It takes 1.2 seconds on the laptop and 2.5 seconds on the sun. This is a big problem, as I have to do three of these queries plus processing and return in under 10 seconds. The laptop takes 6 seconds, the Sun takes 12. I've got a Linux PIII 933MHz Dell Server which at least half as much again faster than the laptop. I've increased the key cache to 250Mb on the Sun (it's at the default on the laptop), but no radical difference was apparent. The Sun install is pretty much out of the box, apart from the file system organisation. MySQL has its own disk. I'm running the 32 bit variants of MySQL. Would the 64 bit be any different (Solaris 9 is installed with 32 bit and 64 bit support)? Has anyone got any similar experience? Can any one recommend trying anything? Any help, comments or suggestions would be very much appreciated. Thanks very much, Andy [mysql query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Solaris Performance
Hi, I've got a mysql application that was developed on win32 and linux that is now going to be deployed on a Sun E250 Solaris 9 box with 3 36Gb non raided SCSI disks. No problem I thought - the performance is fine on my PIII 850MHz laptop, so it will rock on an E250... Not the case - the laptop seems to be performing at twice the speed of the E250 for a simple select from a table of 55 million simple rows (just 3 floating point numbers per row - the .MYD file is 700Mb and the .MYI index file is 2.7Gb). It takes 1.2 seconds on the laptop and 2.5 seconds on the sun. This is a big problem, as I have to do three of these queries plus processing and return in under 10 seconds. The laptop takes 6 seconds, the Sun takes 12. I've got a Linux PIII 933MHz Dell Server which at least half as much again faster than the laptop. I've increased the key cache to 250Mb on the Sun (it's at the default on the laptop), but no radical difference was apparent. The Sun install is pretty much out of the box, apart from the file system organisation. MySQL has its own disk. I'm running the 32 bit variants of MySQL. Would the 64 bit be any different (Solaris 9 is installed with 32 bit and 64 bit support)? Has anyone got any similar experience? Can any one recommend trying anything? Any help, comments or suggestions would be very much appreciated. Thanks very much, Andy [mysql query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Solaris Performance
Simon, Thanks, for the reply. I guess I better try the 64 bit version and see if it makes a difference. I'll report back what I find. Cheers, Andy -Original Message- From: Simon Green [mailto:[EMAIL PROTECTED]] Sent: 13 January 2003 16:58 To: 'Andy Eastham'; [EMAIL PROTECTED] Subject: RE: Solaris Performance Hi Andy Is MySQL loading the tables in to memory? MySQL AB say running MySQL on 64bit system should be faster. Sorry I can not be more help. Simon -Original Message- From: Andy Eastham [mailto:[EMAIL PROTECTED]] Sent: 13 January 2003 15:34 To: [EMAIL PROTECTED] Subject: Solaris Performance Hi, I've got a mysql application that was developed on win32 and linux that is now going to be deployed on a Sun E250 Solaris 9 box with 3 36Gb non raided SCSI disks. No problem I thought - the performance is fine on my PIII 850MHz laptop, so it will rock on an E250... Not the case - the laptop seems to be performing at twice the speed of the E250 for a simple select from a table of 55 million simple rows (just 3 floating point numbers per row - the .MYD file is 700Mb and the .MYI index file is 2.7Gb). It takes 1.2 seconds on the laptop and 2.5 seconds on the sun. This is a big problem, as I have to do three of these queries plus processing and return in under 10 seconds. The laptop takes 6 seconds, the Sun takes 12. I've got a Linux PIII 933MHz Dell Server which at least half as much again faster than the laptop. I've increased the key cache to 250Mb on the Sun (it's at the default on the laptop), but no radical difference was apparent. The Sun install is pretty much out of the box, apart from the file system organisation. MySQL has its own disk. I'm running the 32 bit variants of MySQL. Would the 64 bit be any different (Solaris 9 is installed with 32 bit and 64 bit support)? Has anyone got any similar experience? Can any one recommend trying anything? Any help, comments or suggestions would be very much appreciated. Thanks very much, Andy [mysql query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: converting from foxpro to mysql ???????
I do speak English natively, and I haven't a clue what you're talking about either. Andy mysql query -Original Message- From: Tonu Samuel [mailto:[EMAIL PROTECTED]] Sent: 28 November 2002 09:48 To: toby z Cc: [EMAIL PROTECTED] Subject: Re: converting from foxpro to mysql ??? On Wed, 2002-11-27 at 17:05, toby z wrote: ok guyz i need some inside info . plz help me with: 2. if i ve to conver a db in foxpro to mysql or sql which one should i preffer and y pz do nt use thz hax0r w0rdz It is really hard to read. Many people here do not speak english natively and even just using plain english is complicated. I am not going to read further about your problem. Sorry. Tonu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Application level security
Noel, I'm sorry if this is obvious, but have you considered putting a firewall in the way? If your application is on the same machine as the database, block all connections except to the port your application runs on (ie probably 80 if it's a web application)? The firewall will block connections from any machine to the database, and only local connections can be made to it. If your database is on another machine, protect the database server with the firewall so that only your application server can connect to the mysql server (or configure it so only certain other trusted hosts can too). Andy -Original Message- From: Noel Clarkson [mailto:[EMAIL PROTECTED]] Sent: 28 November 2002 11:10 To: [EMAIL PROTECTED] Subject: Application level security Hi All, I'm writing a application that needs row level security in it so that only certain users can view/change etc. certain records. I know that MySQL currently does not have row level security, and when I've asked this list before I've been given some helpfull suggestions (although none of them would actually work for me). So I've decided that the only way to go is to put the security logic into the application I am building. This works fine for my application, but means that if anyone connects directly to the server using the MySQL client etc, then they'll be able to see/update everything - making it a pretty weak security system. I've had two thoughts about this, but am willing to hear of any other thoughts people might have. One that I could do right now is to get my program to add something to the password of every user (users are created using the program and passwords can be changed using it too). This way if they try to connect to the server directly they won't have the extra bit on their passwords and it won't let them connect. The program could take care of adding this extra bit each time anything password related was needed so it shouldn't be a problem in this respect, however if anyone discovered the extra bit then it would be imposible to change the extra bit without giving everyone new passwords (which would be a real pain). A second way would be if there was like an application password in the security area that could be set and would be needed for connection - sort of like the ssl extra stuff that's been added recently. The downside of this is it isn't currently there and I'd need to convince someone at MySQL that it was worth adding and then wait for it to be added, the upside is that it would be easier to change if the application password was discovered. I've looked a little at the ssl/encryption stuff to see if I could use that but I don't think it can really help me achieve what I'm trying to achieve but if someone thinks it can I'd love to hear how. If anyone has any other ideas, can see problems that I haven't seen in the above ideas then I'd appreciate knowing. Are there any others having this problem (or is it just me!)? Thanks for your thoughts, cheers, noel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql Encryption
Don't forget that SSH (eg OpenSSH) can tunnel regular port connections too. This is dead easy to set up with a client such as SecureCRT from Van Dyke, but this is a paid product (although worth the money in my opinion - I own it). They also do Entunnel which is cheaper product which just does secure tunnelling. But as this tunelling is a fundamental part of SSH, there must be some open source implementation too? Anyone else like to add to this? Andy mysql query -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED]] Sent: 18 November 2002 13:54 To: Alexandre Aguiar; Fraser Stuart Cc: [EMAIL PROTECTED] Subject: RE: Mysql Encryption A windows versionof Stunnel is available from the stunnel website (www.stunnel.org), I would reccomend using it for your needs. Mike Hillyer -Original Message- From: Alexandre Aguiar [mailto:[EMAIL PROTECTED]] Sent: Monday, November 18, 2002 6:09 AM To: Fraser Stuart Cc: [EMAIL PROTECTED] Subject: Re: Mysql Encryption On 14 Nov 2002 Fraser Stuart shaped the electrons to write something about [Mysql Encryption] We are about to embark on a project that requires data encryption - mainly to stop sensitive information being viewed accidentally (ie Isnt it possible to tunnel MySQL connections through ssl? Under Linux stunnel does a great job encrypting protocols that use a single port for connections. I think it would not be hard to port stunnel to other platforms. Under Windows I guess PuTTY (freeware, source available) or one of its related applications (Plink?) (http://www.chiark.greenend.org.uk/~sgtatham/putty/) can handle client side tunneled connections. HTH, Alexandre Aguiar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Newbie: Intersecting entities
John, You're making sense. If you wanted to find all the systems used on project Test Bed Alpa, you'd do something like this: Select sys_name, s.id, project_name FROM systems s, new_req n,ie_sys_req i WHERE s.id = i.sys_id AND n.id = i.proj_name_id AND n.id = 344; Hope this helps, Andy mysql query -Original Message- From: John Coder [mailto:jcoder;insightbb.com] Sent: 07 November 2002 14:59 To: Richard Forgo Cc: [EMAIL PROTECTED] Subject: RE: Newbie: Intersecting entities On Wed, 2002-11-06 at 23:25, Richard Forgo wrote: Sorry for being vague. I think the closest thing I can find to it is, possibly, a three-way join. I'm trying to figure out how to write the query. I've got the following tables: systems sys_nameID Abrams 202 Patriot 544 Stinger 229 ... and ... new_req proj_name ID Test Bed Alpha 344 Tracked Wheels 989 Battle Monitor 823 Shoulder Harness654 Ammo Flask 454 Spotter 773 There may be multiple new requirements for a single system. To accommodate them, I created a new table -- an intersecting entity is what I recall it being called -- to bridge the two. I can't keep the multiple references to new_req in the systems table, nor can I keep them in the new_req table. As best I can figure, I need to have a bridge table ... e.g., ie_sys_req sys_id proj_name_id 202344 202989 202823 544654 544454 544773 If this seems peculiar, have patience with me I'm new at this. If it's right, let me know so I can breath a sigh of relief. And if the structure is right, could someone offer some guidance on how a query that allows me to query new_req using the intersecting entity (or three-way join, whatever it's called) would be written. Thanks, Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 -Original Message- From: John Ragan [mailto:jragan;arkansas.net] Sent: Wednesday, November 06, 2002 6:55 PM To: [EMAIL PROTECTED]; Richard Forgo Subject: Re: Newbie: Intersecting entities if a succinct statement is possible, perhaps you could give us an idea of its nature? somebody may be familiar with the concept under a different name. I'm trying to find some documentation on creating intersecting entities in MySQL, but haven't been able to track any info down on the MySQL site or on the web. At least we called them intersecting entities while I was in Oracle training (which was some time ago). I think I remember how to set them up, but I'd love to have something to refer to quickly before I start. Can anyone point me in the right direction? There is an example and full blown script in a book by Michael Koffler called MYSQL. His Url is www,Kofler.cc If I remember it correctly he had a intersecting entity involving Authors and Publishers in his mylibrary example. John Coder - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: select using regexp
Mark, It looks like you should be using full-text indexes and the match and against functions to me. Check out section 6.8 in the manual. Andy mysql query -Original Message- From: Mark Goodge [mailto:mark;good-stuff.co.uk] Sent: 04 November 2002 11:21 To: [EMAIL PROTECTED] Subject: select using regexp Hi, I've got a problem that I'm hoping someone can help with. I need to do a query against a text column in order to extract entries that will match whole words only - for example, a search for cat should match any of: The cat sat on the mat It was a large cat. Cat food is interesting. Dog. Cat. Fish. but not match in a catatonic state it was a catastrophe scattergun approach It looks as if the MySQL REGEXP function is what I need here, but I can't work out from the documentation how to get what I want. Any suggestions? Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
