Perhaps you want the ON syntax
SELECT x, y, z
FROM table t, lookupA a, lookupB b
WHERE t.aID = a.aID AND t.bID = b.bID
is equivalent to:
SELECT x, y, z
FROM table t
INNER JOIN lookupA a ON t.aID = a.aID
INNER JOIN lookupB b ON t.bID = b.bID
If you were to do:
SELECT x, y, z
FROM table t
INNER
Chris W wrote:
I often find that I have more than one column in a tale that is an
integer ID used to join to a lookup table. If there is only one Join to
do it is to do something like this
SELECT t.data, l.group
FROM table t JOIN lookuptable l USING (groupID)
WHERE whatever
however if I
I often find that I have more than one column in a tale that is an
integer ID used to join to a lookup table. If there is only one Join to
do it is to do something like this
SELECT t.data, l.group
FROM table t JOIN lookuptable l USING (groupID)
WHERE whatever
however if I need to join mor
I thought I'd pass this on for those of you that have slow table joins. I'm
sure a few of you have already figured this out, but if not, here it is.
I have some slow table joins, namely a 6 table join using a primary integer
index field which takes 15 seconds to pull in 18k rows fr
Thanks for the reply, it's much appreciated. I'll have another look at
the tables, although my criteria were such that the query should have
only returned a tiny fraction (<0.1%) of the rows (which is why I was
confused). If I get any closer to a solution I'll try and post with a
bit more info.
An
On 5/4/06, sheeri kritzer <[EMAIL PROTECTED]> wrote:
(again, apologies for the lateness...)
MySQL has a cost-based optimizer. If it's deciding that a full-table
scan is appropriate, there's a reason. If more than 30% (approx) of
the table would be returned in a range query, the optimizer reaso
(again, apologies for the lateness...)
MySQL has a cost-based optimizer. If it's deciding that a full-table
scan is appropriate, there's a reason. If more than 30% (approx) of
the table would be returned in a range query, the optimizer reasons
that it's LESS expensive to just do a full table sc
Hi,
I have been having a hassle getting the index_merge to work as expected
when I am joining 2 tables on MySQL 5.0.19. The following example should
make it clear:
Table A
key1 (primary key)
key2
some_data
Table B
key1 (indexed)
key2 (indexed)
more_data
SELECT a.key1,a.key2,b.more_d
Barry wrote:
Can someone plaese help? I have three tables 1st is a collection of
propertys, 2nd is a list of facilities(98 in total) and the third is a
list of property id's corresponding to the facilities id's offered at
each property,
The query I am running:
'SELECT'
'`'.$type.'`.`id`,'
'`'.$t
Barry wrote:
Can someone plaese help? I have three tables 1st is a collection of
propertys, 2nd is a list of facilities(98 in total) and the third is a
list of property id's corresponding to the facilities id's offered at
each property,
The query I am running:
'SELECT'
'`'.$type.'`.`id`,'
'`'.$t
Can someone plaese help? I have three tables 1st is a collection of
propertys, 2nd is a list of facilities(98 in total) and the third is a
list of property id's corresponding to the facilities id's offered at
each property,
The query I am running:
'SELECT'
'`'.$type.'`.`id`,'
'`'.$type.'`.`name`,'
My responses blended in
Jason Johnson <[EMAIL PROTECTED]> wrote on 04/04/2005 02:19:12 PM:
> The premise of the query is to return required continuing education
> hours for the entire membership of the organization. Limited to one
> member when providing a membership ID.
>
> The query is a
j llarens wrote:
2) varchar(255) on ALL fields? That's unlikely, in-cre-di-ble. Right type for the right
data, int for numbers, float for money, char for fixed string, and certainly NOT 255 for
lenght! If its necesary such amount of characters, TEXT or BLOB must be used, but only if
it is necesary.
I wonder how somebody can approve such a query and tables.
1) There is no indexes, thats the main reason for the time and cpu consuming.
At least,
the fields used in the joins MUST be indexed.
2) varchar(255) on ALL fields? That's unlikely, in-cre-di-ble. Right type for
the right
data, int for
: Monday, April 04, 2005 1:30 PM
To: MySQL list
Subject: Re: Performance Tuning - Table Joins
At 12:22 PM 4/4/2005, you wrote:
>I have been struggling to maintain decent performance on a web/database
>server for a good 6 months now due to MySQL performance issues. I have
>decided that my best
At 12:22 PM 4/4/2005, you wrote:
I have been struggling to maintain decent performance on a web/database
server for a good 6 months now due to MySQL performance issues. I have
decided that my best option at this point is to take it to the list, so in
advance, I thank you all for taking a look.
The premise of the query is to return required continuing education
hours for the entire membership of the organization. Limited to one
member when providing a membership ID.
The query is a little bulky, and fortunately I cannot take credit for
its design, but here goes (keep in mind that so
On Apr 4, 2005, at 1:22 PM, Jason Johnson wrote:
I have been struggling to maintain decent performance on a
web/database server for a good 6 months now due to MySQL performance
issues. I have decided that my best option at this point is to take it
to the list, so in advance, I thank you all for
I have been struggling to maintain decent performance on a web/database
server for a good 6 months now due to MySQL performance issues. I have
decided that my best option at this point is to take it to the list, so
in advance, I thank you all for taking a look.
There is no error messages that c
Is the maximum number of table joins still 31 in MySQL 5.xx?
On Sat, 2004-05-15 at 04:04, David Blomstrom wrote:
> --- Jigal van Hemert <[EMAIL PROTECTED]> wrote:
>
> > If you need to know how to display the resulting
> > record sets, example 1 on:
> > http://www.php.net/manual/en/ref.mysql.php
> > gives you a complete piece of code to print out the
> > re
Brad Eacker wrote:
> Jigal van Hemert writes:
> >Do you mean temporary tables? These are only necessary when there's no
way
> >to solve the problem with a join.
>
> Actually a temporary table can be used with a join to do what is
usually
> knows as a sub-select or sub query. In this fashion y
Jigal van Hemert writes:
>Do you mean temporary tables? These are only necessary when there's no way
>to solve the problem with a join.
Actually a temporary table can be used with a join to do what is usually
knows as a sub-select or sub query. In this fashion you select the elements
that wo
--- Jigal van Hemert <[EMAIL PROTECTED]> wrote:
> If you need to know how to display the resulting
> record sets, example 1 on:
> http://www.php.net/manual/en/ref.mysql.php
> gives you a complete piece of code to print out the
> resulting records.
OK, I think this example points out what I'm doi
--- Jigal van Hemert <[EMAIL PROTECTED]> wrote:
Great, you've given me a lot of ideas (and examples)
to play with. Thanks!
__
Do you Yahoo!?
SBC Yahoo! - Internet access at a great low price.
http://promo.yahoo.com/sbc/
--
MySQL Genera
> I think part of the confusion stems from the dynamic
> tables I was creating with Dreamweaver. I thought they
> were a necessary part of the equation, when they may
> in fact be optional.
Do you mean temporary tables? These are only necessary when there's no way
to solve the problem with a join.
I'm trying to learn how to join tables, but I'm doing
something wrong. I've found lots of examples that look
easy, but something isn't clicking.
I think part of the confusion stems from the dynamic
tables I was creating with Dreamweaver. I thought they
were a necessary part of the equation, when t
You might want to append table to table.. in this case you should use UNION
(not JOIN).. but if you got 2 identical tables of type MyISAM, then you can
define a MERGE table like this:
CREATE TABLE new_table (*table definition of the original tables*)
type=MERGE union=(all_by_Payroll,payinc);
then
Hi,
I have 2 identical tables and wish to join them. I am a complete novice and
thought it was simple!
Here is the code that I am using with asp.net
select Date, Payroll, First, Last, Rank, Number, Division, Reason, ImpDate
from all_by_Payroll, payinc where "+ DropDownList1.SelectedItem.Value +
At 11:11 +0100 9/3/03, Liz Scates wrote:
Hi,
We are running numerous queries which join anything from 3 - 10
tables together.
Can I please ask the advice of the group as to the most efficient
way to write such queries in MySQL V4. The example below has one 3
tables but 6/7 is more common.
If a
Hi,
We are running numerous queries which join anything from 3 - 10 tables together.
Can I please ask the advice of the group as to the most efficient way to write such
queries in MySQL V4. The example below has one 3 tables but 6/7 is more common.
1. using the WHERE statement to show the matc
Ver. 3.23
How do I write a select query to join more than two tables?
Table A relates to table B and table B relates to Table C.
I need to return fields from Table A and C that are related..
Thanks,
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubs
PROTECTED]
Sent: woensdag 9 juli 2003 18:18
To: [EMAIL PROTECTED]
Subject: order of table joins or where clauses relevant?
As we're on this topic in another thread right now:
Say I have a SELECT query from more than one table and with some
conditions, does it matter in what order I enter the tab
The order of the WHERE clause does make a difference.
The same is true of table joins. I don't think that
the order of tables in the FROM clause makes a
difference.
olinux
--- [EMAIL PROTECTED] wrote:
> As we're on this topic in another thread right now:
>
> Say I have a SEL
As we're on this topic in another thread right now:
Say I have a SELECT query from more than one table and with some conditions, does it
matter in what order I enter the tables in the FROM clause and in what order the WHERE
conditions appear in my query? Or does it make any difference if I use W
Hello.
On Wed 2003-01-22 at 09:13:20 +0100, [EMAIL PROTECTED] wrote:
> Steve,
>
> > ([Defendant] Query WITH a join - 8.79 seconds!
> > EXPLAIN SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE
> > Cases.CaseNumber = Defendants.CaseNumber AND Filed <= "1999-01-01" AND
> > (Defendant LIKE "
Steve Quezadas wrote:
PS Here is some information about my tables and indexes:
Maybe I missed it, but where's the EXPLAIN on the JOIN query?
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock
---
6, 12169 Berlin (Germany)
Tel: +49 30 7970948-0 Fax: +49 30 7970948-3
- Original Message -
From: "Steve Quezadas" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 12:32 AM
Subject: Table joins are slow things to deal with. . .
> Maybe I
Maybe I'm dumb for saying this, but sql joins seems expensive to do in
terms of performance (yes, I indexed the joined fields). If I do a query
search of a 2,600,000 record defendant table WITHOUT a join (SELECT
DISTINCT CaseNumber FROM Defendants WHERE Defendant LIKE "owen%" OR
Defendant LIKE
39 matches
Mail list logo
