text search on a many to many linked table

[Andrew Crowe]

Hi guys,

I have a table currently set up like this:

[video] <- [city_video_link] -> [city]
video
------
[p]video_id
video_name
etc.

city_video_link
---------------
[f]video_id
[f]city_id

city
----
[p]city_id
city_name
Where one video can linked to many cities.
I would like to be able to do a free text search on many fields and
other linked tables including cities. So far the best query I've come up
with is

--
SELECT DISTINCT videos.*
FROM video, city_video_link INNER JOIN city ON
city.city_id=city_video_link.city_id

WHERE

(
videos.video_id=city_video_link.video_id
AND
city.city_name REGEXP 'london'
)
--

However as soon as more many to many tables are added to the query then
it soon becomes very slow. eg.

--
SELECT DISTINCT videos.*
FROM video, city_video_link INNER JOIN city ON
city.city_id=city_video_link.city_id,
country_video_link, INNER JOIN country ON
country.country_id=country_video_link.country_id

WHERE

(
videos.video_id=city_video_link.video_id
AND
city.city_name REGEXP 'london'
)
OR
(
videos.video_id=country_video_link.video_id
AND
country.country_name REGEXP 'london'
)
--

I can speed it up by changing it to

--
WHERE
(
videos.video_id=city_video_link.video_id AND
videos.video_id=country_video_link.video_id
)
AND
(
city.city_name REGEXP 'london'
OR
country.country_name REGEXP 'london'
)
--

But if there isn't a corresponding link in one of the tables then the
record isn't found even if the other tables match.

Is there any way I can get this query running at a practical speed, or
will I have to re-think the way the database works?
Thanks
Andrew

[Bill Karwin]

Andrew Crowe wrote:

But if there isn't a corresponding link in one of the tables then the
record isn't found even if the other tables match.

Is there any way I can get this query running at a practical speed, or
will I have to re-think the way the database works?

I recommend you learn how to use an OUTER JOIN. It should be covered in
almost any book on SQL, or you can google for "sql outer join tutorial"
or some such query.

Regards,
Bill K.

[Andrew Crowe]

Bill Karwin wrote:

Andrew Crowe wrote:

But if there isn't a corresponding link in one of the tables then the
record isn't found even if the other tables match.

Is there any way I can get this query running at a practical speed, or
will I have to re-think the way the database works?

I recommend you learn how to use an OUTER JOIN. It should be covered in
almost any book on SQL, or you can google for "sql outer join tutorial"
or some such query.

In MySQL OUTER JOIN is the same as doing a LEFT JOIN, which after a few
tables are added makes the query run very slowly.

Is there a way to make LEFT JOINs faster, or am I going to have to
approach this from a different angle such as using UNIONs?

[Bill Karwin]

Andrew Crowe wrote:

[If] MySQL OUTER JOIN is the same as doing a LEFT JOIN, which after a few
tables are added makes the query run very slowly.

Is there a way to make LEFT JOINs faster, or am I going to have to
approach this from a different angle such as using UNIONs?

I'm assuming you have created indexes on video_id, city_id, country_id,
etc. in all these tables. Try running the query with the EXPLAIN
command to get more information about how MySQL is utilizing indexes.

Also note that REGEXP _cannot_ use an index, because the pattern you are
comparing could be a substring within the city_name or country_name.

Another suggestion is to use a simpler comparison than REGEXP if you are
just looking for fixed strings. Try using LOCATE('london', city_name)
for instance. Read the MySQL docs on string functions for more
possibilities.

Here's one alternate query that should work, if you are using MySQL 4.1
for the subquery support:

SELECT DISTINCT V.*
FROM videos V
WHERE
V.video_id IN (SELECT CV1.video_id FROM city_video_link CV1 INNER
JOIN city C1 USING (city_id) WHERE LOCATE('london', C1.city_name))
OR
V.video_id IN (SELECT CV2.video_id FROM country_video_link CV2 INNER
JOIN country C2 USING (country_id) WHERE LOCATE('london', C2.country_name))
OR ... add other terms as needed ...

I hope MySQL knows how to factor out invariant subqueries. That is,
execute the subquery once and cache the result, instead of for every row
in videos.

Regards,
Bill K.