Re: utf8 options under Mysql
Hi, On 22/04/2016 04:50, Martin Mueller wrote: MySQL has a bewildering variety of unicode collation choices. Most of them are language specific, but what is the difference between "utf8-general-ci", "utf8-unicode-ci", and "utf8-unicode-520-ci." Do they differ in the range of characters they can handle or is it just a matter of the cort order. I understand that utf8-bin is different because it is case sensitive, but the other differences elude me. Under what circumstances does it make a difference to use on or the other? I work with a lot of Early Modern print data and the weird symbols of various kinds they use. I've had trouble at times with the "utf8-general-ci" setting, but it may have been more a matter of settings on my front end tool than of the choice of this rather than unicode collation. Under character sets, there is just one utf8 setting. The simplest way to make sense of the choices would be to say that given a character set (utf8) the collation only makes a difference to the sort but makes no difference to what can be displayed. Is that correct. A collation contains definitions for sorting order and comparison. For most purposes one wants "crème brûlée" to be the same as "creme brulee". For unicode characters these rules can be complex. A character set (in your case UTF-8) defines which character can be stored. utf8-general-ci contains a simplified version of those conversion rules. It works for a lot of Western European languages very well, but in some cases there are problems. For Asian languages there are a lot more problems. For example, 'ß' isn't considered the same as 'ss'. utf8-unicode-ci has more complex rules and works fine for more languages. Due to the more complex rule set it is a bit slower than utf8-general-ci. utf8-unicode-520-ci uses a newer version of the rule set that is used in utf8-unicode-ci. Other utf8-* collations may contain specific rules for specific languages utf8-general-ci is the default collation for utf-8 in MySQL. If you use literal strings MySQL may assume that these have the default collation and comparing them to columns with other collations or performing things like cast operations may produce errors about invalid combinations of collations. -- Met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: To:, CC: mailing lists
Hi, On 17/05/2015 11:37, Emil Oppeln-Bronikowski wrote: Guys, can I implore you to post to a mailing list using its address in To: field and not CC:ing it? You are constantly breaking out of my filters. I've set filters on To: or Cc: contains to catch all the mails. The others in this thread use Gmail which obviously lacks a button Reply to list. -- Met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email [was: Re: table-for-column]
Hi, On 10/12/2014 09:02, Johan De Meersman wrote: - Original Message - From: Jigal van Hemert ji...@xs4all.nl Subject: Re: forum vs email [was: Re: table-for-column] On typo3.org there used to be mailing lists only in a distant past. Later on newsgroups were set up which communicate with the mailing lists (newsgroups are the central source of messages). Rather recently a forum was built on top of the newsgroup data (FUD forum was used). Users on all three message sources can easily communicate with eachother. Hmm. That sounds interesting, I'll have a look. I don't suppose the software is available under a foss license? :-p Mailing lists [1] themselves use Mailman [2] (GPL), forum [3] is done with FUD forum [4] (FOSS GPL2), for integration between mailing lists and newsgroups, please contact the TYPO3 server admin team [5]. I'm sure they will tell you all you want to know about this setup. [1] http://lists.typo3.org/cgi-bin/mailman/listinfo [2] http://www.gnu.org/software/mailman/ [3] http://forum.typo3.org/ [4] http://cvs.prohost.org/index.php [5] http://typo3.org/teams/server-team/ -- Met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email [was: Re: table-for-column]
Hi, On 10/12/2014 10:09, Johan De Meersman wrote: Hm. Typo3 is a CMS; I take it the integration you're speaking of is specific to their support environment, and not part of the CMS? Correct, TYPO3 is a CMS (also FOSS GPL2+) and the integration is indeed not part of the CMS. See my other reply for details on the software that was used. -- Met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email [was: Re: table-for-column]
Hi, On 05/12/2014 20:54, Jan Steinman wrote: From: Johan De Meersman vegiv...@tuxera.be I've long wanted to - but never quite got around to - write a forum that integrated a mailing list. Bar mail clients that don't handle list threads well, it really doesn't seem such a difficult task. There actually seem to be a lot of these around. I'm on several that send me email when there are new forum postings. On typo3.org there used to be mailing lists only in a distant past. Later on newsgroups were set up which communicate with the mailing lists (newsgroups are the central source of messages). Rather recently a forum was built on top of the newsgroup data (FUD forum was used). Users on all three message sources can easily communicate with eachother. Only some mail clients have difficulty keeping the threading headers in tact, but other than that there are no real issues. -- Met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql Access denied error
Hi, On 5-5-2014 10:57, Reindl Harald wrote: Am 05.05.2014 10:19, schrieb Manuel Arostegui: 2014-05-05 10:00 GMT+02:00 Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net: Am 05.05.2014 08:34, schrieb Manuel Arostegui: % doesn't match localhost so if you don't specify it you will be attempting to connect via Unix Socket. If you don't want to specify -hlocalhost all the time, just do the grant with @localhost instead of @% nonsense % matches *any host* Do the test yourself i don't need to test such basics since i am working as mysql administrator the last 11 years and curently responsible for some hundret databases heavily using host specific permissions http://dev.mysql.com/doc/refman/5.5/en/connection-access.html '%' 'fred' fred, connecting from any host In that case you would know that connecting via a Unix socket is not the same as connection via a network. See: http://bugs.mysql.com/bug.php?id=69570 http://dev.mysql.com/doc/refman/5.5/en/connecting.html -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Data masking for mysql
Hi, On 15-4-2014 18:42, Peter Brawley wrote: On 2014-04-15 5:37 AM, reena.kam...@jktech.com wrote: It can be done by data masking tool itself. Its one time activity, I do not need it again again. Rilly? If that's so, the data will never be accessed. I'm starting to think that a concept has been made that includes a database with the original data, a copy with the masked data and then there just needs to be a tool that copies the data and modifies (masks) some fields. Whatever solution we come up with (views, db copy with an update query that modifies the data, ...) it will not be accepted unless it fits the original concept. Most likely the client came up with the concept and then this outsourced development team doesn't dare to suggest that a different concept is probably a better way to reach the goal. But, I may be wrong here :-) -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Data masking for mysql
Hi, On 15-4-2014 11:03, reena.kam...@jktech.com wrote: The main reason for applying masking to a data field is to protect data from external exposure. for example mobile no. is 9878415877, digits can by shuffle(8987148577) or can replace with other letter/number(first 6 digits replace with X-- xx5877) by using data masking. We can use any one data masking technique to protect our sensitive data from external exposure. I need a tool which will mask data in existing mysql db. You could create a VIEW on that table which contains processed columns. Use a special user for the application that has SELECT rights on the view but not on the table. -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Data masking for mysql
Hi, On 15-4-2014 12:36, reena.kam...@jktech.com wrote: Actually data masking is a one time activity, so I need data masking tool. I do not need it again again. So you basically want to replace the data with modified data. You can do that with an update query [1]. There are all kinds of functions available to manipulate the data itself. [1] http://dev.mysql.com/doc/refman/5.5/en/update.html [2] http://dev.mysql.com/doc/refman/5.5/en/functions.html -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Unique on non null entries but allowing multiple nulls
Hi, On 17-10-2011 15:39, Peng Yu wrote: If I use NULL UNIQUE when I create a table, it seems that only one NULL entry is allowed. Since NULL could mean unknown, in this case, two unknowns are not the same and I want to allow multiple nulls but I still want non null entries be unique. Is there a construct in mysql that can create a table column like this? From the documentation: A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. Only for 5.0 there is the exception that the BDB storage engine does not allow multiple NULL values in a column with a UNIQUE index. -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: NULL-safe (in)equality =
Hi, On 1-10-2011 21:51, Halász Sándor wrote: It is, of course, generally considered more natural to make equality primary, not inequality, but that symbol that MySQL uses for NULL-safe equality,=, looks much more like inequality than equality. The whole concept and the name of this operator is wrong IMO. There is nothing NULL-*safe* about it. Equal and unequal operators are in fact more NULL-*safe* than =. But if I write IF A B THEN often I want it NULL-safe, for if one is NULL and the other not, I want that true You are not using NULL as the original concept of it was. NULL means that the value is undefined or unknown. If a value is undefined it may have *any* value. So, if you evaluate (A = NULL) the NULL part can have *any* value, even A. The result of this compare can only be NULL, because it is not known whether it's equal or unequal. Because of this (NULL = NULL) must be NULL too. (NULL NULL) must also result in NULL. The result is just as undefined/unknown as both values which were compared. The usual solution in the case you describe is that you use a normal value in the range of the field type which is not used normally. E.g. for an INT field where you only use values of zero or larger you can use e.g. -1 as a special value. If you insist on using NULL and the crazy = operator you can use NOT to invert it: SELECT NOT(A = B); -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql listed as attach page by google?
Hi, On 26-9-2011 20:30, Michael Albert wrote: I don't suppose I am the first to notice this, but most of the pages on dev.mysql.com have been listed by google as attack pages, e.g http://dev.mysql.com/downloads/. Has there been a problem, or is google being overzealous? I fear Google is right. http://www.net-security.org/malware_news.php?id=1853 -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a lesson in query writing and (maybe) a bug report
Hi, On 28-8-2011 4:08, shawn wilson wrote: On Sat, Aug 27, 2011 at 17:33, Arthur Fullerfuller.art...@gmail.com wrote: I agree 110%. It is completely pointless to index a column with that amount of NULLs. In practical fact I would go further: what is the point of a NULLable column? A NULL 'value' is special in most operations. It indicates that the value is undefined, unknown, uncertain. In this regard it's actually not a value. SELECT 'Uncertain' = TRUE; Result: 0 SELECT 'Uncertain' = FALSE; Result: 1 SELECT 'Uncertain' = NULL; Result: NULL SELECT NULL = TRUE; Result: NULL SELECT NULL = FALSE; Result: NULL SELECT NULL = NULL; Result: NULL (Unfortunately someone decided to add the = operator: SELECT NULL = NULL; Result: 1 Even stranger is that it is documented as NULL safe !?!?) The advantage to me for having NULL 'values' is that it is usually handled as a truly undefined value. (When you compare an undefined value with for example 2, the result cannot be TRUE or FALSE. The undefined value might be equal to 2, or might not be equal to 2. The result can only be undefined.) To deal with NULL results inside expressions COALESCE() is a very useful function. how does null effect an index? i had always assumed that, since there is nothing there, that record wouldn't go into the index hence wouldn't be processed when utilizing the index. MySQL can use NULL in indexes when executing a query. If there are not enough different values in a column (low cardinality) it might be faster to do a full table search instead of first reading the index and then having to go through the table anyway. -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a lesson in query writing and (maybe) a bug report
Hi, On 27-8-2011 1:28, Dave Dyer wrote: Can you post the EXPLAIN EXTENDED output for your before and after queries? also, have you recently run an ANALYZE TABLE on the tables? What was the result of ANALYZE TABLE? What is the engine of the tables involved? // before Used keys: p2.NULL, g.player2, p1.uid In your original post you wrote: The according to explain, the query used gmtdate as an index, an excellent choice. The explain output you posted later indicated that this is not the case (anymore). gmtdate isn't listed as possible index, so what has changed? It seems odd that the query optimizer would choose to scan a 3.5 million entry table instead of a 20,000 entry table. Let's see. Before: 28653 * 41 * 1 rows to consider = 1.1 M rows After: 15292 * 67 * 1 rows to consider = 1.0 M rows Conclusion: the query optimizer didn't choose to scan an entire table. In fact it found a way to have to look at 10% less rows. For the final order by and limit it would be great to have a (partial) index to work with. It's true that planning indexes isn't always an exact science. Generally speaking the goal is to construct both the query and the indexes in a way that you rule out as many rows as possible early on in the process. From your query it becomes evident that you want the latest fifty matches between two players who both have the status is_robot null. Try to create indexes which cover as many of the columns which are involved in the join, where and order parts, and look at the cardinality of those indexes. This will determine how many records can be discarded in each join and keeps the number of records MySQL has to scan as low as possible. Another way is a bit tricky, but can speed up queries a lot: you want the 50 most recent records, so analyse the data and see if you can predict how big your result set will be in a period of time. Let's assume that there are always between 10 and 50 of such records per day. If you want the top 50 it would be safe to limit the search for the last 10 to 20 days. Of course this requires an index which includes gmtdate, but it can make the result set before the limit a lot smaller. -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a lesson in query writing and (maybe) a bug report
Hi, On 27-8-2011 22:52, Dave Dyer wrote: The innocuous change was to add an index for is_robot which is true for 6 out of 20,000 records and null for the rest. Not useful to add an index for that. I also wonder why the value is null (meaning: unknown, not certain) for almost all records. If you want to use such a column in an index it's best to use and index base on multiple columns. This makes it more useful for use in queries. My complaint/question/observation is not how to optimize the query that went awry, but to be alarmed that a venerable and perfectly serviceable query, written years ago and ignored ever since, suddenly brought the system crashing down after making a seemingly innocuous change intended to make a marginal improvement on an unrelated query. Adding an index will most likely trigger some maintenance actions to make sure the table is healthy before adding the index. The query optimizer has an extra index to take into account. I had previously believed that tinkering the schema by adding indexeswas a safe activity. A database should be left alone for a long period. It needs monitoring and maintenance. Changes in the schema and even changes in the data can lead to changes in the behaviour. You can make suggestions for the indexes to be used and you can even force the use of an index if the query optimizer makes the wrong decisions in a case. -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Recognizing utf8 encoded data in latin1 fields/tables
Hi, The TYPO3 CMS I'm working on uses UTF-8 database fields for some time now by default. There are sometimes old installation, which have been updated without properly converting the database. The result: UTF-8 encoded data in (most often) latin1 tables/fields. I have a conversion script which analyses the table definitions and uses the trick of two alter table operations (first to the binary equivalent of the column type and then to the normal type with the utf8 charset) to convert the data to the correct character set. It would be nice to be able to detect this situation using queries only (faster than transferring the data into the PHP script and analysing it there). I have been fiddling a bit with a few columns: test: latin1 (latin1-swedish-ci) contains UTF-8 encoded data test1: latin1 (latin1-swedish-ci) contains latin1 encoded data test: Landrëéüöïß CONVERT(BINARY `test` USING utf8): Landrëéüöïß CONVERT(`test` USING utf8) : Landrëéüöïß test1: Landrëéüöïß CONVERT(BINARY `test1` USING utf8) : Landr CONVERT(`test1` USING utf8) : Landrëéüöïß I'm now looking for an expression which can differentiate between the two situations if possible without having to look for all possible combinations of the encoded data. -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: DBA Mentor?
Hi, On 5-5-2011 0:52, Akachi Pictures wrote: perhaps u misinterpreted. didn't get angry. just moved on :) The reason why you get some surprised messages on the list is because we see the following: May 4th 19:06 - your first message about a mentor May 4th 19:57 - a reply by Claudio explaining where to find information yourself May 4th 21:52 - a reply from you Sorry everyone. Didn't know it'd cause this kind of response. May 4th 21:54 - a reply from you GUYS. I NEVER ASKED TO PAY MONEY OR ASKED FOR TUTORING! GEEZ! We now assume that you received some messages off list from people offering you tutoring for money. Nobody else saw those messages and your replies looked a bit odd because of this. Please keep track of whether a message came from the list or from someone personally... -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql Bug 04/01/11
Hi, On 2-4-2011 2:18, Thomas Dineen wrote: Can't find file: './mysql/host.frm' (errno: 13) http://tinyurl.com/3sc3ydx -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Error 1045
Hi, On 21-9-2010 5:25, Tim Thorburn wrote: Ignore that ... it's amazing how you can solve problems with enough caffeine and enough time away from a computer screen . It's also amazing how frustrating it is for those who are searching for the problem you mentioned to only find threads with 'solutions' such as ignore this, found it myself, never mind, solved, etc. -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Possible tricks to ALTER on huge tables?
Daevid Vincent wrote: We currently have some tables that are approaching 1 BILLION rows (real Billion, with nine zeros, not that silly six zero version). Trying to do an ALTER on them to add a column can sometimes take hours. A few years ago I have tested possible table structures for an application which had to store at least a million profiles of persons. Because we expected that properties would be added (and/or removed) from the database quite often I also tested a structure where the properties of a single profile were stored in tables based on the data type. So we had tables with integers, strings, dates, etc. and used a record for each property; columns were like: id, property name, value, and a few other relevant things to handle and display the data. Most select queries were about as fast as they would be with a single table. Database size was approximately the same because not all profiles used all properties, so we only needed to store the properties a certain profile would use. The only limitation at that time was 31 joins, but I don't think we've ever hit that limit. Adding properties was easy, just adding them to the configuration of the application was enough. It really depends on the situation of your application which table structure is the most suitable. Test the performance of all kinds of operations you need to do with realistic data and various amounts of data to see how it scales. -- Jigal van Hemert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Downloading MySQL
Rhino wrote: I've been away from MySQL for a few years and have not really been keeping close tabs on things, although I have kept my mysql mailing list subscription and am aware of a major player planning to purchase MySQL. Did that actually go ahead? Well... first Sun bought MySQL AB and recently Oracle bought Sun. The reason I ask is that I want to download a free copy of MySQL to use for development purposes and found that I couldn't simply download it any more the way I did several years ago. It gives you a form to complete where you have to supply all kinds of contact information and then assures you that someone will be in touch within 48 hours. What the heck is all that about? Is this some sort of marketing offensive where some sales guy is going to try to push me into purchasing MySQL and a service contract?? Go to www.mysql.com , select the Downloads (GA) tab, click on MySQL Community Server and select the operating system. After you've clicked on the Download button you will be presented with a form to login as a returning user, or register as a new user. Below that form is a link No thanks, just take me to the downloads! Pretty simple to avoid the questions. -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Multiple table engine
Tompkins Neil wrote: Just looking for some confirmation that under a single database - I assume it is perfectly normal to have both MyISAM and InnoDB engines for different tables ? Is there anything I need to be aware of ? In most case no problems. MySQL can mix engines without problems. Every engine uses it's own specific buffers, so if your database becomes big and memory becomes an issue (large buffers needed) it might be handy to use only one engine (so you can set the buffers for the other engine(s) to a very low number). On a daily basis I use databases with mixed MyISAM and InnoDB tables. -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 7-day average
Brian Dunning wrote: My brain just squirted out my ear trying to figure this out, so maybe one of you can help me scoop it back up. Yummy, fresh brain! ;-) I have a table of accounts, and a table of hits-per-day per account. I'm trying to sort my accounts by the most hits-per-day, averaged over the preceding 7 days (8 days ago through yesterday). According to your table definitions: SELECT `account_id`, `company`, AVG(`hits`) AS avg_hits FROM `accounts` JOIN `hits_per_day` USING (`account_id`) WHERE `date` BETWEEN CURRENT_DATE() - INTERVAL 8 DAY AND CURRENT_DATE() - INTERVAL 1 DAY GROUP BY `account_id` ORDER BY avg_hits DESC -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
Martijn Tonies wrote: Martijn Tonies wrote: database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. Why not? Normalizing gets you -more- tables, not less. And normalizing is a goal in itself? I've seen plenty of normalized databases which have become a big mess because of the unnecessarily complex queries you needed to do a relatively simple job. No, it's not a goal in itself, that's not what I said. I didn't say that you said that. You stated that Normalizing gets you -more- tables. It wasn't mentioned why you wanted to normalize the database in the first place. To me your statement looked like it said that normalizing a database would be a requirement for any database. This automatically would produce queries with 61+ joins in them. A lot of the enterprise level features can be useful in certain cases, Normalizing data has nothing to do with enterprise level, it's a matter if keeping your data consistent, being able to create proper constraints at the database, for example. Normalizing has nothing to do with enterprise level, but joining complex views has. Don't ask yourself why you've created the views, just use them in a join. So normalize each database because you may want to create constraints in some situations? This is the behaviour which causes unnecessarily complex databases, queries and applications. If you ask yourself if normalizing a column in a table is useful and if you really need the constraint and if the view, stored procedure, function or whatever you use is really useful, chances are that the application is a lot simpler, faster and easier to maintain. -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
Martijn Tonies wrote: database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. Why not? Normalizing gets you -more- tables, not less. And normalizing is a goal in itself? I've seen plenty of normalized databases which have become a big mess because of the unnecessarily complex queries you needed to do a relatively simple job. A lot of the enterprise level features can be useful in certain cases, but it seems that a lot of times they are just used simply to use them. I cannot find justification for making databases unnecessarily complex, using subqueries when a simple join is all you need, using views, functions, stored procedures in cases that don't require such features, etc. I agree that a lot of people requiring more powerful hard- and software for their application are simply forgetting that they were supposed to produce a working application and not the most normalized database with all the fancy views and other stuff. -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
Rudy Lippan wrote: How about complex data requirements? Depending on the resolution of your data set, I could see a simple person-type object that contained name, address, SSN, mother, and birth_info starting to approach the limit. Cities change, address changes, names change, and even mothers can change. The simple-looking street part of an address can have (at least) number, direction, name, suffix, any of which can change. Okay, so you want to link a person to an address table. I can justify that in the case of multiple addresses with a single person. But then you build a 'city' table to normalize that. Or no, better make a zip code table, link that to the 'city' table. Wait, streets can change names; a 'street' table too to link. Oh no! sometimes streets are split. So an address is a 'property' (a piece of ground), linked to a street, street linked to zip code, zip code linked to city. Damn (sorry), a 'property' can be divided... Oh my... Ever thought about updating a table by renaming a street? Or by selecting a group of street-number combinations and rename them? The real art is trying to balance the need of simplicity and ease of understanding with the need for flexibility, and that has nothing to do with relational theory. In real life the balance tends to go to unnecessary flexibility resulting in systems which are simply too heavy for the actual needs. Complex datasets are, by their nature, complex, and can only be simplified so much. You try to hide the complexity, you shift it, you move-it, you send it to its room, you pretend it is not there. And yet it still pops up at the most inopportune times and has to be dealt with. And still, in a lot of cases the complex datasets are even made more complex by normalization, trying to be ultimately flexible and creating a solution for problems which simply don't exist. In almost all cases a simple solution will be the best. Regards, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help saving MySQL
Claudio Nanni wrote: If he really cared about MySQL he would have not sold it or prevent from selling it to Sun. Initially her was convinced that MySQL as a division of Sun would really benefit the future of MySQL [1]. Obviously his relationship with Sun changed a bit later on. It shows that he really cared about MySQL and in his own way, he still cares for MySQL. This has nothing to do with earning money or selling things. People sell things to companies or other people and think that the new owner will be good for the product they cared about. Sometimes it doesn't work out like you think it would and to me it shows that someone still cares about that product if they try to do something about it. I am not talking about agreeing with mr. Widenius or not; that is a different discussion. [1] http://www.internetnews.com/dev-news/article.php/3760831/MySQL+Back+to+Its+Roots+via+Sun.htm Regard, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help saving MySQL
Claudio Nanni wrote: Due to selling MySQL to Sun, Widenius earned about 16.6 million € in (...) I fail to see the relevance of this quote for this thread... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MBRWithin bug?
Chris, I might be being an idiot. Yes, you are :-) -122.1529 is between -121.148 and -121.1575 This is not true! -122.something cannot be between -121.somethingother and -121.somethingelse -121.1529 is between -121.148 and -121.1575 Regards, -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join question
Dear all, I have two tables,let's call then a and b: Table a: CUI1|CUI2 C001|C002 C002|C003 C003|C055 C004|C002 ... Table b: CUI|STY C001|T001 C002|T002 C003|T003 C004|T004 C005|T006 C055|T061 .. And the join table should be: T001|T002 T002|T003 T003|T061 T004|T002 ... I assume that the third table should be the result. If you need to store the result in a table you can use a INSERT ... SELECT query instead of only a SELECT (look INSERT...SELECT up in the online manual). So,I should convert table a according to table b. Thank you in advance for all your help My approach would be the opposite: SELECT t1.`STY`, t3.`STY` FROM `table_b` AS t1 JOIN `table_a` AS t2 ON t1.`CUI` = t2.`CUI1` JOIN `table_b` AS t3 ON t2.`CUI2`=t3.`CUI`; table_a determines which records from table_b must be connected. -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary table issue
Xiaobo Chen wrote:
Hi, all
I am trying to use this with error:
drop temporary tabel temp_a if exists 'temp_a';
it said syntax error.
Try:
DROP TEMPORARY TABLE IF EXISTS `temp_a`;
('table' instead of 'tabel'; table name only once; backticks around
table name instead of quotes)
http://dev.mysql.com/doc/refman/5.0/en/drop-table.html
Regards, Jigal.
smime.p7s
Description: S/MIME Cryptographic Signature
Re: temporary table issue
Ben Clewett wrote: But the index does not seem nearly as fast as liner (normal) indexes. Hence using a less effective liner index may be better... After this method I still need select a radius within these data points, more effort. This could be an excellent case for using HAVING expr The WHERE can use an index and be very fast selecting the records that might fall within the circle. The HAVING clause will be executed after the record set is built and remove the records outside the circle, but within the square. If the number of records you select is pretty limited (a few hundred or so) the POW() calculations will not ask too much from the server, but the full table scan is avoided... Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
Re: How to update record obtained from a query result?
Jan M schreef: How do I update a record obtained from a query result while ensuring that: 1) The record is the actual record in the database not a possible duplicate, e.g. is there a built-in record number identifying the actual DB record or do I have to organise that in the table structure. There is no built-in record number, but many use AUTO-INCREMENT fields as a unique record ID. In many queries you will retrieve data from more than one table. The result set may thus consist of combined and possibly generated data from more than one table and so you not have the actual record as a single entity. 2) The record hasnt been or cannot be changed by another thread/process, possibly by. locking / re-reading the record (is there another way?). InnoDB (an engine type, see online manual) provides a mechanism called transactions which allow you to lock the rows involved in your query (so others cannot change the data), update them, etc. After you complete your transaction the changes are committed to the database. In case of an problem you can roll back the transaction. Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
Re: Problems with indexes on Date/DateTime fields
Javier Diaz wrote: I would like to know if there is any problem which cause Mysql to not use date indexes at least you use the = operator, because if that is the case we will need to re-visit a few queries If you do a select instead of a delete, will the index be used? (You can check this by using EXPLAIN SELECT) If the index is used in that case MySQL must have a reason for not using the index for deleting a range. With MyISAM tables deleting a single date involves a single leave in the index tree, deleting multiple dates requires MySQL to merge index leaves during the delete. You could use DELETE QUICK to suppress the merging of index blocks, but you need to do an OPTIMIZE later on to reclaim the unused index space. The query optimizer might decide that using the index in this case is slower than a full table scan. If the index is not used with the select it might be because the index makes the optimizer think that more than approx. 30% of the records will be involved. In such a case it is usually faster to directly access the data than to use an index (which would require access to the index plus index to the data). It will be more likely for you to get relevant answers from this list if you supply the definition of the table(s), indexes, engine, etc. (a CREATE TABLE statement is very good for this purpose); the exact query which you use (and nog give a SELECT and later on introduce the fact that it was actually a DELETE query ;-) ); in case of a select the output from EXPLAIN SELECT... With this information the gurus here (and that does not include me :-) ) can more easily judge the situation and tell you what might be the cause of your problem. Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
Re: Problems with indexes on Date/DateTime fields
Javier Diaz wrote: EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) possible_keysdate_idx,date_proc_idx/possible_keys key(NULL)/key rows10778561/rows EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) possible_keysdate_idx,date_proc_idx/possible_keys keydate_idx/key rows1863456/rows MySQL always tries to find the index that will result in the smallest number of rows to evaluate for the final result. These queries show that you have ca. 11 million rows in the table of which ca. 2 million rows are two days old. My guess is that at most 4 million rows are less than two days old, so at least 7 million rows are 2 days old or older. 7 million is far more than 30% of 11 million, so the select for = 2 days old will use a full table, which is likely to be faster in this case. In case of InnoDB the primary key is stored with the data (clustered indexes), so if date_idx were the primary index it would probably have used it, but first getting 65% of a secundary index and then looking it up in the primary index does not make sense. EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) and process=1 possible_keysdate_idx,process,date_proc_idx/possible_keys keydate_proc_idx/key rows550726/rows The half a million or so records for this process that are exactly two days old can be retrieved efficiently by using the combined index date_proc_idx. EXPLAIN SELECT * FROM process_times WHERE date = date_sub(now(), INTERVAL 2 day) and process=1 possible_keysdate_idx,process,date_proc_idx/possible_keys keyprocess/key rows1830334/rows The combined index cannot be used because you cannot make a range condition for this case: (-00-00 00:00:00, 1) (date, process) (2005-01-02 00:00:00, 1) This would include for example (2005-01-02 00:00:00 , 0) which does not meet the condition set in the query. [See: http://dev.mysql.com/doc/refman/5.0/en/range-access-multi-part.html ] As we've already seen earlier the date_idx would result in far more than 30% of the rows, so only the process index can be used (ca. 17% of the rows). I would like also ask if is valid to expect that the results of the Explain statement for a query like this SELECT * FROM TABLE-X WHERE [CONDITIONS] Are valid for the equivalent DELETE FROM TABLE-X WHERE [CONDITIONS] Not necessarily. In many cases it will be about the same, but I suspect that technical conditions such as updating indexes, etc. might influence the execution path that MySQL choses. You might investigate other methods such as moving the remaining records to a new table, dropping the old table and renaming the new table. This is just a quick idea, I haven't looked up how well InnoDB performs these operations and how it influences the other users of your data. Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
Re: Converting table to InnoDB
Jonathan Chong wrote: I have a table on my forum that's frequently accessing with reads and writes. On a busy day, the table is locked when people are posting messages and the page hangs when you try to read a topic. Is it worth me converting the table to InnoDB, as I've heard that InnoDB doesn't lock tables during I/O access. InnoDB has row-level locking (i.e. it basically only locks the rows that might be changed by the query). InnoDB is generally speaking a good choice in 'high concurrency' situations (many reads and writes at the same time); for small read-only or write-only tables InnoDB is usually slower than MyISAM. In your situation it might solve the locking problem. Do read the online manual carefully since there are a lot of differences between MyISAM and InnoDB (the way data is stored, the way indexes are stored, some differences in handling statements and functions,etc.) Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
Re: basic questions: setting a variable and using a column name
Joseph Alotta wrote: greetings, I know this is pretty basic stuff, but I couldn't find an answer in google. 1. I want to set the @maxdate variable, but mysql complains. What am I doing wrong? set @maxdate = select max(date) from positions where account = 111200512343222; select (@maxdate := max(date)) from positions where account = 111200512343222; 2. The 3rd to the last line: and (p.value - t.amount) -1000 I wanted to say diff -1000 but mysql also complained. http://dev.mysql.com/doc/refman/4.1/en/select.html says: It is not allowable to use a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. It is explained further at http://dev.mysql.com/doc/refman/4.1/en/problems-with-alias.html Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
Re: Problema with left join
[EMAIL PROTECTED] wrote: #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 '.manufacturers_id = m.manufacturers_id ) LEFT JOIN specials s USING ( p.produc' at line 4 What's the error in the query?!!? USING ( list_of_column_names ) versus ON expression AFAIK it would be: USING (products_id) or ON p.products_id = s.products_id etc. Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
Re: Problema with left join
[EMAIL PROTECTED] wrote: Hi, I resolve my problem... select count(p.products_id) as total from products_description pd, products p left join manufacturers m using (manufacturers_id), products_to_categories p2c left join specials s using (products_id) where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '4' and p2c.categories_id = '16' Read: http://dev.mysql.com/doc/refman/5.0/en/join.html especially the part starting with: Before MySQL 5.0.12, the comma operator (,) and JOIN both had the same precedence (...). It may be wiser to replace the comma-operator by an explicit JOIN to prevent any problems with precedence, using aliases before they are declared, etc. This part of the manual explains the changes made in MySQL regarding the handling of joins in MySQL 5. Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
Re: Mysql docs
Scott Haneda wrote: Hello, with the release of 5, I seem to have all dead links to my old docs for mysql 4. The way they keep changing the urls is a little maddedning :-) I usually search using: http://www.mysql.com/keyword http://www.mysql.com/subquery will lead to: http://dev.mysql.com/doc/refman/5.0/en/subqueries.html This will lead to the 5.0 documentation, but changing the '5.0' in the url to '4.1' will give you the 4.1 documentation. http://dev.mysql.com/doc/refman/4.1/en/subqueries.html Quite easy I think... Kind regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
Re: PK or simple key?
Eamon Daly wrote: I don't know why I have so much trouble visualizing indexes, but such is life. Imagine a simple table with only two columns: user_id and foo_id. I will only ever be looking up records in this table by user_id, but I will always retrieve both fields. Figure a million rows and maybe 5 or so rows per user_id. Should the table be defined with a PK across all columns: or should the table have no PK and a key on user_id: Eamon, With only two integer fields and MyISAM it might be a solution to use a PK on both fields. In that case MySQL only needs to access the index to retrieve the data also. The actual table data will not be used, but as long as the entire index fits in memory it will be super fast. Kind regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
Re: Sum entire group listing
Noel Stratton wrote: more thing that I can not figure out. I would like to sum all calculations created out of the Total Amount Owed field that was created. Noel, Take a look at the WITH ROLLUP modifier of GROUP BY [1]. This is available since version 4.1.1. Regards, Jigal. [1] http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html smime.p7s Description: S/MIME Cryptographic Signature
Re: chronological auto_increment problem
InterNetX - Andreas Prasch wrote: Hi, I have a master and a slave mysql server. On the master I write binlogs needed for replication. From time to time I have chronological auto_increment problems, here's a short explanation. - the table structure : | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | name | varchar(20) | | | || ... - when I update an the id field in this table (update tablename set id=) and make an insert (insert into tablename set name=...) at the same time, it happens that first the update get performed and than the insert but with the old auto_increment values, here' a snipset from the binlog: --- # at 84043032 #051109 9:38:00 server id 4 log_pos 84043032 Query thread_id=27565652 exec_time=0 error_code=0 SET TIMESTAMP=1131525480; UPDATE tablename SET `id`=15183855 ... WHERE `id`=15159410; # at 84044721 #051109 9:38:00 server id 4 log_pos 84044026 Intvar SET INSERT_ID=15183827; # at 84044749 #051109 9:38:00 server id 4 log_pos 84044026 Query thread_id=27567781 exec_time=0 error_code=0 SET TIMESTAMP=1131525480; insert into tablename set name = ... --- So that the auto_increment value in this example after the queries 15183828, but it should be 15183856. Have anybody the same problems, it this a bug and is there a solution for this problem ? It's not a bug; MySQL keeps track elsewhere what the next AUTO_INCREMENT value should be. You can set it using SET INSERT_ID = value; [1] Things get a bit more complicated with InnoDB tables [2]. Regards, Jigal. [1] http://dev.mysql.com/doc/refman/5.0/en/set-option.html [2] http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-column.html smime.p7s Description: S/MIME Cryptographic Signature
Re: Fulltext boolean search and the asterix
Paul DuBois wrote: At 10:49 +0100 11/8/05, Jigal van Hemert wrote: Lindsey wrote: but do you know how to use the * in regexp searches. err what i mean if i want to search for * and not use it as asterix? To use a literal instance of a special character in a regular expression, precede it by two backslash (\) characters. The MySQL parser interprets one of the backslashes, and the regular expression library interprets the other. In this case, * is to be used as a special character, you don't want to match it literally. Paul, Lindsey wrote: i want to search for * and use it as asterix. I interpreted this as wanting to match the '*' literally... Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
Re: Fulltext boolean search and the asterix
Lindsey wrote:
Lets say the table contains the following brands
SAMSUNG
SIEMENS
SONY
If you do a fulltext boolean search with the term:
-S*Y
-(S*Y)
everyting that starts with an S will be excluded... any solutions?
Although I couldn't find a question in your post, I guess you want to
know how to find anything that does not (start with 's' and ends with 'y').
Unfortunately the asterisk ('*') will not help, because the manual [1]
states:
The asterisk serves as the truncation operator. Unlike the other
operators, it should be *appended* to the word to be affected.
I don't think that full-text indexes were meant for this kind of
queries; although it will not use an index at all (so it might be a slow
query), using LIKE 'S%Y' or using a regular expression might be a
possible solution.
[1] http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html
Regards, Jigal.
smime.p7s
Description: S/MIME Cryptographic Signature
Re: Fulltext boolean search and the asterix
Lindsey wrote: ok thanks, then i know! but do you know how to use the * in regexp searches. err what i mean if i want to search for * and not use it as asterix? i have tried \* but that did't work, it just does the same as *. The manual comes again to the rescue ;-) Appendix G [1] tells us: To use a literal instance of a special character in a regular expression, precede it by two backslash (\) characters. The MySQL parser interprets one of the backslashes, and the regular expression library interprets the other. So: SELECT 'GRANDS*N' REGEXP 'S\\*N'; - 1 [1] http://dev.mysql.com/doc/refman/5.0/en/regexp.html Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
Re: PHP/MySQL vs ASP/MSSQL?
Marc Pidoux wrote: I've been using ASP for years but I have to work on a bigger project involving many users and data so of course, Access is not an option anymore. Since it's a project requiring thousand of files and several applications all linked together, I can't create it once and change it later. Basically, which option is the best between PHP/MySQL and ASP/MSSQL? It's not a giant project but it might include around 20'000 members interacting daily through forums, blogs, messages etc... It's not an easy question to answer, Marc. Win/IIS/ASP/MSSQL might need a bit more hardware to run on compared to Linux/Apache/MySQL/PHP (aka LAMP). The license costs for a LAMP setup will probably be higher. Both will be able to do the job. What you have to include in your decision is what the company you work for is used to have. It's not easy and probably expensive too if they have to support another OS/webserver/scripting languag/database than they're used to. Another point of consideration is what you are used to. You mention years of experience in using ASP. If that means you have to learn PHP/MySQL from scratch then that will be very expensive also (counting the extra hours you have to spend to learn all the ins and outs of both PHP and MySQL). Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick split() and data parse
Scott Haneda wrote: I have a field `fedex`, in it is text date, in tab sep \n delimeted form, for example: 90FedEx Home Delivery (Residential) 14.06 20FedEx Express Saver 22.63 (...) Is there some way I can do some string parsing on the text field and get to the 3rd item where the passed in shippign_method equals? You can do something with a combination of the functions SUBSTRING(), LOCATE() and SUBSTRING_INDEX(), but it would of course be a lot wiser extract the information from this field and put it in a table of some sort. That way it would be a lot faster to find the data (no need to parse every single record) and you wouldn't need to write complex expressions to find the data you want... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Will SELECT COUNT(*) FROM TABLE ever be cheap on INNODB?
[EMAIL PROTECTED] wrote: Jigal van Hemert [EMAIL PROTECTED] wrote on 11/02/2005 03:29:14 AM: If I understand http://dev.mysql.com/doc/refman/5.0/en/table-and-index.html correctly, the index of the primary key is stored as the clustered index together with the data. To me this means that there is no difference between counting the primary key entries and counting the data entries. That would be true if everyone could always see every record. However there could be several active copies (versions) of any record. Each copy would be part of a different transaction. How many records YOU can see can be very different than how many records I can see depending on what's going on in our respective transactions. Sorry for the late reply, I had a day off. I was talking about the difference between COUNT(*) and COUNT(primary key field); I don't think that there is a difference in speed if you take snapshots -- as InnoDB calls them -- into account. If the primary key and the record data are stored together both counts should be equally fast, no matter how many versions of a record exist. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP/MySQL vs ASP/MSSQL?
Hi Joerg (and other list readers), Joerg Bruehe wrote: Jigal van Hemert wrote: Win/IIS/ASP/MSSQL might need a bit more hardware to run on compared to Linux/Apache/MySQL/PHP (aka LAMP). The license costs for a LAMP setup will probably be higher. Both will be able to do the job. ^^ No flame war intended, but: Is this a typo, or on which assumptions / figures do you base that? Oooops, I meant to wright lower. Thanks for correcting this. AFAIK, the whole LAMP setup is free-of-charge GPL software, unless the environment requires a commercial MySQL license. (Support agreements would be a different issue.) I agree, and even with a license and a support agreement it will probably be a lot cheaper than a comparable MS based setup (unless I have to believe http://www.microsoft.com/windowsserversystem/facts/topics/tco.mspx which likes me to believe that an MS-based setup is actually cheaper in the end). Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Will SELECT COUNT(*) FROM TABLE ever be cheap on INNODB?
Shankar Unni wrote: [EMAIL PROTECTED] wrote: If I understand the InnoDB engine correctly, I don't see how they could speed it up unless they start tracking how many records belong to each active version within a database. But one thing you can do to speed it up somewhat is to do a COUNT(PK_column) (rather than COUNT(*)) if you have a column that is a primary key for the table - that's the same thing semantically, and involves searching an index rather than the data records themselves, which should involve less I/O. If I understand http://dev.mysql.com/doc/refman/5.0/en/table-and-index.html correctly, the index of the primary key is stored as the clustered index together with the data. To me this means that there is no difference between counting the primary key entries and counting the data entries. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confusion over multiple table joins
Barry wrote: Can someone plaese help? I have three tables 1st is a collection of propertys, 2nd is a list of facilities(98 in total) and the third is a list of property id's corresponding to the facilities id's offered at each property, The query I am running: 'SELECT' '`'.$type.'`.`id`,' '`'.$type.'`.`name`,' '`'.$type.'`.`addr_1`,' '`'.$type.'`.`addr_2`,' '`'.$type.'`.`addr_3`,' '`'.$type.'`.`addr_4`,' '`'.$type.'`.`post_code`,' '`'.$type.'`.`short_desc`,' '`'.$type.'`.`phone_1`,' '`'.$type.'`.`email`,' '`'.$type.'`.`website`,' '`'.$type.'`.`cost_single`,' '`'.$type.'`.`cost_double`,' '`'.$link.'`.`f_id`, `facilities`.`path`' 'FROM `'.$type.'`' 'inner JOIN `'.$link.'` ON `'.$type.'`.`id` = `'.$link.'`.`id`' 'inner JOIN `facilities` ON `'.$link.'`.`f_id` = `facilities`.`f_id`' 'where `name` like '. $name.'' works after a fashion, except that for each facility I get a duplicate property entry in my results row, (if there are ten facilities I get ten listing for the same property). Which is exactly what you asked for in the query! What I am trying to achieve is one result row for each property along with a list of facilites offered. You can either solve this in the application that displays the result of the query (if the property of the row is the same as that of the previous row, just add the facility to a temporary list and else display the temporary list of facilities), or you can use the GROUP_CONCAT() function along with a GROUP BY (GROUP_CONCAT() was added in MySQL 4.1). http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html E.g. SELECT . , GROUP_CONCAT(`facilities`.`path`) FROM ... WHERE GROUP BY `type`.`id`; Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: run mysqld-nt.exe in background
[EMAIL PROTECTED] wrote: wang shuming [EMAIL PROTECTED] wrote on 10/31/2005 01:11:06 AM: How could run mysqld-nt.exe in background on win2000/XP, not as a would be useful in HIS operating system. AFAIK, there has never, ever, been a Windows (or DOS) command or command modifier to launch an executable in the background and I have been a Windows(DOS) user since the mid 80's. I believe I would have probably run across it a long time ago if it existed. Shawn, Back in the eighties we had several little utility programs that ran in the background. Does the name TSR (Terminate and Stay Resident) (http://en.wikipedia.org/wiki/Terminate_and_Stay_Resident) ring a bell? The ability to TSR was present from MS-DOS 2.0 until Windows ME, so there is no way to do this in Win2k/XP. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY Destroys 2nd Function
David Blomstrom wrote: I have a PHP script that displays data like this: Eurasia Eurasiasupisland/sup Africa Where Eurasia and Africa are mainland parents of ecological regions and Eurasiasupisland/sup is a parent of an ecological system that is associated with a continent. For example, Borneo would be Eurasiasupisland/sup. You've lost me here ;-) I know quite a bit about animals, but ecological regions and their parents are not my cup of tea. ANIMALS TABLE Canis_lupus | wolf Panthera_tigris | tiger JOIN TABLE SPECIES | ECOREGION Canis_lupus | NA1008 Canis_lupus | NA1010 ECOREGIONS TABLE ID | NAME | Geog | Geog2 NA1008 | Alaska tundra | na | na IM1003 | Philippine rainforest | eur | phl (Note that mainland ecoregions feature the continental ID in each of the last two columns, while island ecoregions feature the island's ID in the last column.) GEOGRAPHY TABLE ID | NAME na | North America phl | Philippines I miss quite a bit of data here and there are no queries mentioned. Where can I find Eurasia and Africa here? Maybe you can start by rewriting the problem is pseudo queries: I want a list of the NAMEs from the GEOGRAPHY table for a certain species from the JOIN table for which the ecoregions and the geog are listed in the ecoregions table. Each NAME should only appear once. Or something like that. With such a natural language pseudo query you're often more than half way towards building a real query. At least you're far enough for others to help you build the actual SQL. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: search machine problems
Aleksandra wrote: I've written a search machine. It's working quite well, but I have two problems: 1. When I give the following syntax : cat and dog , as a result I get everything with at least one of the words: 'cat', 'dog' but also 'and'. What can I do, so that it's not looking for the word 'and' or some other similar words like 'or', etc.. 2. I would like the search machine to look for the results that consist both: 'cat' and 'dog' and not only one of this words. I assume that you use Full-Text searches (but somehow you have managed to change the default minimum word length to three). Maybe you can find the functionality you want in Boolean Full-Text searches (http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html). Changing 'cat and dog' to '+cat +dog' is a matter of parsing the input in your application. You have to be beware of expressions such as 'cat dog and man +bird' where your noise words are suddenly relevant when inside double quotes. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Record exists but not found - grrr
Wenca wrote: SELECT * FROM tab_p WHERE d_id = 20602 AND name LIKE 'Machico' - no results Try it with ...AND `name` LIKE '%Machico%'; I have encountered it once when there were non printable characters in front of or after the text itself. Very frustrating! You can see what extra characters are there when you query SELECT *, HEX(`name`) FROM... This will add a column to the output with the hexadecimal representation of each character in the name. Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'Autoextend' datafile - Mysql-Max-4.1.14 - Linux
Tom Brown wrote: hmm i'm not entirely sure i fancy adding another data file on the fly - Can you give me a brief run down about how you would go about this? My collegue the sysadmin usually takes care of keeping the databases happy. But AFAIK it's a matter of modifying the setting in the .cnf file and restarting MySQL. I wouldn't let a process do the editing in the .cnf though, but you may trigger alerts automagically. another option would be for me to create the 100gig as 2 25 gig files from the off - Do you know how well these would compress? Do you know what they are 'filled' with so when it comes to backup they will compress down to virtually nothing? I wouldn't use the InnoDB datafiles in a backup. They are use server wide for storing InnoDB data. You can use a MySQL dump or the InnoDB hot backup tool to backup the data easily. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Peer to Peer Replication?
Eldon Ziegler wrote: We have a geographically dispersed system with a database of student status information that needs to be replicated in as near to real time as we can get. The MySQL master-slave method of replication doesn't seem applicable as students can login to any server and pickup where they were before. How can peer to peer replication be done? Maybe circular replication is something for you? See a recent (september) thread: http://lists.mysql.com/mysql/189179 Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Circular Replication
Stefan Kuhn wrote: Am Thursday 27 October 2005 12:56 schrieb Raphaël 'SurcouF' Bordet: Le vendredi 16 septembre 2005 à 18:14 +0200, Stefan Kuhn a écrit : I'm using it with four machines (geographically separate) and it works fine. Stefan And can writes on each server in simultaneous time ? I don't understand the question. Replication is pretty fast, but is not guaranteed to happen in a certain time (not real-time). Stefan, I think Raphaël wants to know if a user can use any server in the circle to update or insert records and that the changes will be replicated to all other servers. In another thread he stated that it was for maintaining student information on various remote locations (a student can login into the system on any of the locations). What happens if a record is updated on two servers and the changes are forwarded to the other servers in the circle? I dont' think that this would occur often with student information, but both the student and the administration department might update a record simultaniously (in comparison to the speed of replication with several remote locations). Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: search machine problems
Aleksandra wrote: Jigal van Hemert wrote: I assume that you use Full-Text searches (but somehow you have managed to change the default minimum word length to three). As far as I understand what you've written - if I change the minimum word length to 4, I won't get any result with the word 'and', No, I meant that the default minimum word length for full-text indexes is 4. If you can use 'cat and dog' as an argument for non-boolean searches and get relevant results, you must somehow have changed that setting and rebuilt the indexes. That made me wonder whether you used full-text indexes or not... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'Autoextend' datafile - Mysql-Max-4.1.14 - Linux
From: Tom Brown [EMAIL PROTECTED] (...) starting at say 1GB and then 'extending' to 25GB. So is it possible for all datafiles, rather than just the last one, be 'autoextending' The short answer: no. http://dev.mysql.com/doc/refman/4.1/en/innodb-configuration.html states: The autoextend attribute and those following can be used only for the last data file in the innodb_data_file_path line. autoextend is available starting from MySQL 3.23.50 and 4.0.2. You could use two methods to manage the increasing need for space in your situation: 1) Use fixed sizes and keep track of the free space. Use SHOW TABLE STATUS LIKE 'tablename'; and use the `Comment` column which shows you something like InnoDB free: 5278720 kB. If this number gets too low, modify your config file and add an extra datafile. 2) Use an autoextend file and make it fixed size if it gets too large. http://dev.mysql.com/doc/refman/4.1/en/adding-and-removing.html tells you that you can change an autoextend datafile to a fixed size file by getting the size, rounding it to the closest multiple of 1024 x 1024 bytes (1MB), changing the settings in the configuration file in such a way that the autoextend file is now fixed to the size you calculated and that you added a new autoextend file. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting more than one property (req help)
mem bob wrote: | id | model | service_id | ||---|| | 1 | 500 | 1 | | 2 | 500 | 3 | | 3 | 500 | 10 | | 4 | 600 | 1 | From this table i want to extract all distinct models which have service_id=1 *AND* service_id=3 The JOIN-construction is something like: SELECT `model` FROM `i` AS t1 JOIN `i` AS t2 ON t1.`model` = t2.`model` AND t1.`id` != t2.`id` WHERE t1.`service_id` = 1 AND t2.`service_id` = 3 GROUP BY t1.`model`; Experiment to see whether it makes a difference of not if you move the expressions in the WHERE clause to the ON clause of the JOIN. It seems to depend on the version of MySQL that you are using how good the optimizer is at finding out the fastest execution path. Make sure that you have an index on at least `model` and perhaps a combined index on `model`, `id` and/or `service_id`. Experiment with indexes and see which combination is the fastest. Look at the output from EXPLAIN query to see the type of table joins MySQL will use and how many records it estimates it needs to evaluate in each step. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joined My Head to My Ass
G G wrote: I have two tables: Records: member_id - int question_id - int Questions: id - int I'm looking to select a random id from the questions table, given that there is no record of the user already answering that question. SELECT * FROM `Questions` AS t1 LEFT JOIN `Records` AS t2 ON t1.`id` = t2.`question_id` AND t2.`member_id`='memberId' WHERE t2.`question_id` IS NULL ORDER BY RAND() LIMIT 1; The selection of the member_id can also be put in the WHERE clause; this might be a point of debate. To me it's something that limits the records considered for the join instead of something that limits the records in the resulting recordset. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why i can't connect to the mysql server from a client pc?
zhou bin wrote: hi, mysql is a very goode software,so i made mysql_3.23_58 and mysql_4.1 in two of my server, and i changed the root password, but why i can't connect to the mysql server from a client pc(use mysqlcc or any other tools)? the error message is: [206] ERROR 1130: Host '218.4.**.***' is not allowed to connect to this MySQL server Probably user 'root' is only allowed to access MySQL from 'localhost' (which it should be for security reasons). You can learn more about the privilege system and how to grand users access from various locations to various parts of the database at: http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html http://dev.mysql.com/doc/refman/5.0/en/user-account-management.html http://dev.mysql.com/doc/refman/5.0/en/account-management-sql.html etc. Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Input on Materialized Views
Andrew Roth wrote: Hi all, We are a group of three students in Professor Ric. Holt's Software Architecture class at the University of Waterloo. As our project, we would like to examine the MySQL source to determine the best way to implement materlialized views. It would be wise to hang around and see if someone from MySQL AB has time to answer the questions, but here's my personal view... I had to look up materialized views and if I read it correctly what is said about this at http://www.akadia.com/services/ora_materialized_views.html: 1. How feasible would implementing materialized views be? It would be quite an adventure, but they might be useful in certain situations (low concurrency, but need for more speed or higher concurrency and less need for accuracy). 2. Any reasons why materialized views haven't been added already? Since 'views' are only introduced in MySQL 5.0 I guess that: - there has not been time yet to think about materialized views - no programmer has had the time to build it - no-one has sponsored to hire extra programmers to build it 4. Any comments at all relating to this endeavor. Good luck? ;-) I think that you have to make changes in a lot of areas. At least you will have to think of: - storage engine(s) (updating materialized views on commit) - query optimizer (trying to rewrite the query to use MVs instead) - adding handling of the new keywords - sceduling of refreshes during off-peak time Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
Martijn Tonies wrote: | Warning | 1264 | Out of range value adjusted for column 'a' at row 1 Could be me ... but isn't this a little too late? eg: AFTER you have lost your data? IMO, it should raise an error UNLESS you force it to truncate the data. This would contradict the MySQL design philosophy (others call it simply gotcha) that the user should know what he's doing and the DBMS tries its best to obey. Consider this (version 4.1.14): Yeah yeah ... so the MySQL design philosophy is that users never make mistakes... Even in more serious cases MySQL silently modifies data and structures: A large database with an INTEGER column with NULL-'values' allowed was modified to include this field in the PRIMARY key. The column definition was automagically modified to NOT NULL and all NULL-'values' where converted to 0 (zero). Yeah, emmm, well, we actually used the NULLs as no value (like it is supposed to be used AFAIK) and there was no way anymore to distinguish between NULL and 0. Luckily this was done on a test database and we only had to spend half an hour or so to restore the table from a backup. It would have been very nice to know of this action before it was completed, to say the least. If a value doesn't fit (in the domain of tinyint), an exception should be raised. Plain and simple. I fully agree. Maybe an option SQL_IGNORE_WARNINGS or something along those lines should be introduced to force the execution of such queries. At least most users will be prevented from shooting themselves in the foot unless they specificly specify to do so. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: renaming the database
Octavian Rasnita wrote: Is there a command for renaming a MySQL database? Sorry, there is no command for that. For small databases you can use administration tools like phpMyAdmin, etc. which will do it for you by duplicating the database structure and data, and then drop the 'old' database. For big databases this will take quite long, so it's probably better to stick with the old name (what's in a name? ;-) ) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: renaming the database
C.R. Vegelin wrote: What about renaming the folder indicating the database name. I work with Windows XP and renaming a folder works well. Cor, Have you tried it with InnoDB tables or anything other than MyISAM tables? InnoDB uses a single tablespace per server (unless specified that it should use a file per table, but then it still uses a general tablespace), so renaming a directory or folder will probably only confuse InnoDB and prevent it from starting. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default table type = innodb is stable??
- Original Message - From: Hiu Yen Onn [EMAIL PROTECTED] if i uncomment it, then mysql server wont start at all. if i comment it, then, that's ok. please advise. thanks again If the mysql server does not start it will probably leave a lot of messages in the error log. If you send the section of the error log about such a failed start to the list people here can most likely help you find the problem with your server. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upload images / mp3 more Than 1 MB capacity ---- please help
Dan Buettner wrote: I tend to disagree - at my place of employment, a newspaper, we have hundreds of gigabytes of BLOB data (ad and page layouts digital artwork) stored in SQL databases. Granted we are using Sybase for that and not MySQL but there are a lot of advantages to it - access control, change control and tracking, easy insertion and deletion, and access from any client right through the database driver so you can repurpose content more easily. There are situations where it might be useful to store large amounts of binary data in a database. For most situations the best solution is to store metadata about the file in a database and store the file itself on a file system. There have been lots of discussions about it on this list in the past. From those discussions one could conclude that in general a file system is best for storing (large) files and the metadata about these files can live in a database. But there are situations where storing large files in a database has more advantages. Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: setting date format
Ananda Kumar wrote: Hi Friends, I want to set the default date format as dd-mon-. How do i do this in mysql. The short answer: not. http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html lists the variables you can change by the SET GLOBAL or SET SESSION command. 'date_format' is not listed there. Also http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html states: # date_format This variable is not implemented. # datetime_format This variable is not implemented. It's probably listed there for future use. Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: upload images / mp3 more Than 1 MB capacity ---- please help
Kane Wilson wrote: But when i try to store little but huge gif files it wont store . First of all, use the method described at http://www.php.net/manual/en/features.file-upload.php for a safe way to handle file uploads. It could be that you run into a server limit which will show up if you use that method. I do think that you exceeded the max_allowed_packet size for MySQL queries which has a default value of 1048576 (=1MB). You can increase this number (must be done in both client and server!!), but it is usually best to store huge files in a file system and not in a database. Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search on words inside words
Merlin wrote: I am wondering if it is possible to find words inside words with the help of fulltext search. Is this possible? Google does that, so somehow there should be a way. Somehow I don't think that Google runs on a single MySQL database. Full text indexes in MySQL mean that words (MySQL's definition of a word that is) are indexed, not parts of words. Google's purpose is to provide a searchable index, so they have built their own data structures for these features. Another thing is, how do I exclude popular words like and for and similar from the search? Is there a MySQL setting for this. Like words_to_exclude = Fine tuning full-text search can be found at: http://dev.mysql.com/doc/mysql/en/fulltext-fine-tuning.html The default stop words are at: http://dev.mysql.com/doc/mysql/en/fulltext-stopwords.html Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: variable table names in queries ...
C.R. Vegelin wrote: Hi, I am looking for a method to use variable table names. I have various download-tables, all having the same structure. All I want is to run a series of queries on each of these tables. I tried SET @mytable = 'Download200501'; SELECT count(*) FROM @mytable; but this doen't work. Is there any way to work around this ? Thanks, Cor Cor, In http://dev.mysql.com/doc/mysql/en/variables.html it states that User variables may be used where expressions are allowed. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE number LINES clause of a LOAD DATA statement. Since the table name in the SELECT syntax cannot be an expression, you can't use variables here. I think this is a job for the programming environment / shell that you use. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 Bugs
Remo Tex wrote: 1) that's common problem when doing custom install on PATH containing spaces like C:\Program Files. Solution - try custom location with short(er) path and without spaces like C:\mysql5013\. Probably that will solve problem 2. if not then.. Remo Tex, That is not a good excuse for me. A path with spaces is a valid path in Windows (at least recent versions), so any Windows program should be able to deal with this. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database design
Matthew Lenz wrote: anyone using openoffice:base to design mysql db's? back when I tried it earlier this year it wasn't able to define relationships which made it pretty much useless as a time saving tool. Hi Matt, Although it's slightly OT here, there is still a lot of development going on in Base. The most recent version I downloaded is a version 2 beta with internal version 1.9.130. It has lot's of improvements over previous releases, but it's still not the final release version. The best thing you can do is try it with a recent build (the 1.x series also had a recent update to 1.1.5 BTW) and submit an issue in the bug tracking system on the site. If you include version numbers of your OS, MySQL, etc. and detailed instructions on how to duplicate the problems you encountered you can help the development a bit further. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text search
Merlin wrote: Hi there, I am facing problems with fulltext search on MySQL 4.0.18 Problem is, that words which are not seperated by space are not found. Example: A search for dsl will not find DSL-Modem I looked it up on mysql.com help, but despite the fact that this is not seperated by space and only 3 letter, I could not find a solution. Can anybody point me to the right way? Thank you for any help, Minimum word length for full text index is four by default. Information about changing default behaviour: http://dev.mysql.com/doc/mysql/en/fulltext-fine-tuning.html Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing and no values
Gleb Paharenko wrote: I'm not giving an exact answer on your question, however, it might be interesting for you. Usually queries are faster if you define the column as NOT NULL. What's the reason behind this? NULL 'values' are a bit of strange phenomenon. In the EXPLAIN output a query with WHERE col NOT NULL; is of type 'range', which implies that NULL has a position in the range of values of the column. On the other hand UNIQUE indexes allow multiple NULL 'values' (except for BDB tables) and the storage space for various data types does not leave room for an extra 'value' in the range. It almost seems as if NULL is stored as a kind of prefix in an index? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY for ints
Stephen A. Cochran Lists wrote: I'm getting a strange ordering when using ORDER BY on a int column. The rows are being returned sorted as follows: The list is typically the way to order a string. You are most likely to get meaningful suggestions to solve the mystery if you include the table definition (output of SHOW CREATE TABLE tablename) and the query. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding index to a replication slave
Balazs Rauznitz wrote: I have replication set up. Is it OK to alter one of the slaves and add several indexes ? It did seem to work, but I'd like to be sure. Replication does nothing more or less than copying the queries that alter the tables (inserts, updates, alter table, delete, etc.) to the slave in the same order as they were executed on the master (it may be technically a different story, but this illustrates the concept). So, you can change the data or the database structure as much as you want, but errors may occur if the queries fail somehow. Adding indexes is okay as long as you don't add indexes that will cause duplicate key errors (e.g. a UNIQUE index on a field that will not contain unique values). Also, indexes may slow down the queries (more or less) because of the extra execution time needed to update the indexes. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another LOAD Infile Problem
Jason Ferguson wrote: The data is split into about 60 files, average file size of 5 MB (varying from 1 to 10 MB). Since there are many files, I'm trying to minimize the required work (if there was just one consolidated file, no problem). The work can be automated easily with the right tools ;-) If you have for example perl installed on your system and the files all have the '.dat' extension, you can use: perl -pi -e 's/unknown/0/gi' *.dat All instances of 'unknown' (without the quotes of course and case insensitive) will be replaced with '0' in all of the .dat files; use different wildcard constructions if your file have other names. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding index to a replication slave
Mysql Lists wrote: I'm hoping they get replication setup by diffs, only sending the diffs that are tracked from the master.. replicated to the slave.. That would be sweet :) On 9/27/05, *Jigal van Hemert* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Balazs Rauznitz wrote: I have replication set up. Is it OK to alter one of the slaves and add several indexes ? It did seem to work, but I'd like to be sure. Replication does nothing more or less than copying the queries that alter the tables (inserts, updates, alter table, delete, etc.) to the slave in the same order as they were executed on the master (it may be technically a different story, but this illustrates the concept). Please reply to the list and not to me personally, so others can join the thread :-) Using diffs (I assume that you mean a set of records that were changed since the previous moment of synchronisation) is not the way MySQL replication works. The master 'simply' keeps a log of the modifying queries it performed and the slave reads that log from time to time. It's the simplest and safest way to replicate IMHO... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to format(x,d) right justified ?
C.R. Vegelin wrote: Hi All, Does anyone know how to use the format() function in such a way that is displays numbers right justified. For example, see the following query, where I want no decimal places: SELECT 123456.789 AS X, FORMAT(123456.789, 0), FORMAT(123456.789, 0)+0; The 1st column is normally displayed as 123456.789 The 2nd column is displayed as string (left justified) as 123,457 BUT the 3rd column, using +0 to force right-justified, cuts off valid data and displays only 123 Hi Cor, I would use the ROUND() function to do the rounding, as it returns values with the same type as the first argument. http://dev.mysql.com/doc/mysql/en/mathematical-functions.html Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
Martijn Tonies wrote: Given that the OP did not state that there were any issues with an existing website, logical requirements come first. Period. No discussion ;) Logical requirements may come first, but may be overruled later by requirements caused by performance issues or system limitations. If your logic designed a large type of primary key, you may run into problems with InnoDB tables. The PK is stored with the data and other indexes refer to the PK (and not directly to the data as is the case with MyISAM). So a large PK will increase the table size (data + indexes) and may thus lead to performance issues when the database does not fit in memory anymore, or when the buffers,etc. hit the memory limits on your system. A very complex model may lead to queries with more than 31 JOINs, which is not possible with MySQL without modifying the source and recompiling it (and even then the limit seems to be 63). In any case, if this is a read/write application, I would still say that logical requirements should go first. If this is a read only application, do whatever you want. Logic may come first in the time line, but may be overruled by other requirements. Finding people who celebrate their birthday today (or this week) may become a very slow task if you only use a logical data field. Denormalisation by using extra fields for particular tasks is a completely logical solution in this case. If this is the customers own server and everything is logical correct but there are some performance problems, I'd say: throw more hardware at it. Obviously, this makes sense --after-- tweaks to the database engine caching etc etc... Hardware is cheap(ish). If you can control it, do so. Throwing hardware at it is not always a good solution. You know better than that. The customer better not find out that the application could very well run on the original server with a few tweaks as you call them, and that he appears to have lost a lot of money for new hardware and all the time needed to get the new server running in the configuration that you suggested... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very large key_buffer on amd64?
- Original Message - From: Chris Kantarjiev [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, September 19, 2005 9:06 PM Subject: very large key_buffer on amd64? We're starting to use mysql (4.0.25) on an amd64 machine (running NetBSD-3). One of the reasons for doing this is to use much more RAM - we tend to thrash the key_buffer on i386 because one of our indexes is 10GB in size (the table is 15GB). It appears that mysqld won't start if the setting for key_buffer is more than 2GB. Maybe you've also hit the quirks of memory management and malloc, just as we've posted a while ago in http://lists.mysql.com/mysql/186930 ? I assume that you've installed an appropriate 64-bit version of MySQL... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: spanned indexes
Eli wrote: Is it possible, or probably will be possible, to define indexes spanned on more than one table? I mean that if I have column col_a from table tbl_A, and col_b from table tbl_B.. Can I define a KEY with both those columns? Where will the index be saved? Just out of curiosity and to understand things better: what would be the use of such an index? In what circumstances would one need it? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: spanned indexes
Eli wrote: The reason I thought about this is that I got several tables that hold their own specific data and are indexed inside themselves. But I got an issue to search on JOIN of 2 or more tables with comparison and/or ordering on combinations of fields from the various tables. This usualy ORDER BY only uses an index in very specific circumstances IMHO. When using JOINs you will probably end up with MySQL doing a filesort. http://dev.mysql.com/doc/mysql/en/order-by-optimization.html states that it will NOT use indexes if: - the columns in the ORDR BY are not all from the first non-constant table in the execution plan - the ORDER BY and GROUP BY expressions are different - the index type does not store the rows in order (e.g. HASH index) This makes the chances pretty slim for complex queries that it will use an index for sorting. One of the upgrades we recently did resulted in more execution plans which used an index for sorting. We have the impression that older versions of MySQL like to have less records to consider, but that newer versions slightly prefer an execution plan with more records that can actually use an index for sorting. ended up in a case that the first table in the JOIN used his own index, while the rest had to do full table scan, so thought that if there was a spanned index it would be much faster.. Unfortunately, as I thought, it is impossible (for now at least).. Optimizing order by random would also be a welcome improvement. I have the impression that it is currently implemented by adding a column with random values and after collecting all the data executing a filesort on that column. After that the LIMIT, etc. will be used to select the data it will send to the client. IMHO it would be faster to take the limit into account at an earlier stage and using the random part to determine whether the record it is now considering would belong within the limit-selection or not. There will be caveats for sure, but it may be worth considering... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie MySQL/PHP question re output formatting
Bill Whitacre wrote:
printf(trtd {$thearray[org]} /td
td align=right {$thearray[COUNT(*)]} /td
td align=right $ {$thearray[cost]} /td/tr);
If I replace
{$thearray[cost]}
with
number_format({$thearray[cost]}, 2)
Although this is a MySQL mailing list and your problem is not MySQL
related, but a PHP question I'll give you a brief answer.
PHP does not evaluate functions inside a double quoted string, so you
should use:
trtd .number_format($thearray['cost'],2). /tdtd align...
Furthermore, using $thearray[cost] is not advisable; PHP will try to
find a constant named 'cost' and if it can't find one it will use the
string itself as the value. Use a real string (quoted) instead of
relying on this feature:
$thearray['cost'] or $thearray[cost]
Also you can use aliases in your query to avoid things like
$thearray[COUNT(*)];
Use something like this in your query:
SELECT COUNT(*) AS `count` FROM
and you can use $thearray['count'] instead
As a last pointer, printf() is pretty much useless here since you don't
use any variable formatting features of printf() here. print() or echo()
are more suitable in this case.
Regards, Jigal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help .........very urgent
Kane Wilson wrote:
I wanted to check the following condition and if it is
success i wanted to display a massage.I tried as
follows. but no luck. nothing displayed.
$dbQuery = results;
$result = mysql_query($dbQuery) or die(Couldn't get
file list);
if (!isset($result))
{echo NULL;}
//if (mysql_result($result == 0))(
//echo sorry;
?
Although this is something for a PHP mailing list of forum, I'll try to
give you some pointers.
I assume that you first used mysql_connect() to connect to the MySQL db.
mysql_query() returns FALSE if the query failed for some reason. In that
case you can use mysql_error() to retrieve the error message.
$result = mysql_query($dbQuery);
if (!$result) die('Query failed: ' . mysql_error());
Checking isset() is useless, since $result is always set (either with
false, true or the result resource of the query).
http://www.php.net/manual/en/ref.mysql.php contains an example which
shows all the steps you need to set up a connection and run a query.
Regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SCO issue
Schalk Neethling wrote: You guys should subscribe to the Planet MySQL RSS feed. The entire issue was cleared up there by a member of the MySQL AB staff. http://www.planetmysql.org/ Maybe the MySQL AB staff could take the trouble of answering posts about this issue on this list? Maybe the anouncement could be made here? After all, if there is a new version the news is posted here and MySQL AB staff regularly answer technical question on this list... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alphabetizing within GROUPS
Jeff Pflueger wrote: Hi. Thanks for any help on this. I've been beating my head over it for hours. Here's what I am trying to do: I have four tables I am joining via a unique key (Fellow_id). The results I want to group into three categories, each alphabetized within the group. Is this too much to do in a single query? I am very close, but not there yet. Here's the query: SELECT (...) GROUP BY graduate_results_INSTITUTIONS.UNKNOWN ASC, graduate_results_INSTITUTIONS.END ASC, Fellowships.Fellowship_id I want to alphabetize each group with something like: ORDER BY Fellow_contact.Fellow_2nd_name Perhaps the use of GROUP BY is not entirely clear to you; if you GROUP BY a field then each distinct value for that field will result in a single output row. If there were originally 20 records for a single field value you can use functions such as COUNT(), SUM(), etc. to calculate values for the GROUP. Example: you want to know how many times duplicate e-mail addresses are present in a table: SELECT `email`, COUNT(`email`) FROM `table1` GROUP BY `email`; - email count - [EMAIL PROTECTED] 5 [EMAIL PROTECTED] 2 [EMAIL PROTECTED] 1 - If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. MySQL has extended the GROUP BY clause as of version 3.23.34 so that you can also specify ASC and DESC after columns named in the clause. Maybe you want to have nested ORDER BY's. Sort by city, within each city sort by street, within each street sort by number of tv-sets: SELECT () ORDER BY `city` ASC, `street` ASC, `num_tv` DESC; Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Birthday strategy
Pooly wrote: Hi, I would like to display a list of members who have their birthday a given day (today for instance). For such an application I've used a single integer column to store a number consisting of the month and day (day as 2 digits!!!) concatenated. So dates range from 101 to 1231. The range isn't continuous, but at least the dates are ordered correctly. In this situation you can also easily query ranges (who's celebrating their birthday in the next week/month/etc.). The only caveat is when the start and end of the range is in two different years; then you'll have to split the range up and use a range for each year. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create serial number by select
Gyurasits Zoltán wrote: Hello All! I have a little problem. I can't do serial number in result of select. I guess you want to display a sequential number for each row in the result. First of all a warning: if there is no ORDER BY in the query there is no specific order in which the results will be returned. Often it seems as if the records are returned in the order in which they were once inserted, but this is by far not always the case. A solution that might work for you is: SET @count:=0; SELECT @count:[EMAIL PROTECTED] AS `count`, `value` FROM `table`; count value - 1 res1 2 res2 3 res5 4 res18 Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question
- Original Message - From: Kishore Jalleda as per http://dev.mysql.com/doc/mysql/en/replication-compatibility.html there should be no problems On 8/17/05, Jeff [EMAIL PROTECTED] wrote: Does anyone know if there are any problems replicating from a master database on version 4.0.16 to a slave running version 4.1.13? Well, there are a few differences between 4.0.x and 4.1.x that might cause some problems: http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html Lists most if not all of them. At a quarter of the page the block Server changes lists differences in the way tables are built internally, differences in comparing things, etc. Half way on that page the block SQL changes list incompatibilities in query syntax, etc. For example: - DELETE from multiple tables ( In MySQL 4.0, you should refer to the table names to be deleted with the true table name. In MySQL 4.1, you must use the alias (if one was given) when referring to a table name: ) http://dev.mysql.com/doc/mysql/en/delete.html There is no easy solution as the master (4.0) requires a different syntax than the slave (4.1). Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE LIKE in MySQL 3.23
Konrad Billewicz wrote: Hello, I would like to do thing exactly like CREATE TABLE a LIKE b. But... I have MySQL 3.23 and this command is available since 4.1. How would you handle this task using this, older MySQL? http://dev.mysql.com/doc/mysql/en/show-create-table.html Use output of SHOW CREATE TABLE, modify name and execute that query. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: security question CAN-2005-0709 CAN-2005-0710 CAN-2005-0711
[EMAIL PROTECTED] wrote: MySQL has moved WELL past the 3.23.x lineage and is getting close to retiring the 4.0.x lineage (it's only a rumor). So I suggest you update Not completely a rumor; on August 2, Heikki wrote: As far as I know, one release of 4.0 will still be built. Considering the differences between 4.0.x and 4.1.x, I never saw the logic of the minor version change of 4.1 . At the moment the 4.0.x branche is useful as an easy step in the way of upgrading to 4.1. But I agree that upgrading to 4.1 is a sound advice. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Post-Installation Setup Problems: error[2002 1604]
Tim Johnson wrote: grin it get worse . most of my commercial work is running on servers with ver 3.23.X (sun and RH servers). I enjoy a great relationship with my domain hoster (who is also a programmer who uses mysql a lot), but he has been very cautious about upgrading any of his servers, being concerned about code breaking, etc. What argument might be provided to him to upgrade and what caveats might be cited? Details on how to upgrade are available at: http://dev.mysql.com/doc/mysql/en/upgrade.html It seems that the 4.0.x branche will not live very long anymore, so upgrading to 4.1.x (via 4.0) might be a good idea if he wants to upgrade. http://dev.mysql.com/doc/mysql/en/news.html Lists all the changes in each version. The major features can be found in the D.2 and D.3 documents (for 4.1 and 4.0). As a hoster he will probably also need to support PHP, etc. There is also a nice book by Adam Trachtenberg: Upgrading to PHP5 (O'Reilly, July, 2004, 352 p., ISBN 0596006365) which also contains information on how to migrate to PHP5/MySQL4.1 in several steps. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with slow query
Sebastian wrote: this query runs slow because AVG and COUNT on maps_rating table i think. can anything be done to improve? You may want to include: - table definitions (output of SHOW CREATE TABLE table) - output of EXPLAIN query This way the list members can make better suggestions. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TimeZone
Joseph Cochran wrote: Some countries have multiple timezones, so it is not sufficient to know the country code in order to get the timezone. If they have previously posted the timezone, however, then it should be possible to store that information in a cookie on the client machine that your web layer can retrieve. If you want to permanently tie a timezone to a user (assuming that this is an internal system or other system to which your users authenticate -- if it is a public website you're going to have to use cookies), simply include an extra column in the user's record that has a number that stores its differential from GMT (so the USA east coast would be -5) and save all of your data in GMT, applying the timezone column to the time via datetime functions either in the query or in your web layer. One more complication: daylight savings time are not the same world wide. So I would store the time zone and not the difference with GMT. I personally would do al the time zone calculations in the web layer. Most OSs have libraries with more or less knowledge about daylight savings in various countries/timezones. Using the functions in the language of the web layer you're more likely to get things right. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default collation char
Philippe Poelvoorde wrote: Enrique Sanchez Vela wrote: I would like to have MySQL differentiate between 'abc' and 'ABC' both the server and clients. so far anything I've done has not worked. Is altering the column type an option ? If yes, you would change any 'text' for 'blob' and any 'varchar' for 'varchar binary', as a result 'ABC' would next be different to 'abc' (but 'i' would also be different to 'e'). In many case using the BINARY operator in a query will suffice: http://dev.mysql.com/doc/mysql/en/charset-binary-op.html SELECT col1 FROM table1 WHERE BINARY col1 LIKE 'ABC%'; will only find rows with col1 starting with 'ABC', not with 'abc'. If inserts, etc. need also differentiate between upper and lowercase (and differentiate between accented and not-accented characters) Philippes suggestion is probably the way to go. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
