SQL - When to Convert?

T

Tim McGavin

My database (single user) is approaching 17,000 records (about 10 fields
each). Doing searches on them takes a couple of seconds. I only have a
single user.

How do I know when it's time to convert to MySQL? Am I anywhere near that?
(can't afford MS SQL)

Tim.
 
A

Arvin Meyer [MVP]

A search on a properly indexed table of a couple of hundred thousand records
should take under a second. I have 53 users on 130 MB of data and no search
takes over a second.

So your answer is, at your rate of growth, never. But I would suggest you
learn to index and write queries better. MySQL will not be any faster, and
with improperly designed indexes will be significantly slower
 
T

Tony Toews [MVP]

kingston via AccessMonster.com said:
Also, if you only have one user, take a look at MS SQL
Server Express. It's free AFAIK.
Click to expand...

Yes, it is free but many more than one user can use it. It's also a
significantly effort to work with.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
T

Tim McGavin

So your answer is, at your rate of growth, never. But I would suggest you
learn to index and write queries better. MySQL will not be any faster, and
with improperly designed indexes will be significantly slower
Click to expand...

Arvin,

Is there a specific method for indexing? I didn't realize it was something
that had to be learned. Could you point me in the right direction and maybe
include some comments on how to do this?
 
A

Arvin Meyer [MVP]

You index the Primary Key and Foreign key, You also index most fields used
for a lookup. When you build the queries, the proper way is to use the PK
column first, then search criteria on the main table, then the join keys
followed by any other data. If you index FirstName/LastName in the table,
that's the way you should build your query, not LastName/FirstName. In
SQL-Server, it is crucial to get that right.
 
Top