[thelist] MySQL oddness

[Raoul Snyman]

Hi Dan,

On Tue, 29 Jan 2008 10:39:15 -0000, "Dan Parry" <dan at virtuawebtech.co.uk>
wrote:
> SELECT DISTINCT `c`.`id` AS `course_id`, `c`.`course_name`, `mr`.* FROM
> snip_map_request `mr`, snip_course_details `c` INNER JOIN
> snip_course_details ON `c`.`id` = `mr`.`course_id` WHERE `mr`.`status` =
1
> ORDER BY `mr`.`created` ASC
That's a little bit of an arb query, but more on that later...

> Works perfectly on our server but the new one throws this error:
> 
> #1054 - Unknown column 'mr.course_id' in 'on clause'
> 
> Anyone got any ideas? I'm stumped, stressed and knackered and would very
> much appreciate any help :)

I would suggest going back to the basics... does that column actually
exist? Have you checked on the new server that it is there? Just go through
everything systematically, and ask someone else to look at it for you.
Sometimes we miss things that others will see.
mysql> describe snip_map_request;

Ok, and also, that query is a little wacky, let's rewrite it to something a
little more sane.

BTW, rule #1 of writing decent SQL: Never use SELECT *
This causes MySQL to first do a look up on all the columns of the table,
before then using them in the SELECT statement. When you specify the
columns, you firstly reduce the time it takes to execute the query and the
load on the MySQL server, and secondly you narrow the data down to only
that which you really need.

SELECT
  DISTINCT
  c.`id` AS course_id,
  c.`course_name`,
  mr.*
FROM
  `snip_course_details` AS c
JOIN
  `snip_map_request` AS mr ON c.`id` = mr.`course_id` AND mr.`status` = 1
ORDER BY
  mr.`created` ASC

Also, DISTICT is a very expensive query. A GROUP BY, if possible, is better
and faster. Also, making sure that your look-up fields are indexed helps to
speed things up.

I hope this helps.

-- 
Raoul Snyman
Saturn Laboratories
e: raoul.snyman at saturnlaboratories.co.za
w: http://www.saturnlaboratories.co.za/
b: http://blog.saturnlaboratories.co.za/
   http://raoulsnyman.co.za/