Hi there,
I have a huge querry which takes for ever on a table containing only 2000
records.
Maybe there is a way to reorganize this querry since it contains a join and
lots of other stuff I am not really familar with.
Let me first show you the sql query:
SELECT DISTINCT
u.user_id,
u.user_name,
max(
if(u.sex=2,25,0) +
if(u.age=4,25,0) +
if(u.country='gm',25,0)+
if(l.language_id='de',25,0)
)
AS ranking,
c.country_code,
c.country,
ct.city,
l.language_id,
u.age,
u.sex
FROM userdb.user u, userdb.user_languages l, test.countries AS c
LEFT JOIN test.cities AS ct ON ct.ID = u.city
WHERE
l.user_id = u.user_id
AND u.country = c.country_code
GROUP BY u.user_id
HAVING ranking > 0
ORDER BY ranking desc
Limit 0,10
What this humungous thing does, is to sort out users out of the user table
how fit at least on of the criterias and rank them depending on how many
criterias fit. In this example from 0 % in 25 % steps to 100 % ranking.
Every user can speak up to 3 languages (at least one) which are stored in
the language table. this thing makes it especially slow. whithout the
crossover to the language table it runns ok. So.. right now it takes forever
and I did not even include the interests table (every user can have up to 20
interests).
Did I do something wrong with my db design, or is the querry just not that
good? What could be a way to find the things I want faster?
I appreciate every help on that. Thanx in advance,
Andy
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
