MySQL How many records?

[dougw]

I work for the Head Office of a group of retail stores.

I will be getting 2000 records per store, per week from 45 of those stores. The information will be used for sales reporting, ranking, forcasting, etc.

For various reasons I am still using FM 6.04 on Windows 2000 2.4GHZ.

As a test I set up a file and started importing records from just 6 stores. It worked okay until I hit 400,000 records. At 500,000 it's gotten too slow.

I see that FM9 will work with MySQL and am looking at upgrading the system anyways before June 30.

Has anybody had any experience with Filemaker and MySQL where there will be approximately 4.7 million records per year (plus more as we add new stores)?

I need to know though whether it will work or if I should be looking at another method of dealing with the volume of records.

thanks in advance

Doug

[Simon K]

At the risk of providing a provocative response I don't think this (FM/MySQl) is the way to go.

If this was me I would be looking at SQL Server.

Handling 5m+ records, whether you use SQL Server of MySQl) you are going to need to become pretty slick at indexing and clustered indexes etc and SQL structures and that is before you get to the user interface environment.

If you are committed to FM as the user interface/reporting tool then great, but you will also need an add-on of some sort to do any graphical reporting - which will look flash (literally) but maybe require a lot of maintenance for user queries and changes to user queries

FM is a very fast dev environment, once you know what it does - but if you are new to FM it definitely has its quirks

Alternatively, (and if budget is an issue) look at some of the low end (sort of "new generation") off the shelf BI tools eg Omniscope from Visokio, not the prettiest but unbleivelably quick return on your money and you can pretty much let users loose on it immediately - ie they can structure queries directly.

I am sure you have already looked at the big players and they can be a little pricey!

Hope this helps

Simon

(PS we are a software house that specialise in retail management systems epos and e-comm, for multi site, multi channel retailers! so its not without some experience of the problems you are going to face)

[Cortical]

I have accumulated 470,000 records in 2 months in one MySQL table, works well enough. From my understanding of MySQL , a couple of million is trivial.

Using FM as an interface, has a number of issues. Read the FM documentation for starters. Speed can be severely restrictive depending on your design, due to the way FM handles sorting in particular (it downloads 'everything'...).

As the literature suggests, FM is not meant to be an interface for SQL