MS ACCESS frontend SLOW 2

[ZOR]

Having fought my way through getting ODBC running with Access and MySql, I now find its like watching paint dry when I go to look at a table with Access, including scrolling down/doing a query. Is this the best I will get??? or have I missed something.

 

[r937]

indexes on the mysql tables??

r937.com | rudy.ca

 

[ZOR]

Many thanks, I have seen a few comments about indexes. I migrated my tables using the MySql Migration Kit frm Access into the database, I have looked for references to where indexes get set up but lost!! I can use HeidiSQL if I knew where to go. I am still needing to be convinced the work needed in the future to use Access as the front end will not be major. I cannot afford data to get corrupted with Primary and Foreign Keys falling over. Thanks

 

[r937]

primary and foreign keys falling over?

if you could run SHOW CREATE TABLE tablename for one of your tables which you find slow to query...

r937.com | rudy.ca

 

[ZOR]

Sorry, but I cannot find where I run SHOW CREATE TABLE. Thanks

 

[r937]

run it wherever you run any mysql query -- command line, mysql browser, heidisql, phpmyadmin, etc.

r937.com | rudy.ca

 

[ZOR]

I hope this is what you want?

CREATE TABLE /*!32312 IF NOT EXISTS*/ `txmasters` (
`ID1` int(10) NOT NULL auto_increment,
`Barcode` varchar(50) default NULL,
`Barcode2` varchar(50) default NULL,
`TXID` varchar(50) default NULL,
`SeriesName` varchar(50) default NULL,
`EpisodeTitle` varchar(50) default NULL,
`Type of material` varchar(50) default NULL,
`Tape Standard` varchar(50) default NULL,
`No of parts` smallint(5) default NULL,
`Audio 1` int(10) default NULL,
`Audio 2` int(10) default NULL,
`Audio 3` int(10) default NULL,
`Audio 4` int(10) default NULL,
`Subtitles` varchar(50) default NULL,
`Sport or Sports` varchar(50) default NULL,
`Competition` varchar(50) default NULL,
`Stage of competition` varchar(50) default NULL,
`EventDate` datetime default NULL,
`Venue` varchar(50) default NULL,
`ContractName` varchar(50) default NULL,
`ClipRights` varchar(50) default NULL,
`PromoRights` varchar(50) default NULL,
`LicenceStart` datetime default NULL,
`LicenceEnd` datetime default NULL,
`Territories` varchar(50) default NULL,
`Additional Information` varchar(50) default NULL,
PRIMARY KEY (`ID1`),
KEY `Barcode` (`Barcode`),
KEY `Barcode1` (`Barcode2`),
KEY `ID1` (`ID1`),
KEY `TXID` (`TXID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Many thanks

 

[r937]

looks okay to me

you don't need

KEY `ID1` (`ID1`),

because as the primay key, there's already an index on it

can you show a typical query that's running slow?

r937.com | rudy.ca

 

[ZOR]

Thanks. This query takes 12 seconds to run, on a table with 172 rows. When the query returns the data, it takes almost the same time again to scroll to the next records in the result table. The query is performed in Access. I also changed the query once it was written in a select query to a pass through, with little if any change in speed. Regards

 

[ZOR]

Thanks. This query takes 12 seconds to run, on a table with 172 rows. When the query returns the data, it takes almost the same time again to scroll to the next records in the result table. The query is performed in Access. I also changed the query once it was written in a select query to a pass through, with little if any change in speed. Regards

SELECT txmasters.Barcode
FROM txmasters;

 

[r937]

sorry, no idea

but obviously, something is seriously wrong

maybe try reinstalling everything (caveat: i am not a database administrator, so i don;t really know if this will solve anything)

r937.com | rudy.ca

 

[ZOR]

Thanks for trying to help. I seem to read in lots of places of people moaning about the slowness between Access and MySql. Its probably something done in Access to deter people moving away from the product. Never mind I will stay with Access until I find a way to sort it out. Thanks again.

 

[mattkins]

Is there any particular reason you want to use Access for the front-end?

 

[ZOR]

Thanks, yes ease of producing user forms, queries, reports etc. Just wish Access was better for multiple users and large data.

 

[ZOR]

A very big SOLVED. I went into control panel/administration settings/ODBC. I looked through the settings, and clicked on the Do Not Trace button, and hey presto it runs like lightening (well almost). So hope that helps someone else. Thanks again to all that tried helping.