Indexes
Filter-fodder: mysql, query Ah. Sorry about that. Given a query: SELECT academic_institutions.name, academic_institutions.town, countries.abbrv, inst_types.name, academic_institutions.acinstid FROM academic_institutions, inst_types LEFT JOIN countries ON academic_institutions.cykey = countries.cyid WHERE academic_institutions.itkey = inst_types.itid ORDER BY academic_institutions.name DESC EXPLAIN SELECT produces: +-++--+---+ | table| type | possible_keys | key | +-++--++ | academic_institutions | ALL | NULL | NULL| | inst_types | eq_ref | PRIMARY| PRIMARY | | countries | eq_ref | PRIMARY| PRIMARY | ++-++-+ +-++--+---+ | key_len | ref| rows | Extra| +-++--+---+ |NULL | NULL| 6682 | Using filesort | | 2| academic_institutions.itkey |1| | | 2| academic_institutions.cykey |1| | +-+-+--+--+ Is there any way of creating a sorted index on academic_institutions.name DESC in order to avoid using filesort? And some of the more complex queries have Using temporary as well as Using filesort. Donna Maybe none of understand what your question means? I know I don't. Hi, Is this a very hard question or an absurdly simple one? I posted it days ago, and no-one has responded! Please can someone help? How can I avoid Using temporary, Using filesort when creating indices when my selects MUST be ordered? Donna - 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
Indexes
Filter-fodder: mysql, query Hi, How can I avoid Using temporary, Using filesort when creating indices when my selects MUST be ordered? Donna - 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 lost connection
filter-fodder: mysql, query Hi, I have a persistent connection. but when a query fails for whatever reason, ( eg. user doesnt have permission to delete a row ) I lose the connection. HOW can I stop this happening, and WHY is it happening? Donna - 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
Querying the same field multiple times
filter fodder: sql, query Hi, Hope someone can help with this. I have a table subjects: qsid smallint ... name varchar(60) The table contains eg. Mathematics English Biology ... ... I also have a table vx_students: vxid smallint ... pri_qskey smallint not null default 0 sec_subj smallint not null default 0 ter_subj smallint not null default 0 A common entry into vx_students might be: mysql select * from vx_students where vxid = 1; +--++---+---+---+ | vxid | studentkey | pri_qskey | sec_qskey | ter_qskey | +--++---+---+---+ |1 | 1507 | 124 | 77 | 0 | +--++---+---+---+ The two problems: (a) I can't figure out how to construct the query to get ALL the subject names. To just get one, this works: select vxid, subjects.name from vx_students, subjects where vx_students.pri_qskey=subjects.qsid and vx_students.studentkey=1; (b) I can't figure out how to construct the query to skip if eg. ter_qskey == 0 (although pri_qskey || sec_qskey || ter_qskey could be 0 ). Here's hoping some whizz out there knows the answers. Donna - 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 tutorials, users passwords, interface.
Filter fodder: mysql, query Hi, I think the O'Reilly books - Managing and Using MySQL - MySQL and mSQL and _very_ good. and if you want a real tome, then try - Mastering SQL, by Martin Gruber, pub. by SYBEX Donna On Tuesday 20 August 2002 3:40 pm, John Gruber wrote: Actually... Jay Greenspan's MYSQL Weekend Crash Course is a very quick read and has NT instructions in it. It is limited in its functionality, but if you are used to MS SQL Enterprise Manager, MyCC will be a quick learn for you. The Windows version installs quickly and work reasonably well. John Gruber -Original Message- From: Gavin Alexander [mailto:[EMAIL PROTECTED]] Sent: Monday, August 19, 2002 6:03 PM To: [EMAIL PROTECTED] Subject: MySQL tutorials, users passwords, interface. HI, I am just starting out with MySQL, using Tomcat as standalone on a Win2000 machine and java servlets. Could anybody: 1) Recomend a good book which covers the basics of MySQL, /or any good tutorials on the web? 2) Tell me how to set up usernames and passwords for MySQL. 3) Recomend an easy to use interface for MySQL (can I use phpMyAdmin if I'm not using PHP?). Many thanks, Gavin - 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 - 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 - 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 tutorials, users passwords, interface.
Filter fodder: query, sql On Wednesday 21 August 2002 3:03 am, Nathaniel Mallet wrote: I haven't read Managing and Using MySQL, but I do own (and read cover to cover) MySQL and mSQL. While it's not a bad book, it's definitely not for anyone wanting to do anything serious. It's about 460 pages that covers *two* databases, plus references for half a dozen APIs, which means they skim most topics. Yo, okay - but if you recall Gavin said I am just starting out - and that's why I recommended these books :-) and I think they are pretty good jumping-off points. Besides which, I don't think I agree about the serious bit - all beginners have to start somewhere. ( 'course, I was mad fool enough to jump in with just the online manual to hand ...) donna - 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: Access to MySQL
This solution worked really well for me: www.cynergi.net/exportsql/ Donna On Friday 16 August 2002 6:48 pm, Oladejo, Tokunboh wrote: Hi All, Does anyone knows of any easy way/short cut of exporting Microsoft access tables into MySql database. Thanx All - 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 - 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: Manual availability
Course, you could just be v. nice to me - I made it into separate pages in 2 frames for much the same reason. :-) donna ( v. 3.23.34) On Friday 16 August 2002 9:21 pm, Frank Shute wrote: I'm using 3.23.51 and I wondered if the manual was available as separate pages of html in a tarball. The manual as shipped is a single page and as this machine's dog slow and takes an age to jump from one part of the manual to another, I thought separate pages might improve matters. I can only find the manual for 4.0 at mysql.com in the format I desire. - 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
Query help
filter fodder: sql, query Hi all, I feel like I am probly being amazingly stupid, but here goes nothing. I have 3 tables: students undergrads postgrads students holds all common info. eg dt_matric, dob, religion, etc. undergrads hold eg. degree, subject postgrads holds eg. transfer_date, thesis_title What I need to do is something like this (it does _not_ work): set @acyear:='2001'; select students.course_yr, students.dt_start, students.surname, qual_types.abbrv, qual_subjects.name from students, if ( students.st_type='PG',postgrads, undergrads), qual_types, qual_subjects where (postgrads.qskey=qual_subjects.qsid andpostgrads.qtkey=qual_types.qtid andpostgrads.studentkey=students.studentid) or(undergrads.qskey=qual_subjects.qsid andundergrads.qtkey=qual_types.qtid andundergrads.studentkey=students.studentid) and students.studentid=4; how in £$%£$ name can I do this? Donna - 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
Detecting differences
[filter-fodder: sql, query] Hi, I am having trouble finding differences. Table1 holds the current data, and Table2 holds the updates. I need to query the two and find ONLY the rows showing the current+updates. Table1: TABLE dttunes ( id smallint(5) unsigned NOT NULL auto_increment, danceid smallint(5) unsigned NOT NULL default '0', ddref smallint(5) unsigned NOT NULL default '0', tuneid smallint(5) unsigned NOT NULL default '0', tkey smallint(5) unsigned NOT NULL default '0', tname varchar(100) NOT NULL default '', Table2: TABLE ddtunes ( id smallint(5) unsigned NOT NULL auto_increment, ddref smallint(5) unsigned NOT NULL default '0', tkey smallint(5) unsigned NOT NULL default '0', tname varchar(100) NOT NULL default '', This query: select dttunes.*, ddtunes.* from dttunes left join ddtunes on (dttunes.tkey=ddtunes.tkey and dttunes.tname=ddtunes.tname) where dttunes.ddref=ddtunes.ddref and dttunes.danceid=5688; returns this: | danceid | ddref | tuneid | tkey | tname |5688 | 5829 |140 | 156 | Bonnie Lass o' Bon Accord, The |5688 | 5829 | 1493 | 1613 | Singing Bird, The | ddref | tkey | tname | | 5829 | 156 | Bonnie Lass o' Bon Accord, The | | 5829 | 1613 | Singing Bird, The BUT I only want it to return rows in the event of a different tune being assigned to a dance, ie. where dttunes.ddref != ddtunes.ddref It's hard. I have failed so far. Can anyone help? Donna - 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: Trouble with indexing
Hi Jeremy, On Sunday 05 May 2002 5:52 am, Jeremy Zawodny wrote: REGEXP queries don't use indexes. Change the: REGEXP ^A to LIKE A% and it'll use the index and give you a nice speed boost. which indeed it did! I checked the manual (yet again) and it doesn't actually say anything about regexp not using indices (grr). So I went thru the db fns and stripped out regexp and replaced with like. BUT I have hit a snag on the last query to fix: select dances.danceid as id, dancetitles.title from dances, dancetitles where dances.danceid=dancetitles.danceid and dances.has_crib='T' and dancetitles.title regexp ^[T-Z]; Despite multiple offerings of every perm and comb I can think of (and even rtfm) I can't persuade mysql to eat a like query which will return the same ans as the regexp one. Am beginning to think am flogging a dead horse here? Donna - 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: Trouble with indexing
FilterFodder: sql, query Yo Jeremy, On Sunday 05 May 2002 9:31 pm, Jeremy Zawodny wrote: That one isn't fixed as easily. ... You might be able to use a BETWEEN query: (eyes raised to heaven, hands clasped in prayer) o jeremy you are my hero! (grin) seriously, thanks a lot, i really appreciate your efforts on my behalf - all yr suggestions worked a treat. stay well, stay cool Donna - 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
Trouble with indexing
filter-fodder: sql,query
Hi,
I am trying to speed things up a bit by indexing, but am having a bit of
trouble.
Take two tables:
TABLE dances (
danceid smallint(5) unsigned NOT NULL auto_increment,
ddref smallint(5) unsigned NOT NULL default '0',
has_desc enum('T','F') NOT NULL default 'F',
... ...
PRIMARY KEY (danceid),
) TYPE=MyISAM;
TABLE dancetitles (
dtidsmallint(5) unsigned NOT NULL auto_increment,
danceid smallint(5) unsigned NOT NULL default '0',
title varchar(80) NOT NULL default '',
PRIMARY KEY (dtid),
INDEX danceidx (danceid)
) TYPE=MyISAM;
explain select dances.danceid, dancetitles.title from dances, dancetitles
where dances.danceid=dancetitles.danceid and dancetitles.title regexp ^A
and dances.has_desc='T' order by title;
==
| table | type | possible_keys | key| key_len
| dances | ALL | PRIMARY | NULL |NULL
| dancetitles | ref| danceidx| danceidx | 2
-
ref | rows| Extra
NULL| 10235 | where used; Using temporary; Using filesort
dances.danceid | 1 | where used
This is TERRIBLE! But I can't figure out how to improve it.
Can anyone help?
Donna
-
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
Cross querying databases (again)
filter-fidder: sql, query Hi, Hope you don't mind me hassling you about this yet again. ... So you would need to run two queries, a LEFT JOIN and a RIGHT JOIN (or a LEFT JOIN with tables swapped) to get all results. ... ... which is what I did in the end. except that on further checking it doesn't work! This works fine: SELECT dancedetails.dances.* FROM dancedetails.dances LEFT JOIN dancedata.dances ON dancedetails.dances.ddref=dancedata.dances.dkey WHERE dancedata.dances.dkey IS NULL; But this never finishes: SELECT dancedata.dances.* FROM dancedata.dances LEFT JOIN dancedetails.dances ON dancedata.dances.dkey=dancedetails.dances.ddref WHERE dancedetails.dances.ddref IS NULL; And I don't understand why, for the life of me. Donna (PS: and I *have* RTFM) - 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: Update table1 from table2
Hi, I am using MySql Ver ll.15 distrib. 3.23.41 I want to write an sql query something like: update ddata set ddata.pubkey = pubdata.pubkey, ddata.title=pubdata.title where ddata.dkey=pubdata.dkey; This generates an error: ERROR 1109: Unknown table 'pubdata' in where clause and using this: update ddata set ddata.pubkey = pubdata.pubkey and set ddata.title=pubdata.title where ddata.dkey=pubdata.dkey; generates the error: ERROR 1064: You have an error in your SQL syntax near 'set ddata.title=pubdata.title where ddata.dkey=pubdata.dkey' at line 1 So that didn't work either. Donna - 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: Fatal Error: Call to undefined function: mysql_connect()
1. I found I had a RPM Install of PHP and a compiled version. (Off my book cdrom) :-(( happened to me 2 4. I want to start from scratch but don't want to have to re-format nah. you've rpm -e'd the rpm. I had to build from scratch to compile mysql + php in as I couldn't find an off-the-shelf version which had 'em. 6. Could someone suggest where i could get a PHP copy that supports MYSQL ? http://www.php.net Donna - 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
Update table1 from table2
Hi, I am sure this is an old chestnut, but a web search has revealed no answers. I have: table pubdata ( dkey smallint(5) unsigned NOT NULL default '0', pubkey smallint(5) unsigned NOT NULL default '0', title varchar(100) NOT NULL default '' ); table ddata ( dkey smallint(5) unsigned NOT NULL default '0', name varchar(100) NOT NULL default '', type varchar(30) NOT NULL default '', ... ... pubkey smallint(5) unsigned NOT NULL default '0', title varchar(100) NOT NULL default '' ) I want to write an sql query something like: update ddata set ddata.pubkey = pubdata.pubkey, ddata.title=pubdata.title where ddata.dkey=pubdata.dkey; but guess what? it won't work! what am I doing wrong here? Donna - 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
Update table1 from table2
Hi, I am sure this is an old chestnut, but a web search has revealed no answers. I have: table pubdata ( dkey smallint(5) unsigned NOT NULL default '0', pubkey smallint(5) unsigned NOT NULL default '0', title varchar(100) NOT NULL default '' ); table ddata ( dkey smallint(5) unsigned NOT NULL default '0', name varchar(100) NOT NULL default '', type varchar(30) NOT NULL default '', ... ... pubkey smallint(5) unsigned NOT NULL default '0', title varchar(100) NOT NULL default '' ) I want to write an sql query something like: update ddata set ddata.pubkey = pubdata.pubkey, ddata.title=pubdata.title where ddata.dkey=pubdata.dkey; but guess what? it won't work! what am I doing wrong here? Donna - 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: Cross querying databases
Hi, I am going nuts! I am querying two databases which are very similar to find the differences between various tables. These two tables are 15 rows and 14 rows respectively. Why does this work (finds all similar rows): select ddetails.name, ddata.name from dancedetails.coupletypes as ddetails, dancedata.couples as ddata where ddetails.name = ddata.name; +++ | name | name | +++ | 1 couple | 1 couple | | 2 couples | 2 couples | | 3 couples | 3 couples | ... +++ 14 rows in set (0.00 sec) BUT not this (trying to find the different rows): select ddetails.name, ddata.name from dancedetails.coupletypes as ddetails, dancedata2.couples as ddata where ddetails.name != ddata.name; +++ | name | name | +++ | 2 couples | 1 couple | | 3 couples | 1 couple | | 4 couples | 1 couple | | 5 couples | 1 couple | | 6 couples | 1 couple | ... | 3 facing 3 | other | || other | +++ 196 rows in set (0.12 sec) - 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: Re[2]: Cross querying databases
Guten Tag Donna Robinson, Hi 2u2 sir! and god bless your cotton socks. i hope this will help you.. ... it didn't but it gave me enough of a clue so that this did: select dancedetails.coupletypes.* from dancedetails.coupletypes left join dancedata2.couples on dancedetails.coupletypes.name=dancedata2.couples.name where dancedata2.couples.name is null; So tell me - why does a left join work where an inner join refused to? (have only been sql-ing for about 2 wks) Donna - 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
OpenBSD and MySql
Hi, I am using OpenBSD 2.8 and spent a frustrating evening last night trying to make MySql come to heel. Installed just fine - the first sign of something not right was inability to complete the run-all-tests scripts - just hung there gobbling up 95% CPU. Gave up on that eventually, and tried to load the databases with: /usr/local/mysql/bin/mysql --user=root --password=Dusebig7 dancedetails bars.sql Needless to say, the same thing happenned. I am sure I am not alone in this. Found http://www.hostbaby.com - but he's using OpenBSD 3.0. Being a lazy one, I had just installed 2.8 from the site. To implement his fix, I will have to start from scratch. Is there any other option? Is this the problem? Donna [depressed] - 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
