At 09:21 AM 3/4/2007, 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
Jonathan,
If you are going to use CustomerCode and InvoiceNo to retrieve the
rows, then create a compound index with CustomerCode and InvoiceNo in that
one index. Why did you choose InnoDb instead of MyISAM? Do you need RI or
are there a lot of people updating the table at the same time? If not,
MyISAM might be a better choice because it's faster.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
