Creating a list from group-by values
This is not specific to MySQL (probably) but I thought this wizened community might have a definitive answer. Take this simple grouped select: SELECT Value1,Value2 FROM Table1 GROUP BY Value1,Value2 Value1 Value2 --- Joe Red Joe Blue Joe Green Jim Red Jim Green Jack Blue My end goal is really this: Value1Value2s -- Joe Red,Blue,Green Joe Red,Green Jack Blue The normal way to do this is iterate through the table using the app's code. Is this possible to do in MySQL? Using MS SQL Server or Oracle, where you can create user defined functions, I can create a function to return the list given the group-by value - but it is atrociously slow, far less efficient than doing it on the client code. Is there no native SQL way to return a list of values from within a group-by value? Seems a nice function like LIST(Value, Separator) would complement MAX(), MIN(), and so on nicely... but it seems not to exist. -- Jamie - 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
UNIQUE index on nullable collumn - can't alter table
MySQL 3.23.47: I want to change a column that is currently non-indexed. SessionID is a nullable int column: alter table tbl_Orders add unique SessionID(SessionID); results in Column 'SessionID' is used with UNIQUE or INDEX but is defined as NOT NULL (A throwback to version 3.22???) I have tried creating new (simple) tables with such indices and they work fine. I've also tried adding an index to an existing test table, in the same scenario as this one. I can't seem to reproduce the error under test conditions, but I simply cannot add this index! In the end, I deleted recreated the entire table with the index I was trying to create defined. It worked. Some vestige of mysql's pre-nullable-index days are still haunting it for the alter, though. -- Jamie - 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[2]: MySQL FullText improvements in V4.0
You can do this yourself, pretty easily by building an index in advance. I've been using the following technique to implement full text searching since before it existed in MySQL in any form. Tokenize each unique word in a document and store the word count in tables: tbl_tokens: token_id int token varchar tbl_doc_index: token_id int doc_id int word_count int Populate the tables from your document database either what documents are saved or in some other offline process. When someone searches on words, first convert to tokens using the first table and look up in the 2nd table using whatever search/join technique works best in your situation. Jamie At Monday, May 14, 2001 on 2:18:38 PM, you wrote: I too am curious! I think one feature that I'd really like to see is the ability to tell the number of times a string appears inside another string. This would help a lot when trying to do search results weighting. -Chris - 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[2]: MySQL vs Microsoft SQL
Gotta say that this is a bad reason to choose one product vs. another... because it lacks a GUI to build queries? Undertanding SQL is all well and good, but I can tell you that I would give anything to have a query builder that works like the one for MS SQL server. You may enjoy writing incredibly long join queries because it demonstrates your understanding of SQL syntax. Personally, I'd rather spend my time watching grass grow. What is the value of doing something in code which is inherently visual, and is a lot easier to do visually? A query builder will not make a database developer out of a novice, but it can make a professional developer's job a lot easier. I can spend my time writing complex code that can't be done with a tool, and not waste it doing repetitive, boring joins that take a few seconds with a visual tool. I am not arguing for MS SQL Server vs. SQL server, I just think having tools is better than not having them. You would always be free to write your MS SQL Server code by hand... Here's looking forward to the future Mascon release that includes a query builder... Jamie At Friday, March 23, 2001 on 6:58:26 PM, you wrote: Not only that, but mySQL "forces" you to learn SQL. Not by wizards that build the SQL statement for you. Like J. Zawodny says, if it meets your needs, then do it. - 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 vs Access; you'd *think* the choice is obvious...
Actually there is no reason to expect MySQL to perform better than Access for a nominally sized database and everything running on one PC. Access was designed and optimized for JET whereas ODBC is a general-purpose API. Also, the connection method impacts this significantly, as do the complexity of the query. For a simple test I did comparing connection methods from Access check http://www.trewtech.com/sqltest.html However, to see Access flounder horribly, simply put your MDB backend at the other end of an ethernet connection from your frontend. Now, for even more pain, add a few more users connecting to it at the same time. Access is _not_ a server and the kiss of death for an access database is to try to use it as a backend over a network. It's amazing how inefficient a query is when the "server" is actually the network filesystem. Add in absurdly long-running bugs that Microsoft denies exist - well, rather, since it's extremely difficult to actually report a bug to Microsoft without paying them for the privilege, perhaps they just don't know - such as randomly corrupting memo fields - and it's essentially useless for anything but a very small database with a very small number of users, or only running on one PC with no network. Jamie Regards, Jamesmailto:[EMAIL PROTECTED] Thursday, February 08, 2001, 5:02:59 PM, you wrote: Q We've got a server app that does a lot of 'small' database reads and Q writes. We were originally using MS Access via DAO (Jet Engine) and we Q wanted to tighten up DB performance, so we've written a general ODBC Q database wrapper object, but mainly just to connect to MySQL. I figured Q there'd be ODBC overhead, but its a lot worse than I imagined. Q I want to know: does it make sense that our original system, connecting via Q 'Jet-engine' to Access, is actually much faster than connecting to MySQL Q via ODBC? This seems to be what's happened. Q I'm wondering if it has to do with the overhead of connecting to a Q server-based database via a tcp socket (even on localhost) rather than the Q direct-to-disk Jet engine; maybe because we do so many small reads/updates Q it's actually faster with Access? Any thoughts? Is it worth my time to Q look into using MySQL directly instead of thru ODBC? Q I'm obviously working on Windows (NT), connecting at ODBC version 2.0 to Q MySQL server 3.23, using a database converted directly from Access to MySQL Q using the cool (but unstable) DBTools GUI, which kindly retained all keys Q and indexes (which have been reviewed for speed). - 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
Log connections but not queries?
I would like to log connections to a file (e.g. the connection info provided using the --log option) but I don't want every query logged as well, or at the very least would like them to go to a different file. Is there any way to do this? Jamie - 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: Your favorite Windows MySQL GUI client?
I've been very happy with mascon (scibit.com). Payware with a limited free version that I think covers all your requirements. I felt it was worth the price for the real thing. Their support is excellent, as is the incredible frequency of releaseing updates with significant new features. Jamie At 01:08 PM 1/15/01, Philip Mak wrote: Hello, What is your favorite Windows MySQL GUI client? I'm developing a database driven website and I'm looking for a program that I can use to maintain the database. One of my special requirements is that I need a client that can edit text blobs. So far the clients I've seen display the text blob as one very long line that is difficult to edit. Criteria: - fairly stable - free - can directly edit the data by clicking on rows - can edit text blobs in a multiline text area Thanks, -Philip Mak ([EMAIL PROTECTED]) - 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
