I am building a query to select contacts and filter by category. I was
wondering if anyone could explain why the first select statement takes 950
times as long to execute compared to the second. I'm sure it has something
to do with the way the join is processed but I'm still curious what the
engine actually does in each case. In my test the first statement executed
in 0.03 seconds while the second took
Some additional info- In the tests there were 3911 contact records and 17164
contact_category records. The following fields have BTREE indexes:
contact.id, UNIQUE(contact_category.contact_id,
contact_category.category_id)
SELECT DISTINCT contacts.id FROM contacts
LEFT JOIN contact_category ON contacts.id=contact_category.contact_id
WHERE category_id IN (1,2,3,4)
LIMIT 0,500;
----------------------------------------------------------------------------------------
SELECT DISTINCT contacts.id FROM contacts, contact_category
WHERE contacts.id=contact_category.contact_id AND category_id IN (1,2,3,4)
LIMIT 0,500;
----------------------------------------------------------------------------------------
Any tips or clarification on the queries or suggestions how to improve them
or the indexes would be greatly appreciated.
Alex 3 1443
The primary difference is the type of joins being performed. The first
query is (obviously) a left join, using indexed columns from each table
as the join condition. The second, slower, query is an inner join,
which, according to the docs:
INNER JOIN and , (comma) are semantically equivalent in the absence of
a join condition: both produce a Cartesian product between the specified
tables (that is, each and every row in the first table are joined to all
rows in the second table).
which is then (presumably) joining on some non-indexed columns, and,
save the limit clause, would return 67,000,000+ records as opposed to
3911 in the first query.
An EXPLAIN SELECT is very helpful in determining where index and query
optimization should take place.
Alex Glass wrote: I am building a query to select contacts and filter by category. I was wondering if anyone could explain why the first select statement takes 950 times as long to execute compared to the second. I'm sure it has something to do with the way the join is processed but I'm still curious what the engine actually does in each case. In my test the first statement executed in 0.03 seconds while the second took
Some additional info- In the tests there were 3911 contact records and 17164 contact_category records. The following fields have BTREE indexes: contact.id, UNIQUE(contact_category.contact_id, contact_category.category_id)
SELECT DISTINCT contacts.id FROM contacts LEFT JOIN contact_category ON contacts.id=contact_category.contact_id WHERE category_id IN (1,2,3,4) LIMIT 0,500;
----------------------------------------------------------------------------------------
SELECT DISTINCT contacts.id FROM contacts, contact_category WHERE contacts.id=contact_category.contact_id AND category_id IN (1,2,3,4) LIMIT 0,500;
----------------------------------------------------------------------------------------
Any tips or clarification on the queries or suggestions how to improve them or the indexes would be greatly appreciated.
Alex
Thanks for the reply, but if you re-read what I wrote the bizarre thing that
is happening is that the INNER JOIN (Cartesian product) is faster that the
LEFT JOIN which is what I don't quite understand. It seems counter-intuitive
that this be the case...
"hoonew" <ho****@ino.now> wrote in message
news:tlzEe.116931$gc6.54828@okepread04... The primary difference is the type of joins being performed. The first query is (obviously) a left join, using indexed columns from each table as the join condition. The second, slower, query is an inner join, which, according to the docs:
INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table are joined to all rows in the second table).
which is then (presumably) joining on some non-indexed columns, and, save the limit clause, would return 67,000,000+ records as opposed to 3911 in the first query.
An EXPLAIN SELECT is very helpful in determining where index and query optimization should take place.
Alex Glass wrote: I am building a query to select contacts and filter by category. I was wondering if anyone could explain why the first select statement takes 950 times as long to execute compared to the second. I'm sure it has something to do with the way the join is processed but I'm still curious what the engine actually does in each case. In my test the first statement executed in 0.03 seconds while the second took
Some additional info- In the tests there were 3911 contact records and 17164 contact_category records. The following fields have BTREE indexes: contact.id, UNIQUE(contact_category.contact_id, contact_category.category_id)
SELECT DISTINCT contacts.id FROM contacts LEFT JOIN contact_category ON contacts.id=contact_category.contact_id WHERE category_id IN (1,2,3,4) LIMIT 0,500;
----------------------------------------------------------------------------------------
SELECT DISTINCT contacts.id FROM contacts, contact_category WHERE contacts.id=contact_category.contact_id AND category_id IN (1,2,3,4) LIMIT 0,500;
----------------------------------------------------------------------------------------
Any tips or clarification on the queries or suggestions how to improve them or the indexes would be greatly appreciated.
Alex
Indeed, I need to read more carefully... What does EXPLAIN SELECT return?
Alex Glass wrote: Thanks for the reply, but if you re-read what I wrote the bizarre thing that is happening is that the INNER JOIN (Cartesian product) is faster that the LEFT JOIN which is what I don't quite understand. It seems counter-intuitive that this be the case... This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jesse Sheidlower |
last post by:
After some discussion in a separate thread, I've been trying
to get a better understanding of the workings of multiple-column
indexes, and think I'm still missing the point. I understand
indexing...
|
by: ensnare |
last post by:
This query is running REAL slow ... like 1.2 secs ... any ideas on how
I could optimize it? Perhaps my indexes are incorrect?
$this->query = "SELECT m.username as username,
e.title as title,...
|
by: Ike |
last post by:
Oh I have a nasty query which runs incredibly slowly. I am running MySQL
4.0.20-standard. Thus, in trying to expedite the query, I am trying to set
indexes in my tables.
My query requires four...
|
by: Kenneth Courville |
last post by:
I have a couple of tables that look like this (not excactly but close
enough):
id int
fname varchar(50)
lname varchar(50)
id int
|
by: nospammmer |
last post by:
Hello group,
I have a rather general but interesting inquiry that is related to PHP
and I hope this is the appropriate place to post it.
I'm looking for a way to improve dramatically the...
|
by: Troels Arvin |
last post by:
Hello,
At
http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems
it's stated that DB2 doesn't have bitmap indexes. This seemed strange to
me, so I tried looking in...
|
by: Zamdrist |
last post by:
I am tasked with maintaining a large database still on SQL Server 7.0.
Performance is an issue not surprisingly and I've targeted two rather
large tables to see what I can do with the indexes.
...
|
by: annroberti |
last post by:
I have web page that users use to specify sort and selection critieria for a query. So there is a "most common" type set of where clauses, but they can change. Every time the query is run, there...
|
by: Don Li |
last post by:
Hi,
Env: MS SQL Server 2000
DB Info (sorry no DDL nor sample data):
tblA has 147249 rows -- clustered index on pk (one key of
datatype(int)) and
has two clumns, both are being used in joins;...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |