While mysql does have some problem with optimizing subselects it's ususally not that slow.

If you use DBI you can change the database without modifying 99% of your code - just plug in a different DSN, and it uses another DBD::something backend.

If you wrote non-standard SQL you might have to fix some of that when you switch to a different DBMS.

Update: There are other ways of keeping your applications cross-platform btw. One is an ORM-Wrapper like Rose::DB::Object or DBIx::Class, another one is to use SQL::Abstract directly.

Mysql has a my.cnf file that tells mysql how much of your resources to use when executing various types of queries. It ships with quite frugal settings. If you give it more resources (appropriate to your schema) it will often run much faster, often order of magnitude faster.

Even fixing/tweaking that you still have the problem that your queries are glacial. Try:

mysql>EXPLAIN SELECT.......
[download]

Databases are really only fast if you use their power to index. If the DB can not find an appropriate index to use it is forced to iterate through your data. I suspect you don't have the right indexes.

Nobody can really give you much other advice unless you become more specific about what the task is, what your schema is, and what you slow query actually looks like.

As others have mentioned ... your table indexes matter. For example, if you have the two tables defined below, each with a million records:

create table t1 (
   val1 varchar(10) primary key,
   val2 varchar(10)
)

create table t2 (
   tmp1 varchar(10) primary key,
   tmp2 varchar(10)
)
[download]

then the statement:

select t1.val2, t2.tmp2
from t1 join t2 on val2=tmp2
[download]

would likely evaluate to a table scan on t1 with a table scan on t2 for each row in t1, because the join isn't on keyed columns. So it would have to do those table scans on t2 to find the appropriate record(s).

Now the very similar SQL statement:

select t1.val2, t2.tmp2
from t1 join t2 on val1=tmp1
[download]

would evaluate to a table scan on t1 operating in parallel with a single table scan of t2 because the database would be able to take advantage of it's knowledge of the order of items in the two tables.

The overall result is that the first statement is about a million times slower than the second one--all due to indexing.

NOTE: I've oversimplified things a bit, and I don't know the details of mySQL (so I've used syntax compatible with MS SQL Server instead). So while the details may/will be a little off, the overall picture should be accurate.

-sam

Like everyone said, it's not the shoes, it's the dancer..

Please post your table and index definitions, number of rows in each table, and the queries you are trying to run.

All modern databases that have any sort of large user base -- Postgres, MySQL, Oracle, DB2, etc -- can do general "database stuff" more or less equally well (sub-selects being an exception). You just need to know what you are doing with them.

Don't drive a Porsche in first gear and complain about its speed :-)