473,560 Members | 3,030 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

mysql vs postgres vs microsoft access

mysql v4.0.16:

I had been using mysql with innodb and thought that was fine, until i used
it for something requiring a few - perhaps slightly involved - joins, and
have now seen the performance become totally unacceptable.

I have a query that takes over 35 seconds using mysql and innodb, for
reasons that are completely a mystery to me, in a result set consisting of
only a handful of items.

I can't go into exact specifics, but I have 2 tables being joined in a
"typical" and very simple manner (e.g. customer to order) and then I have to
do two further joins from that onto a single pretty large table (over 25,000
rows). This large table is being joined onto two times using a standard
inner-type join, i.e. all the 25,000 rows are not coming back in the query,
in fact the number of records coming back is quite small.

microsoft access timings
- 1-2 second perhaps, because 2-4 seconds is the result on a MUCH SLOWER
machine than the Linux machine in question. I'm being conservative because
the Linux machine's processor is 4x faster than the 2-4 second machine. On
another machine that has a 60% faster processor than the Linux one, the
query is essentially instantaneous, definitely less than a second.

mysql timings (using mysql interpreter and timings therefrom directly
(<mysql -p> from command line):
- myisam
- 6.61 seconds
- 6.81 seconds
- innodb
- 35.79 seconds (first time after create table/inserts/etc)
- 35.70 seconds

postgresql timings (using postgresql interpreter directly (<psql> from
command line):
- 6 seconds (first time after create table/inserts/etc)
- 5 seconds
- 5 seconds

MySql is setup on a Linux machine to take over like half of the resources as
well, so I don't think it's a problem of resource starvation.

I thought myisam was supposed to be fast. I would have thought it could go
at least as fast as access. Even postgresql is quicker.

Any comments? I'm now wondering if mysql is total crap and even if postgres
is pretty bad, since it too looks quite poky, though I'm thinking now I
should probably have been using postgres all the time. The postgres setup
is just straight out of the box, not "tuned" at all for performance, whereas
the mysql one is supposed to be somewhat tuned that way, configuration file
is in the "medium" mode as they describe it in the default config files,
wherein it hogs a chunk of the machine's RAM.
Jul 20 '05 #1
8 5130
I wouldn't consider comparison between MS Acess and any true RDBMS
product to be a valid comparison. They are intended to solve different
problems, so they have different constraints in their implementation.

That said, if MS Access solves your application needs and it is faster,
then use MS Acess. But be sure to adopt a rigorous data backup policy! ;-)

But as for the efficiency of MySQL, have you tried using the EXPLAIN
command to see how the RDBMS is optimizing your query? Do you have
appropriate indexes established to help the query in your test case?

Have you read through the optimization tips on Mysql.com and tried some
of the suggestions?
http://dev.mysql.com/doc/mysql/en/My...imization.html
http://dev.mysql.com/doc/mysql/en/Query_Speed.html
http://dev.mysql.com/doc/mysql/en/InnoDB_tuning.html

I don't recommend measuring performance of queries against an InnoDB
table immediately after a data load. There is likely to be some
performance hit as the various caches are primed. Subsequent queries
might become faster when they are able to take advantage of cached
indexes and data.

Regards,
Bill K.
Jul 20 '05 #2
wlcna wrote:
I had been using mysql with innodb and thought that was fine, until iused
it for something requiring a few - perhaps slightly involved - joins,and
have now seen the performance become totally unacceptable.

I have a query that takes over 35 seconds using mysql and innodb, for
reasons that are completely a mystery to me, in a result set consisting of
only a handful of items.

I can't go into exact specifics, but I have 2 tables being joined in a
"typical" and very simple manner (e.g. customer to order) and then I have
to do two further joins from that onto a single pretty large table (over
25,000 rows).**This*la rge*table*is*be ing*joined*onto *two*times*usin g*a
standard inner-type join, i.e. all the 25,000 rows are not coming back in
the query, in fact the number of records coming back is quite small


[snip]

You do have indexes on the columns you are joining, don't you?

You can also use "explain query" where "query" is the query you are running.
This gets MySQL to tell you exactly how it's going to run the query andyou
can work out from the results how much work is actually being done, and
where you need to optimise it.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/
Jul 20 '05 #3

"Bill Karwin" <bi**@karwin.co m> wrote in message
news:cj******** *@enews4.newsgu y.com...
I wouldn't consider comparison between MS Acess and any true RDBMS product
to be a valid comparison. They are intended to solve different problems,
so they have different constraints in their implementation.

Well, since as far as I understand, myisam does table-level locking and
doesn't support lots of common features of RDBMS systems, like stored
procedures and many other things, many would say it is not a "real" RDBMS
either, so I think comparison to access is fairly valid.
That said, if MS Access solves your application needs and it is faster,
then use MS Acess. But be sure to adopt a rigorous data backup policy!
;-)

MS Access doesn't solve the application needs, but it's a performance
baseline.
But as for the efficiency of MySQL, have you tried using the EXPLAIN
command to see how the RDBMS is optimizing your query? Do you have
appropriate indexes established to help the query in your test case?

The timings had an equal playing field, which in all three cases was
virtually no indexing, but you think this explains innodb taking 36 seconds
versus postgres 5-6, and do you think it explains myisam going slower than
postgres? These are the points I'm raising.
Have you read through the optimization tips on Mysql.com and tried some of
the suggestions?
http://dev.mysql.com/doc/mysql/en/My...imization.html
http://dev.mysql.com/doc/mysql/en/Query_Speed.html
http://dev.mysql.com/doc/mysql/en/InnoDB_tuning.html

Optimization is something I would do when I find a performance problem.
This is a definite problem but it is so large with innodb that it is causing
me to dump innodb altogether rather than looking for optimization solutions.
I'm currently switching to postgres, which while still poky, presumably once
optimized will be hopefully tolerable.
I don't recommend measuring performance of queries against an InnoDB table
immediately after a data load. There is likely to be some performance hit
as the various caches are primed. Subsequent queries might become faster
when they are able to take advantage of cached indexes and data.


I've had the data that caused the original problem for days now and the
innodb still takes the same length of time. I just timed it again and it's
36.03 seconds.
Jul 20 '05 #4
wlcna wrote:
The timings had an equal playing field, which in all three cases was
virtually no indexing, but you think this explains innodb taking 36
seconds versus postgres 5-6, and do you think it explains myisam going
slower than postgres?**Thes e*are*the*point s*I'm*raising..


So did you try adding indexes to see if that helps? I've seen on various
different database systems massive differences in adding indexes. Therewas
one SQL Server database table which had several million records and would
grind through the queries taking up to 15 minutes to run some of our more
complex queries. We took a look at the way some of the tables and queries
were structured and modifed the indexes and the time for these queries
dropped to seconds.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/
Jul 20 '05 #5
"Chris Hope" <bl*******@elec trictoolbox.com > wrote in message
news:Y2******** ***********@new s.xtra.co.nz...
wlcna wrote:
The timings had an equal playing field, which in all three cases was
virtually no indexing, but you think this explains innodb taking 36
seconds versus postgres 5-6, and do you think it explains myisam going
slower than postgres? These are the points I'm raising.
So did you try adding indexes to see if that helps? I've seen on various


I just tried adding a few indexes where I thought they should have been
needed on the innodb, and the time did improve vastly I must say.

It's now .04 seconds!

I guess I feel a little more confident about using it now... I think I
figured it would probably only drop to 10 seconds. :)

I knew indexes would help, but I still wonder why it's so much slower than
the others without any such extra help.
different database systems massive differences in adding indexes. There
was
one SQL Server database table which had several million records and would
grind through the queries taking up to 15 minutes to run some of our more
complex queries. We took a look at the way some of the tables and queries
were structured and modifed the indexes and the time for these queries
dropped to seconds.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/


Jul 20 '05 #6
wlcna wrote:
> The timings had an equal playing field, which in all three cases was
> virtually no indexing, but you think this explains innodb taking 36
> seconds versus postgres 5-6, and do you think it explains myisam going
> slower than postgres? These are the points I'm raising.


So did you try adding indexes to see if that helps? I've seen on various


I just tried adding a few indexes where I thought they should have been
needed on the innodb, and the time did improve vastly I must say.

It's now .04 seconds!

I guess I feel a little more confident about using it now...**I*think *I
figured it would probably only drop to 10 seconds.**:)

I knew indexes would help, but I still wonder why it's so much slowerthan
the others without any such extra help.


The slowness is often because you're compounding large non-indexed tables
with other large non-indexed tables. Say for example you have two tables of
20,000 records and you join them on a non-indexed column. It effectively
has to do 20,000 x 20,000 comparisons (ie 400 million) to try to find what
you're wanting. (OK so it's probably not quite as inefficient as that, but
I'm trying to prove a point here). If you have those columns indexed then
it should be able to seek to the appropriate records with only a few looks
at the indexes of each table.

Here's a real world example (well the tables and number of records are,but
I'd never run this query normally). The DBR_Plate table has 286,633 records
in it. The Plate_Propertie s table has 94,019

EXPLAIN SELECT *
FROM DBR_Plate p
INNER JOIN Plate_Propertie s pp ON p.Plate_ID = pp.Plate_ID

table type possible_keys key key_len ref rows Extra
pp ALL PRIMARY NULL NULL NULL 94019
p eq_ref PRIMARY PRIMARY 6 pp.Plate_ID 1

Running this query (ie removing the explain part) took 0.0201 seconds

I removed the indexes and this happens:

table type possible_keys key key_len ref rows Extra
p ALL NULL NULL NULL NULL 286633
pp ALL NULL NULL NULL NULL 94019 Using where

Running this query takes... well I gave up after waiting about 5 minutes and
changed the query to select a single record based on the Plate_ID value..
This query took 0.7223 seconds. Running it again after recreating the
indexes took just 0.0006 seconds.

You can see the massive improvement, especially given I could select all
records with indexes 36 times faster than selecting a single record when
there were no indexes, and selecting a single record was 1200 times faster
with indexes than without. The value of indexes... :)

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/
Jul 20 '05 #7
"wlcna" <wl***@nospam.c om> wrote in message
news:0r******** *********@newss vr19.news.prodi gy.com...
"Chris Hope" <bl*******@elec trictoolbox.com > wrote in message
news:Y2******** ***********@new s.xtra.co.nz...
wlcna wrote:
> The timings had an equal playing field, which in all three cases was
> virtually no indexing, but you think this explains innodb taking 36
> seconds versus postgres 5-6, and do you think it explains myisam going
> slower than postgres? These are the points I'm raising.


So did you try adding indexes to see if that helps? I've seen on various


I just tried adding a few indexes where I thought they should have been
needed on the innodb, and the time did improve vastly I must say.

It's now .04 seconds!


Further update on the other alternatives, fwiw:

mysql w/myisam - 0.00 seconds every time!
postgresql - 11.105, then 0.820, then 0.818 seconds. (these timings have
been with latest 8.0 beta, beta 3, perhaps non-beta older version might be
faster?)

[...]
Jul 20 '05 #8
if you want a mature open source database then check out ingres at ca.com
Jul 20 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
3567
by: OneSolution | last post by:
I need to decide on which database system to use for our company. What is the popular opinion on PostGres vs. MySql? Thanks, Santosh
29
2563
by: smorrey | last post by:
I've been thinking on this long and hard, and I can't seem to come up with an answer on it. Why is it almost always assumed the MySQL will be the server for nearly any PHP app? Why is it MySQL and not PostGRES or SQLite? At this point the only reason I can think of is that MySQL has a much more friendly name. But is that really it?
1
616
by: Fabio Benavides Murillo | last post by:
Hello I have a problem, I want to migrate the data of a postgres' database to a mysql's database, this because i need to move to a windows. I tried making a postgres' backup using pg_dump, but this file doesn't have the insert in Transac-sql. Any help is welcome
74
7903
by: John Wells | last post by:
Yes, I know you've seen the above subject before, so please be gentle with the flamethrowers. I'm preparing to enter a discussion with management at my company regarding going forward as either a MySql shop or a Postgresql shop. It's my opinion that we should be using PG, because of the full ACID support, and the license involved. A...
175
11259
by: Sai Hertz And Control Systems | last post by:
Dear all, Their was a huge rore about MySQL recently for something in java functions now theirs one more http://www.mysql.com/doc/en/News-5.0.x.html Does this concern anyone. What I think is PostgreSQL would have less USP's (Uniqe Selling Points
67
12457
by: Bob Powell | last post by:
To whom it may concern: I find the recent articles in various trade publications a little disturbing due to the lack of PostgrSQL mention. I continue to see articles about how IBM may be considering MYSQL for development an open_source web database. Why isn't PostgreSQL being considered or talked about by major industry giants? As a...
11
9924
by: Mark Cubitt | last post by:
the reason I ask is I have always used postgres at home and work, but my new web host only has mysql :( I want to know what sort of differences I will have in regards to programming php/perl webpages using the database. I current use sequences in postgres and understand this will be different in mysql, so can you tell me what the...
2
3502
by: | last post by:
Perhaps posted it on wrong forums initially as I got only one response (which was helpful but I was looking for more). Please help if you can! =============================================== Hi Database Gurus, Not to start a war among fanatics, but I just wanted to get honest opinion/advise of smart folks like you about this. We are...
9
2450
by: Jacques Lebastard | last post by:
I have a problem writing PHP ODBC scripts that suit both MySQL and PostgreSQL. So far, the following syntaxes seem to apply to each database (this is an 'insert' example; the same differences apply to 'select commands): MySQL: INSERT INTO MyTable (col1, col2) VALUES ("value1","value2") PGSQL: INSERT INTO "MyTable" (col1, col2) VALUES...
0
7636
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7557
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8070
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7603
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6194
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3608
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3590
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2051
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
882
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.