Welcome to WebmasterWorld Guest from 107.20.54.98

Forum Moderators: coopster & jatar k & phranque

Message Too Old, No Replies

SQL Joins & Speed

Which is faster or is there no differnece?

     
8:30 am on Mar 29, 2003 (gmt 0)

Senior Member

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

joined:Feb 4, 2002
posts:5044
votes: 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

12:34 pm on Mar 29, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 22, 2002
posts:2546
votes: 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,

12:50 pm on Mar 29, 2003 (gmt 0)

Senior Member

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

joined:Feb 4, 2002
posts:5044
votes: 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

1:03 pm on Mar 29, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 22, 2002
posts:2546
votes: 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? ;)

4:36 pm on Mar 29, 2003 (gmt 0)

Senior Member

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

joined:Mar 15, 2002
posts:6807
votes: 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...

4:44 pm on Mar 29, 2003 (gmt 0)

Senior Member

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

joined:Mar 15, 2002
posts:6807
votes: 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...
4:54 pm on Mar 29, 2003 (gmt 0)

Senior Member

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

joined:Feb 4, 2002
posts:5044
votes: 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

7:56 pm on Mar 29, 2003 (gmt 0)

New User

10+ Year Member

joined:Mar 1, 2003
posts:19
votes: 0


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

Jocelyn

8:23 pm on Mar 29, 2003 (gmt 0)

Junior Member

10+ Year Member

joined:July 9, 2002
posts:134
votes: 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.

10:35 pm on Mar 29, 2003 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Oct 15, 2002
posts:710
votes: 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>

11:21 pm on Mar 29, 2003 (gmt 0)

Senior Member

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

joined:Mar 15, 2002
posts:6807
votes: 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...