Jonathan Trahair wrote:
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
Have you thought about putting in an index on CustomerCode? And have
you run EXPLAIN on your select statement?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
