Hi Everyone. I have just upgraded a Visual Basic 6 project which used an Access database as a data back end, using DAO and SQL strings. The Access database was exceedingly slow, and prone to glitches. I have changed the VB code to ADO, and set up a MySQL database in the hope that the MySQL database would be better suited to the large size of some tables, and would therefore return queries more quickly. It is now populated with the data from the old Access tables.
So far, the MySQL speed has been very slow. I'm talking about 30 seconds to return the 2 records found by SELECT * FROM OrderItems WHERE CustomerCode = 'ABE001' AND InvoiceNo = 0 ORDER BY OrderNumber, ProductCode, Colour, RecNo from the OrderItems table - 309,000 rows (ie. records). And this is in the MySQL Query Browser, not the VB code. Database information: all 23 tables use the InnoDB, 2 tables have more than 276,000 records, most have a lot less. Uses ODBC. My questions are: 1. Have I chosen the right database? What databases do people use which return data from huge tables in micro-seconds (well, alright, seconds, then!)? 2. Should I set up the database in a different way, if so, how? 3. Is this the right list for such questions? Thanks in advance. Jonathan Trahair