MySQL Join Optimization?
This is your original query, formatted. Below it are a few thoughts SELECT p. Userid, p.
Firstname, p. Lastname, p. Gender, p.
Dob, x. Relationship, IF(p. Picture!
=1, IF(p. Gender! = 'm', '/sc/f-t.
Jpg', '/sc/m-t. Jpg'), concat('/sc/pthumb/', p. Userid, '.
Jpg' )) AS picture FROM `social` AS p LEFT JOIN `friendlist` AS f1 ON (f1. `friendid` = 1 AND f1. `userid` = p.
`userid` AND `f1`. `status` = 1) LEFT JOIN `friendlist` AS f2 ON (f2. `friendid` = p.
`userid` AND f2. `userid` = 1 AND `f2`. `status` = 1) LEFT JOIN `x_relationship` AS x ON (x.
`id` = p. `relationship`) LEFT JOIN `auth` AS a ON (a. `userid` = p.
`userid`) WHERE 1 AND ( f1. `userid` IS NOT NULL OR f2. `userid` IS NOT NULL AND (a.
`banned`! = 1 AND a. `deleted`!
= 1) ) ORDER BY RAND() LIMIT 0,10 Think if some of your left joins could be inner joins. If so, change them Your WHERE clause is messed up. You are mixing AND and OR without properly prioritizing with parentheses.
Try: WHERE a. `banned`! = 1 AND a.
`deleted`! = 1 AND ( f1. `userid` IS NOT NULL OR f2.
`userid` IS NOT NULL ) You could also try: INNER JOIN `auth` AS a ON ( a. `userid` = p. `userid` AND a.
`banned`! = 1 AND a. `deleted`!
= 1 ) WHERE f1. `userid` IS NOT NULL OR f2. `userid` IS NOT NULL You seem to be joining against friendlist solely to check for record existence.
You might want to give this a try as well: FROM `social` AS p INNER JOIN `auth` AS a ON ( a. `userid` = p. `userid` AND a.
`banned`! = 1 AND a. `deleted`!
= 1 ) LEFT JOIN `x_relationship` AS x ON ( x. `id` = p. `relationship` ) WHERE EXISTS ( SELECT 1 FROM `friendlist` WHERE `friendid` = 1 AND `userid` = p.
`userid` AND `status` = 1 ) OR EXISTS ( SELECT 1 FROM `friendlist` WHERE `friendid` = p. `userid` AND `userid` = 1 AND `status` = 1 ) ORDER BY RAND() might not be the fastest thing on earth, but if this is what you need... Try ordering by a indexed column to see how much impact ORDER BY RAND() has Indexing: You should create a composite index on friendlist(friendid, userid, status) Make sure there is an index on relationship(id) Make sure there is an index on auth(userid).
This is your original query, formatted. Below it are a few thoughts. SELECT p.
Userid, p. Firstname, p. Lastname, p.
Gender, p. Dob, x. Relationship, IF(p.
Picture! =1, IF(p. Gender!
= 'm', '/sc/f-t. Jpg', '/sc/m-t. Jpg'), concat('/sc/pthumb/', p.
Userid, '. Jpg' )) AS picture FROM `social` AS p LEFT JOIN `friendlist` AS f1 ON (f1. `friendid` = 1 AND f1.
`userid` = p. `userid` AND `f1`. `status` = 1) LEFT JOIN `friendlist` AS f2 ON (f2.
`friendid` = p. `userid` AND f2. `userid` = 1 AND `f2`.
`status` = 1) LEFT JOIN `x_relationship` AS x ON (x. `id` = p. `relationship`) LEFT JOIN `auth` AS a ON (a.
`userid` = p. `userid`) WHERE 1 AND ( f1. `userid` IS NOT NULL OR f2.
`userid` IS NOT NULL AND (a. `banned`! = 1 AND a.
`deleted`! = 1) ) ORDER BY RAND() LIMIT 0,10 Think if some of your left joins could be inner joins. If so, change them.
Your WHERE clause is messed up. You are mixing AND and OR without properly prioritizing with parentheses. Try: WHERE a.
`banned`! = 1 AND a. `deleted`!
= 1 AND ( f1. `userid` IS NOT NULL OR f2. `userid` IS NOT NULL ) You could also try: INNER JOIN `auth` AS a ON ( a.
`userid` = p. `userid` AND a. `banned`!
= 1 AND a. `deleted`! = 1 ) WHERE f1.
`userid` IS NOT NULL OR f2. `userid` IS NOT NULL You seem to be joining against friendlist solely to check for record existence. You might want to give this a try as well: FROM `social` AS p INNER JOIN `auth` AS a ON ( a.
`userid` = p. `userid` AND a. `banned`!
= 1 AND a. `deleted`! = 1 ) LEFT JOIN `x_relationship` AS x ON ( x.
`id` = p. `relationship` ) WHERE EXISTS ( SELECT 1 FROM `friendlist` WHERE `friendid` = 1 AND `userid` = p. `userid` AND `status` = 1 ) OR EXISTS ( SELECT 1 FROM `friendlist` WHERE `friendid` = p.
`userid` AND `userid` = 1 AND `status` = 1 ) ORDER BY RAND() might not be the fastest thing on earth, but if this is what you need... Try ordering by a indexed column to see how much impact ORDER BY RAND() has. Indexing: You should create a composite index on friendlist(friendid, userid, status). Make sure there is an index on relationship(id) Make sure there is an index on auth(userid).
OMG.. only adding idex on friendlist decreased the load time by almost 200% – atif089 Nov 12 '09 at 14:52.
FROM a JOIN be LEFT JOIN c ON (c. Key) LEFT JOIN d ON (d. FROM be JOIN a LEFT JOIN c ON (c.
Key) LEFT JOIN d ON (d. SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2. SELECT * FROM t1, t2 WHERE t2.
Column2=5 AND t1.
Yes. Use a view to collect up all the sql common to both queries: CREATE VIEW PUB_ORGS AS SELECT * FROM tx_oriconvocatorias_publicadas AS pubs INNER JOIN tx_oriconvocatorias_publicadas_lineas_mm as ml ON ml. Uid_local=pubs.
Uid INNER JOIN tx_oritablascomunes_lineas as lins ON ml. Uid_foreign=lins. Uid INNER JOIN tx_oriconvocatorias_publicadas_modalidades_mm as mm ON mm.
Uid_local=pubs. Uid INNER JOIN tx_oritablascomunes_modalidades as mods ON mm. Uid_foreign=mods.
Uid INNER JOIN tx_oriconvocatorias_publicadas_organizaciones_mm as mo ON mo. Uid_local=pubs. Uid INNER JOIN tx_oritablascomunes_organizaciones as orgs ON mo.
Uid_foreign=orgs. Uid then build your unioned queries from this.
Yes. Use a view to collect up all the sql common to both queries: CREATE VIEW PUB_ORGS AS SELECT * FROM tx_oriconvocatorias_publicadas AS pubs INNER JOIN tx_oriconvocatorias_publicadas_lineas_mm as ml ON ml. Uid_local=pubs.
Uid INNER JOIN tx_oritablascomunes_lineas as lins ON ml. Uid_foreign=lins. Uid INNER JOIN tx_oriconvocatorias_publicadas_modalidades_mm as mm ON mm.
Uid_local=pubs. Uid INNER JOIN tx_oritablascomunes_modalidades as mods ON mm. Uid_foreign=mods.
Uid INNER JOIN tx_oriconvocatorias_publicadas_organizaciones_mm as mo ON mo. Uid_local=pubs. Uid INNER JOIN tx_oritablascomunes_organizaciones as orgs ON mo.
Uid_foreign=orgs. Uid; then build your unioned queries from this.
Thanks. I will do that. – Memochipan Jul 3 at 18:33.
Joining on the node's name rather than NodeId (the primary key) doesn't look good at all.
I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.