Welcome to WebmasterWorld Guest from 54.147.20.131

Forum Moderators: coopster & jatar k & phranque

Message Too Old, No Replies

SQL Joins & Speed

Which is faster or is there no differnece?

     

Nick_W

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

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



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

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

WebmasterWorld Senior Member 10+ Year Member



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

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

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



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

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

WebmasterWorld Senior Member 10+ Year Member



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

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

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



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

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

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



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

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

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




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

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

10+ Year Member



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

Jocelyn

chameleon

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

10+ Year Member



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

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

WebmasterWorld Senior Member 10+ Year Member



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

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

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



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

 

Featured Threads

Hot Threads This Week

Hot Threads This Month