Re: OT: where to get mySQL questions answered?
From: Five Cats (cats_spam_at_[127.0.0.1)
Date: 12/30/03
- Next message: CountScubula: "Re: Dynamic directory structures"
- Previous message: CountScubula: "Re: PHP poll results question"
- In reply to: Zaphod Beeblebrox: "OT: where to get mySQL questions answered?"
- Next in thread: Randell D.: "Re: where to get mySQL questions answered?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Tue, 30 Dec 2003 09:23:34 +0000
In message <C41Ib.860092$6C4.759329@pd7tw1no>, Zaphod Beeblebrox
<foo@spamsnothere.org> writes
>Hi:
>
>After resolving some performance issues with mysql in building initial
>queries for a search engine, I'm experiencing more crippling problems as
>soon as I add an OR to my WHERE x LIKE '%blah%' type queries.
>
>Does anyone know where I should ask this type of question? I can't find any
>usenet groups on my ISP's server that match, and am in a bit of a bind here.
I also can't find a comp.databases.mysql, sadly.
>
>If anyone could be so kind as to point me in the right direction as to where
>to ask, or can answer the following question, it would be greatly
>appreciated.
>
>My php code dynamically builds an sql query. If I type in a single word in
>a text box, it generates something like this:
>
>SELECT tus_site.arsi_database_id AS foo, 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.sinaalt_arsi_database_id INNER
>JOIN oral_history ON
>tus_site.arsi_database_id=oral_history.orhi_arsi_database_id WHERE
>oral_history.orhi_desc LIKE '%things%' OR tus_site.arsi_location_desc LIKE
>'%things%'
>
>If more than one word is typed in, it adds to the WHERE clause.
>
>With even one word being searched for (basically two criteria in the where
>clause) mysqld locks up completely on Win98. If I simplify the query by
>hand to have only one criterion in the WHERE clause, it works just fine and
>returns expected results. I've used similar queries in a number of other
>database systems, and never had a problem. Any insight? As a side note, to
>get as far as I have so far, I had to manually add an index to the tus_site
>table.
>
Investigate what the correct syntax is in a command-line interface to
MySQL, then tweak your code to correct it. I suspect that maybe you
should be using the 'IN' clause not the 'LIKE' clause.
See:
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#
IDX1291
and
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#
IDX1217
BTW this looks like a potentially very inefficient query if the DB is of
any size because of the WHERE clause - it might well have to scan the
whole table, depending on your indexing.
You can also use simple joins in the WHERE clause instead of the INNER
JOIN syntax:
WHERE tus_site.arsi_database_id=site_name_common.sinaco_arsi_database_id
AND
tus_site.arsi_database_id=site_name_alternate.sinaalt_arsi_database_id
AND tus_site.arsi_database_id=oral_history.orhi_arsi_database_id
BTW I like to keep the table & column names short - 18 chars or under.
It reduces typing and if I can't think of a suitable on in that length I
don't know that I can with a longer one.
>
-- Five Cats Email to: cats_spam at uk2 dot net
- Next message: CountScubula: "Re: Dynamic directory structures"
- Previous message: CountScubula: "Re: PHP poll results question"
- In reply to: Zaphod Beeblebrox: "OT: where to get mySQL questions answered?"
- Next in thread: Randell D.: "Re: where to get mySQL questions answered?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
|
