php/mysql search engine (partly OT?)

[Zaphod Beeblebrox]

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_database_id, tus_site.arsi_local_site_num,
tus_site.arsi_initial_site_num FROM tus_site INNER JOIN site_name_common ON
tus_site.arsi_database_id=site_name_common.sinaco_ arsi_database_id INNER
JOIN site_name_alternate ON
tus_site.arsi_database_id=site_name_alternate.sina alt_arsi_database_id INNER
JOIN oral_history ON
tus_site.arsi_database_id=oral_history.orhi_arsi_d atabase_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_common
site_name_alternate
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?

[Zaphod Beeblebrox]

Zaphod Beeblebrox <fo*@spamsnothere.org> wrote in message
news:zUJHb.852187$pl3.192715@pd7tw3no...

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_database_id, tus_site.arsi_local_site_num,
tus_site.arsi_initial_site_num FROM tus_site INNER JOIN site_name_common ON tus_site.arsi_database_id=site_name_common.sinaco_ arsi_database_id INNER
JOIN site_name_alternate ON
tus_site.arsi_database_id=site_name_alternate.sina alt_arsi_database_id INNER JOIN oral_history ON
tus_site.arsi_database_id=oral_history.orhi_arsi_d atabase_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_common
site_name_alternate
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?

AHA! I figured it out. No indexes on the tables from the conversion from
Access to mySQL. Adding an index to the pk in the tus_site table made the
query almost instantaneous! wooohooo, life is good :)

[Nikolai Chuvakhin]

Wow... People still read Douglas Adams after all those years...

"Zaphod Beeblebrox" <fo*@spamsnothere.org> wrote
in message news:<zUJHb.852187$pl3.192715@pd7tw3no>...

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.

Index all fields referenced in JOIN, WHERE, GROUP BY, and ORDER BY
clauses.

Cheers,
NC

[Zaphod Beeblebrox]

Nikolai Chuvakhin <[e-mail]> wrote in message
news:32**************************@posting.google.c om...

Wow... People still read Douglas Adams after all those years...

Yes, though I still prefer the radio play done by the bbc.
"Zaphod Beeblebrox" <fo*@spamsnothere.org> wrote
in message news:<zUJHb.852187$pl3.192715@pd7tw3no>...

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.

Index all fields referenced in JOIN, WHERE, GROUP BY, and ORDER BY
clauses.

Cheers,
NC