PostgreSQL and Firebird overtaking MySQL lead?
http://linux.inet.hr/poll_favorite_database.html I don't think so. Do something! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Searching Fields for a SubString
Hi, I'm a little bit lost on MySQL statements. I am implementing a many:many relationship using a string (wrong way, I know, but I'm trying to keep the design simple and this is a trivial application). In a VARCHAR field, I will store a comma-delimited set of integers corresponding to the indices in another table. It might look like: ,1,2,9, to specify a link to records 1, 2, and 9. I'm not sure how to find, for example, all records where the text of a given field contains ,2,. I'm not interested in an efficient search, just a search. I know it will be O(N) unless indexing of some kind is used. My explanation above might be confusing, so let me give an example that will give the same answer ... Suppose I'm interested in searching a table for all records with last names that contain sh. How would I do that? (Different problem, same answer.) Thanks, Dave.
Re: Searching Fields for a SubString
Dave, you could just use a LIKE statement SELECT * FROM table WHERE mystring LIKE %,2,% Of course if it is at the beginning or end, or the only item, it may look like '2' '2,' or ',2' and not ',2,' so this would work in all cases I think: WHERE (mystring LIKE %,2,% OR mystring LIKE 2,% OR mystring LIKE %,2 OR mystring = 2) Performance will be terrible if this grows much but for a trivial implementation it should work. Dan On 11/4/06, David T. Ashley [EMAIL PROTECTED] wrote: Hi, I'm a little bit lost on MySQL statements. I am implementing a many:many relationship using a string (wrong way, I know, but I'm trying to keep the design simple and this is a trivial application). In a VARCHAR field, I will store a comma-delimited set of integers corresponding to the indices in another table. It might look like: ,1,2,9, to specify a link to records 1, 2, and 9. I'm not sure how to find, for example, all records where the text of a given field contains ,2,. I'm not interested in an efficient search, just a search. I know it will be O(N) unless indexing of some kind is used. My explanation above might be confusing, so let me give an example that will give the same answer ... Suppose I'm interested in searching a table for all records with last names that contain sh. How would I do that? (Different problem, same answer.) Thanks, Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching Fields for a SubString
Thanks for the help. As often happens in these situations, a little searching and experimentation got me a workable answer before any replies arrived. In my case, I was able to use the LOCATE operator, i.e. select idx, lname,schedalonerscs from usrs where LOCATE(,7,, schedalonerscs) != 0; I'm very sensitive to the observations of Peter Bradley. This is the first thing I've done with MySQL, and I didn't know in advance how fast it was or was not. On the page: http://fboprimedevel.e3ft.com the day view scheduler page has to load rather quickly. I was rather cautious to have a simple design for the database. Now that I understand it all a bit better, my next project might use MySQL the right way. Thanks, Dave. On 11/4/06, Dan Buettner [EMAIL PROTECTED] wrote: Dave, you could just use a LIKE statement SELECT * FROM table WHERE mystring LIKE %,2,% Of course if it is at the beginning or end, or the only item, it may look like '2' '2,' or ',2' and not ',2,' so this would work in all cases I think: WHERE (mystring LIKE %,2,% OR mystring LIKE 2,% OR mystring LIKE %,2 OR mystring = 2) Performance will be terrible if this grows much but for a trivial implementation it should work. Dan On 11/4/06, David T. Ashley [EMAIL PROTECTED] wrote: Hi, I'm a little bit lost on MySQL statements. I am implementing a many:many relationship using a string (wrong way, I know, but I'm trying to keep the design simple and this is a trivial application). In a VARCHAR field, I will store a comma-delimited set of integers corresponding to the indices in another table. It might look like: ,1,2,9, to specify a link to records 1, 2, and 9. I'm not sure how to find, for example, all records where the text of a given field contains ,2,. I'm not interested in an efficient search, just a search. I know it will be O(N) unless indexing of some kind is used. My explanation above might be confusing, so let me give an example that will give the same answer ... Suppose I'm interested in searching a table for all records with last names that contain sh. How would I do that? (Different problem, same answer.) Thanks, Dave.
Re: left join , Unknown column 't1.itemid' in 'on clause'
This is makes more sense because it causes a SQL developer to group the joined tables in a logical manner. - Original Message - From: wang shuming [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 3, 2006 8:04:20 PM GMT-0500 US/Eastern Subject: left join , Unknown column 't1.itemid' in 'on clause' Hi, select * from t1,t2 left join t3 on t3.itemid=t1.itemid mysql4.1 works, but mysql5.0 shows Unknown column 't1.itemid' in 'on clause' If change into select * from t2,t1 left join t3 on t3.itemid=t1.itemid mysql5.0 works Shuming　Wang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multilanguage database design issue
Hello, I'm not very experienced with MySql and I'm building a website that deal with user profiles. User profiles involves lists of preferences to choose from (for example, your contry, your profession, etc.), so the user chooses from a drop-down lists. The most natural solution for this is to define the related fields sometimes with an ENUM statement, sometimes with a SET statement. And now, the problem comes... the problem is that my site will be available in several languages, so it will have several versions, for example, www.englishversion.com, www.spanishversion.com, etc. Obviously I will not create a different database for each site with the same data in different languages. I could define many fields in the same table, suppose in my table Profile, I would put fields like country_french_enum, country_english_enum and so on. Each one would contain a list of all countries in french, in english, etc. But I feel it a little cumbersome and difficult to work with. Another solution whould consist to not define enums or sets at all. Instead I could just define tha country field as varchar in the Profile table and create a new table with static data, with the following fields or such: table name: country_names table fields: (country_id, country_name_en, country_name_fr, country_name_es, country_name_it, ...) So each time a form with a country drop-down list is loaded, it would load the country names from country_name table according to the language of the site. And when form is submitted, the choosen value would be validated and inserted in the country varchar field of profile table. This gives me the advantage of take my profile table simpler and faster. But the inconvenient is that now, I have much more field validations to do, I must validate each submitted choice, because a varchar field doesn't limit or delimit allowed values as ENUM or SET do. If I get speed with the database, I actually loses speed with the website or server load. If at one side I simplify things, I complicate them in another. So, I wrote this message to get some help and knowing more how these kind of issues are solved in real-world situations. Internet has lots of multilanguages database driven websites with similar data issues. Surely some standard solution is applied. Do you know any better solution than these ones I described? If not, cound you suggest me which is the best of the two? Thanks, I would appreciate a lot your help. Alphonse - Want to start your own business? Learn how on Yahoo! Small Business.
Re: How many colums should a index contain?
Thanks all. And after these two days I have understood how to create a index to help my query to run more faster,also I found that use 'explain' before a query just get a result only provide reference cause it doesn't show the really result how the query is run,for example the 'key' item. That is my option. 2006/11/4, Andy Eastham [EMAIL PROTECTED]: 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]
