Re: Mysql bug, doesnt use INDEX for ORDER BY!!
On Mon, 16 Jul 2001, Benjamin Pflugmann wrote: If I understand it correctly, this is a known and fixed bug: From http://www.mysql.com/documentation/mysql/bychapter/manual_News.html: -- F.2.3 Changes in release 3.23.38 [...] * Changed optimizer so that queries like SELECT * FROM table_name,table_name2 ... ORDER BY key_part1 LIMIT # will use index on key_part1 instead of filesort. [...] -- Since 3.23.30, as you said you use, MySQL has experienced a lot of improvement, so you may want to upgrade to the current release 3.23.39. Very good, I'm very happy that this is fixed :-) Btw, it is always a good idea to upgrade to a recent version before reporting bugs. If that is not possible, please check the archives or the changes history whether the bug has been fixed meanwhile. ups, shame on me. I will consider this in future. regards Gunnar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql bug, doesnt use INDEX for ORDER BY!!
Hello, On Don, 12 Jul 2001, Gerald Clark wrote: It may be the DESC that prohibits the use of the index. Try an normal ascending order. Thanks for you idea, but DESC has no influence whether Mysql uses the INDEX or not. I think maybe that this is a real _bug_ in mysql. :-( Does somebody know if this will be fixed sometimes? best regards Gunnar von Boehn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: why doesn't Mysql use INDEX for ORDER BY ?
Could somebody please explain to me why mysql doesn't use an INDEX for ORDER BY in joined selects with LIMT and no WHERE clause. thanks Gunnar von Boehn On Mit, 11 Jul 2001, Gunnar von Boehn wrote: using mysqlversion 2.23.30 I have the problem that mysql doesn't want to use an index. I tried USE INDEX() but that doesn't help either. I have a tables with 250k rows person( idint, (indexed) name varchar(40), fatherid int, motherid int, creationtime timestamp (indexed) ) I want to see the last 10 persons and their parents that are added to my database. SELECT p.name, father.name as fathername, mother.name as mothername FROM person as p LEFT JOIN person as father ON (p.fatherid=father.id) LEFT JOIN person as mother ON (p.motherid=mother.id) ORDER BY p.creationtime DESC LIMIT 10; I thought that mysql would use the INDEX on creationtime to easely find the 10 rows and than use the other indexes to fetch their parents. But mysql preferes to scan the whole table (250K rows) and than to sort the result. This is of course very,very slow. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql doesn't use INDEX for ORDER BY with LIMIT and no WHERE
Hello all, using mysqlversion 2.23.30 I have the problem that mysql doesn't want to use an index. I tried USE INDEX() but that doesn't help either. I have a tables with 250k rows person( idint, (indexed) name varchar(40), fatherid int, motherid int, creationtime timestamp (indexed) ) I want to see the last 10 persons and their parents that are added to my database. SELECT p.name, father.name as fathername, mother.name as mothername FROM person as p LEFT JOIN person as father ON (p.fatherid=father.id) LEFT JOIN person as mother ON (p.motherid=mother.id) ORDER BY p.creationtime DESC LIMIT 10; I thought that mysql would use the INDEX on creationtime to easely find the 10 rows and than use the other indexes to fetch their parents. But mysql preferes to scan the whole table (250K rows) and than to sort the result. This is of course very very slow. How can I force mysql to use the index on creationtime? Or is this a mysql bug? Because mysql uses the right INDEX for little querys like this SELECT id FROM person ORDER BY creationtime LIMIT 10; I use this query as a workaround and fetch the persons in a little loop. But thats not the best solution a thing :-/ best regards, Gunnar von Boehn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto-increment sequence question
thank you all for your help, this was the explanation I needed, LAST_INSERT_ID() with an argument creates a value that can be treated just like it's an AUTO_INCREMENT value. That means you can call LAST_INSERT_ID() without an argument later in the current session to retrieve the value you generated. This also has the advantage that you don't need to set up any locks. thanks Gunnar p.s. for the spamfilter mysql, database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
auto-increment sequence question
Hello everybody, I thing, that I have a problem that needs a sequence and that auto-increment can't do the job this time. I already consulted the manual and the mailarchive. I saw several different proposals, now I'm a bit confused and don't know exactly what the rigth way is to do it. Could somebody please clarify the following? We are using ISAM tables on mysql 3.22.32. We have an id-column but the id will be generated out of two different ranges. Example: all VIP-customers will get an id from 1000- and normal customers from 1-999. So, I think the only way to solve this is a sequence table. Lets take a single row, integer value for holding the sequence: CREATE TABLE sequence( id int4; ); My question is this: Is this the best way to get and evalute the sequence? LOCK TABLES sequence WRITE; UPDATE sequence SET id=id+1; SELECT id FROM sequence; UNLOCK TABLES; Thanks Gunnar von Boehn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How can I build a sequence table, please help!
Hello, We are using mysql 2.22.32 (yes antique, but ISPs) I need an sequence table. Every time a client reads, the value shall be incremented. A single row, integer value would be an ideal sequencen I think. CREATE TABLE sequence( id int4; ); now, is this the correct way to emulate a sequence in mysql? LOCK TABLES sequence WRITE; UPDATE sequence SET id=id+1; SELECT id FROM sequence; UNLOCK TABLES; This seems a bit clumsy to me. I already checked the manual but found no recommendat example. Thanks for any help Gunnar von Boehn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How can I build a sequence table, please help!
Hallo Jason, my problem is that the mysql 'auto_increment' behaviour will increase the 'sequence' always to the maxnumber +1. We now have p.e. a table with customers. Every new customer will of course get a customer_id. We now have 2 different id_ranges. Some customers should get their id range a) 1-9 But some will get their id from range b) 100-999 The 'auto_increment' field is no help here. regards Gunnar On Don, 21 Jun 2001, Jason Burfield wrote: I'm not sure about a version that old, buy why not just do this: create table sequence( id int unsigned not null auto_increment primary key ); Then, just do: insert into sequence values(null); That will increment the number each time. -- Jason - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: breaking the 31 tables in a join limitation, round 2
Hello SixK, Are you sure, that you are running in a join limitiation? I think that just your max-open-tables is per default = 32. Look at /etc/my.cnf set-variable= table_cache=64 Just change this variable to do what you want. As every open table uses resources, an 'infinite' value might not be the best idea. regards Gunnar von Boehn On Mon, 11 Jun 2001, SixK wrote: Hello, I always have my problem with the 31 tables in a join limitation. In crash-me database comparaison page, I saw that mysqld 3.23.39 could manage up to 63 tables in a join. I didn't see in history log that the limite has been updated from 31 (in 3.23.36 version we use) to 63 tables. So, is it a new feature not yet listed in MySqld3.23.39 history log ? Is there a parameter in 3.23.36 version to change to be able to use up to 63 tables in a join ? Why only 63 tables, it not look like a C limitation??? infinite would have been better ;)) -- Best regards, SixK // \\ //Amiga spirit will never die \\/ OT: Amiga was a very fine machine, sometimes I miss the good old days :-) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ID numbers: auto-increment or sequences ?
Hi, For identification of each record in our database we use an 'autovalue'-column. It seems that this was a rather bad idea. As we are now planning to run two individual databases. In both of them records shall be written. Later we plan to merge them together again. But how can we do this? With the 'autovalue'-ids we may end with record having the same ids. Can I somehow say that in database (1) the autovalues should start with 1 and in database (2) maybe with 1 ? With 'postgres' I could use a sequence for that. Can mysql the same or do I have to build a sequence table on my own? If I have to build my own sequence table, how do I handle the locking? thanks for any help Gunnar von Boehn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to index on soundex / define own soundex-function
Hello I'm run a family/pedigree database with about 250.000 records of dogs. The dogs are mostly (to 90%) searched by name. To find similar records I have my own 'soundex' colums wich is searched and ofcourse indexed. table dog id int4(indexed) name varchar soundex varchar (indexed) fatherid int4(indexed) motherid int4(indexed) My problem is: I want to search for my own definition of soundex. Thats why I have my own soundex column. Question: - Is there a way to define my very own soundex or string-translation funktion? - Can I build an index on an self defined function? - Can I even define my own function without compiling mysql? Because my Provider wouldn't let me do this. Thank for answering Gunnar von Boehn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to syncronisy/mirror two or more databases?
Hi I run a database using mysql 2.23 by a webspace provider. I would like to keep a 1to1 mirror of it at home. Unluckiely I have no shell/ftp access to the tables directory. The database-dump (with php) is far too big to download it every day. - Any ideas how to backup it? - I'm dreaming of the changing records on the website and at the mirror at home and then magically syncronising the database. - Any good books/docs that explain how to resolve this problems? Thanks, Gunnar von Boehn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Question: Tuning Ram vs Hardrive ?
Hi, I would like to tune the performance of my database. The database is rather small about 80 MB altogether. (with 20 Mb indexes) The most complex selections open 32 tables at ones. The database is mostly readonly (99%). What settings show I use to maximize the speed of the queries? What would happen if I equip the computer with very much RAM? Will all the tables be cached and the speed of the harddrives not matter anymore? Is there a way to hold all tables in memory and only regularly (every x min) sync them to disk to improve the speed of reads? thanks for *any* answer Gunnar von Boehn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
