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_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?

[Zaphod Beeblebrox]

Zaphod Beeblebrox <fo*@spamsnothe re.org> wrote in message
news:zUJHb.8521 87$pl3.192715@p d7tw3no...

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?

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*@spamsnothe re.org> wrote
in message news:<zUJHb.852 187$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.goo gle.com...

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*@spamsnothe re.org> wrote
in message news:<zUJHb.852 187$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