[plug] Optimise MySQL query

[Shannon Carver]

> -----Original Message-----
> From: plug-bounces at plug.org.au [mailto:plug-bounces at plug.org.au] On Behalf
> Of Bernd Felsche
> Sent: Wednesday, 29 March 2006 2:31 PM
> To: plug at plug.org.au
> Subject: Re: [plug] Optimise MySQL query
> 
> "James L. Clarke" <jamesc at global.net.au> writes:
> 
> >Knowing how to write SQL queries is one of the most useful skills
> >you can aquire if you are doing any type of programming that
> >requires interaction with a relational databases. Although each
> >different database has special things it can do with SQL, most
> >databases support the ANSI Stardard SQL, so learning that would be
> >your first step.
> 
> More importantly, you should first learn about RDBMS. Codd and Date.
> 
> Start:
> 	http://en.wikipedia.org/wiki/Codd's_12_rules
> 
> If you know the rules and appreciate why they are, then most
> database structures and their applications software look very
> depressing.
> 
> Once you understand normalisation,. most real-life applications look
> definitively evil.
> 
> >P.S. I know some people on the Linux group will debate whether
> >Access is a relational database. For less than five users (ie small
> >business) it is fine as long spilt the code and data it two
> >seperate files and you design the thing properly, for more than
> >five users you can still use the access for the application but put
> >the data in SQL Server, Oracle, DB2 or MySQL. I guess the reason it
> >has such a bad rap is that because it is cheep so many people who
> >don't really know what they are doing have used it and made a mess.
> >However the same could be said of MySQL now-a-days.
> 
> Access is *useless* if you have more than a few thousand records.
> 
> It is painfully slow. I was able to write an application using *nix
> utilities in a shell program that worked faster and more reliably.
> And it took me less time to write the shell-based application than
> it took to load the data into Access.
> 
> I later converted the shell programs and data into an application
> built on a proprietary (Progress) database.
> 
> Access gives you NO transaction control. I found no way to persuade
> it to index data. There is no locking. Data can change while you're
> trying to change it if you've foolishly allowed others access to the
> same files.
> 
> There is no crash recovery. If you're writing to the database when
> the machine wedges or power goes off, your database has a high
> probability of being screwed.
> 
> Some people foolishly share their access database files over the
> network. That is bad in a great number of ways.
> 
> I will not hesitate to recommend against the use of Access for any
> serious work. I don't even use it for trivial stuff.

Amen to that.  The last time I had to use Access in a serious sense was on a
web project a few months ago, a fairly large real estate/rentals listings
website.  The original creators of the website/database had done the
database setup via access then were accessing it via ASP/OLEDB.  This worked
OK when the project first started, of course as the site grew, performance
dropped to its knees.

Needless to say, a few days work and move to MS-SQL saved the day (We ran
Windows & Linux Webservers at work of course).  The thing that amazed me
most was the complete lack of design in some of the pages.  They had the one
DB Design for say houses, then 5 copies of that database for all the
different types of properties (holiday, commercial, low-cost, apartments,
etc etc) then a copy of the lot for rentals.

Yes, that's right, when I was given it, there was 12 identical access
databases for 12 identical sections all referenced from the one site.
> --
> /"\ Bernd Felsche - Innovative Reckoning, Perth, Western Australia
> \ /  ASCII ribbon campaign | "Laws do not persuade just because
>  X   against HTML mail     |  they threaten."
> / \  and postings          | Lucius Annaeus Seneca, c. 4BC - 65AD.
> 
> _______________________________________________
> PLUG discussion list: plug at plug.org.au
> http://www.plug.org.au/mailman/listinfo/plug
> Committee e-mail: committee at plug.linux.org.au