[thelist] MySQL oddness

[Dan Parry]

> -----Original Message-----
> From: thelist-bounces at lists.evolt.org [mailto:thelist-
> bounces at lists.evolt.org] On Behalf Of Raoul Snyman
> Sent: 29 January 2008 11:13
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] MySQL oddness
> 
> 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;

First thing I did was assume the other person hadn't uploaded the SQL dump
properly so check the fields first :)
 
> 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

Yep that worked... Thanks very much for that :-D

> 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.

Essentially this is a really old query back from the time when my SQL skills
weren't that great... They're not great now but I'm learning every
opportunity I get :)

> I hope this helps.

It certainly does... Only now I have to trawl through loads of code to find
these dodgy statements :)

Dan

> --
> 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/
> 
> --
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
> 
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.516 / Virus Database: 269.19.15/1248 - Release Date:
> 28/01/2008 21:32
> 

No virus found in this outgoing message.
Checked by AVG Free Edition. 
Version: 7.5.516 / Virus Database: 269.19.15/1248 - Release Date: 28/01/2008
21:32