homepage Welcome to WebmasterWorld Guest from 54.234.0.85
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
Forum Library, Charter, Moderators: coopster & jatar k & phranque

Perl Server Side CGI Scripting Forum

    
SQL Joins & Speed
Which is faster or is there no differnece?
Nick_W

WebmasterWorld Senior Member nick_w us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 2330 posted 8:30 am on Mar 29, 2003 (gmt 0)

Hi all,

With SQL Joins, would there be any speed differnce or other performance considerations between these 2 scenarios?

table1
id INT NOT NULL AUTO_INCREMENT,
...seven/eight other columns

table2
id INT NOT NULL UNIQUE (gets value from table above's id)
...2-3 other columns

The 2 queries would be either to select stuff from table1,table2 WHERE table1.id=table2.id

Or the other way around.

Can anyone offer some comment?

Many thanks!

Nick

 

Birdman

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 2330 posted 12:34 pm on Mar 29, 2003 (gmt 0)

I don't completely understand the question, Nick. By "two scenarios", do you mean either using one or two tables.

When I add another table to a database to avoid repetitive data(which I assume is what you are doing), I usually give that table it's own unique id and then add another field to cross reference the first table.

Table "Foo"
foo_id INT NOT NULL AUTO_INCREMENT,

Table "bar"
bar_id INT NOT NULL AUTO_INCREMENT,
foo_bar_id INT NOT,

Nick_W

WebmasterWorld Senior Member nick_w us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 2330 posted 12:50 pm on Mar 29, 2003 (gmt 0)

Hi Birdman,

I meant in the SELECT. Would there be a diffence in starting with one table or the other?

SELECT * FROM table1,table2

or table2,table1

But, since posting this I have toyed with the idea of just adding extra fields to table1.

It's for the 'blog' I've been boring you all about for the last week. I need to categorize posts and thus need a few more fields. Currently I have:

id INT NOT NULL AUTO_INCREMENT,
authorid INT NOT NULL,
date TIMESTAMP,
description VARCHAR(255) NOT NULL,
title(VARCHAR(255 NOT NULL,
text TEXT NOT NULL,

and another table for 'live' and 'allow comments'

I need to add

  • section
  • level (beginer,advanced,scary)
So I was thinking of having 2 tables (and merge the 'live' and 'allow coments') like this:

table1

  • Id
  • Authorid
  • Text
and
  • id
  • Corresponing Id
  • section
  • level
  • is_live
  • allow_comments
  • date
  • title
  • description
Which I think would make it much quicker to buile section indexes and 'recent posts' etc...

Does that make any sense?

Nick

Birdman

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 2330 posted 1:03 pm on Mar 29, 2003 (gmt 0)

I don't think there is a difference in the order you list the tables, yet I could be wrong. Your structure looks good except it seems that 'Level' could go in the first table, since it refers to the user and not the blog entry.

DB dev is fun stuff, huh? ;)

DrDoc

WebmasterWorld Senior Member drdoc us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 2330 posted 4:36 pm on Mar 29, 2003 (gmt 0)

If you're going to query the database where the ID's have to match, then you should definitely do a join, not just look something up in two tables. Also, it's better to join tables than querying the db twice.

Speed? Well, unless your queries get extremely complicated, or the tables are enormous... I'd worry more about server load :) Join is still faster though...

Question: what are the 'live' and 'allow comments' columns for? If 'live'=true and 'allow comments'=true then you can post? If so, why not just make it:

live=0 - not live, no comments
live=1 - live, no comments
live=2 - live, comments

Or, did I misunderstand something? :)

Oh, and table order shouldn't matter...

DrDoc

WebmasterWorld Senior Member drdoc us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 2330 posted 4:44 pm on Mar 29, 2003 (gmt 0)

Also, hmmm... You know it's good to avoid REGEXPs as much as possible, right? If you're not being careful it is possible to create looping REGEXPs that will match either everything or nothing, but you won't see the result until it has tried all possibilities...

Nick_W

WebmasterWorld Senior Member nick_w us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 2330 posted 4:54 pm on Mar 29, 2003 (gmt 0)


live=0 - not live, no comments
live=1 - live, no comments
live=2 - live, comments

Now that's a damn fine idea! - Cheers ;)

Nick

Jocelyn

10+ Year Member



 
Msg#: 2330 posted 7:56 pm on Mar 29, 2003 (gmt 0)

To know more about how mySQL executes your query, do:
EXPLAIN <query>

Jocelyn

chameleon

10+ Year Member



 
Msg#: 2330 posted 8:23 pm on Mar 29, 2003 (gmt 0)

One other thought -- I haven't tried this myself, but I've read that using an INNER JOIN statement is faster than what you have. In other words, this:

SELECT *
FROM from table1 INNER JOIN table2
ON table1.id = table2.id

Instead of this:

SELECT *
FROM table1,table2
WHERE table1.id = table2.id

This was for MS SQL Server, but it may be true of other databases as well.

I've got a lot of great links with SQL performance tips. If anyone wants them, just sticky mail me.

jamesa

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 2330 posted 10:35 pm on Mar 29, 2003 (gmt 0)

On a related note... From my experience you need to watch for the WHERE statement when doing joins. If you have something like this is the WHERE statement you can run into trouble, regardless of how you've indexed:

WHERE table1.id=table2.id AND table1.field1='x' AND table2.fielda='y'

That's because you're actually searching for matches across more than one table, which turns out to be much more resource intensive.

I worked on a site that had only about a couple-hundred thousand rows of data in their database. All the queries searched across multiple tables and they were taking 1-5 minutes to complete depending on the query, and some queries would just hang indefinitely. The ultimate solution was to have all the searchable fields in one table, which brought the times down to a couple of milliseconds per query. Dramatic difference.

<edit>Corrected typo</edit>

DrDoc

WebmasterWorld Senior Member drdoc us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 2330 posted 11:21 pm on Mar 29, 2003 (gmt 0)

Very important point, jamesa! Thanks for the advice.

And, chameleon, you're right... inner joins are more efficient. However, that also depends on the type of query you have...

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Perl Server Side CGI Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved