As much of this question relates to mysql, it may be OT?
I'm trying to make a search engine for a reasonably complex database that
was originally developed by someone else in Access. I've ported the data to
mySQL and am reasonably happy with everything except the performance I'm
getting from queries with multpile joins. Although the database is fairly
complex, it's not very large (less than 2 Mb as an Access db, less than that
in mysql tables). Doing a query with a single joined table is fast, two
joined tables is around 2 seconds, and three joined tables takes 12 seconds.
Here's an example of the query:
SELECT tus_site.arsi_d atabase_id, tus_site.arsi_l ocal_site_num,
tus_site.arsi_i nitial_site_num FROM tus_site INNER JOIN site_name_commo n ON
tus_site.arsi_d atabase_id=site _name_common.si naco_arsi_datab ase_id INNER
JOIN site_name_alter nate ON
tus_site.arsi_d atabase_id=site _name_alternate .sinaalt_arsi_d atabase_id INNER
JOIN oral_history ON
tus_site.arsi_d atabase_id=oral _history.orhi_a rsi_database_id
I tried adding a where clause in this, and it was slow enough I had to kill
the mysql process and start again.
There are four tables:
tus_site
site_name_commo n
site_name_alter nate
oral_history
Eventually, I'll have to add a couple of extra tables to the query.and build
an extremely long complex WHERE clause to it. I've done similar queries
using access, ms sql server and oracle and they've ripped through the
queries in milliseconds. Does anyone have any hints on how to get this to
work better with mysql or any other rdbms that won't require an insane
investment in software (the specs for this project state that it must run
from a DVD on each computer). Or is there anything I'm obviously doing
wrong here? I can't believe that mysql can't handle queries like this -
there must be some kind of user error involved?
I'm trying to make a search engine for a reasonably complex database that
was originally developed by someone else in Access. I've ported the data to
mySQL and am reasonably happy with everything except the performance I'm
getting from queries with multpile joins. Although the database is fairly
complex, it's not very large (less than 2 Mb as an Access db, less than that
in mysql tables). Doing a query with a single joined table is fast, two
joined tables is around 2 seconds, and three joined tables takes 12 seconds.
Here's an example of the query:
SELECT tus_site.arsi_d atabase_id, tus_site.arsi_l ocal_site_num,
tus_site.arsi_i nitial_site_num FROM tus_site INNER JOIN site_name_commo n ON
tus_site.arsi_d atabase_id=site _name_common.si naco_arsi_datab ase_id INNER
JOIN site_name_alter nate ON
tus_site.arsi_d atabase_id=site _name_alternate .sinaalt_arsi_d atabase_id INNER
JOIN oral_history ON
tus_site.arsi_d atabase_id=oral _history.orhi_a rsi_database_id
I tried adding a where clause in this, and it was slow enough I had to kill
the mysql process and start again.
There are four tables:
tus_site
site_name_commo n
site_name_alter nate
oral_history
Eventually, I'll have to add a couple of extra tables to the query.and build
an extremely long complex WHERE clause to it. I've done similar queries
using access, ms sql server and oracle and they've ripped through the
queries in milliseconds. Does anyone have any hints on how to get this to
work better with mysql or any other rdbms that won't require an insane
investment in software (the specs for this project state that it must run
from a DVD on each computer). Or is there anything I'm obviously doing
wrong here? I can't believe that mysql can't handle queries like this -
there must be some kind of user error involved?
Comment