Re: questions about timestamps and DST
On 31/03/2015 12:20, Larry Martell wrote: On Tue, Mar 31, 2015 at 1:13 AM, Andrew Moore eroomy...@gmail.com wrote: When you use a timezone with DST there is no such thing as 2.30am on the date of changeover. That hour doesn't exist. I am using UCT - I am not using a timezone. In MySQL 5 and above, TIMESTAMP values are converted from the local server time to UTC at storage and then back again at select. So if the local server is using a DST timezone, then your TIMESTAMP value will always reflect local DST. Look up the difference between timestamp and datetime data types. I did do that before I posted, but it wasn't really clear to me, but I think I need to use a DATETIME instead of a TIMESTAMP. Correct? Yes. As a highly-rated comment on StackOverflow puts it: Timestamps in MySQL generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field. http://stackoverflow.com/questions/409286/datetime-vs-timestamp As a more general rule of thumb, use DATETIME unless you have a specific application for which you know that TIMESTAMP is more appropriate. They're not interchangeable, and not intended to be. Mark -- http://www.markgoodge.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email
On 10/12/2014 23:40, Reindl Harald wrote: Am 10.12.2014 um 18:38 schrieb h...@tbbs.net: 2014/12/10 09:00 +0100, Johan De Meersman One of the (for me, at least) defining features of a forum, is that the subjects tend to be divided up into a tree structure, which has it's own benefits Something more sophisticated than grouping messages by trimmed subject-lines? maybe involving such header lines as were used in the old netnews (if e-mail is part of it)? every sane MUA supports threading see attached screenshot Indeed. That, to me, is one of the key arguments in favour of a mailing list: people can choose how to view the list according to their own preference (some like it threaded, others prefer a flat view based simply on message date). Other arguments in favour of email include: * Email is a push medium. I don't have to continually re-check a website to see if there's any new messages, they simply arrive in my list mailbox and I view them at my convenience. * Individual emails can be forwarded and/or saved independently of the others. * Email gives me a local archive of messages in addition to any central archive. having said that, I think that web-based archives of mailing lists can be very useful, particularly for a public list where the archive is open to search engines. That makes them a valuable historical resource as well as merely a for-the-moment discussion forum. And, if you're going to have a web-based archive, it isn't a huge step from there to add the ability to post to the list via the web as well. That can be helpful for people on corporate email systems who don't easily have the ability to subscribe to a list (or filter mail from it into a separate folder), as well as people who only need to contribute very infrequently and don't want to have to subscribe in order to do so. But all this should, IMO, be in addition to the core features of an email mailing list, rather than a replacement for them. Mark -- http://www.markgoodge.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Unique index - opinions sought
I have a MySQL table (call it, say, item_spine) which contains three fields which, together, form a unique key. These three fields are a guid, a start date and an end date. The guid is alphanumeric (a fixed-length six characters) and the dates are ISO format dates (-MM-DD). I also have another table (item_detail) containing data which is keyed to the first in that the unique key here is the unique key from item_spine plus a line id (which is numeric). At the moment, I simply have the three fields in the item_spine set as a unique key, and replicate those three columns in item_detail and have those plus line_id as the unique key, thus making a four-column key. But, for performance reasons, I was wondering if it might make more sense to create a single column in item_spine containing data which is generated from the original three and use that as a unique key instead. I then only need a single column in item_detail to link to item_spine, and thus my unique key there can be only two columns. Another option is to have an autoincrement column as a primary key for item_spine, and then use that as the link key for item_detail. But I'd prefer to avoid that, because the content of item_spine has to be updated on a regular basis from external data and using autoincrement means I can't do that using REPLACE INTO while still maintaining a key association with item_detail. Any thoughts? How would you do it? Mark -- Sent from my Turing-Flowers Colossus http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Unique index - opinions sought
On 16/07/2012 17:39, Rick James wrote: How many rows? If 1K, it does not matter. If 1 billion, we need to discuss in more detail. Let's assume 1M... Around 1M in the item_spine table and 10M in item_detail. Dates should be stored in DATE datatype, which is 3 bytes. Your GUID is non-standard, but should probably be stored in CHAR(6) CHARACTER SET ascii, unless it is expected to have non-ascii characters. Is case folding important? Given those, the PRIMARY KEY is 6+3+3=12 bytes long. This is not bad for a million-row table. If bigger, then the AUTO_INCREMENT should be considered. The guid is case-insensitive. ISO dates map directly to MySQL's internal DATE type, so that's already taken care of. All data is ascii, and all alpha data is not case-sensitive. I should maybe have mentioned earlier that this is external data over which I have no control (but do have a specification which I expect to be honoured). My task is to store it and make it searchable for display. Is this InnoDB or MyISAM? (Please provide SHOW CREATE TABLE, not English, for describing tables.) It's MyISAM. I don't actually have a CREATE yet, as this is still just hypothetical :-) Let's see the SELECTs that will be hitting the tables. Then we can discuss in more detail. A typical select would be something like this: SELECT guid FROM item_spine WHERE start_date = NOW() AND end_date = NOW() AND location_code = '$query' followed by SELECT * FROM item_detail WHERE guid = '$guid' AND start_date = NOW() AND end_date = NOW() ORDER BY sequence where $query is the initial query from the user and $guid is the result of the first query. location_code is VARCHAR(10) and is an alphanumeric string. (I'm avoiding joins because doing multiple selects in the code is usually much faster) Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Postal code searching
On 24/04/2012 17:24, Tompkins Neil wrote: How about if I want to only return postal codes that are like W1U 8JE not W13 0SU. Because in this example I have W1 as the postal code and W13 is the other postal code No, you don't. In this example you have W1U as one outbound code and W13 as the other. W1U postcodes are not a subset of W1 postcodes, any more than IP27 postcodes are a subset of IP2 postcodes. The fact that in W1U the district segment is in the form of NA rather than NN doesn't change the fact that it's an indivisible two-character code. So I think the first question has to be, why do you want to get W1 as a particular substring from the postcode W1U 8JE? British postcodes have a structure which is easy for humans to understand, although (unfortunately) rather hard to parse automatically. Essentially, every full postcode contains four elements: Area code: one or two alpha characters, either A or AA District code: one or two alphanumeric characters the first of which is always numeric, either N, NN or NA Sector code: single numeric character, always N Walk code: two alpha characters, always AA It's customary, but not part of the formal specification, to insert whitespace between the District and Sector codes. So, given the postcode WC1H 8EJ, we have: Area: WC District: 1H Sector: 8 Walk: EJ Taken together, the first two sections form the outbound part of the postcode, and the second two form the inbound. (That is, the first two identify the destination sorting depot that the originating depot will send the post to, and the second two are used by the destination depot to make the actual delivery). The reason for mentioning this is that postcodes, having a wide range of possible formats, are not easy to handle with simple substring searches if you're trying to extract outbound codes from a full postcode. It can be done with regular expressions, but you have to be wary of assuming that the space between District and Sector will always be present as, particularly if you're getting data from user input, it might not be. In my own experience (which is quite extensive, as I've done a lot of work with systems, such as online retail, which use postcodes as a key part of the data), I've always found it simpler to pre-process the postcodes prior to inserting them into the database in order to ensure they have a consistent format (eg, inserting a space if none exists). That then makes it easy to select an outbound code, as you can use the space as a boundary. But if you want to be able to go further up the tree and select area codes (eg, distinguishing between EC, WC and W) then it's harder, as you have to account for the fact that some are two characters and some are only one. You can do it with a regular expression, taking everything prior to the first digit, but it's a lot easier in this case to extract the area code prior to inserting the data into the database and store the area code in a separate column. Mark -- Sent from my ZX Spectrum HD http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: One inst has 39 columns- the other 40
On 22/03/2012 04:41, Brown, Charles wrote: Look man, there has to be someone out there that can tell me why one user table has 5 extra columns. As far as I can see, plenty of people already have told you. Is it version related or my sysprog person missed out on a step. Yes, it's related to your version. You probably have different versions of MySQL on different nodes of the cluster. Your sysprog person has missed out the step of ensuring that all nodes are upgraded at the same time in order to maintain consistency. Its hard to believe that this problem is unique to my site. No, the problem is not unique to your site. It's an easy mistake to make. Please could you now give some indication of having read and understood this reply. Mark -- Sent from my Babbage Difference Engine 2 http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: In case you all missed it.
On 11/11/2011 16:29, Curtis Maurand wrote: mysql select date_format(now(),'%m-%d%-%y %h:%i:%s') AS time; +---+ | time | +---+ | 11-11-11 11:11:11 | +---+ 1 row in set (0.00 sec) Actually, it should be select date_format(now(),'%y-%m%-%d %h:%i:%s') :-) Mark -- Sent from my Babbage Difference Engine http://mark.goodge.co.uk http://www.ratemysupermarket.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Databasename/Tablename is marked as crashed and should be repaired
On 14/10/2011 08:07, James wrote: Hello, I have the following error on my mysql server log and managed to repaired the broken table. However, it keeps occurring by time to time. I am using MyISAM storage engine to all database and having some locking table which I know / aware about the disadvantage of MyISAM. './Databasename/Tablename' is marked as crashed and should be repaired Are there any ways to solved permanently? Any advise would be appreciated. If it's happening repeatedly, and the MySQL server itself is running without any problems (ie, it isn't crashing and restarting) then you may have problems with the hardware - with the disk itself. Mark -- Sent from my Babbage Difference Engine http://mark.goodge.co.uk http://www.ratemysupermarket.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Allowing all users to access a specified database
Hi, I have a database server with multiple users and multiple databases. I have a situation where I want to allow any user to connect to a specified database. Unfortunately, the documentation has this to say: MySQL does not support wildcards in user names. Which means, that, although I can use this syntax to grant access for a user to all databases: GRANT SELECT, INSERT, UPDATE, DELETE ON * TO 'someuser'@'%'; I can't do something like this to grant all users access to a database: GRANT SELECT, INSERT, UPDATE, DELETE ON mydyb TO '%'@'%'; So, my question is this: How can I allow any user to use a specific database, without individually granting them all access? The reason I want to be able to do this is that the server hosts a large number of individual ecommerce sites running on the same core software. Each site has its own database for products, customer data, etc, which is only accessible to that site. But each site also needs to be able to access a single, server-wide database which both stores configuration information for the software that the sites run on and store usage statistics for each site which can be accessed by the server admins without needing to have access to each site's own database. Clues, anyone? Mark -- Sent from my Babbage Difference Engine http://mark.goodge.co.uk http://www.ratemyairport.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Allowing all users to access a specified database
On 03/06/2011 11:24, John Daisley wrote: The reason *GRANT SELECT, INSERT, UPDATE, DELETE ON mydyb TO '%'@'%';* * * does not work is because that command would be suicidal in terms of security. If you are hosting a large number of ecommerce sites and granting any user access to those databases then you would want security to be far tighter. Allowing that sort of access is about as secure as publishing the data on facebook. That's not a problem in this case - the data is *intended* to be shared between all users of the system. It's data required by the software that the sites run on - which is simple, non-confidential stuff like basic settings as well as data which each site deliberately exports for copying by the others. The end users are not different organisations, they are different trading divisions within the same organisation. What version of MySQL are you using? 5.0.7 Mark -- Sent from my Babbage Difference Engine http://mark.goodge.co.uk http://www.ratemyairport.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ` vs '
On 30/03/2011 09:05, Brent Clark wrote: Hiya Im wondering if someone could help me understand this. If you look at my two queries below. By the ORDER BY one is using ` and the other ', as a result, if you do an explain you will see that the top query does a filesort, while the other does not. Because column names either need to be unquoted or enclosed in backticks. If you put a string inside ordinary quotes (either single or double) then it's treated as a string variable. And you can't sort by a string variable. As a demonstration, try these: SELECT * FROM contacts LIMIT 10 SELECT * FROM `contacts` LIMIT 10 SELECT * FROM 'contacts' LIMIT 10 The first two will work. The third will fail, as you can't select from a variable. Alternatively, try this: SELECT id FROM contacts LIMIT 10 SELECT `id` FROM contacts LIMIT 10 SELECT 'id' FROM contacts LIMIT 10 and all will be even more clear :-) Mark -- http://mark.goodge.co.uk http://www.ratemysupermarket.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto-Increment Values in Mysql
On 09/02/2011 11:41, Adarsh Sharma wrote: Dear all, I have an auto-increment column in Mysql database table. Let's say the column has below values : 1 2 3 4 5 6 7 8 9 10 Now if i deleted some rows where id= 3 ,5 and 8 The data look like as : 1 2 4 6 7 9 10 I want to have it id's as 1 2 3 4 5 6 7 and next data is inserted right at 8 Please help how to achieve it. Firstly, if this matters to you then an autoincrementing value is probably not what you should be using in the first place. The main point of autoincrement is that it doesn't matter what the actual value is, it's just a way of achieving a unique key for the table. If you care what the values are, then just use an INT field and generate the numbers yourself. That said, if you want to renumber an autoincrementing field then the simplest way is to drop it and re-add it: ALTER TABLE `mytable` DROP `myfield`; ALTER TABLE `mytable` ADD `myfield` INT NOT NULL AUTO_INCREMENT PRIMARY KEY; Mark -- http://mark.goodge.co.uk http://www.ratemysupermarket.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: CURRENT insert ID
On 24/01/2011 15:42, Jerry Schwartz wrote: -Original Message- From: Donovan Brooke [mailto:li...@euca.us] Sent: Friday, January 21, 2011 7:28 PM Cc: mysql@lists.mysql.com Subject: Re: CURRENT insert ID Just an idear.. Don't auto_increment the main table.. create a unique Id table, auto_increment that, and grab that value first for use with both fields in your main table. [JS] I've thought of that, but it creates another problem. Let's say I add a record to the ID table, thereby auto-incrementing its key. Now I need to retrieve that key value. How do I do that while retaining some semblance of data integrity? I'd have to do something like SELECT MAX(), which fails to retrieve my value if someone else has inserted a record in the meantime. That's what LAST_INSERT_ID() is for: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id This is on a per-connection basis, so even if another connection inserts a line in the meantime your query will return the auto-increment value of the line you inserted. Most programming languages with an interface to MySQL, either built-in or via a module, implement this natively. For example, in PHP: mysql_query(insert into mytable set name = 'foo'); $id = mysql_insert_id(); the value of $id will be the auto-increment number from the line you just inserted. Mark -- http://mark.goodge.co.uk http://www.ratemysupermarket.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Lowest non-zero number
Given a table containing a range of INT values, is there any easy way to select from it the lowest non-zero number? Obviously, MAX(column) will return the highest, but MIN(column) will return 0 if any row contains a 0, which isn't what I want. Any clues? Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Lowest non-zero number
On 03/12/2010 16:56, Paul Halliday wrote: On Fri, Dec 3, 2010 at 12:50 PM, Mark Goodgem...@good-stuff.co.uk wrote: Given a table containing a range of INT values, is there any easy way to select from it the lowest non-zero number? SELECT number FROM table WHERE number 0 ORDER BY number ASC LIMIT 1; Sorry, I should have said that I need to do this as part of a query which returns other data as well, including data from the rows which have a 0 in this column. So I can't exclude them with the WHERE clause. What I'm actually doing is something like this: SELECT name, AVG(score) as average, count(score) as taken FROM tests GROUP BY name and I want to extend it to something like this: SELECT name, AVG(score) as average, COUNT(score) as attempts, SUM(score = 0) as failed, SUM(score 0) as passed, MAX(score) as best_pass, . as lowest_pass FROM tests GROUP BY name and I need an expression to use in there to get the lowest non-zero value as lowest_pass. Does that make sense? And, if so, is there any easy way to do it? Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Possible causes of table crashing
Hi, I have a very strange problem whereby one particular table in our database is repeatedly (on average, every couple of days) generating errors stating that the table is crashed and needs to be repaired. Running a repair fixes it. What makes it strange (and something that I've never encountered before) is the following: 1. There is one particular table which it happens to a lot, and a few other tables where it happens occasionally. Everything else is fine. 2. None of the tables where it happens are among the most heavily used or commonly updated. 3. There is no shortage of disk space. 4. The disk system reports no errors. 5. We have multiple databases with an identical structure but different content (we are an online retailer and each database is a separate storefront), but the problem occurs in all the databases - but the same tables in each database. It doesn't always happen to each database at the same time - it seems to be random. 6. (This is the really strange one) We have two separate servers with identical copies of the databases, one for production use and one for development use, and it happens independently on both of them - but still the same tables (and, specifically, the same table that it happens to more often than any other). 7. And, of course, the obvious statement: we haven't changed the structure of this database recently (it hasn't changed for months, if not years). We are running MySQL 5.0.77 on Centos. All the databases use MyISAM exclusively. Given the above, can anyone suggest any possible causes? Thanks Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: backfill results for the same month and year
On 11/11/2010 09:00, HaidarPesebe wrote: Dear All, I have the following data ID CITY QTY MONTH YEAR --- 1 Chigago10 11 2010 1 NewYork 22 11 2010 1 London 54 11 2010 1 Chigago7 10 2010 1 NewYork 26 10 2010 1 London 33 10 2010 ID = ID PRODUCT. How can I backfill results with the same ID by month and year, with results like this: MONTH/YEARQTY TOTAL --- 11/201086 10/201066 and etc. select ID, concat(MONTH,'/',YEAR) as MONTHYEAR, sum(QTY) as TOTAL from MYTABLE group by concat(ID,MONTH,YEAR) Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Order by in clause
Hi, I have a query like this: select id, title from product where id in (1,3,5,8,10) What I want it to do is return the rows in the order specified in the in clause, so that this: select * from product where id in (10,3,8,5,1) will give me results in this order: +--+-+ | id | title | +--+-+ | 10 |foo | +--+-+ | 3 |baz | +--+-+ | 8 |bar | +--+-+ | 5 | wibble | +--+-+ | 1 | flirble | +--+-+ Is this possible? If so, how? Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SELECT WHERE IN help
On 21/09/2010 16:44, Tompkins Neil wrote: Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3 only exists once in the table my_table. However, because 3 exists twice within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is it possible ? No, that isn't possible. Why do you want a duplicate record to be retrieved? There may be a better way of doing it. Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best method to keep totals
On 03/09/2010 16:32, Arthur Fuller wrote: While I agree with the general take on this subject (Never store totals without a good reason and where there is duplication there is the opportunity for skew), I must say that there are exceptions. A couple of years ago I worked on an inherited database in which the operant principle was sum don't store; the problem was that many of the rows summed dated back a year or two or more, and as an accountant friend of mine loved to say, A paid transaction is history; an unpaid transaction is fiction. The other exception is also where financial data is being stored. If you have, say, a database containing sales order records, then as well as storing the individual values of each item in each order, you also need to store the total value of the order, the total price charged to the customer and the total paid by the customer. These three should, of course, be not only identical to each other but also to the sum of the individual items, so there is not only duplication but the potential for skew. But that, of course, is precisely *why* you store them, as any discrepancy indicates an error which needs to be investigated. Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performing subtraction between fields
On 21/08/2010 07:25, b...@qxhp.com wrote: Hello, For simplicity's sake, let's say I have three fields, A, B and C, all of which are integers. I'd like the value of C to be equal to A less B (A-B). Is there a way I can perform this calculation? I'm guessing it would happen when I INSERT a row and specify the values for A and B. Feel free to direct me to the fine manual I should have read. Given two variables, $a and $b: INSERT INTO mytable SET A = $a, B = $b, C = $a - $b or INSERT INTO mytable (A, B, C) VALUES ($a, $b, $a - $b) or, if you've previously inserted A and B: UPDATE mytable SET C = A - B http://dev.mysql.com/doc/refman/5.1/en/numeric-functions.html Simples :-) Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: searching serialized data stored in mysql
On 09/08/2010 18:33, Norman Khine wrote:
hello, i have a table called checkout, this has a row called products
which has contains a python dictionary data, like
http://pastie.org/1082137
{products: [{productId: 123, productName: APPLE,
productPrice: 2.34, productUrl: http://appple-fruits.net,
productDescription: nice juicy apples},
{productId: 333, productName: ORANGE,
productPrice: 4.21, productUrl: http://appple-fruits.net,
productDescription: nice juicy oranges},
...]}
what will be the correct way to make a search on this data, for
example if i want to search for a range of products with a price
between €2 - €4
is this the correct way to store this type of data?
No, it isn't, not if you're going to be searching for individual
elements of the array. Your products table should really have separate
columns for productId, productName, productPrice, productUrl and
ProductDescription. If you want to be able to store arbitrary key=value
pairs then a separate table with columns for productId, keyName and
keyValue would be a useful way of doing it.
Storing serialized data in single MySQL column is really only useful if
that data will never be directly manipulated by MySQL itself - that is,
if its only ever being used as the input to a separate program that
handles all the searching and manipulation.
Having said that, I've just looked at the URL you link to
(http://pastie.org/1082137) and what that's demonstrating isn't an
example of a products table, it's an example of a ecommerce checkout
table where the cart contents are a single column of serialized data
within the cart line. Personally, that's not the way I'd do it[1], but
it is a perfectly valid method if you start from the assumption that
you're never going to want to find individual orders by searching the
contents of the order. If you're looking at this as an example of a
product table that you would use to search for products, then you're
misunderstanding the example being given.
[1] I'd have a cart table with one line per cart, and then a separate
cart_contents table with one line per product and a cart_id column which
links it to the cart table. That also allows a separate cart_address
table which can have multiple addresses per cart (eg, billing address,
delivery address).
Mark
--
http://mark.goodge.co.uk
--
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 me
On 21/07/2010 16:33, Karthik Pr wrote:
I have created a table as follows but i was not able to use full text search on
a specific data.
create table racebike (id int auto_increment not null primary key, name
varchar(10), user text,fulltext(name,user));
[snip]
The query is
mysql select * from racebike where match (user) against ('speed');
It should be:
mysql select * from racebike where match (name,user) against ('speed');
When using a fulltext index, the query has to name all the fields
included in the index, unless you're performing the search in Boolean mode.
http://dev.mysql.com/doc/refman/5.1/en/fulltext-restrictions.html
Mark
--
http://mark.goodge.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Decimal points
On 19/07/2010 10:04, Ashley M. Kirchner wrote: Is there a way to tell MySql to only return '2' in the first select as opposed to '2.0'? The second select is correct and should remain as such. Not easily, no. Basically I have two columns, one with an integer and another with a decimal. And I'm adding the two, but for those where the decimal has a .0, I just want the result to not have the .0 and for those that do have anything other than .0, to display it accordingly. This is the sort of thing that is far better handled in the application layer, rather than the database layer. PHP, for example, even has a built-in function which will do this: setype($value,float); for example: ? $val = 2.1; setype($val,float); echo $val; ? = 2.1 ? $val = 2.0; setype($val,float); echo $val; ? = 2 http://www.php.net/manual/en/function.settype.php Even if other languages don't have built-in functions to do this, it's a trivial piece of code to recreate it. Mark -- http://mark.goodge.co.uk -- 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 Replication
On 24/06/2010 09:18, Tompkins Neil wrote: HI, We have set-up MySQL Community Server 5.1.46 with Master to Slave replication and everything appears to be working correctly, however I have a couple of questions which I hope somebody can shed some light. (1) When the network connection goes down between the master and slave servers, it would appear that the updates are only sent from the master to the slave, but not from the slave to the master when the connect is re-established. Is this correct ? Yes. Replication is one-way by default. If you want two-way replication you have to set it up explicitly with both servers simultaneously acting as both master and slave. (2) What is the situation regarding conflicts if the same master and slave record is edited at the same time ? You shouldn't normally edit records on the slave while it's acting as a slave. Replication has two main functions: to provide a hot backup of the master so that you can switch to the slave as the new master instantly should the master fail, and to allow load balancing by performing all reads on the slave (or multiple slaves) and updating only the master (eg, where you have a web cluster with each web server having its own MySQL instance acting as a slave from a central master updated from your CMS). Two-way replication is possible, but there are rarely any significant benefits from it. If you do use two-way replication, you have to implement locking at the application level as MySQL doesn't provide it natively. See the replication FAQ for more information: http://dev.mysql.com/doc/refman/5.1/en/replication-faq.html Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: substring query
On 10/06/2010 16:55, Aaron Savage wrote:
I am looking for some guidance on creating a substring query. I have
a column that stores a path to a file. I would like to extract that
file extension and that is it and display it on my results. However,
the paths are different lengths and some extensions are 3 letter and
some are 4, eq 'html'. The only common they all have is the period
before the extension. Anyone created a nested substring query that
can do what I am looking to do?
SUBSTRING_INDEX should do what you want.
SELECT SUBSTRING_INDEX('myfile.path','.',-1)
= 'path'
SELECT SUBSTRING_INDEX('myfile.pth','.',-1)
= 'pth'
or, in a version that's closer to real life usage:
SELECT SUBSTRING_INDEX(myfield,'.',-1) from mytable
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index
Mark
--
http://mark.goodge.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Master - master replication
On 24/05/2010 13:40, Walter Heck wrote: Carl, if you want to be secure, do not use the internet to transfer your data. SSH, VPN and SSL can not give you the kind of security a private line can give you. That is a tad expensive though :) That's true, but again that's not really answering the question which was asked. Plenty of sites use PCI-compliant transmission of data across the Internet, there's no reason why that should be an issue provided it's done correctly. The question here is whether MySQL natively supports the tools necessary to do it correctly, and if so how to implement them. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Array data type
On 14/05/2010 09:54, Joerg Bruehe wrote: Hi Samrat, all! Samrat Kar wrote: Hello, How to store multiple values in a single field? Is there any array data type concept in mysql? Multiple values in a single field would be an explicit violation of the relational model (on which the SQL language is based) and cause all kinds of trouble in your queries. Ever and again, developers use some kind of encoding to store a combination of values (like flags in a bit field) in one database field, but in many cases this makes queries very hard to write, and may prevent optimization of the SQL statement. It depends on your application, especially on whether this field will be used in search conditions (... WHERE combined_field has flag_X ...), to decide about a sensible approach. In general, I would prefer separate fields for different flags, and a separate table for a truly multi-valued field (like multiple postal or mail addresses for a person). If you're merely *storing* the data in the table, and will only ever retrieve it based on other factors - that is, you'll never use that field for any operands including joins and 'where' clauses - then it's often useful to store a flattened array (eg, one created by PHP's serialize() function, javascript JSON or even XML) as a string and then expand it to an array again after retrieving it. That can often be a useful way of storing meta-data about a data object (eg, EXIF data from a photograph), especially where you can't know in advance what the array structure will be when you create the database. However, that's not really an array datatype in MySQL, it's simply a method of storing an array as a string. So it's of fairly limited application, there are cases where it's very useful but it's not a substitute for storing the array values separately using the appropriate table design where you do need to run queries against it. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fulltext Match BOOLEAN MODE not searching integers
On 31/03/2010 16:52, Tompkins Neil wrote:
Hi
I have the following fulltext search which appears to work fine for string
phrases. However if I search like just 51 which is part of the string
name like 51 Blue Widget in the table it doesn't return any results.
However if I search like bl it returns the 51 Blue Widget result. My
query is as follows :
SELECT Name MATCH (Name) AGAINST ('51*') as Relevance
FROM Products
WHERE MATCH (Name) AGAINST ('51*' IN BOOLEAN MODE)
ORDER BY Relevance DESC
Any ideas what the problem might be ?
51 is too short to be included in the index by default, so will never
match. Blue, on the other hand, is indexed and therefore is returned
by a search.
The default minimum word length is four characters. See
http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html for
more information on how to change that if necessary.
Mark
--
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 to find this duplicate?
On 13/02/2010 16:12, Brian Dunning wrote: Hey all - I have a table listing references for chapters in a book. I'm trying to find all the cases where a single chapter lists more than one reference from the same author. In this example table, I want it to find IDs 1 and 2, because they're both from the same author, and both in chapter 1 of the book. It should not return ID 4, because that's in a different chapter. Note that J. and John have to be considered the same. For my purposes, it's sufficient to look at the first word, Smith, and consider that a duplicate. ++--+-+ | ID | Author | Chapter | ++--+-+ | 1 | Smith, John |1| | 2 | Smith, J.|1| | 3 | Williams, B. |1| | 4 | Smith, John |2| ++--+-+ I haven't been able to even get a start on this. Any suggestions? Try this: select count(id) as total, concat(substring_index(Author,,,1),Chapter) as my_reference from my_table group by my_reference having total 1 That may or may not work straight off, I haven't tested it. But the thing you're looking for is something involving a substring_index on the Author column. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Good source for sample data?
On 29/01/2010 03:18, John Meyer wrote: If I may recommend: http://www.generatedata.com/#download That's brilliant. The only minor issue is that, at least for UK data, it won't validate for mapping purposes - the postcodes are syntactically correct, but non-existent. I don't know if it would have the same problem for US or Canadian data. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Good source for sample data?
On 29/01/2010 15:20, Jerry Schwartz wrote: That's brilliant. The only minor issue is that, at least for UK data, it won't validate for mapping purposes - the postcodes are syntactically correct, but non-existent. I don't know if it would have the same problem for US or Canadian data. [JS] Sorry, my suggestion won't work either: I didn't notice that you are from the UK. Well, the name part will work; and perhaps the UK has databases similar to what the USPS (US Postal Service) makes available. I'd think they would, since they are accountable to the public. You might think so; you'd be wrong! In the UK, postcode data - even a comprehensive list of allocated postcodes - is the copyright of Royal Mail and only available under license. (Off-topic, but relevant to UK-based developers: please take a look at my blog at http://mark.goodge.co.uk/musings/422/locate-that-postcode/ for a topical twist on this) Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record old passwords ?
On 21/01/2010 11:07, Lucio Chiappetti wrote: On Tue, 19 Jan 2010, Tompkins Neil wrote: I can enforce that the user can't use the same password as the previous four - when they change their password. However, the user can manipulate this by changing the password four times and then resetting back to there original password. How would I overcome this problem ? Any thoughts or recommendations ? Probably if your users do that, it means they (rightfully) consider A DAMN NUISANCE the fact to be compelled to change password. Abandon the idea. I share their feeling about forcing this change of passwords, and cannot see almost no real life application (unless perhaps one is a spy) which really require this degree of security ! The real life application most commonly encountered where this is necessary is where your organisation wishes to process credit card or other financial data, and needs to be certified as PCI compliant by the banks and card companies in order to be able to process payments via their systems. One of the requirements of PCI compliance is that any login which has access to financial data must have the password changed regularly, with restrictions on reusing recent passwords. Now, you may well argue that the PCI requirements are wrong in this respect, and if so then a lot of people may well agree with you :-) However, unless you are a huge multinational and able to negotiate your own terms with the banks, disagreeing with the requirements doesn't alter the need to comply with them - at least, not if you want to be able to use their payment APIs. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record old passwords ?
On 19/01/2010 14:44, Tompkins Neil wrote: Hi All, Following on from my earlier email - I've the following question now : I can enforce that the user can't use the same password as the previous four - when they change their password. However, the user can manipulate this by changing the password four times and then resetting back to there original password. How would I overcome this problem ? Any thoughts or recommendations ? Store the date/time that the password was changed, and as well as not alllowing one within the past four passwords you can also disallow one that was last used within the past N days, for whatever value of N you prefer. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Record old passwords ?
On 19/01/2010 09:14, Tompkins Neil wrote: I think I will go with the four additional column approach as I proposed (in the current table) - since this need is a PCI compliancy security requirement. Do you have a reference for that? Storing past passwords as additional fields like that is inflexible and generally bad database design. I'd be somewhat surprised if PCI compliance really did require it. Mark -- http://mark.goodge.co.uk - blog htp://www.good-stuff.co.uk - stuff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: last_insert_id
Gary Smith wrote: Steve Edberg wrote: (2) autoincrement values are not reused after deletion, so if you deleted the record with ID=1000 inserted in (1), the next autoincrement would still be 1001, even if the existing records are IDs 1,2,3. This is usually the desired behavior, but again, may not be what *you* need. Aah... I'd mis-remembered on this. Is there an SQL mode or somesuch which changes this behaviour, or is my mind dribbling out of my ears? As far as I'm aware there's no mode to change the default behaviour, but you can always reset the autoincrement value: ALTER TABLE tbl AUTO_INCREMENT = n; Do that, and the next inserted record will have id = n, provided that n is greater than the current maximum value. If, on the other hand, n is lower than or equal to the current maximum value, the next id will be the next value higher than the current maximum. So ALTER TABLE tbl AUTO_INCREMENT = 1; on a non-empty table is functionally equivalent to ALTER TABLE tbl AUTO_INCREMENT = MAX(id) + l (which isn't valid SQL, so don't try it!) If you want to reuse autoincrement values above the current maximum, therefore, you can achieve that in practice by resetting the autoincrement value prior to any insertion. What you can't do, though, is get autoincrement to insert values into the middle of a sequence. So if you have, say, ids 1,2,3,4,5,8,9 and you issue ALTER TABLE tbl AUTO_INCREMENT = 1; or ALTER TABLE tbl AUTO_INCREMENT = 6; then the next inserted id will still be 10, not 6. Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join Statement
Victor Subervi wrote: On Mon, Dec 14, 2009 a mysql select SKU, Quantity, Name, Price, t.sizes, t.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID http://t.prodid-p.id/; Empty set (0.00 sec) mysql select ID, SKU, Name, Price from products; ++--+---++ | ID | SKU | Name | Price | ++--+---++ | 2 | prodSKU1 | name1 | 555.22 | ++--+---++ 1 row in set (0.00 sec) t.ProdID == 2 p.ID == 2 That's a match. So why does my select join fail? Because you're using a minus sign where you should be using an equals sign. This is what you're doing: select SKU, Quantity, Name, Price, t.sizes,t.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID-p.ID This is what you should be doing: select SKU, Quantity, Name, Price, t.sizes,t.colorsShadesNumbersShort from tem126080739853 t join products p on t.ProdID=p.ID Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update Doesn't Update!
Jørn Dahl-Stamnes wrote:
On Friday 11 December 2009 10:38, Victor Subervi wrote:
Hi;
mysql update products set sizes=('Small', 'Large') where ID=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
Look at the message, 0 rows changed and 1 warning.
You cannot have ID=0 if ID is an index.
You can, but not if it's an auto-increment field.
Mark
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump vs phpmyadmin dump
Wang Zi Feng wrote: Hi everyone, Here is a rookie question. The problem what I found is that mysqldump and phpmyadmin generate different size of backup file against same database. I try to dump same database with the 2 different methods, the original database is 2.8mb, phpmyadmin export 1.5mb file, and mysqldump export only 941kb file. mysqldump has a number of different settings, and the file size will vary according to which you use. For example, using extended insert syntax will significantly increase the size of the output, and that could easily account for the difference between your two files. What's probably happening is that the settings you're using when running mysqldump from the command line are different to those used by phpMyAdmin, so you end up with differently formatted files. I know there must be some difference between the two export method, but after I import the 941kb file which mysqldump created into a new database, it just works fine. So I'm not sure if I can use mysqldump as the best option to do mysql backup, can someone can help me to figure out why phpmyadmin would generate twice big file? And I see some post that address it is not recommend to import mysqldump file by using phpmyadmin, because it will cause problem. Importing any large file via phpMyAdmin is likely to have problems, as you'll find yourself limited by the maximum upload file size of the web server where phpMyAdmin is running (that's typically 2Mb for PHP on Apache, although the administrators can change that). But the source of the file is irrelevant; so long as it's within the file upload limit then it doesn't matter whether it was exported by phpMyAdmin itself or created using mysqldump from the command line. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Another Join Problem
Victor Subervi wrote: Hi; I get the following error: *SQL query:* SELECT ID, Item FROM products JOIN categories ON categories.ID = products.Category LIMIT 0 , 30; *MySQL said:* #1052 - Column 'ID' in field list is ambiguous Please note the error is about ambiguity. products has an ID field and so does categories. If I run the statement taking out the ID from the select, it runs. So, where is the ambiguity?? The ambiguity is that the select clause doesn't know which table you're referring to, since you're joining two of them that both have an 'ID' field. This will work: SELECT products.ID, Item FROM products JOIN categories ON categories.ID = products.Category LIMIT 0 , 30; Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Wierd PHP/MySQL result
Mogens Melander wrote:
Well, no typos.
The relevant piece of code:
if (! $cust = mysql_fetch_assoc($res))
{
echo {success: false, errors: { reason: 'Fetch Customer failed: .
htmlspecialchars(mysql_error(),ENT_QUOTES) .
br . htmlspecialchars($sql,ENT_QUOTES) . '}};
}
else
{
echo {success: true, total: 1, results:[ . json_encode($cust) . ]};
}
What happens if you simplify the PHP?
$cust = mysql_fetch_assoc($res);
print_r($cust);
That will tell you what PHP is seeing from MySQL. If the results have
the correct values, then the problem is elsewhere in the code.
I suspect that the problem is in the json_encode() function. According
to the PHP documentation this requires utf8-encoded data. If your values
in oname are not utf8, then that may explain why the function is
returning a 'null' where it should have a string.
Mark
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to compare 2 columns in different tables.
John Furlong wrote: My question is, can the argument in AGAINST() refer to a column in another table or does it have to be a specific string you are searching for? If the MATCH() function won't work, any suggestions on how else to compare table1.name against table2.name? The columns are defined as VARCHAR. Does anyone have an answer to this? I, too, would like to know how to use fulltext to compare data between two columns (as opposed to comparing data between a column and a pre-defined string). Is there any way to do this? Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Fulltext query expansion query
Hi, I'm currently working on a project which uses fuulltext searching. The with query expansion feature is useful, but I was wondering if there's any way to obtain the list of terms that the expanded query uses (other than those originally input, of course). Is that possible, and, if so, how? Thanks Mark -- Stuff: http://www.good-stuff.co.uk Blog: http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysqld fails to start
JD wrote: Hello List, mysqld fails to start, and emits the following error messages in /var/log/mysqld.log 090127 10:00:30 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist 090127 10:00:30 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 090127 10:00:30 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. That's most likely to be the real error. Or, at least, the most important one. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. 090127 10:00:30 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended So, I tried to run mysql_upgrade: # mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck'... mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect FATAL ERROR: Upgrade failed So, it seems like a chicken and egg thing??? Cannot run mysql_upgrade bcause the server is not running. Cannot run the server because I must first run mysql_upgrade. I think you're being misled by the fact that there are two errors: A missing table and a file system that mysqld doesn't have access to. Fix the file permissions first, and then try starting mysqld. You'll then be able to run mysql_upgrade to fix the missing table problem (which is a warning, not a fatal error, hence why you need to have the server running to be able to fix it). Mark -- http://www.good-stuff.co.uk - Stuff, some of it good -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Optimizing nullable expiration dates
Norman Elton wrote: I've got a table that tracks expiration dates. Currently, if the record hasn't expired, the column is stored as NULL. In order to find records that haven't expired, I search for WHERE expire_at NOW() OR expire_at IS NULL. This seems dirty, and I suspect it makes my indexes very angry. I suppose I could pick some arbitrary future date (Dec 31 2999) to use in place of NULL. Is there a better way? Surely I'm not the first to run into this! If the field is only updated when the record expires, then a simple where expire_at is NULL will do. You only need to compare expiration dates with NOW() if they can be in the future - which, from your description, isn't possible. If an expiry date can be either future, past or non-existent, then your existing selector is as good as any. Mark -- http://mark.goodge.co.uk - my pointless blog http://www.good-stuff.co.uk - my less pointless stuff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: normalised designs: customer database
metastable wrote: Hello all, I have a question that's been bugging me for quite some time. Let's say we have a small business that has both private and corporate customers. We want to store contact and address data about these customers, as well as invoicing data. Off course, only companies have VAT numbers. It's more complex than that, because there isn't a clear distinction between individuals and companies. A customer may be a company, a partnership, a charity, a sole trader or a non-trading individual. All but the non-trading individual may have (but are not necessarily required to have) a VAT number. When normalising this design, you would reach something like the following: - table for contact details (separate, because multiple contact details may apply) - table for address details (separate, because multiple addresses may apply) - table for people (first name, last name, etc) - table for companies (company name and vat number) - tables that link the above data to each other (people-contact, people-address, people-company, company-address, ...) - table for customers, i.e. 'entities' that are invoiced What do you guys think about this ? Which option is most viable ? Which solution have you chosen ? What I'd do is have a table for customer, which is essentially the invoice information (since that is, effectively, what defines a customer to you). I'd have separate tables for alternate addresses and contacts. The customer table would have a field defining the customer type, and another field containing an id which is the key to further information about that customer in the relevant table, where necessary. I wouldn't try to arbitrarily normalise the database for SQL efficiency. In a real-life situation, it's more important that the database design reflects your actual workflow and business requirements. Having a field that's empty 50% or more of the time is far less of a problem than not being able to process a sale because your database structure is too inflexible :-) Mark -- http://mark.goodge.co.uk - my pointless blog http://www.good-stuff.co.uk - my less pointless stuff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Most efficient way of handling a large dataset
Joerg Bruehe wrote: Hi Mark, all! Mark Goodge wrote: I'd appreciate some advice on how best to handle a biggish dataset consisting of around 5 million lines. At the moment, I have a single table consisting of four fields and one primary key: partcode varchar(20) region varchar(10) location varchar(50) qty int(11) PRIMARY KEY (partcode, region, location) The biggest variable is partcode, with around 80,000 distinct values. For statistical purposes, I need to be able to select a sum(qty) based on the other three fields (eg, select sum(qty) from mytable where partcode ='x' and region = 'y' and location = 'z') as well as generating a list of partcodes and total quantities in each region and location (eg, select sum(qty), partcode from mytable where region = 'y' and location = 'z' group by partcode). Sorry, I don't get it - I think there is a contradiction in your mail: Your table has four fields, three of which form the primary key. This means that for any combination of values for those three fields (partcode, region, location) there will be at most one row, and so only one qty value. Why do you want to sum over one value? Sorry, my mistake. I mean that I need to be able to sum any combination of two from three of the first three fields. And in your second query there is also no need for sum(qty), a plain qty will do because for each group there will be only one row (region and location are set to fixed values in your statement). IMO, the main question is whether all your statements use fixed values for region and location (like your second statement), but only some do for partcode (your first statement). No; any of the three can be fixed or not, as the case may be. If that holds true for your statements, then your primary key is defined in the wrong order: it should have partcode as the last field (= the least significant one). Then, all your statement could use the primary key, and you need no separate index. [[...]] Does anyone have any suggestions? My initial thought is to replace the region and location varchar fields with int fields keyed to a separate list of region and location names. Would that help, or is there a better way? Well, if your data are integer values, then using integer as column type should speed up your operations considerably: Operations (including comparisons) on integers are faster than on character strings, and reduced data size means shorter (= faster) transfers and more elements in caches (assuming same cache size). At the moment, only the qty is an integer value. The others are strings (a variable-length alphanumeric for partcode, and county/town names for region and location). For presentation reasons, I need to display the human-readable names of the region and location, but this could be done by means of having the names in a separate table with integer ids that's joined to the main table when querying. Mark -- http://mark.goodge.co.uk - my pointless blog http://www.good-stuff.co.uk - my less pointless stuff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Most efficient way of handling a large dataset
I'd appreciate some advice on how best to handle a biggish dataset consisting of around 5 million lines. At the moment, I have a single table consisting of four fields and one primary key: partcode varchar(20) region varchar(10) location varchar(50) qty int(11) PRIMARY KEY (partcode, region, location) The biggest variable is partcode, with around 80,000 distinct values. For statistical purposes, I need to be able to select a sum(qty) based on the other three fields (eg, select sum(qty) from mytable where partcode ='x' and region = 'y' and location = 'z') as well as generating a list of partcodes and total quantities in each region and location (eg, select sum(qty), partcode from mytable where region = 'y' and location = 'z' group by partcode). The selection is done via a web-based interface. Unfortunately, it's too slow. So I want to be able to optimise it for faster access. Speed of updating is less crucial, as it isn't updated in real-time - the table gets updated by a nightly batch job that runs outside normal working hours (and, apart from the rare occasion when a location is added or removed, the only thing that changes is the value in qty). Does anyone have any suggestions? My initial thought is to replace the region and location varchar fields with int fields keyed to a separate list of region and location names. Would that help, or is there a better way? Mark -- http://mark.goodge.co.uk - my pointless blog http://www.good-stuff.co.uk - my less pointless stuff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reset auto increment to a lesser value
abhishek jain wrote: On Sat, Aug 2, 2008 at 10:49 But as per a thread on this link, if the resetted value is less than the highest value already in table then the effective increment value will start from highest number and not from 100. I have deleted some rows and i want the increment to start from those row-ids. That can't be done. An auto-increment value must be higher than the highest existing value. Otherwise, it would not be an auto-increment value at all - it would be an auto-interpolate. On a more general note, if the actual value of the primary key matters for anything other than simply existing as a primary key, then you shouldn't be using auto-increment at all. You should generate the value through some other means and insert it with the value that you want it to be. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reset auto increment to a lesser value
Chris W wrote: Mark Goodge wrote: On a more general note, if the actual value of the primary key matters for anything other than simply existing as a primary key, then you shouldn't be using auto-increment at all. You should generate the value through some other means and insert it with the value that you want it to be. Can you elaborate on that point? Do you not use auto-increment values to link records in a one to many relationship? Yes, but the relevant factor here is that in the table where the auto-increment value is generated it has no meaning other than as a unique id. In the other tables that use it as a reference, then it has meaning there and needs to be inserted as a known value. An auto-increment field can only be used where that value never needs to be set by reference to an external value. It can be a value that other external values are set to (such as in a one-to-many relationship), but in the other tables that use it as a reference then it isn't inserted as an auto-increment. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blocking HTML code in inserts?
Skip Evans wrote: Hey all, What is the most effective way to block HTML code in insert statements? I have a client with a comments form that is being bombarded with people inserting references to their own sites, etc, and I need an effective way to filter basically any HTML tags at all. You'll need to do that before inserting it into the database. How you do that depends on which scripting language you're using to generate the inserts. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Trying to find a .zip binary install for Windows 2000 for 4.0.7 gamma
At 11:41 30/12/2002 -0600, Cal Evans wrote: Is there a problem with your mail server or do you keep re-sending this message? If the latter, please stop. Those that have read the message do not have an answer. Continuing to spam this overloaded list with the same request will simply make those who may know the answer less likely to help you. Not to mention that the requested file is right there on the site! You'd have to be pretty dumb to miss it, IMO. But, for the benefit of the dumb, here it is: http://www.mysql.com/downloads/mysql-standard-4.0.html mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
combine data from two tables
Hi, I'm hoping that someone can give me some pointers on this problem. I have two tables that contain similar data (eg, people.employees and people.volunteers). I need to run a select statement to retrieve names from both tables, and then display the data sorted by name so that it doesn't matter which table it came from. For example, if people.employees contains Amy, Charles and Eric, while people.volunteers contains Brian, David and Fred, then the resulting output needs to be displayed in this order: Amy Brian Charles David Eric Fred I could do this by post-processing the data in the script which retrieves it, but I'd prefer to get it in the correct order direct from MySQL. Any suggestions? Thanks Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Sorting UK Postcodes (WAS Sorting Results)
At 22:53 12/12/2002 +0100, Alliax wrote: Sorry, I forgot to say that postcode can be one or 2 letters in front of the numbers. -Message d'origine- They can be E1,E2,..,E12,E13 Order by name would do: E1,E10,E11,E12,E13,E2,E3,E4,... how can I get with a simple ORDER BY query E1,E2,E3,E4,E5,... ? If sorting by number is important, then split them on input and store them in two separate files. But I don't really know why you'd want to sort them numerically anyway - UK postcodes aren't sequential in operation: E1 is not necessarily adjacent to E2 on the map. So having a sequence of E1,E10,E2, etc is no less applicable than E1,E2,E10 would be. Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Time comparisons
Can anyone help with (what I hope is) a pretty simple time function query? I need to extract a set of records from a table based on separate date and time columns, where both the date and time are older or equal to now. For example, my sql query is going to look a bit like this: select ID, Live_Time, Live_Date from schedule where TO_DAYS(NOW()) = Live_Date and SOMETHING = Live_Time and it's the SOMETHING that I need to know! (Before you ask, there are reasons why the date and time are separate columns rather than being a single datetime column, and I don't want to rewrite the structure unless I have to) Thanks Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select using regexp
Hi, I've got a problem that I'm hoping someone can help with. I need to do a query against a text column in order to extract entries that will match whole words only - for example, a search for cat should match any of: The cat sat on the mat It was a large cat. Cat food is interesting. Dog. Cat. Fish. but not match in a catatonic state it was a catastrophe scattergun approach It looks as if the MySQL REGEXP function is what I need here, but I can't work out from the documentation how to get what I want. Any suggestions? Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql vs. Oracle and concat ||
At 20:46 26/09/2002 +0200, MySQL wrote: Hi all, I'm a DBA in the Oracle World. I want to make a sql query in mysql, with a concat (||) known i Oracle world. Like this. select numer ||','|| text from Table: You need to specify the keyword CONCAT and enclose it in brackets, like this: select CONCAT(number,,,text) from Table Mark -- http://www.good-stuff.co.uk Life is bigger, it's bigger than you - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INSERT IGNORE
At 10:24 17/09/2002 +0100, [EMAIL PROTECTED] wrote: According to the manual, if INSERT IGNORE finds a row already present in the table, it does not update the row with the new data. How can I get the opposite behaviour? In my case, there shouldn't be a record with the unique ID I am inserting. But if there is, my recovery behaviour is to delete the old one and then do the insert again - I don't care what the old data was, my new data is correct for the new situation. Can I get MySQL to do that for me? The command you're looking for is REPLACE. Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql and Cobalt
At 07:45 05/07/01, dijana cetinic wrote: Hi all, just a quick question .. has neone setup mysql on a cobalt RAQ4 before? If so ne recommendations on how to go about doing it? I've installed it on a Cobalt RAQ. I tried several of the rpm files (from both MySQL and Red Hat), but none of them worked, so in the end I downloaded the source tarball: http://www.mysql.com/Downloads/MySQL-3.23/mysql-3.23.39.tar.gz) and then followed the instructions on MySQL.com. The chapter in the online manual is 4.7 Installing a MySQL Source Distribution which you can find at: http://www.mysql.com/documentation/mysql/bychapter/manual_Installing.html#Installing_source I didn't have any problems doing it this way - I accepted all the defaults when prompted and it worked first time. It is more timeconsuming than using a binary or rpm, but the Cobalt is such an odd environment that the source is probably the best option! Mark -- With sufficient thrust, pigs fly just fine. From RFC1925 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
full outer join?
According to the MySQL documentation, it doesn't currently support full outer joins and won't do in the immediate future. Does anyone have any suggestions for a workaround? I need to join three tables, only one of which has a common identifier with the others, and produce a result which shows not only the matching information (eg, where A.first_id = B.first_id and A.second_id = C.second_id), but also find any rows in either B or C that don't have a matching row in A, as well as any rows in A that don't match either B or C. Starting with A, I can easily show rows that don't have a corresponding match in B or C by means of a left join, but I can't work out how to get the reverse information in the same select. Any ideas? Or am I missing something obvious? Mark -- With sufficient thrust, pigs fly just fine. From RFC1925 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
