Re: Field name DESC
DESC is a reserved word in MySQL: it is short for DESCENDING and is used to reverse the sort order in SELECTs. You an create a field with that name by enclosing it in backticks: `desc` whenever you need it. However, this would be regarded by many as very bad practice. It would be better to change the field name e.g. to descr or even description. Making the field name longer and more meaningful costs next to nothing. Alec Anthony [EMAIL PROTECTED] 13/07/2006 16:42 To mysql@lists.mysql.com cc Subject Field name DESC Hello, i want to know how i can create a table with the feild name desc ? when i do: CREATE TABLE bank ( name varchar(50) NOT NULL default '', desc varchar(50) NOT NULL default '', ) the desc is badely interpreted... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Reply / Return Address of this List
The battle has been fought before - and the list administrator has given his reasons why he has not made the requested change. The way the list currently behaves is not an accident or omission, but a deliberate decision. I do not recall the grounds for that decision - maybe RFCs or the behaviour of certain email clients (which probably does not include your own), or maybe the fact that an individual reply is often very difficult if the default is group reply but no the other way round. But before asking for a change, I suggest you go back into the archives and find out why the administrator decided as he did, and prepare a refutation for his argument at that time, not a general complaint that it doesn't suit your personal needs. Alec Andy Eastham [EMAIL PROTECTED] 18/04/2006 13:16 To mysql@lists.mysql.com cc Subject RE: Reply / Return Address of this List Yes this battle has been fought before. But this is still a pain in the ass. Whilst the list is unmoderated, surely someone at MySQL has the capacity to make a change to the server configuration of whatever hosts the list? How many times has someone had their problem solved by someone who accidentally emailed them direct, rather than via the list. So the solution was never seen by anyone else and never made the archives. Hence the same question gets asked again... and again... I know I've accidentally emailed suggestions to people directly a number of times because of this (I, like many, many others pragmatically use MS outlook), and invariably the only reason I even find this out is when the person thanks me directly rather than through the list. Come on, let's move into the nineties and sort this out. Andy -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: 18 April 2006 13:05 To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: RE: Reply / Return Address of this List [snip] 1. Please always reply to the List. Who runs this list? Could it please be configured to send replies back to the list rather than the individual? It's really annoying to keep ending up with a personal address - it would make things so much easier, and is, to my knowledge, standard practice for mailing lists to have replies automatically go to the list itself. [/snip] This is an un-moderated list and this little battle has been fought. Just hit Reply-all or whatever your e-mail client allows. Many lists (many, many older lists especially) are set up just like this one. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Key and Primary Key
news [EMAIL PROTECTED] wrote on 06/04/2006 14:39:33: IF I have a table like this: id int not null, field2 int not null, .., primary key (id), key (field2) ) ENGINE=MyISAM; The primary key is id only or (id, field2)? If this is the case which constraint are aplied on field2? You have defined two separate keys, one on ID and one on field2. The id field, being primary, must be without nulls and each entry must be unique. The key on field2, not being a primary key, may contain duplicates and nulls. Are you searching for the synax ... primary key keyname (id, field2) ... ? This creates a single key in which neither of the fields may be null and the combination of the two fields (but not the two fields separately) must be unique. The effects on the two formulations both on constraints and on search performance are different. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about autoincrement ID
[EMAIL PROTECTED] (saf) wrote on 23/03/2006 16:10:04: On Thu, Mar 23, 2006 at 11:04:55AM -0500, [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (saf) wrote on 03/23/2006 10:50:10 AM: The short answer is no. The Record #2 already existed. It's current status is deleted. If you had other tables that linked their data to record #2 and you created a new #2 to replace the one you already deleted then you could possibly be making a bad match between the old data and the new data. For the sake of data consistency and for all of the other good reasons to have a relational database, once an auto_increment value has been issued it's considered used and no other record should ever have that number. Only if you completely reset your table (see the command TRUNCATE TABLE) could it be possibly safe to begin re-issuing the smaller numbers. Again, it's only possible if all of the child records that used to point to the old data were also deleted. Do not rely on the auto_increment value for record sequencing. If you need your records serialized in some sequential way, you will need to code the support for those sequential numbers in your application. So I must do a big SELECT and then check my self every time (for each INSERT), which IDs are free? Hmm if the table has more than 100 000 entries, this will slow down my system. Specialitty because the check function would be written in PHP. Lots of ways round this. Instead of deleting records, add a boolean deleted flag. All selects then need to add and deleted = 0. But you can find a (random) deleted row with select id from table where deleted = 1 limit 1. If this returns a result, use update to re-populate that record, clearing the deleted flag. If it returns nothing, use insert to create a new record. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to shutdown mysql from Java
Rhino [EMAIL PROTECTED] wrote on 21/03/2006 13:41:49: - Original Message - From: Zsolt [EMAIL PROTECTED] To: MySql Mailing List mysql@lists.mysql.com Sent: Tuesday, March 21, 2006 2:58 AM Subject: How to shutdown mysql from Java Hi, my application starts mysqld (via Runtime.getRuntime().exec) and I would like to stop it also from Java (because of technical reasons I cannot use mysqladmin). What is the best way stop shutdown mysqld? 1. FLUSH TABLES 2. Process.destroy() 3. kill PID auf Unix What do you think? Most database management systems, like MySQL and DB2, are designed to run 24 X 7 with occasional breaks for maintenance like taking backups. Why do you want start and start MySQL from an application? Isn't this going to preclude most of the users from using it? After all, databases usually have large numbers of users, not just one. Obviously not in this case. While MySQL can do many things, it also works perfectly well as a single user repository. It is perfectly reasonable for a sing user to regard it as part of a single application and start and stop it from the application. However, the cost of an inactive MySQL running all the time is tiny. And treating MySQL like this would cause problems if, for example, you wanted to run two such applications, because they would fight over it. Or if your application wanted to run on a PC which already had MySQL running for some other purpose. So I would suggest that it would be wisest to do as Rhino implies and to install MySQL and leave it running all the time. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
Martijn Tonies [EMAIL PROTECTED] wrote on 16/03/2006 11:02:32: Well, the question still is if you should store unknown at all ;) Not according to Date: you should store what is known. See the remarks about the true propositions, from which relational databases are derived (but you probably know that). As someone totally unread in the theory of databases, that seems unduly puritanical. I assume that what Date would propose is that you have another table (related by master key) in which, if you do not know something, you do not enter it. But this means that if you have 10 different pieces of potentially but not necessarily available information about a single master record (e.g. a person), you have to do a 10-way join in order to retrieve all the information about them. Replacing a theoretically ugly null flag with a 10 way join strikes me, as an engineer rather than a theoretician, the wrong side of the elegance/practicality trade-off. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
Martijn Tonies [EMAIL PROTECTED] wrote on 16/03/2006 11:32:45: From: [EMAIL PROTECTED] Martijn Tonies [EMAIL PROTECTED] wrote on 16/03/2006 11:02:32: Well, the question still is if you should store unknown at all ;) Not according to Date: you should store what is known. See the remarks about the true propositions, from which relational databases are derived (but you probably know that). As someone totally unread in the theory of databases, that seems unduly puritanical. I assume that what Date would propose is that you have another table (related by master key) in which, if you do not know something, you do not enter it. But this means that if you have 10 different pieces of potentially but not necessarily available information about a single master record (e.g. a person), you have to do a 10-way join in order to retrieve all the information about them. Replacing a theoretically ugly null flag with a 10 way join strikes me, as an engineer rather than a theoretician, the wrong side of the elegance/practicality trade-off. Using NULLs as well as de-normalization brings the risk of integrity problems to your storage, storing what is right is only a good thing. And when it comes to having to writing JOINs for all your queries, lo and behold, I bring you the wonder of the VIEW. ;-) The VIEW eases the syntax, but does it do anything for performance? Surely it must be much slower to read 11 different tables (Master record containing all NOT NULL fields, and 10 slave records which may or may not contain relevant fields)? Ignoring caching, you are going to have at least one disk access for every NULL field (index lookup which fails) and two for every non NULL field (index lookup, data lookup) for every null field. This means that you have multiplied your number of disk accesses (ignoring caching, again) by 6-11 times (assuming the master record takes two disk accesses). That again seems a very high price to pay for theoretical elegance. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
Martijn Tonies [EMAIL PROTECTED] wrote on 14/03/2006 11:32:10: Hello Paul, I suggest you reply to the mailinglist :-) ... The developer insists that for scalability issues, this was the answer. It is likely, for example in my deployment, that these tables would see upwards of 10 million records or more. Well, if there are problems with scalability, I guess you could split it up in a few (not 1600) tables and have them avaialble on different physical hard drives... In my opinion, splitting things into merge tables has a *strong* anti-scalability component. Searching a single table with indexes is O(log n), whereas searching MERGE tables is O(n). Therefore, by splitting your table into very many pieces, you sharply reduce your scalability in time while increasing it in space. Presumably, you want to scatter your table across several drives, so that you will not have problems when you fill one drive. But you are never likely to have 1600 drives, so 1600 is a ridiculously large number of tables to split it into. You should probably split it into no more than two or three times the largest number of disks you ever expect to have. And even so, I would rather combine disks in RAID arrays rather than uses separate tables. This can give you RAID protection as well as more disk space. Which to you expect to run out of first, space or time? You seem to have some heavyweight i.e. time intensive queries, which suggests that you will run out of time first. If that is so, the requirement for scalablity says that you should combine, not split, tables. To quote Donald Knuth (derived from Hoare) Premature optimisation is the root of all evil. You should be sure that you are optimising in the right place before you dive in: your problem suggests that you are trying to fix that which is not broken, and breaking other things in the process. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
Paul Halliday [EMAIL PROTECTED] wrote on 14/03/2006 12:09:10: As an example: There was a table called event. This table is now broken up like this: event _sensor_date. So for every sensor, and every day, there is now a new table. So if I have 20 sensors, every day I will have 20 new tables. With this in mind, does this design make sense? how will this scale? Is there anything I can do through configuration (I doubt the developer will change the design) to speed things up? or a workaround that I could do on my end to compensate? Could you explain how this is meant to improve scalability? Because to my mind it is probably the best way I can imagine to make the system unscaleable. To me, this design very much does *not* make sense. You have bought, in MySQL, a highly tuned specialist engine for seqrching and sorting stuff in the most efficent manner. And then you have said that you will disable all its optimisation and force it into a linear search. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
nigel wood [EMAIL PROTECTED] wrote on 14/03/2006 13:09:08: [EMAIL PROTECTED] wrote: Paul Halliday [EMAIL PROTECTED] wrote on 14/03/2006 12:09:10: As an example: There was a table called event. This table is now broken up like this: event _sensor_date. So for every sensor, and every day, there is now a new table. So if I have 20 sensors, every day I will have 20 new tables. With this in mind, does this design make sense? how will this scale? Is there anything I can do through configuration (I doubt the developer will change the design) to speed things up? or a workaround that I could do on my end to compensate? Could you explain how this is meant to improve scalability? Because to my mind it is probably the best way I can imagine to make the system unscaleable. To me, this design very much does *not* make sense. You have bought, in MySQL, a highly tuned specialist engine for seqrching and sorting stuff in the most efficent manner. And then you have said that you will disable all its optimisation and force it into a linear search. I can think of a reason for doing this but not to extent described. Is your developer trying to create a situation where it's easy to archive of results earlier than a given day? So you store say 1000 days of data and can quickly archive the oldest day at midnight each day. Assuming this is the case: There's no point splitting further than by day so tables per day/sensor don't make any sense unless your worried about sub second locking (i.e. doing it wrong). You should make the unmerged tables as large as possible without the time to delete having an impact on your application. Having an impact depends on your applications tolerence to locking and the amount of data your adding and removing, you'll need to find it by testing. The table type you use will have a big impact on concurrent access locks. MyiSAM and Innodb are the two main candidates MyISAM is quick but is doesn't allow concurrent access to the table. Innodb will allow concurrent access but still locks rows and can lock the 'head point' during certain inserts. The fact your storing sensor data worries me. How tolerent of lag/locking on insert or retreval is your application? If it's sensitive to more than a seconds lag you need a careful review of your design. If it's hard real-time sack the developer then review the design. I take your point to a certain extent. Of course, in the end it comes down to the searches being used. I would make it a rule of thumb that any search which requires more than a 10 tables is a Bad Thing. So if the very large majority of searches are for 1-4 sensors over 1-4 days, this architecture might make sense. But if searches are over 10 sensors or 10 days, this architecture will b become astoundingly inefficient. Generally, I would expect MERGE tables to be used on much larger lumps of time. If you have tables per month, any random period of a month can be checked very efficiently by merging two tables - the start month and the end month. This is the sort of thing that people tend to want to do. The OP of course knows his application, but I think it unusual for people to slice queries that small. And if the queries are of the form upon which days/sensors did event X happen, then splitting the table up is a one way path to doom. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: primary key
Ronan [EMAIL PROTECTED] wrote on 16/02/2006 11:56:18: Im trying to set up a primary key of server(text), date (date), hour (small int) but when i try to include the server field in the key it replies with ALTER TABLE `exim` DROP PRIMARY KEY , ADD PRIMARY KEY ( `date` , `hour` , `server` ) #1170 - BLOB/TEXT column 'server' used in key specification without a key length i have googled, but not much is relevant to my example i dont think.. CREATE TABLE `exim` ( `date` date NOT NULL default '-00-00', `server` longtext NOT NULL, `hour` tinyint(4) NOT NULL default '0', `count` smallint(6) NOT NULL default '0', PRIMARY KEY (`date`,`hour`), ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='exim realtime stats'; A LONGTEXT field may be up to 4 Gigabytes long. MySQL cannot (and, I would think, should not) include thenwhole 4Gb in the index. It therefore needs you to indicate how many characters of the server field it should actually use in the index. To get it to use only the first 64 characters in the key, you should put in server(64) (the single quotes you are using are necesary only if you wish to give a colum the same name as a reserved wioord - a vary bad practice). However, if you are using it as a PRIMARY KEY or UNIQUE KEY, *you* must guarantee that those firat 64 characters are unique. within any given date and hour i.e., I would guess, that your servers are unique within the first 64 (or however many you choose) characters. May I suggest that a more conventional way to do what I think you are doing woiuld be to have two tables. Allocate each server a number, and put the number in the exim table. Then have another table to convert the server name to a number. It is then trivially easy to use that table to convert from server number to name or vice versa. And the server name no l;onger has to be unique in the first N characters: as long as the names differ, the table will work. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie wants to load a couple of tables and join them
Al Sparks [EMAIL PROTECTED] wrote on 13/02/2006 16:11:49: Basically, I'm new to mysql (or to any database for that matter). I have an old version installed on my linux machine. I thought, as a learning exercise I'd take 2 files (tab separated tables) load them into mysql and then merge or join them. So what are the steps? The first thing I tried was to create a database with mysqladmin create MACARP and the error I get is CREATE DATABASE failed; error: 'Access denied for user: '@localhost' to database 'MACARP'' A similar attempt to create a user ended similarly. Can I get some hints? When your system was installed, it was installed with security turned on (which is definitely a wise thing to do). MySQL security is a bit like linux secutiry, in that there is a user called root who is usually omnipotent, and other users with lesser rights, and you cannot do anything unless yuou have the appropriate rights. However, it is not the same as linux security - your linux user name and your MySQL user name are different entities, not the same unless you choose to make them so. I would reccommend that you try and find out the root password for your system from whoever installed it. If not, and you think that no data on the system is valid, de-intall MySQL, remove the data directory (which also contaisn the security data), and re-install. Alternatively, the MySQL installation usually sets up a database imaginatively named test with wide rights, so that you could run you experiments within database test. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB and locking
Patrick Duda [EMAIL PROTECTED] wrote on 10/02/2006 16:28:56: I guess I don't understand this locking stuff. I have a InnoDB table that has one thing in it, a counter. All I want to do is have multiple instances of the code read this counter and increment it. I want to make sure that each one is unique. Here is what I am doing in java: c.setAutoCommit(false); ... rs = statement.executeQuery(select request_id from requestid_innodb for update); ... String updateQuery = update requestid_innodb set request_id=; updateQuery = updateQuery + nextRequestId; tempStatement = c.createStatement(); tempStatement.executeUpdate(updateQuery); ... c.commit(); c.setAutoCommit(true); If I have multiple instances of this code running I end up with duplicate keys. I thought this was suppose to lock the table so that would not happen. What am I not doing right? What am I not understanding about locking? I think this problem is explained in detail at http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Complexity Limit Question [Newbie Question]
David T. Ashley [EMAIL PROTECTED] wrote on 07/02/2006 14:03:04: Hi, I have several tables linked in various ways so that an inner join is possible. However, at the same time and in the same SQL query, I'd also like to query by some field values in one of the tables. Two quick questions: a)Will MySQL allow joins that involve more than two tables (in my case, perhaps as many as 5)? b)Can limits on a key field be included in the join in the same SQL statement as does the join, i.e. ... WHERE N3 AND N20 ... or something like that. Yes, you can do multi-way joins, and people often do. My biggest is 3-way, but some people do at least 5-way. Beware that it is easy to specify operations which will heavily load the system if you are not careful. The constraints in the WHERE statement are *logically* and syntactically done on the huge table produced by the joins. However, the MySQL optimiser is not stupid and will perform the filter upstream of the JOIN where possible. Some experimentation and use of the EXPLAIN statement may be necessary to find the best ordering for queries. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Huge number of tables with InnoDB
To reply to this, I think we have to understand why you have chosen to
split the tables at all. It seems to me that this, by introducing a
two-level lookup, is certain to be slower than any possible single table
lookup. Generally, Log A + log B is bound to be larger than log (A*B).
It appears that you are querying prediminantly by time. In this case, your
index *must* start with the timestamp, not the monitor ID. I would suggest
that you need an index on timestamp, and possible one on monitor ID - but
not if, as you say, you never query by monitor ID at all. Do you need a
PRIMARY KEY at all? In what way will your system break if there happen to
be two entries with the same timestamp and monitor ID? Presumably this
will reflect two events very close together: Wouldn't you rather store
that fact rather than lose it?
Generally, I would query your decision to have multiple tables by date to
whatever. In my experience, whenever I have introduced such concepts into
my early designs, they have disappeareed later into a better design. It
looks to mee as if you are using a tool optimied to do fast searches on
large databases, then crippling its ability to optimise.
I would expect the use of thousands of tables effectively to disable
MySQL's caching capability, which is one of the biggest performance
boosters.
Alec
John McCaskey [EMAIL PROTECTED]
13/01/2006 17:20
To
MySQL mysql@lists.mysql.com
cc
Subject
Huge number of tables with InnoDB
Hi everyone,
I'm running MySQL 4.0.18 on Debian with a 2.6 linux kernel using ext3 as
the underlying filesystem for the database storage.
I currently have some InnoDB tables with the following structure:
Log_20060101 {
Monitor_id medium int,
Timestamp timestamp,
Avg float,
PRIMARY KEY Monitor_id, Timestamp
}
We partition these tables by date as you can see as they grow very large
and they get to be slow to insert and query to over time. We have the
idea to change the partitioning so the tables are as follows:
Log_[monitor_id] {
Timestamp,
Avg float,
PRIMARY KEY Timestamp
}
This seems to have several key advantages:
1) Reduced disk space usage
2) Easier querying of data across time (but not across individual
id's, it turns out that doesn't ever really happen in our data usage
anyway though)
3) Smaller tables, resulting in faster reads/writes, also smaller
data volume hopefully also resulting in faster reads/writes due to less
disk io neccesary
However, in our actual testing the 'faster writes' expectation is
getting shot down. With 20,000 unique monitor id's and 8928 unique
timestamps inserting to the old set of tables (20051201-20051231 in this
case) is taking me about 4 hours 20 minutes. Inserting to the 20,000
new tables (Log_0-Log_2) is taking about 10 hours. I expected this
to be much faster as I hoped finding the right table for an insert would
be a Hash type lookup taking linear time, while inserting into the large
tree structure in the old tables which have a much higher volume of rows
would be logarithmic time. Where did I go wrong? My only real thought
so far is the disk subsystem of the OS being slow with large numbers of
files, but I thought it wouldn't matter for InnoDB as the data storage
is all one file. Does anyone know what would cause the inserts to be so
much slower?
John A. McCaskey
Software Development Engineer
Klir Technologies, Inc.
[EMAIL PROTECTED]
206.902.2027
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0, FULL-TEXT Indexing and Search Arabic Data, Unicode
AmirBehzad Eslami [EMAIL PROTECTED] wrote on 24/11/2005 18:36:25: On 24/11/2005, Alec worte: I think this is your problem: MySQL does not properly support Unicode until version 4.1. I am successfully using FullText with MySQL 4.1 to sort UTF-8 encoded Japanese text. I see no reason why it should not work for Arabic - if you upgrade. Dear Alec, Thank you for your prompt reply. You're right. That's my problem. I admit it. But I'm really unable to solve this by upgrading. Many of the Hosting Companies, which I use their services [even the HostRocket.com] still use MySQL 4.0 !!! Googling for hosting mysql 4.1 gives a number of companies offering MySQL 4.1 and PHP 5. Obviously I cannot comment on their competence. 1) Would you recommend any hosting company with PHP 5 and MySQL 4.1 support? 2) What about if my client only use MySQL 4.0 for his reasons. In this case, I really can't use FULL-TEXT search? There is no any solution? No. It is inherent in the Fulltext mechanism that the text indexing engine knows which bytes represent indexable characters and which separators. Before 4.1, Fulltext was 8-bit only - end of story. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0, FULL-TEXT Indexing and Search Arabic Data, Unicode
AmirBehzad Eslami [EMAIL PROTECTED] wrote on 24/11/2005 17:48:29: Dear list, I'm considering programming a simple Search Engine for a website, to find Arabic/Persian data within a MySQL database. This database contains a huge amount of data, encoded with Unicode(UTF-8). The big deal is to ** reduce the response time ** to end-users. My first solution is to create an Index and use the FULL-TEXT Searching method. Luckily, MySQL's provides FULL-TEXT Indexing support in MyISAM tables. But unfortunately, it doesn't support multi-byte charsets (e.g. Unicode). [1] Technically, MySQL creates Indexes over words. A word'' is any sequence of characters consisting of letters and numbers [2]. Assuming this, I tried to save the records as Unicode Character References (#;), but the search failed again :-( Any suggestion? I appreciate any solution to solve this problem. Thanks in Advance, Behzad [1] MySQL Manual - 6.8.3 Full-text Search TODO [2] MySQL Manual - 6.8 MySQL Full-text Search P.S. *** I use MySQL 4.0 *** I think this is your problem: MySQL does not properly support Unicode until version 4.1. I am successfully using FullText with MySQL 4.1 to sort UTF-8 encoded Japanese text. I see no reason why it should not work for Arabic - if you upgrade. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question for JDBC and Mysql
Xiaobo Chen [EMAIL PROTECTED] wrote on 01/11/2005 20:28:38: Hi, all I have a question like this: There's a field in table_A, date_time, if I say this in Mysql: select min(date_time), max(date_time) from table_A; it returned something like this: +-+-+ | min(date_time) | max(date_time) | +-+-+ | 2003-05-06 11:59:00 | 2003-05-23 11:59:00 | +-+-+ My question is that if I used JDBC like this: String sqlcmd = select min(date_time), max(date_time) from table_A; Statement Stmt = conn.createStatement(); ResultSet RS = Stmt.executeQuery(sqlcmd); How should I get the values, like this? start_time=RS.getString(1); end_time=RS.getString(2); or start_time=RS.getString(min(date_time)); end_time=RS.getString(max(date_time)); You could do String start_time = RS.getString (1) ; but you would be much better advised, in my opinion, to do java.sql.Date start_time = RS.getDate (1) ; which then allows you to use all the Java library's excellent date handling features. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Not finding customers without invoices
Brian Dunning [EMAIL PROTECTED] wrote on 02/11/2005 16:22:29: I'm trying to find a list of customers including a count of all their invoices, but it's not including customers who have no invoices - and it should. What's broken? SELECT customers.company, count(invoices.id) as invcount FROM customers, invoices WHERE customers.id= invoices.customer_id GROUP BY customers.id ORDER BY customers.creation desc You need what is called a LEFT JOIN, which enforces that every record on the left of the join must appears even if there is no record on the right side. In this case the right side is filled with nulls I think the SQL would be: SELECT customers.company, count(invoices.id IS NOT NULL) as invcount FROM customers LEFT JOIN invoices ON customers.id= invoices.customer_id GROUP BY customers.id ORDER BY customers.creation desc But read up on LEFT JOINs. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: String insertion
Andreas Steichardt [EMAIL PROTECTED] wrote on 24/10/2005 10:34:08: Hi! I just hit something really strange which is either a bug in MySQL or a dumbness bug of me. I am trying to insert a string ending with a simple space and i really want this space at the end of my string ;). Unfortunately MySQL kills this whitespace when inserting into normal (var)char columns: mysql create table test_strings (foo_1 varchar(255),foo_2 char(255),foo_3 text,foo_4 blob); Query OK, 0 rows affected (0.00 sec) mysql insert into test_strings set foo_1=' test ',foo_2=' test ', foo_3=' test ',foo_4=' test '; Query OK, 1 row affected (0.00 sec) mysql select length(foo_1),length(foo_2),length(foo_3),length(foo_4) from test_strings; +---+---+---+---+ | length(foo_1) | length(foo_2) | length(foo_3) | length(foo_4) | +---+---+---+---+ | 5 | 5 | 6 | 6 | +---+---+---+---+ 1 row in set (0.00 sec) Is this a feature or am i missing something. text would do it for mebut it is a total waste of space. Any ideas? This is a feature of VARCHAR in MySQL V4 and before. It is fixed in 5.0.3. The Manual ( http://dev.mysql.com/doc/refman/5.0/en/char.html ) suggests using BLOB or TEXT instead of VARCHAR to avoid this behaviour in earlier versions. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Clarification required
[EMAIL PROTECTED] wrote on 21/10/2005 12:28:18: Hi, I had installed a free version of mysql database software in one of the servers that are available to me. I had installed the mysql in the c:/ folder, is it possible for me to change the data storage location from C:\mysql\data\ to D:\mysql\data. ( i.e., changing the drive location from c:\ to d:\) Yes, you can change the place data is stored. This is usually done my setting the value of mysql-data-dir in the my.ini file, which will probably have been setup by your installation. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function to show when a field's value was last updated
news [EMAIL PROTECTED] wrote on 17/10/2005 15:45:15: I need it some info to help a client defend against a legal challenge. Is there a MySQL function that will allow me to ascertain the date and time that a particular field's value was last updated. I can't find anything in the MySQL documentation. It is almost certainly not possible. If you look in the manual for the storage space occupied by each field, you will see there is no space to store any form of timestamp. Since MySQL does not store the data you want, it cannot extract it for you. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Raw devices and MySQL
Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote on 14/10/2005 03:28:15: Hi Listers, Does anybody know if the MyISAM engine (apart from InnoDB) allows the use of raw disk space rather than having cooked files? If not, is this feature likely to be included in a future release? I had a quick scour of the MySQL website but can't seem to find a page with upcoming features. Is there such a beast? Given the way MyISAM works, I would have thought it very unlikely that this would ever happen. It would mean the SQL team developing their own special-purpose file system. Why bother, when they already have such a file system, called InnoDB? It is difficult to see what gain there would be for investing a very large amount of effort which could probably better spent elsewhere. As I understand it, the gains of using raw devices with InnoDB are, while not zero, small. Why do you want such a feature? Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.13 lint?
Hugh Sasse [EMAIL PROTECTED] wrote on 13/10/2005 16:27:44: I'm fairly new to MySQL and am getting an error messages like: ERROR 1064 (42000) at line 5: 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 'id int(14) unsigned NOT NULL auto_increment, forename varchar(40) NOT NU' at line 2 neelix hgs 18 % So it doesn't tell me exactly where, or what the nature of the syntax error is (and it can't even tell me it is version 4.1.13 which I know already). It has truncated the second line, so it's not that the rest is missing. My editor's syntax highlighter doesn't show anything awful. This is actually for lines 7 and 8 of the input, the first 4 lines being comments, so the numbering in the output is wrong. Are there any tools (like lint for C) to be more verbose and helpful about this? No, I don;'t think there are any such tools. When you get this sort of message, the error is nearly always *just before* the quoted bit. Which means that you have to get hold of the full command line that you sent and find out what immediately preceded the characters it has given as an error. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to avoid redundancy between PK and indices ?
C.R. Vegelin [EMAIL PROTECTED] wrote on 04/10/2005 12:52:01: Hi List, Is anyone familiar with optimizing indices, including primary key ? I do have a large myisam table with 6 non-unique key fields, lets say named A, B, C, D, E and F. Each of these columns may have NOT NULL values from 0 to 999, and are defined as SmallInt. Requirement: each row must have a unique combination of these 6 key fields (all Btree) ! To force uniqueness on this table, I can define a PRIMARY KEY (A, B, C, D, E, F); But I suppose that MySQL makes a separate (physical) index for the primary key, besides the 6 member indices. I do not think this is true. If you specify an index, be it primary or not, there is only one index. As you describe it, none of the separate columns is a candidate for a primary key, visible or otherwise, because none of them is of itself unique. And apart from uniqueness, this primary key does not have any added value for programming purposes. Right ? I believe that if you have InnoDB tables, searching by the primary key is likely to be significantly faster than searching by secondary keys. My question: does MySQL allow some kind of virtual primary key, where uniqueness is enforced by MySQL by checking its member indices ? In MyISAM tables, the Primary key, or UNIQUE keys have no other function than this. In structure, a primary key is no different to any other key. At this moment my table has more than 13 million rows (about 1100 MB Data_Length). And the Index_Length is about 500 MB, for the 6 indices and the primary key, consisting of these 6 indices. In this case a virtual primary key could save maybe 200 MB in stead of a real primary key index and could speed up the updating processes. I like to hear from you. I think you are wrong in your presumption that there are individual indexes. Consider a telephone directory: this may be regarded as indexed on FamilyName, GivenName. There is only one index even though it is over two fields (the order in the telephone directory). You would only need another index if you wanted to search over GivenName,FamilyName. This would then require an extra index, which would have to be put in the back. Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to avoid redundancy between PK and indices ?
I agree that if you want to do the searches you describe, you will need extra keys. But MySQL does *not* create these keys automatically - it creates the keys you ask for *and no more*. You have asked, correctly, for a primary key on ABCDEF. MySQL will create exactly that key and no others. This will allow you to search on A, AB, ABC etc. If you want a separate search on B, or on D, or on F, *you* must request individual indexes on these columns. MySQL does not implicitly create hidden indexes for you; there is exactly one index for each PRIMARY KEY/UNIQUE/INDEX. This, of course, produces redundancy; but only the redundancy that you request. Without these redundant indexes, a search on B will be forced to do a full table scan. And you *need* those indexes to do the searches you want. Without them, all searches become full table scans. And without a PRIMARY KEY index, every insert would have to include a full table scan. Alec C.R. Vegelin [EMAIL PROTECTED] 04/10/2005 15:10 To mysql@lists.mysql.com cc Subject Re: How to avoid redundancy between PK and indices ? Hi Alec, Thanks for your comment. Well, we disagree on a few points. Suppose I have a table with columns CountryID, CompanyID, SectorID and ProductID. And let's say that all these columns are NOT NULL, but indexed as non-unique. I need to select on specific countries, specific products etc. So I need 4 separate indices, where CountryId may occur more than once in the CountryId index, CompanyID may occur more than once in the CompanyID index etc. But if these 4 columns together are defined as Primary Key, then each combi of CountryID, CompanyID, SectorID and ProductID is unique. In my point of view this can only be realized with a separate PK index, leading to redundancy in the indices. My theory is backed by what I read in the manual ... if I read it right ... If you use ALTER TABLE on a MyISAM table, all non-unique indexes are created in a separate batch. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. These 2 features can only be realized if MySQL uses separate indices for non-uniques and for PK's. Don't you think ? Regards, Cor From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: How to avoid redundancy between PK and indices ? C.R. Vegelin [EMAIL PROTECTED] wrote on 04/10/2005 12:52:01: Hi List, Is anyone familiar with optimizing indices, including primary key ? I do have a large myisam table with 6 non-unique key fields, lets say named A, B, C, D, E and F. Each of these columns may have NOT NULL values from 0 to 999, and are defined as SmallInt. Requirement: each row must have a unique combination of these 6 key fields (all Btree) ! To force uniqueness on this table, I can define a PRIMARY KEY (A, B, C, D, E, F); But I suppose that MySQL makes a separate (physical) index for the primary key, besides the 6 member indices. I do not think this is true. If you specify an index, be it primary or not, there is only one index. As you describe it, none of the separate columns is a candidate for a primary key, visible or otherwise, because none of them is of itself unique. And apart from uniqueness, this primary key does not have any added value for programming purposes. Right ? I believe that if you have InnoDB tables, searching by the primary key is likely to be significantly faster than searching by secondary keys. My question: does MySQL allow some kind of virtual primary key, where uniqueness is enforced by MySQL by checking its member indices ? In MyISAM tables, the Primary key, or UNIQUE keys have no other function than this. In structure, a primary key is no different to any other key. At this moment my table has more than 13 million rows (about 1100 MB Data_Length). And the Index_Length is about 500 MB, for the 6 indices and the primary key, consisting of these 6 indices. In this case a virtual primary key could save maybe 200 MB in stead of a real primary key index and could speed up the updating processes. I like to hear from you. I think you are wrong in your presumption that there are individual indexes. Consider a telephone directory: this may be regarded as indexed on FamilyName, GivenName. There is only one index even though it is over two fields (the order in the telephone directory). You would only need another index if you wanted to search over GivenName,FamilyName. This would then require an extra index, which would have to be put in the back. Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Double indexes on one field
Yannick Warnier [EMAIL PROTECTED] wrote on 03/10/2005 11:18:05: Hi all, Using PhpMyAdmin, I seldom get the warning message: PRIMARY and INDEX keys should not both be set for column `ID` I understand its meaning, but I was wondering to what extent having a field indexed AND being a primary key might slow down/speed up my queries. Is that gonna take twice the time if I am searching on the ID field, just because there are two indexes? I'd like to have a rough idea of how serioulsy I need to avoid these. It will not slow down your searches at all, but it will slow down your inserts. Since a PRIMARY KEY is a UNIQUE index that happens to have been declared to be primary, you are simply storing the same information twice. I cannot think of any possible benefit in having two identical indexes on a table, and there is a cost to maintaining two index trees. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice Required
Vinayak Mahadevan [EMAIL PROTECTED] wrote on 27/09/2005 04:55:13: I am creating an application in Visual Basic 6.0 which will require a centralised database server. All this while I had been planning to use MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms but not for an enterprise level rdbms. So I am planning to use MySQL as the backend for the application. What should be the minimum system requirement to run the database on. MySQL can run on almost nothing. The question is not what system you need, but what performance you want. I think you *could* run MySQL on a P200, Win 98, 128Mb ram, 40Mb disc. But the performance you would get would be seriously disappointing. You need to think what size of database you want, how many queries and updates per second you will need, and how complex your queries will be. However, since MySQL is freely available, why not just download it, install it on your development machine, and run a few tests. The only real measurement of performance is actual tests: predictions often err, both high and low. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice Required
Vinayak Mahadevan [EMAIL PROTECTED] wrote on 27/09/2005 11:28:51: [EMAIL PROTECTED] wrote: Vinayak Mahadevan [EMAIL PROTECTED] wrote on 27/09/2005 04:55:13: I am creating an application in Visual Basic 6.0 which will require a centralised database server. All this while I had been planning to use MS-Access. But then I found out that MS-Access is ok to be a desktop rdbms but not for an enterprise level rdbms. So I am planning to use MySQL as the backend for the application. What should be the minimum system requirement to run the database on. MySQL can run on almost nothing. The question is not what system you need, but what performance you want. I think you *could* run MySQL on a P200, Win 98, 128Mb ram, 40Mb disc. But the performance you would get would be seriously disappointing. You need to think what size of database you want, how many queries and updates per second you will need, and how complex your queries will be. Thanks for the response I am planning to run it on a x205 series IBM Server which right now has 256 mb ram but will be upgraded to 1 gb. And the maximum number of connections at any point of time will be say around 10 That sounds reasonably competent hardware. But it is not the number of connections that matters, it is the number and complexity of queries. One connection can generate a massive query which will lock out others; idle connections consume a small amount of memory but no other resources. I can only suggest you try to set up a representative test load and see if the performance is adequate for you. If performance is not adequate, after having examined your slow queries carefully and checked your indexing, extra ram is the first hardware upgrade to do. However, I think newcomers to MySQL are frequently surprised by its performance once properly indexed; don't spend money on extra ram until you have tried out a real (or simulated) test. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Circular Replication
Sid Lane [EMAIL PROTECTED] wrote on 19/09/2005 15:02:58: stupid ?: what keeps them from getting caught in a write loop? turning off log_slave_updates? I had never thought of this but is has intriging possibilities... Each update is marked with the unique server id of the server which originated it. When the update returns to its originating server, it is dropped instead of being executed. That is why every server must have a unique id. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Myisam or innodb
Scott Haneda [EMAIL PROTECTED] wrote on 15/09/2005 10:31:48: I have moved my data from mysql 3 to 4, across various updates, over the past few years. I use phpmyadmin generally, and it defaults to making tables myisam. I can not seem to find a really clear answer as to why I want to use one over the other. Generally, I use myisam and change it only when I need to do a rollback or something like that. Since I seem to be able to toggle from myisam to innodb without any adverse affects, why would one chose one over the other? Is there a way to prevent the use of myisam if it is old and slowly being deprecated? I am a host and do not control the table structure of clients databases. Innodb does not yet support FullText. While I believe that there are circumstances in which InnodDB is faster, I think that many applications will find MyIsam faster.\\ I don't thing InnoDB supports Load Data From Master, making adding a replication slave harder. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Talking Limit
'Yemi Obembe [EMAIL PROTECTED] wrote on 08/09/2005 10:33:25: Talking limit (in select query), does it limit the search result after ordering according to relevancy and the likes, or before? thanks LIMIT operates after ORDER BY. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SCO issue
__ Mirza [EMAIL PROTECTED] wrote on 05/09/2005 14:31:12: pissed I would like someone from MySQL AB to clarify issue with SCO asap. I wouldn't like to use technologies for my business that later could be used against me (in legal sense). Does MySQL AB understand that it helps funding their legal cases against us (GPL users) ? If someone feels OK with SCO partnership, good luck, but (being long time MySQL user and alpha bug reporter) I would switch to Embedded PostgreSQL myself and encourage other people to do the same. I use _tons_ of GPL software so should I help funding of my own annoyance (albeit poorly supported with facts) ? /pissed All the press releases I have seen appear to originate from SCO. There is not, in any of them, any suggestion that money has passed or will pass from MySQL to SCO. SCO has for a long time been one of the many varieties of Unix that MySQL supports. MySQL cannot stop SCO from distributing their product (hypocritically) under the GPL. On the other hand, if they allow SCO to include offical releases of MySQL, they may get some support customers - which is where they earn their real income. The press blurbs are essentially saying that SCO resellers will market MySQL Network - to the benefit of MySQL. If there is any money flow, I would have thought it would be more likely to be the other way: SCO paying MySQL to ensure that one of the premier Unix applications remains supported on their platform. Of course, MySQL may say otherwise, but I think this is a piece of SCO hype intended to imply MySQL support of SCO when all they are really doing is supporting their own product on whatever platform their customers may choose - even when that platform is marketed by a company who many of us find totally repulsive. If you let yourself be hyped into dropping MySQL, you will be harming a company that is, in my opinion, a model of how to provide full commercial quality software (or better) with an Open Source licence, while not (I think) harming SCO in any way. Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: migrating from Postgres to MySQL
Wiebe de Jong [EMAIL PROTECTED] wrote on 02/09/2005 17:32:07: I used mixed case in naming my schemas, tables and fields in Postgres 7.1. (i.e. onDemand.callDetailRecord) Now I have to move my app over to MySQL. What are the best practices for naming in MySQL? Definitely use lower case only for databases (schemas) and tables. Since (for MyIsam at least) these map on to the underlying filesystem, which is cases sensitive for Unixes and case independent for Windows, you avoid a lot of trouble if you stick to lower case. Fields are, I think, case insensitive throughout, so do as you will. Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Collation ?
m i l e s [EMAIL PROTECTED] wrote on 30/08/2005 15:28:31: Hi, Ive noticed that my ALL my databases and tables have latin1_swedish_ci as the collation...h that wouldn't be so bad except that I didn't set it that way by default, and I don't speak swedish. Not that swedish isn't a fine language, or sweden isn't a fine country (although Ive never been there) so I hear. However, that's NOT what I want. 1.) What should the db collation be set to if I am in the USA - latin_1_bin ? 2.) and would doing so cause a problem that anyone can think of ? I think the answer is that if you are in the USA, any of the latin collations will do equally well. The will only cut in when you use characters outside the range 32-126, which you will not do in US English. MySQL defaults to Swedish, I would guess, because that handles its two largest customer groups: English and Swedish speakers. You can set the collation to latin1_bin. Chapter 10 of the manual tells you many ways to do this (command line, my.ini, per database, per table). If you choose to change it, rather than blindly changing it to bin, you should perhaps consider you real needs. For example, might not a Spanish collation serve better than a binary one? Many Americans speak Spanish, few binary. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
Hal Vaughan [EMAIL PROTECTED] wrote on 24/08/2005 17:41:36: # Okay, so INSERT IGNORE only works if I am avoiding duplicate keys. Is there any way to use INSERT the way I thought INSERT IGNORE worked -- in other words is there any keyword for the INSERT command to keep it from duplicating rows if there isn't a key? I don't think so. But may I inquire why you do not want to have a key? What you are saying is How can I do a job without using the tool designed for the job?. If there is no key, in order to do what you want, MySQL would have to do a linear search through the table in order to check for duplicates - the kind of lengthy operation it is designed to avoid whenever possible. The key is a necessary part of the effect you want to achieve. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
The insert will only be bounced where you specify the columns as unique. Thus you need either separate UNIQUE indexes on Name and Value, if you want them to be individually unique, or a single joint UNIQUE index if you want them to be jointly unique but separately duplicable. The INSERT command only checks columns that is instructed are to be unique. The purpose of the IGNORE modifier is simply to ignore the error produced when a duplicate occurs. Alec Hal Vaughan [EMAIL PROTECTED] 24/08/2005 07:47 Please respond to [EMAIL PROTECTED] To mysql@lists.mysql.com cc Subject INSERT IGNORE Doesn't Seem To Work I may have a misunderstanding of this, but as I have been told, if I have a table with 3 columns, Idx (an Index column, unique, auto-increment), Name, Value (both varchar), and I try a command like this: INSERT IGNORE INTO myTable SET Name = Variable1, Value = 100; or INSERT IGNORE INTO myTable (Name, Value) VALUES(Variable1, 100); AND I already have a row with the matching Name and Value columns matching in value, that MySQL will detect that and not insert the redundant values. I've also tried this without a unique, auto-increment column, just trying to insert by specifying values for all 3 columns that already match an existing row, and it still doesn't work. I thought the IGNORE keyword was intended to be used to prevent duplicating values, and that it matched the values in the INSERT statement (even if not all columns in the table were given a value) against the ones in the table and would NOT INSERT the row if it matched. I'm using MySQL 4.023 on Debian Linux (installed through apt-get, not through downloading). So this brings up a few questions: 1) Am I doing something wrong? 2) Is this what INSERT IGNORE is supposed to do -- if not, what does it do?, and 3) If this isn't what INSERT IGNORE does, how can I do what I *thought* it did -- insert only if the value doesn't already exist? Thanks! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext max size
Yui Hiroaki [EMAIL PROTECTED] wrote on 24/08/2005 10:57:20: I created table for fulltext. I publish SQL: sqlcreate table (test title longtext)TYPE=MyISAM; sqlalter table test add fulltext title (4); But title is so small to insert text. what biggest text I can insert title column? When I publish SQL: alter table test add fulltext title(4294967295); I got a error??? What do you expedt the number in brackets to do? I cannot find any documentation on it, but if anything I would expect it to be maximum length of a single word in the indexed column. You surely cannot expect to get a word 4294967295 characters long. Indeed, if you expect a word 4 characters long, I think you are using the wrong tool. I think your coad should read: sqlcreate table (test title longtext)TYPE=MyISAM; sqlalter table test add fulltext (title); Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Partial Filtering
Blue Wave Software [EMAIL PROTECTED] wrote on 18/08/2005 15:57:34: I'm having one of those slow brain days. I want a partial filter egg. All records where field1 begins with ABC any body know the where clause to do this. In Access it's where field1 = 'ABC*' but I can't find the MYSQL equivalent, or isn't there one. WHERE field1 LIKE 'ABC%' ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complex query. (It's killing me)
Just in the spirit of refining my own skills, here is how I would tackle
the problem. It parses, but I haven't populated the tables so I don't know
if it works:
SELECT s.dateshipped, COUNT(r.type=undelivered), COUNT(r.type =
customer), COUNT(r.status=open)
FROM shipments s JOIN returns r ON s.id = r.id
GROUP BY s.dateshipped
ORDER BY s.dateshipped DESC ;
Does this do anything worthwhile?
James M. Gonzalez [EMAIL PROTECTED]
12/08/2005 16:16
To
mysql@lists.mysql.com
cc
Subject
Complex query. (It's killing me)
Hello list, I got a sql query that's is just beating me (5-0). Have have
read here and there, and MySQL Query Browser is just fed up with all the
tries I have made it do. It is just not working.
First, the tables (simplified version, if need more info just tell me):
[shipments]
ID int
DateShipped DATE
[returns]
ID INT
DateReturned DATE
Type ENUM('undelivered','customer')
Status ENUM('open','close')
Second, the expected result:
Shipped Undelivered Returned Open
12/8/2005 143 3 3
11/8/2005 131 1 1
10/8/2005 223 8 7
09/8/2005 169 5 6
08/8/2005 283 6 7
Explanation of field columns:
Shipped: rows from [shipments] that have the field 'DateShipped'
populated.
Undelivered: rows from [returns] that have Type = 'undelivered'
Returnded: rows from [returns] that have Type = 'customer'
Open: rows from [returns] that have Status = 'open'
Third, the sql query I have came out with so far is (my non-working best
solution)
SELECT
shipments.DateShipped,
SUM(CASE
WHEN shipments.DateShipped IS NOT NULL THEN 1
ELSE 0
END) AS shipped
SUM(CASE
WHEN returns.DateReturned='undelivered' THEN 1
ELSE 0
END) AS undelivered,
SUM(CASE
WHEN returns.DateReturned='customer' THEN 1
ELSE 0
END) AS returned,
SUM(CASE
WHEN returns.Status='open' THEN 1
ELSE 0
END) AS open
FROM shipments, returns
GROUP BY shipments.DateShipped
ORDER BY shipments.DateShipped DESC
LIMIT 5
That's it. Im SO stuck with this query. I would really really appreciate
any help, hints, links, or ideas about it.
James G.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complex query. (It's killing me)
Sorry - I think you need a LEFT JOIN or it won't count shipments which are
not returned.
Alec
[EMAIL PROTECTED]
12/08/2005 16:38
To
[EMAIL PROTECTED]
cc
mysql@lists.mysql.com
Subject
Re: Complex query. (It's killing me)
Just in the spirit of refining my own skills, here is how I would tackle
the problem. It parses, but I haven't populated the tables so I don't know
if it works:
SELECT s.dateshipped, COUNT(r.type=undelivered), COUNT(r.type =
customer), COUNT(r.status=open)
FROM shipments s JOIN returns r ON s.id = r.id
GROUP BY s.dateshipped
ORDER BY s.dateshipped DESC ;
Does this do anything worthwhile?
James M. Gonzalez [EMAIL PROTECTED]
12/08/2005 16:16
To
mysql@lists.mysql.com
cc
Subject
Complex query. (It's killing me)
Hello list, I got a sql query that's is just beating me (5-0). Have have
read here and there, and MySQL Query Browser is just fed up with all the
tries I have made it do. It is just not working.
First, the tables (simplified version, if need more info just tell me):
[shipments]
ID int
DateShipped DATE
[returns]
ID INT
DateReturned DATE
Type ENUM('undelivered','customer')
Status ENUM('open','close')
Second, the expected result:
Shipped Undelivered Returned Open
12/8/2005 143 3 3
11/8/2005 131 1 1
10/8/2005 223 8 7
09/8/2005 169 5 6
08/8/2005 283 6 7
Explanation of field columns:
Shipped: rows from [shipments] that have the field 'DateShipped'
populated.
Undelivered: rows from [returns] that have Type = 'undelivered'
Returnded: rows from [returns] that have Type = 'customer'
Open: rows from [returns] that have Status = 'open'
Third, the sql query I have came out with so far is (my non-working best
solution)
SELECT
shipments.DateShipped,
SUM(CASE
WHEN shipments.DateShipped IS NOT NULL THEN 1
ELSE 0
END) AS shipped
SUM(CASE
WHEN returns.DateReturned='undelivered' THEN 1
ELSE 0
END) AS undelivered,
SUM(CASE
WHEN returns.DateReturned='customer' THEN 1
ELSE 0
END) AS returned,
SUM(CASE
WHEN returns.Status='open' THEN 1
ELSE 0
END) AS open
FROM shipments, returns
GROUP BY shipments.DateShipped
ORDER BY shipments.DateShipped DESC
LIMIT 5
That's it. Im SO stuck with this query. I would really really appreciate
any help, hints, links, or ideas about it.
James G.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newb learner question
Bob Rea [EMAIL PROTECTED] wrote on 28/07/2005 18:19:34: I am learning MySQL from an older book, and some of the examples it give do not work in MySQL, so I am going to ask for help on those. select cust_contact from Customers where cust_contact like '[JM]%'; returns Empty set (0.00 sec) What is the right way to do this? Likewise: mysql select prod_name from Products where not vend_id = 'DLL01' order by prod_name; Empty set (0.00 sec) You have to give more information about what your database actally contains, and why you expected non-null results from those queries. Are you sure that your customers table contains a customer whose name starts [JM] ? Both commands look perfectly sensible to me. If your tables are small, post the results of Select * from customers ; or Select * from products ; Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to sort results with german umlauts in a UTF8 MySQL Database
Nico Grubert [EMAIL PROTECTED] wrote on 22/07/2005 09:06:25: Hi there, I have a MySQL 4.1 DB running including a database whose character set is set to utf8. In the database I have a table tblmembers with some records containing german umlauts. How do I sort results with german umlauts if the database character set is set to utf8? According to http://dev.mysql.com/doc/mysql/en/charset-unicode-sets.html you might achieve the effect you want by setting the collation to utf8_unicode_ci Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple indexes on same column
Andrea Gangini [EMAIL PROTECTED] wrote on 22/07/2005 10:17:34: I have a column in one of my table, which: - must be unique - must be indexed because almost all queries are SELECT .. WHERE COLUMN LIKE I created two indexes this column, one of type UNIQUE and a normal one, because I thought that the unique index and a normal indexes were different... is it really so? Could I use only the UNIQUE index also for speeding up the queries? There is no difference between unique and non-unique indexes at search time, so there is no point in having two indexes. The only difference is at insert time, when the unique index enforces the uniqueness rule. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: use of indexes
The system cannot used the index on field2 because it is the second half of the index in both cases, and it can only use indexes in order. It cannot use the separate indexes on field 1 and field 2 because the are ORred together. If you rephrase your query SELECT * from table WHERE field2 LIKE 'VALUE2% AND ((field1 = 'VALUE1') OR (field3 = 'VALUE3')) ; it becomes obvious that an index on field2 will be used, followed by searches of the results field1 and field3 . As a matter of interest, what numbers of hits do you expect on each of the three terms separately? If the field2 hit is is pretty selective, it does not really matter what the others do. Alec Chris Faulkner [EMAIL PROTECTED] 22/07/2005 12:46 Please respond to Chris Faulkner [EMAIL PROTECTED] To mysql@lists.mysql.com cc Subject Re: use of indexes Hi field2 is indexed. I have 2 indexes. One is on field1 and field2, the second indexes field3 and field2. You mean a separate index which only indexes field2 ? Ithought that the type of query I am doing is a good reason for doing composite indexes. Chris On 7/22/05, Eugene Kosov [EMAIL PROTECTED] wrote: Chris Faulkner wrote: HI I have a query like this select * from table where ( ( field1 = 'VALUE1' and field2 like 'VALUE2%' ) OR ( field3 = 'VALUE1' and field2 like 'VALUE2%' ) ) I have created two composite indexes - one on field1 + field2 and one on field3 + field2. Explain on the SQL indicates that the indexes are possibly used. The query takes an age to run and looking at my log indicates a full table scan. I have also tried indexing just field1 and field3 separately but this doesn't help. I have run an analyze. Chris Mysql use an index only if indexed field(s) present(s) in both OR arguments.. Sorry, but i can't find it in docs right now, so i can't give you any helpful link. I think index on field2 may help you here.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary Key question
Haisam K. Ido [EMAIL PROTECTED] wrote on 01/07/2005 15:04:01: I've created the following table (server 4.1 in win2k) CREATE TABLE `os` ( `id` tinyint(10) NOT NULL auto_increment, `name` varchar(255) NOT NULL default '', `description` varchar(255) default NULL, PRIMARY KEY (`id`,`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; and was very surprised that I can do the following twice. Should'nt this be rejected since name is a primary key ad has already been used? INSERT INTO os (name,description) VALUES ( 'winxp','winxp'); No. What you have requested is that the combination of id AND name be unique. Since id is auto-increment, every record will be unique unless you manually force the id to an old value. I guess you want the values to be separately unique, in which case you want PRIMARY KEY (id), UNIQUE (name) Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Avoiding full table scans
David Kagiri [EMAIL PROTECTED] wrote on 30/06/2005 09:44:11: Hi I our database there is one table that will grow into tetrabytes within a short time. I would like to know how i can reduce full table scans. I have used separate tables as per region for now but the problem is if i create a new table i am forced to recompile the application. I can easily avoid this by using one table and an extra field to flag which region a paricular transaction happened.However i need ideas of how i can avoid full table scans because slow queries are unacceptable. it will also give me more flexibility in writing reports I will gladly appreciate any links that are specific to this problem and case studies.There people who need to be convinced The answer to your question is Indexes. Ensure that you have indexes on your tables for all the different searches you do. Use the EXPLAIN command to find out which SELECTs are doing full table scans, and add Indexes as appropriate. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Records which link to other records in same table
news [EMAIL PROTECTED] wrote on 30/06/2005 16:47:43: I'm designing a simple family tree db which is at present just a flat table In which each record everyone has a father mother, a variable number of wives,and variable number of children. The links to other family tree members is always the record_id of another record. At present I'm manually inserting the links eg my father is record_id 52, my mother recordid 60 my children records 100,101,102 I can write queries to display everyone's father mother children etc no problem. * But My question is Is it possible/useful to define any sort of relationships/linking the wife/father/mother/children fields and the recordid of other records I'm fogging on this, your advice pls This is a link, previously recommended on this list, which I have found very useful: http://www.sitepoint.com/article/hierarchical-data-database Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create unique index
Scott Purcell [EMAIL PROTECTED] wrote on 28/06/2005 14:36:37: Hello, I am reading the docs, but I am slightly confused. I have a table with a varchar(50) column (not a primary column) where I do not want duplicates. It is a properties column, and I am getting duplicates inserted, which is causing problems in my display. An Oracle DBA that works with me suggested creating a unique index on the column. I am reading the docs here: http://dev.mysql.com/doc/mysql/en/create-index.html but I am not have a clear understanding of an index, so I am having trouble visualizing what I need to do. The column already exists. Your DBA is correct: you need to add a UNIQUE index onto the column. MySQL has no way of knowing that you want a column to be unique unless you tell it so. And if you want it to be unique, you have to create an index so that MySQL can do a fast lookup to see if the column already exists before adding a new one. If you did not have an index, MySQL would have to search the entire table to check for duplicates on each insert - an intolerably slow operation. So you need a UNIQUE index. It is very easy to add an index to an existing table: ALTER TABLE properties ADD UNIQUE(property) ; but you must get rid of the duplicates first - it cannot create a UNIQUE index where duplicates exist. Also, when you make a column unique, you must consider what the software that inserts records is to do if it encounters a duplicate. You may need, for example, to convert your INSERT commands into REPLACE (see manual). I am running 4.0.15 on a PC. The current column type is: MyISAM. I am not sure if that is proper or not. Its usage is for a web-site. Here is what I created a while back: CREATE TABLE PROPERTIES ( property varchar(50), value varchar(200), description varchar(200) ) TYPE=MyISAM; Also, if this is doable, can I also create an index across two columns? I have another situation where I need a combination of two columns to be unique. Yes, you can - and it is the correct thing to do in this case. ALTER TABLE my_table ADD UNIQUE index_name (col_1, col_2) ; If, as you say, you do not have a clear visualisation of an index, I suggest that you should attempt to acquire one fast. Indexing is absolutely central to database programming and no-one should be writing database access software without understanding it. The concept is not very complex. You are probably familiar with indexes in the back of reference books: a list of words drawn from the text of the book is listed in alphabetic order, and each entry gives the page number(s) upon which you find those words. So that if you want to find a word, look it up in the index and then turn straight to the right pages, rather than having to read the entire book to find the reference you want. A database index is the same, except that it indexes every word in a particular column, and the page number it looks up is the databases hidden internal reference to the record which contains the indexed word (or number, or date, or ...). The marvellous thing is that you just tell MySQL you want an index, and MySQL magically creates and maintains the index, then uses it when appropriate to speed up database searches. Of course, maintaining an index takes extra CPU and disk power, so the database will not build an index unless you ask for it, which you should only do for columns used in WHERE clauses. But once created, the rest happens by magic (or rather, courtesy of the skills of the MySQL engineers). Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about field length for integer
[EMAIL PROTECTED] wrote on 27/06/2005 16:33:44: Are you actually saying that you have a database with more than 1.8e+19 records in it? I don't think you do. If you were to add records at the rate of a million a second, which is, I think, beyond the capabilities of any foreseeable future hardware and software, it would still take half a million years to add that number of records. It is therefore fairly easy to deduce that the OP has not got, and will not have within any of our lifetimes, a database that big. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql overall stability
Zachary Kessin [EMAIL PROTECTED] wrote on 22/06/2005 09:12:22: Martijn van den Burg wrote: Stephane, I've was wondering if anyone is using MySql as their main ERP production database, if so how stable and reliable is it? SAP and MySQL have teamed up to certify (part of?) SAP's R/3 ERP system on MaxDB. Check: http://searchsap.techtarget.com/originalContent/0,289142,sid21_gci967139 ,00.html. The page is a year old, and I have no idea how far they have progressed by now. -- Martijn ASML ITMS Application Support / Webcenter What is the relationship between mysql and maxdb. (thats Mysql the software not the company). Are they basicly the same software or is Maxdb a totally different thing? MaxDB is a totally different thing to the main MySQL Database, though MySQL AB is atring to converge the SQL dialects to make them more interchangeable. See http://dev.mysql.com/doc/mysql/en/maxdb-history.html To respond to the original question, I would not know about ERP in particular, but a lot of people are using MySQL in demanding, mission critical systems. I think most users would classify the production versions of MySQL as very stable indeed. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concorrent users
'Yemi Obembe [EMAIL PROTECTED] wrote on 17/06/2005 09:40:39: Hi all, just want to know if there is a specific number of concorent users dat can query from a mysql databasee at d same time. MySQL has a configurable limit to the number of simultaneous connections that it can support. See http://dev.mysql.com/doc/mysql/en/server-system-variables.html This can be increased provided you have the system resources to handle the increased number of connections. On a finer grain, MySQL will interleave simultaneous queries, subject to table locking, as they pause requiring disk access. However, once they are performing memory-locked operations, a single query will lock a CPU. On multi-CPU machines, it will generally run queries in parallel on the separate CPUs. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query..
Cory Robin [EMAIL PROTECTED] wrote on 16/06/2005 08:09:22: I need to speed up a search, big time. I have an application that searches for records on a date field. If it doesn't find an exact date match, it keeps searching adjacent days until it finds a certain amount of records. The problem now is, I'm using my application to loop through and run multiple queries and it's dog ass slow..I'm hoping that one of you SQL gurus can point me in the right direction to create a query that will work it out for me. Here's the logic the best I can explain.. I want to return a minimum of 15 records.. I'm searching for records on or around 2005-10-01 Select * from table_x where row_date = '2005-10-01' /* at this point if matched records are = 15 then simply return the records on that date.. If not..*/ Select * from table_x where row_date = '2005-09-31' Select * from table_x where row_date = '2005-10-02' And so on until it finds = 15 records or it searches through 5 days (+- 3 on search date) I hope this makes sense.. I'm new to all this stuff. Eventually I'm going to do the same thing for times as well.. Heres a suggestion: select * from table_x where row_date between date_sub(now(), interval 3 day) AND date_add(now(), interval 3 day) order by abs(time_to_sec(datediff(created, now( limit 15 ; This does times relative to now(), but I am sure you can generalise it. The first line specifies the desired fields The second selects (in principle) all the records within your largest target window The third orders them by closeness to your target time and the last says you only want 15 of them. This version is based on exact seconds from the target time (now() in my case): the version which works in whole days would only be slightly different. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query..
[EMAIL PROTECTED] wrote on 16/06/2005 16:29:46: [EMAIL PROTECTED] wrote on 06/16/2005 11:30:10 AM: Cory Robin [EMAIL PROTECTED] wrote on 16/06/2005 08:09:22: I need to speed up a search, big time. I have an application that searches for records on a date field. If it doesn't find an exact date match, it keeps searching adjacent days until it finds a certain amount of records. The problem now is, I'm using my application to loop through and run multiple queries and it's dog ass slow..I'm hoping that one of you SQL gurus can point me in the right direction to create a query that will work it out for me. Here's the logic the best I can explain.. I want to return a minimum of 15 records.. I'm searching for records on or around 2005-10-01 Select * from table_x where row_date = '2005-10-01' /* at this point if matched records are = 15 then simply return the records on that date.. If not..*/ Select * from table_x where row_date = '2005-09-31' Select * from table_x where row_date = '2005-10-02' And so on until it finds = 15 records or it searches through 5 days (+- 3 on search date) I hope this makes sense.. I'm new to all this stuff. Eventually I'm going to do the same thing for times as well.. Heres a suggestion: select * from table_x where row_date between date_sub(now(), interval 3 day) AND date_add(now(), interval 3 day) order by abs(time_to_sec(datediff(created, now( limit 15 ; This does times relative to now(), but I am sure you can generalise it. The first line specifies the desired fields The second selects (in principle) all the records within your largest target window The third orders them by closeness to your target time and the last says you only want 15 of them. This version is based on exact seconds from the target time (now() in my case): the version which works in whole days would only be slightly different. Alec Only one problem with your solution, LIMIT tells how many records AT MOST to return, he wants to get 15 AT LEAST and stop appending records once he gets over 15 total results. I can't seem to make a query (in reply to his problem) to return AT LEAST 15 rows without some sort of iteration or flow control involved in the process. Neither on of which is available in MySQL SQL until 5.0+. Since he didn't say which version he is using I am assuming a target version of 4.1 or less for the solution. Does anyone else have a non-scripted solution? That wasn't the way I read it And so on until it finds = 15 records or it searches through 5 days - within I interpret as wanting all the records within 5 days up to a limit of 15. I presume that if 15 records are found, those closes to the target time are preferred. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN?
Angelo Zanetti [EMAIL PROTECTED] wrote on 08/06/2005 17:06:51: Hi guys. I'm having a problem deciding whether a left join is suitable for what i want to do. I have two tables A Users -userID -isactive B BuddyList -userID -buddyID what i want to do is to get all the users from A that don't exist as a buddyID for a user (buddyList) also the user must be active (isactive=1) but i cant get the correct result. Is the LEFT JOINcorrect for this operation or should i try using the NOT EXISTS command? LEFT JOIN sounds right to me: SELECT a.* FROM a LEFT JOIN b ON a.userID = b.userID WHERE a.isactive = 1 AND b.buddyID IS NULL ; All A A's which are active and do not have a buddy. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help?
Digvijoy Chatterjee [EMAIL PROTECTED] wrote on 01/06/2005 17:13:25: Hello all, My question is if unix Epoch time started on January 1st 1970 ,and mysql uses the same implementation of time , what is the logic mysql developers have used to offset it by 30 odd years that is the max date for mysql is 2068 and not 2038 18th January, i work in a Financial services firm where its important to calculate mortgages beyond 2038 , Linux and Windows are caught in trouble ,rather my more general question is what effect does The Y-2038 bug have on MySQL Any sort of pointers will help You should be using the DATETIME column thpe for this sort of calculation, not the timestamp. DATETIME has a suorted range from 1000AD to AD, which should be enough for your purposes. TIMESTAMP is, as its name applied, mostly used for timestamping records at create time. It should not generally be used for extensive chronological calculations. TIMESTAMP almost always point to the past. MySQL wil therefore have to take some action before about 2060 (to allow users a few years to upgrade). The obvious thing would be to implement a 64-bit LONGTIMESTAMP. This will become easier in a few years when 64-bit OSs become mor the norm. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index Question in MyISAM
Dan Salzer [EMAIL PROTECTED] wrote on 16/05/2005 14:36:41:
I have the following table:
CREATE TABLE `Article_Search` (
`ArticleID` int(11) NOT NULL default '0',
`Content` text NOT NULL,
PRIMARY KEY (`ArticleID`),
FULLTEXT KEY `Content` (`Content`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
This table has several million rows, but I only want to search a subset
of
the table. IE:
SELECT * FROM Article_Search WHERE MATCH(Content) AGAINST('rubber
duckies' IN BOOLEAN MODE) AND ArticleID IN (100, 23, 223, 98, 4018,
1452,
91)
The reason I'm specifying a set of ArticleIDs is that I know any hits
are
going to be within those articles. So the presence of the IN() clause is
purely there for performance. However, an explain on this Statement
shows
that it is using the Full-Text index. Is mysql text-searching the entire
table under the hood, or does it use the PK to reduce the dataset before
the
text-search.
MySQL can only use one index at a time. So if it used the ArticleID index
and your IN clkause as the primary index, it would be reduced to doing the
MATCH() the hard way, line by line, in the articles returned by the IN
clause.
On the other hand, you know that the only articles which contain the words
that you specify, it will be doiing a relatively fast lookup in the
FULLTEXT index to get the same set of IDs that you are feeding it, or an
even smaller one (because some even of those will not contained in the
hits). the only case where the simply doing the FUULTEXT search would not
be as fast as you quote would be when one of the separate words rubber
or duckies has a very large number of hits but the phrase does not.
In sum, I wouldn't bother with this optimisation unless your search truens
out in practice to be slow.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SATA vs SCSI
Scott M. Grim [EMAIL PROTECTED] wrote on 12/05/2005 16:42:00: I've fairly extensively (although not necessarily scientifically) tested SATA 150 vs. SCSI U320 and find that if you're doing a lot of random reads and writes (such as with a database server), SCSI provides nearly 5x the performance as SATA so, for us, it's well worth the additional expense. It's also my experience that even the best SATA drives seem to be disposable. There's a huge difference in reliability and life expectancy between SATA and SCSI drives because they put a bit more quality into SCSI drives as they are expected to perform in an enterprise environment. With RAID arrays and hotswap bays, it's easy enough to deal with SATA's unreliability, but it's always best to not have to swap and rebuild because every failure has the potential to cause some cascade that can become devestating. I would concur with this. Having talked to drive manufacturers, they use the Scsi interface, which is not in itself significantly faster than Sata, as a marker for what you might call Professional grade drives. Components such as bearings etc. are built to a higher spec, head actuators are more powerful, buffers are bigger, more effort is put into optimising the drive's internal code to do better overlapping, there are more self diagnostics etc. As is usually true, you pay for what you get. While there might be a slight element of gouge in it, SATA drives are basically consumer-grade drives with a fast interface, which SCSI drives are what the manufacturers think of as professional grade. What are the warranties and MTBF on the SATA drives like? A year or so ago, the manufacturers drastically cut the warranties on their ATA drives, without changing the SCSI. Where to SATA fall in this spectrum? Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Row Numbers?
news [EMAIL PROTECTED] wrote on 10/05/2005 15:13:49: In article [EMAIL PROTECTED], [EMAIL PROTECTED] writes: Hi, Have you forgotten what's a primary key ? Using order by will sort data, and if it's already sorted, it willbe sorted again. Time, memory and maybe disk io. If MySQL really does that, I'd consider this a bug. I agree. MySQL knows if the search order implicitly delivers the data in the requested order, and skips the sort phase if so. Using the marco example, i gaved a solution considering iy's what he wants. Till now i don't know if it's ok or not. if so, just add : select * from temp order by Id LIMIT 3,4; if no, the primary key index will give you the order. I dunno what you're talking about, but definitely not MySQL 4.1.11: CREATE TEMPORARY TABLE tbl1 ( id INT UNSIGNED NOT NULL, val INT UNSIGNED, PRIMARY KEY (id), UNIQUE KEY (val) ); INSERT INTO tbl1 (id, val) VALUES (1, 1); INSERT INTO tbl1 (id, val) VALUES (2, 2); INSERT INTO tbl1 (id, val) VALUES (3, 3); INSERT INTO tbl1 (id, val) VALUES (4, 4); SELECT * FROM tbl1; DELETE FROM tbl1 WHERE id = 3; INSERT INTO tbl1 (id, val) VALUES (5, 5); SELECT * FROM tbl1; The first SELECT happens to return 1/2/3/4, but the second one returns for me 1/2/5/4. InnoDB would probably do this, but MyISAM probably woudl not. If it chooses to do a fill table scan, it will deliver the results iht the essentially random order it stores them. If it uses and index, it is qitel likely to deliver them in the order of that index - which may not be the primary key. Indeed, the optimiser theoretically might use different indexes for the same query on different days, as the table cnages. It is therefore *never* safe to assume any sort of ordering unless you specify it. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie: how to sort a database without extracting the data
MY first guess is that you simply need an ORDER BY field in yout (later, ad you put it) SELECT. However, the whole point of using a database such as MySQL is that you do not worry about how exactly your data is stored. there are many different tricks that a database can use to optimise both storage and retrieval, and it is the job of a good DBMS to implement as many of them as it can, and select the appropriate tricks to optimise your queries. However, in order to do this, you have to give it some hints, and the way you do this is by telling it to build indexes based on the fields which you intend to use for SLECTing data or for ORDERing, This allows the database to search un-ordered data in an ordered manner. The database automatically and invisibly maintains an index, or several indexes, on your data as you add and remove records. Once you have created the index, you need take no further action It sounds as if your field should be give a special kind of index called a PRIMARY KEY. This allows the database to ensure that entries ar unique, and to retrieve data very fast when selec ted or ordered by that column. You should search the MySQL manual (and poosibly the net) for PRIMARY KEY. I think you had the idea oc actually sorting the data in the file. This would be horrendously slow: basically, it would ahve to shuffle on average half the records in the database every time you did an insert or delete. You say that you don't want to sort the records during SELECT. But to do exactly this is what databases are designed to do: to accept data essentially randomy, build and maintain indexes on that data, and use those indexes at SELECT to produce a finely crafted subset of your data. Alec Christoph Lehmann [EMAIL PROTECTED] 04/05/2005 00:38 To mysql@lists.mysql.com, [EMAIL PROTECTED] cc Subject Re: newbie: how to sort a database without extracting the data thanks Damian but I don't understand this: My field according to which I want the database to be sorted IS an unique number. eg I have 1 ab 33 1 cd 21 1 ac 32 2 aa 22 2 cd 25 3 kw 03 3 ie 02 2 ei 05 2 wk 00 I need it in the form: 1 ab 33 1 cd 21 1 ac 32 2 aa 22 2 cd 25 2 ei 05 2 wk 00 3 kw 03 3 ie 02 what do you mean by adding an index thanks for your help cheers christoph Damian McMenamin wrote: add an index on the field. would be quickerthan any exporting importing. --- Christoph Lehmann [EMAIL PROTECTED] wrote: Hi I am really new to mysql. I need my database to be sorted according to one field. But since the database with 1200 records is huge, I don't want to do it using SELECT. What I need is just the stored database being sorted on hard-disk. Is there any way doing this like creating a new database and importing the old one but being sorted? many thanks for your kind help cheers christoph (p.s. I need this for later chunk-wise data-fetch with one chunk being homogenous in regard to one (the sorted) field) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Yours Sincerely, Damian McMenamin Analyst Programmer Melbourne Australia Cell: (61)040-0064107 Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: full-text search
Ron McKeever [EMAIL PROTECTED] wrote on 29/04/2005 14:09:38: I have a TEXT field in my db (4.0) that has lists of IP numbers; can a full-text search be done for IP numbers? Unfortunately not, because Fulltext regards the dots as terminators. The IP address 192.168.32.2 will therefore be keyed as the four words 192, 168, 32, and 2. Since some of these fall below the minimum word length, they will be ignored. However, you might look at the functions INET_NTOA() and INET_ATON() which convert between the string and 32-bit binary representations of an IP address. http://dev.mysql.com/doc/mysql/en/miscellaneous-functions.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question from a new user:MySQL vs MS SQLserver merges
jatwood [EMAIL PROTECTED] wrote on 28/04/2005 22:35:45: I am new to MySQL. Please excuse my ignorance if this question has been previously discussed. I was not able to find an answer to my question by searching the archives. I have MySQL installed on a dedicated AMD-64 computer with the MS XP Pro operating system. MS-SQLServer is installed on a 32-bit machine with a slower clock speed. I am running the free binary distribution of MySQL for testing purposes and have been generally impressed with MySQL's comparative performance in all but one area. I am finding that both inner and outer merges take substantially longer with MySQL than with the SQLServer using the same basic code. In one application a left outer merge between a file with 600,000 records and a file with 4,500,000 records took about 10-15 minutes with SQLServer and 11 hours with MySQL. I am repeatedly having similar experiences with both inner and outer merges. Is there any way I can try to improve MySQL's performance with respect to merges? Needless to say, unless I can improve MySQL's performance, I will not be converting to MySQL at this time. You need to post the results of EXPLAIN your select statement together with the structures of your tables, including indexes. This sort of performance dropoff is usually due to inappropriate indexes, and can be solved by adding or changing indexes. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to make a virtual SELECT?
[EMAIL PROTECTED] wrote on 26/04/2005 14:46:37: Hello, I have a big problem, I only want to check if it's the minute 45 currently. I want to make a virtual SELECT without tables: mysql SELECT MINUTE(CURRENT_TIMESTAMP()) WHERE MINUTE(CURRENT_TIMESTAMP()) = 45; ERROR 1064 (42000): 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 'WHERE MINUTE(CURRENT_TIMESTAMP()) = 45' at line 1 mysql It works only when I put a FROM with an existing table on it. Is there a solution to do it without FROM or to use a virtual table? What response do you want, exactly? Try either of the two formulations below, depending upon whether you want to know what the minute is, or just whether or not it is 45 (returns 1 if it is). mysql select minute(now()) ; +---+ | minute(now()) | +---+ |58 | +---+ 1 row in set (0.06 sec) mysql select minute(now()) = 45 ; ++ | minute(now()) = 45 | ++ | 0 | ++ 1 row in set (0.00 sec) Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIKE question - is it possible?
Micha Berdichevsky [EMAIL PROTECTED] wrote on 14/04/2005 12:53:31: Hi group. I have a table with a varchar(250) column in it (let's call it c) I want to select values that contain a number of given words in them (three or more), in any words order I currently use SELECT * FROM table WHERE c LIKE %word1%word2%word3%; I was wandering if it is possible to use a query where the LIKE (or anything else) searches for my given strings in any order. I'm using MySQL 4.1.11 on windows XP, if it matters. I think you want to do a FULLTEXT search: see http://dev.mysql.com/doc/mysql/en/fulltext-search.html This requres using a FULLTEXT index on your column and using the MATCH command. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to run a file in MySQL
The command you need is source filename ; Alternatively, if you are outside the mysql clined mysql filename Alec Joppe A [EMAIL PROTECTED] 04/04/2005 09:59 To mysql@lists.mysql.com cc Subject how to run a file in MySQL Hello all, This is probably really basic for all of you but I have been trying to find it in the manual without success... My question is if it is possible when you are logged in to MySQL to run a file with sql-statements in, instead of sit and execute each statement seperatly. The file I have is a to clean up my DB and to erase data that I don#t want to have, som all rows in the file is normal DELETE-statetments. Thanks in advance! /Joppe -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index on boolean column
Du?an Pavlica [EMAIL PROTECTED] wrote on 30/03/2005 16:35:40: Hello, maybe this is a silly question but how useful it is to create indexes on columns containing only values 0 and 1 (true and false)? Since I believe that MySQL ignores indexes if it expects to get more than 30% hits, it will probably be ignored unless the distribution of 0s and 1s is very skewed. If you only have a tiny fraction of (say) 1s, it might be useful to extract that tiny fraction - but useless for the opposite. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if statement help
Christopher Vaughan [EMAIL PROTECTED] wrote on 30/03/2005 16:48:47: I have data in a table listed as 44:22:22 333:33:33 It stands for hhh:mm:ss I want to break each part of the data into different parts based on the ':' to separate them. Then I want to take that data and sum it. I wrote an if statement to parse through this table but I can't get it to work. I am not sure If my syntax is wrong because I can't find anything to check against it. Here is the syntax: IF (SELECT job_walltime FROM time WHERE CHAR_LENGTH( job_walltime ) =9) THEN (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` , 4, 2 )) , sum( right( job_walltime, '2' ) ) seconds FROM `time`) ELSE (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` , 3, 2 ) ), sum( right( job_walltime, '2' ) ) seconds FROM `time`) END I know this isn't the only way to do this but this but this is the first suggestion that comes to mind. Any input would be great. IF is an operator, not a command, so it comes after the SELECT. In C terms, it is more like the ?: operator than an if()...else. Thus you can do SELECT x, IF (x y, IS BIGGER THAN, IS SMALLER THAN), y FROM table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with a mutuality check (good query exercise :)
select l.b, r.a = l.b from tab l join tab r on l.a = r.b where l.a = 1 ; seems to produce the result you want Gabriel B. [EMAIL PROTECTED] 29/03/2005 09:30 Please respond to Gabriel B. [EMAIL PROTECTED] To mysql@lists.mysql.com cc Subject help with a mutuality check (good query exercise :) I got stuck in this one, and i belive there's a solution, i just don't happen to see it. i have a table with conections between itens. something like +---+---+ | A | B | +---+---+ | 1 | 2 | | 1 | 3 | | 1 | 4 | | 2 | 1 | +---+---+ i'm trying to solve with one query a way to get all of the relations with 1 on the A colum but having another field, telling me if the relation is mutual. something that would return +---++ | B | mutual | +---++ | 2 |1| | 3 |0| | 4 |0| +---++ Can you think of anything that doesn't involve some big temporary tables or one extra query for every row found on the first one? , Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: search through one/several tables
mel list_php [EMAIL PROTECTED] wrote on 23/03/2005 10:14:07: Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do that with mysql? Somebody has some tips/doc where I could look for search engines?My problem is that I don't have one big table with all the data but several little ones with few fields, so I don't think solutions like Lucene could work. If your tables are all identical, which it sounds like, you want to create a Merge Table: see http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: search through one/several tables
I am not quite sure I understand your question, then: you would have to make your table structure a little clearer. Generally, however, puristic database design would say that you do not have the same class of data in different tables. Instead, you have one master table with all the similar data in it, tagged by a unique ID, and all the other tables refer to the master copy by that ID. You then reconstruct the original table at query time using a JOIN. Then, of course, it is trivial to search the master table. I will say that I think fulltext will not help you, if I understand your problem. Fulltext divides a column into separate words, based on the spaces (and non alphanumerics) in the string. Since, as I understand it, your DNA sequences have no natural breaks and the words, such as they are, can start at any base, fulltext will not help you. mel list_php [EMAIL PROTECTED] wrote on 23/03/2005 11:00:08: Unfortunatly they are not, I have something like 30 tables, with I would say 10 to 15 fields per table. The number of row per table is quite low, i think it won't exceed 500-1000/table. But I may sometimes have to search into dna sequences (around 5000 atcg characters in any order), so that is quite heavy. Maybe for that field a fulltext index would be helpful? Thanks for your help, Melanie From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] wrote on 23/03/2005 10:14:07: Hi list, I would like to search for something into one or several tables. My first idea was to retrieve the tables' names, then for each of them retrieve the columns' name and have a look in each of this column. Is there a more elegant (fast) way to do that with mysql? Somebody has some tips/doc where I could look for search engines?My problem is that I don't have one big table with all the data but several little ones with few fields, so I don't think solutions like Lucene could work. If your tables are all identical, which it sounds like, you want to create a Merge Table: see http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replicating InnoDB tables in new database
Andy Hall [EMAIL PROTECTED] wrote on 18/03/2005 12:06:30: Hi, I have tried the following process in order to try and replicate a database with InnoDB files: 1. created a new database in PHPMyAdmin 2. via command line, copied all the .frm files from the old database directory into the new database directory 3. changed all the ownership and permissions 4. restarted mysql The database is now recognised in PHPMyAdmin, but when I click on any of the tables I get the message cannot find [table].InnoDB. Originally, the tables in the source database were MyISAM and then converted to InnoDB. I tried renaming one of the [table].frm files to [table].InnoDB, but now this does not show up on the table list. I tried the described method as I have done this before with MyISAM tables successfully. What am I missing? Or is a completely invalid way to move the database? No, this is a completely invaild way to to copy InnoDB files. What you previously did wit MyISDAM files was orbably to copy the .FRM (table descriptor) file AND ALSO the .MYD (table data) and .MYI (Indexes) files. This works for MyISAM, since each table is stored separately. However, thei does not work for InnoDB tables, which are stored in a very different fashion. InnoDB files are stored, all together, in files called ibdata*. Yo cannot split separate tables. As far as I know, there is no file fiddling way of doing what you wish to achieve. You need, I guess, the InnoDB Hot Backup tool - see http://www.innodb.com. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update a field with multiple value
Eko Budiharto [EMAIL PROTECTED] wrote on 18/03/2005 16:54:09: Hi, I am trying to to update one field with multiple value. I tried with regulare update command syntax does not work. How to update a field with multiple value. regular update syntax is this, UPDATE variableInfo SET variable='A' WHERE variable is null; but what I want to do is UPDATE variableInfo SET variable='A, B' WHERE variable is null; when I use that command, mysql does not understand the syntax. How to update a field with multiple value or with an array? +-+ + variable + +-+ + + + + +-+ but I want to update this column into +-+ + variable + +-+ + + + A, B + +-+ MySQL does not support arrays of data in one field. You cannot enter multiple entries into a numeric field. You could, of course, enter it as a string, but this is regarded as very bad practice. Most users would inquire why you need to do this, and suggest that you should be reconsidering your table design if you need this sort of facility. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary tables
Ted Toporkov [EMAIL PROTECTED] wrote on 15/03/2005 11:04:09: List, I'm trying to create tables that will store data temporarily, if a php page generates data to fill the table, then any number of queries could be run on the table, and then be automatically be deleted if it's not queries for let's say an hour or something like that. Can this be accomplished with temporary tables, or should i just create static tables and then use a cron job to delete unused ones? What is the best way to approach this? Temporary tables are private to a single Connection, and would therefore not be an appropriate solution to this problem. If you have to do it, the cron job appears youe best bet. However, I query the requirement. ISTM that you are basically saying that you do not trust MySQL's cachein ability, both to cache recently used table blocks and to cache the result of recent queries. I would take the first approximation of trusting MySQL and only attempting solutions such as that which you propose when you know for certain that the system will not handle them without. Have you fully characterised the behaviour of the system without this kludge in place? Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Compressing after Deletion
Note that with MySQL, unlike some other databases, you don't have to do this. If you are going to add new records to replace the deleted ones, MySQL will re-use the space freed by deletions with no special actions on your part. Of course, if you are not going to replace the deleted records, the commands suggested by David will certainly free space and probably improve performance. Alec Cawley Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote on 08/03/2005 22:58:12: Hi Chris, For MyISAM/BDB tables use OPTIMIZE TABLE your table name; For InnoDB tables try ALTER TABLE your table name TYPE=InnoDB; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, 9 March 2005 9:19 AM To: mysql@lists.mysql.com Subject: Compressing after Deletion I have looked in the documentation and either I am not looking for the right thing or have simply overlooked it. But my question is this, I have a database with 35 Million records, and I need to delete about 25 million of those. After deletion I would think that I would need to compress, shrink, or otherwise optimize the database. How is that done? do I need to do it? What commands should I be looking up in the docs? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Automatic server-id generation for slaves?
Kevin A. Burton [EMAIL PROTECTED] wrote on 28/02/2005 17:41:07: Right now one of the only reasons we can't put our entire config for our slaves in CVSup is that the config *requires* the ability to set a server-id for each machine. Seems like it would be pretty trivial to support a hostname based policy for this. You could simply look at the IP/hostname and set the value from this (though you might need a tracking table). Policies could include: - IP based server-id (IPs are 32bit) - parse the hostname for an ID (db4.server.com would yield a server-id of 4) - Adler32/SHA1 truncate the hashcode of the hostname The first two seem sufficient. This wouldn't be the default of course and would require an explicit config. Thoughts? Nice. At the moment, because I have a supervisory application, I have a table inside the database with hostname-serverid lookup. The machine starts up with the slave thread disabled, and the supervisory app reads the slave id from the database and sets it before enabling the slave thread. This could be replicated inside MySQL, with a hostname to slave ID table in the mysql database. Obviously, explicitly assigned slave IDs would override this. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id
mel list_php [EMAIL PROTECTED] wrote on 15/02/2005 10:18:55: Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? - or do I have to lock my table, execute the query, retrieve the id, unlock the table? - is there a way to make an atomic query with this that would avoid me to use locks? last_insert_id is on a per-connection basis i.e. it gives the last id inserted using that particular connection. Therefore you will always get the most recent ID that you inserted, not the most recent that anyone inserted. I think, therefore, that the natural behaviour is what you want. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to make question that check the last hour
Jesper Andersson [EMAIL PROTECTED] wrote on 15/02/2005 13:15:43: Hello, I relly new with databases and writing sql-questions. But in my db want I to check what have new rows have come the last hour. the db have I as follow: ID email created updated 001 [EMAIL PROTECTED] 20050215131034 20050215133401 063 [EMAIL PROTECTED] 20050215141034 20050215141201 76 [EMAIL PROTECTED] 20050215134500 20050215134556 Now I would like to make a sql-question that show which new users have come the last hour, without that I need to edit the question each time I want to ask. select colums from table where date_sub(now(), interval 1 hour) = created ; Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto-increment stops at 127
mel list_php [EMAIL PROTECTED] wrote on 14/02/2005 13:54:35: additional test, it is always bugging at the key 127... I put a backup online, with until 106. Added few test records, from key 127 it just doesn't want to increment the auto-increment field anymore. I'm completly lost here, any help would be greatly appreciated.. Please show your table description. This behaviour corresponds to the AUTO_INCREMENT column being defined as a TINYINT, range -128..+127. You probably need to change the definition of your key column. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to specify autoincrement primary key value
Denis Gerasimov [EMAIL PROTECTED] wrote on 10/02/2005 10:59:11: Hello, One simple question... AFAIK I can specify value for an autoincrement primary key (int) when inserting a record like this: INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test') But it doesn't work for id = 0. Why? I would like to use some primary key values for special purpose, e.g. id 0 means root/default record and so on. Is that recommended? Are there any alternative ways? Autoincrement starts from 1. That is the way it is, and cannot, I think, be changed. I would advise against using autoincrement keys for special uses. This is muddling two different functions into one. Remeber that primary keys must be unique: if, at some time, you need to have more than one of a special value, you will be in trouble. The function of autoincrement keys is to assign unique record identifiers. Do not mix this with other tasks. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: Slow Replication
Hannes Rohde [EMAIL PROTECTED] wrote on 10/02/2005 11:44:13: I don't think we are dealing with an IO bottleneck here because the slave server should quite faster with writings to the disc at least since we are using Raid 0 here. Or is there any way which could explain an IO bottleneck even though the slave is not running as many selects as the master is? In this case we are talking about one replicated database on a dedicated slave system. As I understand the previous posts, the problem is that the replication process is single-threaded while the updates on the original master are multi-threaded. On the original server, if Update 1 stalls because it has to fetch data of disk, Update 2 can proceed. If Update 2 stalls, Update 3 can proceed - and so on. This means firstly that Updates which can take advantage of the cache take no effective time - they come in, do their job, and exit while peer updates are stalled in Disk wait. This also means that lower-level software can optimise disk performance by re-ordering IO operations to minimise head movements. In my experience, having up to 4 parallel streams of disk operations, and allowing the disk to pick its preferred order of execution, usually adds about 50% to disk performance and can double it. However, when they are replicated to the slave server, the updates are put into a strictly First In, First out queue. If Update 1 stalls, Update 2 cannot be started - and nor can Update 3. When Update 3 does finally start, it cannot overlap the others, so that the time it takes, albeit small because it does not access disk, is added on to the other times rather than included within them. And since you are performing strictly one operation at a time (on the Updates side at least) Raid 0 does not help you, because there are no overlapping reads to get from alternate disks. Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance of Sockets vs. TCP/IP connections on localhost?
Lasse Laursen [EMAIL PROTECTED] wrote on 09/02/2005 13:24:27: Hi all! Simple question: What is fastest when doing a connection to a local machine? TCP/IP or connecting via the socket? the application is a ACL helper program under Squid which makes a persistent connection to the database (100 parallel programs runs on the machine each connecting to the MySQL database) From the Connector/J documentation: Named pipes only work when connecting to a MySQL server on the same physical machine as the one the JDBC driver is being used on. In simple performance tests, it appears that named pipe access is between 30%-50% faster than the standard TCP/IP access. Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ft_stopword_file
* Searching New England brings up all entries with england, regardless of new. * Changing the stoplist to blank (and doing a REPAIR TABLE) generates the same results. * Also changing it to a blank custom stopword list has the same results. * Searching only on New finds no entry (even though it's in there). * Searching on The gets the same. Changing the custom stopword list to include England makes the New England serach come up blank. You need to set the global variable ft_min_word_len to 3 to achieve what you want. As shipped, it is set to 4, which means that words of three or less letters are ignored. After changing the variable, you need to rebuild the index. Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax diagram, where is it located in the doc?
Thomas Sundberg [EMAIL PROTECTED] wrote on 04/02/2005 11:39:12: Hi! I'm looking for the syntax diagram for MySQL and can't find it. I have downloaded the entire MySQL manual as one html page and searched it for the definition of where_definition and I cant find it. Could somebody please point me to a location where the complete syntax diagram can be found? Does anybody at the list know the answer to my question? I sent it a few days ago and haven't received any response. It does exist a syntax diagram for MySQL, doesn't it? Since no-one replied to your first post, apparently not. I have never seen such a thing. Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Syntax diagram, where is it located in the doc?
Thomas Sundberg [EMAIL PROTECTED] wrote on 04/02/2005 12:46:02: Does anybody at the list know the answer to my question? I sent it a few days ago and haven't received any response. It does exist a syntax diagram for MySQL, doesn't it? Since no-one replied to your first post, apparently not. I have never seen such a thing. Strange, where is the definition for the syntax element where_definition done then? That is the part of the syntax diagram I currently looking for. It is defined as an element in the select syntax diagram. But when trying to find the definition for what is legal to put in a where clause, I just can't find it. Could somebody point in me the correct direction? The WHERE keyword is followed by an expression. There appears to be no no formal definition of expression, but it could be informally defined as the a combination of Operatiors applied to column names and constants. See manual chapter 12: Operators. The WHERE clause restricts to rows where the expression returns true. Alec Cawley. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Syntax diagram, where is it located in the doc?
Thomas Sundberg [EMAIL PROTECTED] wrote on 04/02/2005 13:48:03: It is very simple but absolutely not straight forward. It really doesn't say anything. Just that you should do things right and then you will not have any problems. The concrete problem I tried to solve were if MySQL supports xor in a where clause. And if so, how should the syntax be written? That would have been extremely simple if the syntax diagram started just above the quote you supplied us with had been completed and not ended when things got a bit interesting. It would probably not have been very hepful because it would simply have mentioned operators and referred you back to section 12 of the manual for a complete (and growing) list of operators. Good database practice suggests that the same data - the list of valid operators - should not be in two places unless there is an aoutomated method of deriveing the lesser from the greater.. The master copy is the list of operators in the Syntax section of the manual. Since operators includes words like IN, AND, NOT, the syntax of operators is roughly [non-space-character]* . If you looked in the manuel, under operators, then bitwise operators, you would find xor near the top of the table - togehter with the information (not available in a syntax diagram) that it is only available since 4.0.2. Alternatively , a second's experimentation (SELECT 5^1;) would have shown that it has the obvious syntax - or the alternative syntax (SELECT 5 XOR 1;) ; Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data in different tables or is one big table just as fast?
Jacob Friis Larsen [EMAIL PROTECTED] wrote on 28/01/2005 11:23:46: We have a table that grow by 200MB each day. Should we put data in different tables or is one big table just as fast? We will for new data do select, update and insert and for old data only select. It is not possible to answer in the general case - you would need to explain more about your table and usage patterns. However, if you can use MyISAM tables, you should at least consider using MERGE tables: http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html If your database is well indexed and your searches can generate relatively small amounts of data on the first key used, the overhead of having enormous files is small. At the other end, if the MySQL is reduced to a linear search, the impact of a huge file is enormous. If most of your SELECTs are on recent data, it is work considering splitting the table into daily, weekly, or monthly sub-tables. This means that you can archive very old months, and pack recent months, while keeping only the current month active. But if you need to search the entire archive frequently, this will reduce performance. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble with Virus checkers
My product has at is centre a Windows PC whose sole purpose is to run MySQL plus my middleware layer. However, it installed on a site with a large amount of heterogeneous IT department and an active IT department managing the whole corporate IT structure. This IT department insists that, if it is a Windows PC with any connection to the corporate network, it *must* run a virus checker. However, it appears that the virus checker (McAffee, as it happens, but I think the problem may be general) feels a need to check the MySQL data files every time they change. As the system load is bean ramped up, more and more time is being spent in the virus checker. The quick solution is to tell the virus checker to ignore the whole mysql\data directory. This solves the problem, but leaves the IT department nervous because something is not being checked. I cannot see how a virus could infect via the data directory, but I am no virus expert. It is also my view that a machine with no actual humans using it (no email, no web), with all unnecessary services disabled and which is behind a good firewall should be pretty well protected and should not need a virus checker. Am I right in this? Have other people had this sort of problem, and how did they cope with it? To my regret, the reply switch to *nix is unacceptable to my management. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can Mysql hold possible field values?
shaun thornburgh [EMAIL PROTECTED] wrote on 17/01/2005 14:57:39: Hi Guys, Thanks for your replies, i have also found SET which appears to do the same thing, is there a reason why everyone suggested ENUM as opposed to SET? SET and ENUM are different things. An ENUM can have only one value e.g TRUE or FALSE. A SET is a group of possible values e.g. IN_STOCK, ON_ORDER, SHORTAGE, OBSOLETE. A in the first case the thing must be either TRUE or FALSE, but there are sizteen posssible combinations such as IN_STOCK/OBSOLETE, ON_ORDER/SHORTAGE and so on.. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Amount of data stored in a blob
Mads Kristensen [EMAIL PROTECTED] wrote on 13/01/2005 12:42:13: Is it true that I can only store 65535 bytes (2^16-1) in a field of type BLOB? I thought that a BLOB was supposed to be able to hold 2^16 bytes of data. If it is indeed only possible to store 2^16-1 bytes I would like to know why this has been implemented in that way (yes, I am always this curious ;-) The BLOB has to have a length, which is stored in 16 bits, range 0-65535. The concept of a zero length blob, as distinct from a NULL record, is perfectly valid, and may in some contexts be meaningful. Also, it avoids special logic for mapping the 65538 case to zero and erroring the zero case. If you want 65536, go for a MEDIUMBLOB: the overhead is only one part in 60,000. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full text Searching on C#, C++
Brent Baisley [EMAIL PROTECTED] wrote on 07/01/2005 17:03:36: I'm having a lot of trouble trying to get this to work. I've set the minimum word length to 2, restarted MySQL and rebuilt the indexes, but can't seem to get this to work. Is it because MySQL is not indexing # and ++? I can't seem to find a setting to alter this behavior. The only other thing I can think of is that I need to escape # and ++, but any of the normal escape character I've tried don't seem to do anything. I think Fulltext indexes only words which consist of A-Z, a-z, 0-9 and _ . I don't think is has any escape behaviour at all, and no control other than the word length. A feature that I would like would be to be able to define the set of characters which constitute a word. In my case, I would like to consider video timecodes (form hh:mm:ss:ff) to be words, so that I would like : to be regarded as a letter. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: adding automatic alert
Roger Baklund [EMAIL PROTECTED] wrote on 06/01/2005 12:25:31:
Aji Andri wrote:
Hi all,
I'm make an inventory goods database, in one of my
table I need to make an automatic alert when my stock
reach it's limit (say for tires it's limit is 4), can
I make an automatic alert for it so when it reach it
limit I can have an alert may be a beep ?
This is a typical task for the programming language/tool you are using
to create your application. What you ask may look a bit like a task for
a trigger, but triggers work serverside, and I assume you want the beep
on the client. I really think you would be better of just using a simple
script, running every 30 minutes or so. Something like this (meta code):
cnt = select stock from goods where goodstype='tires'
if (cnt 5):
beep()
send_email('[EMAIL PROTECTED]','Out of tires!')
(This script will of course keep beeping/sending emails every 30 minutes
until you stop the script or increase the registered stock above the
limit... )
Or, more generally, add a column min_stock to the table so that it will
check all lines:
cnt = select stock from goods where stock min_stock ;
if (cnt 5):
beep()
send_email('[EMAIL PROTECTED]','Restock needed for items')
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Search - Limits?
EP [EMAIL PROTECTED] wrote on 15/12/2004 15:44:15: Thomas Spahni [EMAIL PROTECTED] wrote: the column type will limit the number of characters per row. A column of type TEXT will hold up to 65,535 characters but with LONGTEXT you can put up to 4,294,967,295 charcters into one row. I have an application with Texts of up to 200 pages in one column. Full-Text Search is handling this very well. Thanks... Really?! If I can follow-up with another question, does experience suggest Full-Text Search handles a large number of such documents efficiently? For example, I am expecting to have (up to) one million documents in my database. I was considering breaking each document into paragraphs for search efficiency, but if Full-Text Search can search return results quickly on a large number of long (e.g. 10,000+ character) documents, my database has just become much simpler. I see no reason why not. AIUI, Full Text search breaks the documents up into words and indexes each document by avery word in the document. Breaking into paragraphs gives you an approximate position within the document for a hit, and changes the behaviour for multiple keywords (they must both be in the same pararaph) but should have little effect on the efficiency of the index. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication question...
Jason Lixfeld [EMAIL PROTECTED] wrote on 03/12/2004 03:32:32: I'm very new to mysql and replication. I've got a case where I have 2 servers, each have database A and database B. I want server 1 to be master for database A and slave for database B and I would like server 2 to be slave for database A and master for database B. From what I've read, if a server is a master, it's a master and likewise for a slave and they can't share the role depending on the requirements of the user. Can someone enlighten me? No, I think it is more flexible than that, and will probably fulfil your needs. You can set up circular replication, of which your two-server case is the simplest example. It is then up to you to ensure at application level that no single table is updated on more than one machine. If you observer that rule than, so far as I understand, replication will do what you want. If you break that rule, behaviour will be unpredictable. You might want to use the replicate_do_db variable to provide a partial enforcement of the rule. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: binary column type
Chenri J [EMAIL PROTECTED] wrote on 26/11/2004 10:52:51: What is 'binary' in column type stand for? is it describe how the data is stored (in biner value)? what do we want use it for? - encryption ? - space efficiency ? - fast index ? - or ? I've searched the mysql manual but didn't find any clue about it thanks for your help before. BINARY is usually used on character columns to tell MySQL to do an case-dependent rather than case-independent indexing and selection. By default, character columns use case-independent ordering. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert statement problem
Stuart Felenstein [EMAIL PROTECTED] wrote on 26/11/2004 13:54:31: I'm trying to build an insert query that will add a value into a field , after the first insert query adds the record. Relevant table information: +-+-++--+ | RecordID| InitOn | LenChoice | EndDate | + [PrimID,AutoInc]| [Date] | [int]| [Date] | +-+-++--+ So in the first insert the RecordID, InitOn (Using select NOW()), and LenChoice would be inserted. Looking something like this: +-+---++--+ | RecordID| InitOn| LenChoice | EndDate | +-+---++--+ | 10043 | 11/26/2004| 7 | | +-+---++--+ Now I try to use (and I've tried an update statement as well: Insert MyTable (EndDate) Values(DATE_ADD(InitOn, INTERVAL LenChoice DAY)) Which I would hope to result in: +-+---++--+ | RecordID| InitOn| LenChoice | EndDate | +-+---++--+ | 10043 | 11/26/2004| 7 |12/02/2004| +-+---++--+ However what is returned is an error message Column EndDate cannot be NULL. Anyway idea what I'm doing wrong ? I think you need an UPDATE statement UPDATE MyTable set EndDate=DATE_ADD(InitOn, INTERVAL LenChoice DAY) WHERE RecordID = value ; Insert *always* creates new records if successful and cannot be used to modify them. Update *always* updates recirds in position and cannot be used to insert them Replace is a hybrid whcih can do either if you set your indexes right. I think what you want is an Update, not an Insert. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: row numbers, jumping rows]
I think the reason nobody has replied is that the term row number does not really have any meaning in a DBMS. How the database stores rows inteneally is the DBMS's private business, and should not be visible to you. I think it is true that MySQL does not *have* an internal row number, so there is nothing to skip by. All it stores in MyISAM table is file offsets. However, even if I am wrong, it doesn't matter: that is an internal implementation detail and should not be visible to you. However, if I read you rightly, what you want it to extract a random tenth of your table. You could do this by something like SELECT columns FROM table ORDER BY rand() LIMIT table size / 10 ; João Borsoi Soares [EMAIL PROTECTED] wrote on 23/11/2004 11:30:26: No body has answered my question so far. Does that mean there is no way to retrieve current row numbers in a query or no way to retrieve results jumping steps? I would appreciate any help. Thanks, Joao. -Mensagem encaminhada- From: João Borsoi Soares [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Subject: row numbers, jumping rows Date: Sun, 21 Nov 2004 11:23:41 -0200 Hello list, I wonder if someone could help me to get the fastest result on my query. I need to classify one entity, according to a specific attribute (let's call it X), looking at a sample extracted from a database table. First I need to sort my sample ordered by a specific table field (X). Then I should divide my sorted sample in 10 equal groups (same number of rows). Finally, I should classify my entity (1 to 10), finding out at which group its attribute X fits in. Let me explain my thoughts. First I should make a SELECT COUNT(*).. to find the total number of rows in my sample. Then I make queries using LIMIT until I find which group the X attribute fits in. In the worst case I will have to do 10 queries. Which I think should take too long in my case. I wanted to make it in only one query. It could be possible if I could make a query which returns result jumping rows, like STEP N where N could be the number of items in each group. Is there anything in mysql that can give me that? I also thought if I could have the row numbers in the query result, I could solve it with a simple condition like, WHERE rowNum % N = 0. Any ideas? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: row numbers, jumping rows]
João Borsoi Soares [EMAIL PROTECTED] wrote on 23/11/2004
12:34:01:
First thanks for the answer Alec. But I think you didn't understood my
problem. Maybe nobody replied because of that. Let me try again.
Suppose I make a select which returns 100 ordered rows. I only want to
read rows number 10, 20, 30, 40, 50, 60, 70, 80, 90 and 100.
To read the 10th row I would make SELECT columns FROM table ORDER
BY my_field LIMIT 10. To read the 20th it would be SELECT columns
FROM table ORDER BY my_field LIMIT 10,10. And so on..
What I want is to make all of these queries in only one.
That's why I said if I could get the row number retrieved from the
query, I could do: SELECT columns FROM table WHERE (rowNumber %
(tableSize/10)) = 0 ORDER BY my_field
I am not a real MySQL wizard, so there may be better ways. But the way I
would do it would be with a temporary table. This may sound cumbersome,
but as far as I can see MySQL would have to create a temporary table
internally to satisfy your request anyway.
CREATE TEMPORARY TABLE temp
{row INT AUTOINCREMENT NOT NULL,
other columns as you need
) ;
INSERT INTO temp SELECT NULL other columns FROM table ORDER BY
criterion ;
SELECT columns FROM temp WHERE row % 10 = 0 LIMIT as needed ;
DROP TABLE temp ;
A bit clunky, I agree, but the only way I can see of solving your problem
;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 ways Replication in MySQL
Mojtaba Faridzad [EMAIL PROTECTED] wrote on 18/11/2004 13:59:21: Hi, I need to expand the database to 3 different locations. We have 3 servers in 3 cities. So far the database has been in one city and 2 others have been linked to it and worked. But sometimes for a day or more a city lost the connection to the master database and the users could not work. Now I am going to change the method to have a copy of database on each location. Each location should be able to change the data also. All tables are myISAM. I am thinking about 2 ways Replication in MySQL but as MySQL document recommended not to do it because there is no guarantee that we won't have any problem (slow connection or losing connection in a period of time). Have you ever had this kind of situation? How did you solve it? Have you ever found any problem in your solution? What you are attempting to do is inherently difficult, and I don't think any DBMS has solved it. What do you expect to happen if the links between cities are down, and the *same* row in the database is updated differently by different users? Even when the link is up, you have the possibility of a race condition if users in different places update records within a narrow window. The closest we got to this was having a master database in one place and read-only slaves in another. UPDATE commands were always sent to the master copy, and could not be done when the link was down. SELECTs were sent to the local slave and could therefore continue when the link was down. At the application level, we pipelined a few necessary but uncomplicated updates to be done when the link returned. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Three Questions
litlpooh [EMAIL PROTECTED] wrote on 12/11/2004 07:46:02: I have three questions about mysql. 1. mysql CREATE TABLE board ( - boardid char(6) binary NOT NULL, - title varchar(128) binary NOT NULL, - ) ENGINE=MYISAM; Query OK, 0 rows affected, 0 warning (0.00 sec) mysql desc board; +--+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | boardid | varchar(6) | | PRI | | | | title | varchar(128) | | | | | +--+--+--+-+-+---+ 2 rows in set (0.00 sec) char type column cannot be with varchar type column. is there any plan to fix it..? if there is not.. when do you think it will be fixed? This is a documented feature of MySQL: http://dev.mysql.com/doc/mysql/en/CHAR.html It will thereforer probably never be changed. Why to you say that it cannot b of varchar type? I think that the opinion of the MySQL team is that there is no performance impact and no functional difference with the change. 2. my second and last question is index descending.. in the mysql document, An index_col_name specification can end with ASC or DESC. These keywords are allowed for future extensions for specifying ascending or descending index value storage. Currently they are parsed but ignored; index values are always stored in ascending order when do you think it will be possible? what I want from you is just roughly plan. I have not heard of any plans for this. Since you can specify that results are ORDERED in ascending or descending order, what does it matter to you what order the index is held in? If MySQL can search an ascending index backwards, the effect is the same as a descending index. 3. I want to get 'DATE' typed data which including millisecond format. What can I use for this? Which function can be made it possible? No table type that I am aware of. The manual doeaz not contain msec, millisec or millisecond, so I don't think there is any such type. When I have to store such types, I use absolute milliseconds stored in BIGINTs. Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of Rows in DB.
Michael J. Pawlowsky [EMAIL PROTECTED] wrote on 14/10/2004 17:01:34: Getting closer Thanks... This db had LOTS of tables... That's why I'm simply trying to get the total. Is there a way to only get one column of this. then I can do something like select SUM(show table status (rows)); IF all the tables have the same table structure, create a MERGE table of them all, then SELECT count(*) FROM merged ; Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
