have you indexed table on uniqueref field? if the table is not indexed on uniqueref than your query will require a full table scan.
(my goodness, that's a HUGE table!) regds, -----Original Message----- From: John Berman [mailto:[EMAIL PROTECTED] Sent: Friday, February 28, 2003 03:34 To: [EMAIL PROTECTED] Cc: 'Jeremy Tinley' Subject: RE: Why Is my dbase slow Jeremy select id from global where uniqueref = '2'". In this case uniqueref = 2 relates to uk civil birth, deaths and marriage records Please see info as requested. Field,Type,Null,Key,Default,Extra ID,int(11),,PRI,NULL,auto_increment UNIQUEREF,double,YES,,NULL, JGSGBCERTNO,varchar(255),YES,,NULL, DISTRICTREG,varchar(255),YES,,NULL, REGDISTRICT,varchar(255),YES,,NULL, COUNTY,varchar(255),YES,,NULL, NOTICEOFMARRDATE,varchar(255),YES,,NULL, dob,varchar(255),YES,,NULL, PLACEOFBIRTH,varchar(255),YES,,NULL, FATHERSNAME,varchar(255),YES,,NULL, FATHEROCCUP,varchar(255),YES,,NULL, MOTHERGNAME,varchar(255),YES,,NULL, MOTHERMNAME,varchar(255),YES,,NULL, MOTHERPSURNAME,varchar(255),YES,,NULL, CONGREGATION,varchar(255),YES,MUL,NULL, SYNAGOGUEREGISTER,varchar(255),YES,,NULL, UNDERTAKER,varchar(255),YES,,NULL, UNDERTAKERSPAGENO,varchar(255),YES,,NULL, CEMETERY,varchar(255),YES,,NULL, CEMETERYADDRESS,varchar(255),YES,,NULL, PLOTLOCATION,varchar(255),YES,,NULL, dode,varchar(255),YES,,NULL, dodh,varchar(255),YES,,NULL, DATEOFBURIAL,varchar(255),YES,,NULL, DATEOFSTONESETTING,varchar(255),YES,,NULL, OCCUPATION,varchar(255),YES,,NULL, TITLE,varchar(255),YES,,NULL, SURNAME,varchar(255),YES,MUL,NULL, MAIDENNAME,varchar(255),YES,,NULL, OTHERSURNAMES,varchar(255),YES,,NULL, GIVENNAMES,varchar(255),YES,,NULL, HEBREWNAMES,varchar(255),YES,,NULL, GENDER,varchar(255),YES,,NULL, AGEATDEATH,varchar(255),YES,,NULL, ADDRESS,varchar(255),YES,,NULL, ADDITIONALADDRESS,varchar(255),YES,,NULL, RANKORPROFESSION,varchar(255),YES,,NULL, DATEOFBIRTH,varchar(255),YES,,NULL, PLACEOFBIRTHa,varchar(255),YES,,NULL, PLACEOFDEATH,varchar(255),YES,,NULL, CAUSEOFDEATH,varchar(255),YES,,NULL, SPOUSENAMESe,varchar(255),YES,,NULL, SPOUSENAMESh,varchar(255),YES,,NULL, SPOUSERANK,varchar(255),YES,,NULL, FATHERNAMES,varchar(255),YES,,NULL, FATHERNAMEh,varchar(255),YES,,NULL, MOTHERNAMES,varchar(255),YES,,NULL, MOTHERNAMESh,varchar(255),YES,,NULL, OTHERSURNAME,varchar(255),YES,,NULL, MOUNRNEDBY,varchar(255),YES,,NULL, SURNAMEOFINFORMANT,varchar(255),YES,,NULL, GIVENNAMEINFORMANT,varchar(255),YES,,NULL, ADDRESSOfINFORMANT,varchar(255),YES,,NULL, RELATIONSHIP,varchar(255),YES,,NULL, MARRIAGEDATE,varchar(255),YES,,NULL, HEBREWMARRIAGEDATE,varchar(255),YES,,NULL, GROOMGIVENNAMES,varchar(255),YES,,NULL, GROOMHEBREWNAME,varchar(255),YES,,NULL, GROOMSURNAME,varchar(255),YES,,NULL, OTHERWISE,varchar(255),YES,,NULL, GROOMAGE,varchar(255),YES,,NULL, GROOMCONDITION,varchar(255),YES,,NULL, GROOMRANK,varchar(255),YES,,NULL, GROOMADDRESS,varchar(255),YES,,NULL, GROOMFATHERGIVENNAME,varchar(255),YES,,NULL, GROOMFATHERHEBREWNAME,varchar(255),YES,,NULL, GROOMFATHERSURNAME,varchar(255),YES,,NULL, GROOMFATHERCONDITION,varchar(255),YES,,NULL, GROOMFATHERRANK,varchar(255),YES,,NULL, BRIDEGIVENNAME,varchar(255),YES,,NULL, BRIDEHEBREWNAME,varchar(255),YES,,NULL, BRIDESURNAME,varchar(255),YES,,NULL, BRIDEPREVIOUSSURNAME,varchar(255),YES,,NULL, BRIDEAGE,varchar(255),YES,,NULL, BRIDECONDITION,varchar(255),YES,,NULL, BRIDERANK,varchar(255),YES,,NULL, BRIDEADDRESS,varchar(255),YES,,NULL, BRIDEFATHERGIVENNAME,varchar(255),YES,,NULL, BRIDEFATHERHEBREWNAME,varchar(255),YES,,NULL, BRIDEFATHERSURNAME,varchar(255),YES,,NULL, BRIDEFATHERCONDITION,varchar(255),YES,,NULL, BRIDEFATHERRANK,varchar(255),YES,,NULL, BRIDESMOTHERGIVENNAME,varchar(255),YES,,NULL, BRIDEMOTHERHEBREWNAME,varchar(255),YES,,NULL, BRIDEMOTHERMAIDENNAME,varchar(255),YES,,NULL, PLACEOFMARRIAGE,varchar(255),YES,,NULL, SURNAMEWITNESS,varchar(255),YES,,NULL, GIVENNAMEWITNESS,varchar(255),YES,,NULL, SURNAMEWITNESSa,varchar(255),YES,,NULL, GIVENNAMEWITNESSa,varchar(255),YES,,NULL, MINISTERREGISTRARSURNAME,varchar(255),YES,,NULL, MINISTERREGISTRARGIVENNAME,varchar(255),YES,,NULL, PARENTGUARDIAN,varchar(255),YES,,NULL, PARENTORGUARDIANOCCUPATION,varchar(255),YES,,NULL, RELIGIONENTERED,varchar(255),YES,,NULL, AGENOTES,varchar(255),YES,,NULL, MARITALSTATUS,varchar(255),YES,,NULL, CHILDREN,varchar(255),YES,,NULL, PARENTS,varchar(255),YES,,NULL, SIBLINGS,varchar(255),YES,,NULL, YEARSMARRIED,varchar(255),YES,,NULL, JCPUBLICATIONDATE,varchar(255),YES,,NULL, POSITION,varchar(255),YES,,NULL, TRADE,varchar(255),YES,,NULL, DIRECTORYSECTION,varchar(255),YES,,NULL, LOCATION,varchar(255),YES,,NULL, DATEREGISTERED,varchar(255),YES,,NULL, YEAR,varchar(255),YES,,NULL, PHOTOFILENAME,varchar(255),YES,,NULL, PROPOSEDRELEASE,double,YES,,NULL, UNIQUEGEOGRAPHICALREFERENCE,varchar(255),YES,,NULL, NOTES,varchar(255),YES,,NULL, LEDGERNO,varchar(255),YES,,NULL, AMOUNTDUE,varchar(255),YES,,NULL, FUTURE4,varchar(255),YES,,NULL, FUTURE5,varchar(255),YES,,NULL, FUTURE6,varchar(255),YES,,NULL, FUTURE7,varchar(255),YES,,NULL, FUTURE8,varchar(255),YES,,NULL, FUTURE9,varchar(255),YES,,NULL, regiment,varchar(255),YES,,NULL, JCPAGENUMBER,varchar(255),YES,,NULL, displaysource,varchar(255),YES,,NULL Table,Non_unique,Key_name,Seq_in_index,Column_name,Collation,Cardinality ,Sub_part,Packed,Comment global,0,PRIMARY,1,ID,A,NULL,NULL,NULL, global,1,free,1,CONGREGATION,A,NULL,NULL,NULL,FULLTEXT global,1,free,2,ADDRESS,A,NULL,NULL,NULL,FULLTEXT global,1,free,3,GROOMADDRESS,A,NULL,NULL,NULL,FULLTEXT global,1,free,4,BRIDEADDRESS,A,NULL,NULL,NULL,FULLTEXT global,1,free,5,PLACEOFMARRIAGE,A,NULL,NULL,NULL,FULLTEXT global,1,free,6,TRADE,A,NULL,NULL,NULL,FULLTEXT global,1,free,7,PARENTGUARDIAN,A,NULL,NULL,NULL,FULLTEXT global,1,free,8,COUNTY,A,NULL,NULL,NULL,FULLTEXT global,1,free,9,REGDISTRICT,A,NULL,NULL,NULL,FULLTEXT global,1,surnamei,1,SURNAME,A,NULL,NULL,NULL,FULLTEXT global,1,surnamei,2,GROOMSURNAME,A,NULL,NULL,NULL,FULLTEXT global,1,surnamei,3,BRIDESURNAME,A,NULL,NULL,NULL,FULLTEXT -----Original Message----- From: Jeremy Tinley [mailto:[EMAIL PROTECTED] Sent: 27 February 2003 21:18 To: [EMAIL PROTECTED] Subject: RE: Why Is my dbase slow Can you post a "desc <table name>" and a "show index from <table name>" to the list so we can see your layout? Also, post "explain select id from global where uniqueref = '2'". Part of the time that is calculated is the transmission time over the network. Try running the query locally (if you can) and see if your results improve. -----Original Message----- From: John Berman [mailto:[EMAIL PROTECTED] Sent: Thursday, February 27, 2003 3:06 PM To: [EMAIL PROTECTED] Subject: Why Is my dbase slow Hi Im fairly new but thought I was doing ok. I have a single table with 123 columns and currently it holds 49,000 records My ISP is running MySql v: 3.23.38 I use MySql Front to access the table The following query took 32 seconds to retrieve 941 records the primary key is the ID field and each records has a unique field, records are grouped into sets by this field. select id from global where uniqueref = '2' Im on a 1MB broadband link and this type of query was taking 1 or 2 seconds when I had 30,000 records, now it seems ot have slowed right down. Any thoughts would be appreciated. Regards John Berman --------------------------------------------------------------------- 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
