Re: LIKE sql optimization
On 12/02/2014 13:16, Morgan Tocker wrote: Hi Zhigang, On Feb 11, 2014, at 8:48 PM, Zhigang Zhang zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. I think I understand the question - you are asking why MySQL will not index scan, find matching records, and then look them up rather than table scan? I believe the answer is that there is no way of knowing if 1 row matches, or all rows match. In the worst case (where all rows match), it is much more expensive to traverse between index and data rows for-each-record. So a table scan is a “safe choice / has less variance. In addition to what Morgan writes, then with an index scan you will end up doing a lot of random I/O: even if the index scan itself is one sequential scan (which is not guaranteed) then for each match, it will be necessary to look up the actual row. On the other hand a table scan will generally be more of a sequential read as you already have all the data available for each match. Random I/O is more expensive than sequential I/O - particularly on spinning disks - so in general the optimizer will try to reduce the amount of random I/O. In some cases though, you may see the index scan be preferred. Assume you have a query like: SELECT val FROM table WHERE condition LIKE '%abcd'; and you have an index (condition, val) or (val, condition) then the whole query can be satisfied from the index (it's called a covering index). In that case the index scan is usually preferred over the table scan. For the purpose of using an index to do index lookups to find the matching rows rather than doing either a table or index scan for WHERE clauses like LIKE '%abcd' you can do a couple of things: * Duplicate the column used in the WHERE clause, but reverse the string. That way the above WHERE clause becomes: WHERE condition_revers LIKE 'dcba%' This can use an index as it is a left prefix. * If you always look for around the same number of characters at the end in your WHERE clause, you can create a column with just those last characters, e.g. so the WHERE clause becomes: WHERE condition_suffix = 'abcd' Do however be careful that you ensure you have enough selectivity that way. If for example 90% of the rows ends in 'abcd' an index will not do you much good (unless you are looking for the last 10% of the rows). Best regards, Jesper Krogh MySQL Support
RE: LIKE sql optimization
Done. Thand you very much! Zhigang _ From: Jesper Wisborg Krogh [mailto:my...@wisborg.dk] Sent: Wednesday, February 12, 2014 5:30 PM To: Morgan Tocker; Zhigang Zhang Cc: mysql@lists.mysql.com Subject: Re: LIKE sql optimization On 12/02/2014 13:16, Morgan Tocker wrote: Hi Zhigang, On Feb 11, 2014, at 8:48 PM, Zhigang Zhang mailto:zzgang2...@gmail.com zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. I think I understand the question - you are asking why MySQL will not index scan, find matching records, and then look them up rather than table scan? I believe the answer is that there is no way of knowing if 1 row matches, or all rows match. In the worst case (where all rows match), it is much more expensive to traverse between index and data rows for-each-record. So a table scan is a “safe choice / has less variance. In addition to what Morgan writes, then with an index scan you will end up doing a lot of random I/O: even if the index scan itself is one sequential scan (which is not guaranteed) then for each match, it will be necessary to look up the actual row. On the other hand a table scan will generally be more of a sequential read as you already have all the data available for each match. Random I/O is more expensive than sequential I/O - particularly on spinning disks - so in general the optimizer will try to reduce the amount of random I/O. In some cases though, you may see the index scan be preferred. Assume you have a query like: SELECT val FROM table WHERE condition LIKE '%abcd'; and you have an index (condition, val) or (val, condition) then the whole query can be satisfied from the index (it's called a covering index). In that case the index scan is usually preferred over the table scan. For the purpose of using an index to do index lookups to find the matching rows rather than doing either a table or index scan for WHERE clauses like LIKE '%abcd' you can do a couple of things: * Duplicate the column used in the WHERE clause, but reverse the string. That way the above WHERE clause becomes: WHERE condition_revers LIKE 'dcba%' This can use an index as it is a left prefix. * If you always look for around the same number of characters at the end in your WHERE clause, you can create a column with just those last characters, e.g. so the WHERE clause becomes: WHERE condition_suffix = 'abcd' Do however be careful that you ensure you have enough selectivity that way. If for example 90% of the rows ends in 'abcd' an index will not do you much good (unless you are looking for the last 10% of the rows). Best regards, Jesper Krogh MySQL Support
LIKE sql optimization
For example: Select * from T where col like ‘%abcd’; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. Thanks. Zhigang
Re: LIKE sql optimization
Am 12.02.2014 02:23, schrieb Zhigang Zhang: For example: Select * from T where col like ‘%abcd’; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan because ‘%abcd’ can't work in case of a index how do you imagine that? read how a index works technically 'abcd%' may work but '%abcd' is impossible independent what engine, this don't work and won't ever work you may have luck with fulltext search (and it's other drawbacks) https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html signature.asc Description: OpenPGP digital signature
Re: LIKE sql optimization
Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like '%abcd'; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. Thanks. Zhigang
RE: LIKE sql optimization
I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. zhigang _ From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] Sent: Wednesday, February 12, 2014 9:41 AM To: Zhigang Zhang; mysql@lists.mysql.com Subject: Re: LIKE sql optimization Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like ‘%abcd’; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. Thanks. Zhigang
Re: LIKE sql optimization
*read how a index works technically* On Wed, Feb 12, 2014 at 12:48 PM, Zhigang Zhang zzgang2...@gmail.comwrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. zhigang _ From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] Sent: Wednesday, February 12, 2014 9:41 AM To: Zhigang Zhang; mysql@lists.mysql.com Subject: Re: LIKE sql optimization Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like '%abcd'; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. Thanks. Zhigang
Re: LIKE sql optimization
because a index is not just a dumb copy of the whole field and you simply can't seek in the middle of it? http://en.wikipedia.org/wiki/B-tree http://mattfleming.com/node/192 Am 12.02.2014 02:48, schrieb Zhigang Zhang: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] Sent: Wednesday, February 12, 2014 9:41 AM To: Zhigang Zhang; mysql@lists.mysql.com Subject: Re: LIKE sql optimization Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like ‘%abcd’; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. signature.asc Description: OpenPGP digital signature
Re: LIKE sql optimization
MySQL can't use index when '%' condition gives even oracle and you can try full-text search 2014-02-12 9:55 GMT+08:00 kitlenv kitl...@gmail.com: *read how a index works technically* On Wed, Feb 12, 2014 at 12:48 PM, Zhigang Zhang zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. zhigang _ From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] Sent: Wednesday, February 12, 2014 9:41 AM To: Zhigang Zhang; mysql@lists.mysql.com Subject: Re: LIKE sql optimization Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like '%abcd'; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. Thanks. Zhigang -- Phone: +86 1868061 Email Gtalk: yloui...@gmail.com Personal Blog: http://www.vmcd.org
Re: LIKE sql optimization
Same reason as why composite index works only if you supply first field or fields .. example index on a,b,c if you have a query : select * from tbl where a = 'whatever' and b = 'something it will use the index .. but a query like this one : select * from tbl where b = 'something' and c = 'something else' won't use the index .. - Just like an index in a book ... 2014-02-11 21:03 GMT-05:00 louis liu yloui...@gmail.com: MySQL can't use index when '%' condition gives even oracle and you can try full-text search 2014-02-12 9:55 GMT+08:00 kitlenv kitl...@gmail.com: *read how a index works technically* On Wed, Feb 12, 2014 at 12:48 PM, Zhigang Zhang zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. zhigang _ From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] Sent: Wednesday, February 12, 2014 9:41 AM To: Zhigang Zhang; mysql@lists.mysql.com Subject: Re: LIKE sql optimization Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like '%abcd'; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. Thanks. Zhigang -- Phone: +86 1868061 Email Gtalk: yloui...@gmail.com Personal Blog: http://www.vmcd.org
RE: LIKE sql optimization
I checked a myisam table index, the index is a copy of the whole field. Zhigang -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Wednesday, February 12, 2014 10:02 AM To: mysql@lists.mysql.com Subject: Re: LIKE sql optimization because a index is not just a dumb copy of the whole field and you simply can't seek in the middle of it? http://en.wikipedia.org/wiki/B-tree http://mattfleming.com/node/192 Am 12.02.2014 02:48, schrieb Zhigang Zhang: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] Sent: Wednesday, February 12, 2014 9:41 AM To: Zhigang Zhang; mysql@lists.mysql.com Subject: Re: LIKE sql optimization Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like ‘%abcd’; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan.
Re: LIKE sql optimization
Hi Zhigang, On Feb 11, 2014, at 8:48 PM, Zhigang Zhang zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. I think I understand the question - you are asking why MySQL will not index scan, find matching records, and then look them up rather than table scan? I believe the answer is that there is no way of knowing if 1 row matches, or all rows match. In the worst case (where all rows match), it is much more expensive to traverse between index and data rows for-each-record. So a table scan is a “safe choice / has less variance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: LIKE sql optimization
Thank you very much! Zhigang -Original Message- From: Morgan Tocker [mailto:morgan.toc...@oracle.com] Sent: Wednesday, February 12, 2014 10:16 AM To: Zhigang Zhang Cc: mysql@lists.mysql.com Subject: Re: LIKE sql optimization Hi Zhigang, On Feb 11, 2014, at 8:48 PM, Zhigang Zhang zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. I think I understand the question - you are asking why MySQL will not index scan, find matching records, and then look them up rather than table scan? I believe the answer is that there is no way of knowing if 1 row matches, or all rows match. In the worst case (where all rows match), it is much more expensive to traverse between index and data rows for-each-record. So a table scan is a “safe choice / has less variance.=
Complex SQL optimization vs. general-purpose language
Any SQL rewriting gurus know how I might be able to optimize this
query? The schema:
mysql show columns from transactionlog;
+---+---+--+-+-++
| Field | Type | Null |
Key | Default | Extra |
+---+---+--+-+-++
| id| int(11) | NO |
PRI | NULL| auto_increment |
| transactionid | varchar(10) | NO |
MUL | NULL||
| queryid | tinyint(4)| NO |
| NULL||
| tableid | varchar(30) | NO |
MUL | NULL||
| tupleid | int(11) | NO |
| NULL||
| querytype | enum('select','insert','delete','update') | NO |
| NULL||
| schemaname| varchar(20) | YES |
| NULL||
| partition | tinyint(3) unsigned | YES |
| NULL||
+---+---+--+-+-++
8 rows in set (0.04 sec)
The query:
select concat(weight, ' ', ids, '\n')
from (
select
tableid,
tupleid,
group_concat(id separator ' ') as ids,
(
select count(distinct transactionid)
from transactionlog
where transactionid in (
select transactionid
from transactionlog
where (tableid, tupleid, querytype) =
(t.tableid, t.tupleid, 'update')
group by transactionid
having count(*) 0
)
) weight
from transactionlog t
group by tableid, tupleid
having weight 0 and count(*) 1
) u;
This is the output of EXPLAIN and mk-visual-explain:
++++---+---+---+-+---+--+--
+
| id | select_type| table | type | possible_keys |
key | key_len | ref | rows | Extra
|
++++---+---+---+-+---+--+--+
| 1 | PRIMARY| derived2 | ALL | NULL |
NULL | NULL| NULL | 13 |
|
| 2 | DERIVED| t | ALL | NULL |
NULL | NULL| NULL | 68 | Using filesort
|
| 3 | DEPENDENT SUBQUERY | transactionlog | index | NULL |
transactionid | 12 | NULL | 68 | Using where; Using index
|
| 4 | DEPENDENT SUBQUERY | transactionlog | ref | tableid |
tableid | 36 | func,func |2 | Using where; Using
temporary; Using filesort |
++++---+---+---+-+---+--+--+
Table scan
rows 13
+- DERIVED
table derived(t,transactionlog,temporary(transactionlog))
+- DEPENDENT SUBQUERY
+- DEPENDENT SUBQUERY
| +- Filesort
| | +- TEMPORARY
| | table temporary(transactionlog)
| | +- Filter with WHERE
| |+- Bookmark lookup
| | +- Table
| | | table transactionlog
| | | possible_keys tableid
| | +- Index lookup
| | keytransactionlog-tableid
| | possible_keys tableid
| | key_len36
| | reffunc,func
| | rows 2
| +- Filter with WHERE
| +- Index scan
|keytransactionlog-transactionid
|key_len12
|rows 68
+- Filesort
+- Table scan
rows 68
+- Table
table t
That is a lot of work. I can write the equivalent logic in Python
while making a single pass:
results = query(
select tableid, tupleid, transactionid, id, querytype
from transactionlog_2warehouse
)
_tab, _tup = None
ids = []
weight = 0
saw_upd = False
for tab, tup, txn, id, qt in results:
if (_tab, _tup) != (tab, tup):
if len(ids) 1 and weight 0:
print weight, ids
weight = 0
ids = []
_txn = None
if _txn != txn:
saw_upd = False
if qt == 'update' and not saw_upd:
weight += 1
saw_upd = True
ids += [id]
Is it possible to achieve the Python single-pass performance using
pure SQL? Thanks in advance!
--
Yang Zhang
http://www.mit.edu/~y_z/
Ugly sql optimization help?
I have the following horrible sql. I need one result that has all the data in one row. I am currently using 3 sub queries and figure it must be a better way... SELECT 'FS_DEV', ifnull(a.severity, 0) AS aseverity, ifnull(a.eventid, 0) AS aeventid, ifnull(a.description, 'None') AS adescription, ifnull(a.hostid, 0) AS ahostid, ifnull(a.color,'#E5E0DC') as acolor, ifnull(a.fontcolor,0) as afontcolor, ifnull(a.severitydesc,'None') as aseveritydesc, ifnull(p.severity, 0) AS pseverity, ifnull(p.eventid, 0) AS peventid, ifnull(p.description, 'None') AS pdescription, ifnull(p.hostid, 0) AS phostid, ifnull(p.color,'#E5E0DC') as pcolor, ifnull(p.fontcolor,0) as pfontcolor, ifnull(p.severitydesc,'None') as pseveritydesc, ifnull(s.severity, 0) AS sseverity, ifnull(s.eventid, 0) AS seventid, ifnull(s.description, 'None') AS sdescription, ifnull(s.hostid, 0) AS shostid, ifnull(s.color,'#E5E0DC') as scolor, ifnull(s.fontcolor,0) as sfontcolor, ifnull(s.severitydesc,'None') as sseveritydesc FROM (SELECT e.severity, e.eventid, e.description, e.hostid, fs.color, fs.fontcolor, fs.severitydesc FROM fs_events e, fs_severity fs WHERE hostid in (21,22,23,24,15,16) and e.severity = fs.severityid AND e.event_perf = 1 AND e.time_stamp = date_sub(now(), INTERVAL 30 DAY) AND e.acknowledged in ( 0,1) ORDER BY e.severity DESC, e.time_stamp DESC LIMIT 0, 1) p, (SELECT e.severity, e.eventid, e.description, e.hostid, fs.color, fs.fontcolor, fs.severitydesc FROM fs_events e, fs_severity fs WHERE hostid in (21,22,23,24,15,16) and e.severity = fs.severityid AND e.event_avail = 1 AND e.time_stamp = date_sub(now(), INTERVAL 30 DAY) AND e.acknowledged in ( 0,1) ORDER BY e.severity DESC, e.time_stamp DESC LIMIT 0, 1) a, (SELECT e.severity, e.eventid, e.description, e.hostid, fs.color, fs.fontcolor, fs.severitydesc FROM fs_events e, fs_severity fs WHERE hostid in (21,22,23,24,15,16) and e.severity = fs.severityid AND e.event_sec = 1 AND e.time_stamp = date_sub(now(), INTERVAL 30 DAY) AND e.acknowledged in ( 0,1) ORDER BY e.severity DESC, e.time_stamp DESC LIMIT 0, 1) s -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need sql optimization help
I have the following sql that works for what I want to see but is
terribly slow due to the subquery. Any suggestions on how to get same
result faster? I have tried group by and cant seem to get the correct
results that way
Also is there a product that can help optimize sql and indexing?
SELECT DISTINCT e.severity, e.time_stamp, replace(e.description,
'{HOSTNAME}', h.host) AS description, h.host, h.hostid, e.value,
e.triggerid, s.color FROM fs_events e, hosts h, fs_severity s WHERE
e.hostid = 10011 AND e.hostid = h.hostid AND e.time_stamp = (SELECT
max(time_stamp) FROM fs_events WHERE triggerid = e.triggerid) and
e.severity = s.severityid ORDER BY e.time_stamp DESC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need sql optimization help
Bryan,
A 'Not Exists' query
http://www.artfulsoftware.com/infotree/queries.php#41 is usually
faster when coded as an exclusion join, eg for max-some-value per key,
left join on the key variable and left.value right. value where
left.key is null, so you would need something like ...
SELECT DISTINCT
e.severity,
e.time_stamp,
replace(e.description,'{HOSTNAME}', h.host) AS description,
h.host,
h.hostid,
e.value,
e.triggerid,
s.color
FROM fs_events e
JOIN hosts h USING (hostid)
JOIN fs_severity s ON (e.severity = s.severityid)
LEFT JOIN fs_events e2
ON e.triggerid=e2.triggerid
AND e.MAX(time_stamp) e2.MAX(time_stamp)
WHERE e.hostid = 10011
AND e2.triggerid IS NULL
ORDER BY e.time_stamp DESC
PB
Bryan Cantwell wrote:
I have the following sql that works for what I want to see but is
terribly slow due to the subquery. Any suggestions on how to get same
result faster? I have tried group by and cant seem to get the correct
results that way
Also is there a product that can help optimize sql and indexing?
SELECT DISTINCT e.severity, e.time_stamp, replace(e.description,
'{HOSTNAME}', h.host) AS description, h.host, h.hostid, e.value,
e.triggerid, s.color FROM fs_events e, hosts h, fs_severity s WHERE
e.hostid = 10011 AND e.hostid = h.hostid AND e.time_stamp = (SELECT
max(time_stamp) FROM fs_events WHERE triggerid = e.triggerid) and
e.severity = s.severityid ORDER BY e.time_stamp DESC
RE: Need sql optimization help
Gives me invalid use of group function:
SELECT DISTINCT
e.severity,
e.time_stamp,
replace(e.description,'{HOSTNAME}', h.host) AS description,
h.host,
h.hostid,
e.value,
e.triggerid,
s.color
FROM fs_events e
JOIN hosts h USING (hostid)
JOIN fs_severity s ON (e.severity = s.severityid)
LEFT JOIN fs_events e2
ON e.triggerid=e2.triggerid
AND MAX(e.time_stamp) MAX(e2.time_stamp)
WHERE e.hostid = 10011
AND e2.triggerid IS NULL
ORDER BY e.time_stamp DESC
From: Peter Brawley [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 03, 2007 1:40 PM
To: Bryan Cantwell
Cc: mysql@lists.mysql.com
Subject: Re: Need sql optimization help
Bryan,
A 'Not Exists' query
http://www.artfulsoftware.com/infotree/queries.php#41 is usually
faster when coded as an exclusion join, eg for max-some-value per key,
left join on the key variable and left.value right. value where
left.key is null, so you would need something like ...
SELECT DISTINCT
e.severity,
e.time_stamp,
replace(e.description,'{HOSTNAME}', h.host) AS description,
h.host,
h.hostid,
e.value,
e.triggerid,
s.color
FROM fs_events e
JOIN hosts h USING (hostid)
JOIN fs_severity s ON (e.severity = s.severityid)
LEFT JOIN fs_events e2
ON e.triggerid=e2.triggerid
AND e.MAX(time_stamp) e2.MAX(time_stamp)
WHERE e.hostid = 10011
AND e2.triggerid IS NULL
ORDER BY e.time_stamp DESC
PB
Bryan Cantwell wrote:
I have the following sql that works for what I want to see but
is
terribly slow due to the subquery. Any suggestions on how to get
same
result faster? I have tried group by and cant seem to get the
correct
results that way
Also is there a product that can help optimize sql and indexing?
SELECT DISTINCT e.severity, e.time_stamp, replace(e.description,
'{HOSTNAME}', h.host) AS description, h.host, h.hostid, e.value,
e.triggerid, s.color FROM fs_events e, hosts h, fs_severity s
WHERE
e.hostid = 10011 AND e.hostid = h.hostid AND e.time_stamp =
(SELECT
max(time_stamp) FROM fs_events WHERE triggerid = e.triggerid)
and
e.severity = s.severityid ORDER BY e.time_stamp DESC
Re: Need sql optimization help
Your inner query guarantees that MySQL will have to test an awfull lot
of combinations: (# of records in fs_events)^2 * (# of records in
hosts) * (# of records in severity)
(SELECT max(time_stamp) FROM fs_events WHERE triggerid = e.triggerid)
needs to be recalculated every time we try to match a row in the outer
query.. that is going to hurt a lot.
- michael dykman
On 3/3/07, Bryan Cantwell [EMAIL PROTECTED] wrote:
Gives me invalid use of group function:
SELECT DISTINCT
e.severity,
e.time_stamp,
replace(e.description,'{HOSTNAME}', h.host) AS description,
h.host,
h.hostid,
e.value,
e.triggerid,
s.color
FROM fs_events e
JOIN hosts h USING (hostid)
JOIN fs_severity s ON (e.severity = s.severityid)
LEFT JOIN fs_events e2
ON e.triggerid=e2.triggerid
AND MAX(e.time_stamp) MAX(e2.time_stamp)
WHERE e.hostid = 10011
AND e2.triggerid IS NULL
ORDER BY e.time_stamp DESC
From: Peter Brawley [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 03, 2007 1:40 PM
To: Bryan Cantwell
Cc: mysql@lists.mysql.com
Subject: Re: Need sql optimization help
Bryan,
A 'Not Exists' query
http://www.artfulsoftware.com/infotree/queries.php#41 is usually
faster when coded as an exclusion join, eg for max-some-value per key,
left join on the key variable and left.value right. value where
left.key is null, so you would need something like ...
SELECT DISTINCT
e.severity,
e.time_stamp,
replace(e.description,'{HOSTNAME}', h.host) AS description,
h.host,
h.hostid,
e.value,
e.triggerid,
s.color
FROM fs_events e
JOIN hosts h USING (hostid)
JOIN fs_severity s ON (e.severity = s.severityid)
LEFT JOIN fs_events e2
ON e.triggerid=e2.triggerid
AND e.MAX(time_stamp) e2.MAX(time_stamp)
WHERE e.hostid = 10011
AND e2.triggerid IS NULL
ORDER BY e.time_stamp DESC
PB
Bryan Cantwell wrote:
I have the following sql that works for what I want to see but
is
terribly slow due to the subquery. Any suggestions on how to get
same
result faster? I have tried group by and cant seem to get the
correct
results that way
Also is there a product that can help optimize sql and indexing?
SELECT DISTINCT e.severity, e.time_stamp, replace(e.description,
'{HOSTNAME}', h.host) AS description, h.host, h.hostid, e.value,
e.triggerid, s.color FROM fs_events e, hosts h, fs_severity s
WHERE
e.hostid = 10011 AND e.hostid = h.hostid AND e.time_stamp =
(SELECT
max(time_stamp) FROM fs_events WHERE triggerid = e.triggerid)
and
e.severity = s.severityid ORDER BY e.time_stamp DESC
--
- michael dykman
- [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need sql optimization help
Sorry for the typo, lose the MAX! ...
SELECT DISTINCT
e.severity,
e.time_stamp,
replace(e.description,'{HOSTNAME}', h.host) AS description,
h.host,
h.hostid,
e.value,
e.triggerid,
s.color
FROM fs_events e
JOIN hosts h USING (hostid)
JOIN fs_severity s ON (e.severity = s.severityid)
LEFT JOIN fs_events e2
ON e.triggerid=e2.triggerid
AND e.time_stamp e2.time_stamp
WHERE e.hostid = 10011
AND e2.triggerid IS NULL
ORDER BY e.time_stamp DESC
PB
Michael Dykman wrote:
Your inner query guarantees that MySQL will have to test an awfull lot
of combinations: (# of records in fs_events)^2 * (# of records in
hosts) * (# of records in severity)
(SELECT max(time_stamp) FROM fs_events WHERE triggerid = e.triggerid)
needs to be recalculated every time we try to match a row in the outer
query.. that is going to hurt a lot.
- michael dykman
On 3/3/07, Bryan Cantwell [EMAIL PROTECTED] wrote:
Gives me invalid use of group function:
SELECT DISTINCT
e.severity,
e.time_stamp,
replace(e.description,'{HOSTNAME}', h.host) AS description,
h.host,
h.hostid,
e.value,
e.triggerid,
s.color
FROM fs_events e
JOIN hosts h USING (hostid)
JOIN fs_severity s ON (e.severity = s.severityid)
LEFT JOIN fs_events e2
ON e.triggerid=e2.triggerid
AND MAX(e.time_stamp) MAX(e2.time_stamp)
WHERE e.hostid = 10011
AND e2.triggerid IS NULL
ORDER BY e.time_stamp DESC
From: Peter Brawley [mailto:[EMAIL PROTECTED]
Sent: Saturday, March 03, 2007 1:40 PM
To: Bryan Cantwell
Cc: mysql@lists.mysql.com
Subject: Re: Need sql optimization help
Bryan,
A 'Not Exists' query
http://www.artfulsoftware.com/infotree/queries.php#41 is usually
faster when coded as an exclusion join, eg for max-some-value per key,
left join on the key variable and left.value right. value where
left.key is null, so you would need something like ...
SELECT DISTINCT
e.severity,
e.time_stamp,
replace(e.description,'{HOSTNAME}', h.host) AS description,
h.host,
h.hostid,
e.value,
e.triggerid,
s.color
FROM fs_events e
JOIN hosts h USING (hostid)
JOIN fs_severity s ON (e.severity = s.severityid)
LEFT JOIN fs_events e2
ON e.triggerid=e2.triggerid
AND e.MAX(time_stamp) e2.MAX(time_stamp)
WHERE e.hostid = 10011
AND e2.triggerid IS NULL
ORDER BY e.time_stamp DESC
PB
Bryan Cantwell wrote:
I have the following sql that works for what I want to see but
is
terribly slow due to the subquery. Any suggestions on how to get
same
result faster? I have tried group by and cant seem to get the
correct
results that way
Also is there a product that can help optimize sql and indexing?
SELECT DISTINCT e.severity, e.time_stamp, replace(e.description,
'{HOSTNAME}', h.host) AS description, h.host, h.hostid, e.value,
e.triggerid, s.color FROM fs_events e, hosts h, fs_severity s
WHERE
e.hostid = 10011 AND e.hostid = h.hostid AND e.time_stamp =
(SELECT
max(time_stamp) FROM fs_events WHERE triggerid = e.triggerid)
and
e.severity = s.severityid ORDER BY e.time_stamp DESC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sql optimization
Hi All, I am usingINNODB.I have a delete quetry something like this : delete from modnaptrrecord_zone where modnaptrrecord_zone.modnaptrrecord_id in (593536 ,593537 ,593538 ,593539 ,593540 ) and modnaptrrecord_zone.modnaptrrecord_id not in (593536 ) This is taking a verylong time to execute..somewhere around 15mins. The values in the "id in"and "not in" of the where clause are around 1. I am sending the "SHOW INNODB STATUS also. Can somebody let me know, why this is taking such a long time ? And how to optimize this query ? Thanks Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com [EMAIL PROTECTED] /usr/local/mysql/bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 27 to server version: 4.1.7-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use ipworks Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql show innodb status - ;
Re: Sql optimization
You're hard-coding ten thousand discrete values between the two IN clauses?? I'm not sure how MySQL processes a query like that but I can imagine that the performance would not be good. Frankly, I am: a)suprised that you had the patience to type in all of those values (andverify that you typed them correctly) b) amazed that MySQL actually executes a statement that long without complaining that the statement is too long Are the discrete values really sequential like the example you show? Because if they are you might find your life a lot easier if you simply write your INs as BETWEENs. For example, delete from modnaptrrecord_zone where modnaptrrecord_zone.modnaptrrecord_idbetween 593536 and 789123and modnaptrrecord_zone.modnaptrrecord_id notbetween 593536 and 789123 Even if there were a few ranges, the query would still be a lot shorter if written with BETWEENs. For example, delete from modnaptrrecord_zone where (modnaptrrecord_zone.modnaptrrecord_id (between 100 and 500) or (modnaptrecord_zone.modnaptrrecord_id between 1 and 11000)) and (modnaptrrecord_zone.modnaptrrecord_id not (between 100 and 500) or (modnaptrecord_zone.modnaptrrecord_id between 1 and 11000)) I've probably got at least one of those conditions backward but you probably get the idea. It's a LOT easier to write ranges than long lists of sequential values! Another way that might improve performance a lot is if you use a subqueries with your IN clauses. For example: delete from modnaptrrecord_zone where modnaptrrecord_zone.modnaptrrecord_id in (select id from other_table where customer_country = 'USA') and modnaptrrecord_zone.modnaptrrecord_id not in (select id from other_table where customer_country = 'CANADA') In other words, if the values that need to go into the IN list can be obtained by reading a table with a SELECT, put the SELECT statement within the brackets instead of listing the hundreds of discrete values. Of course, this assumes that you are using a version of MySQL that supports subqueries!! By the way, I'm assuming that the lists needed for your two IN clauses are different from one another in some way.Your query makes no sense if the two lists are the same. For example, if your lists were the same - I'll just use a few values for each list to make my point clear - you might have a query like this: delete from modnaptrrecord_zone where modnaptrrecord_zone.modnaptrrecord_id in (1, 2, 3) and modnaptrrecord_zone.modnaptrrecord_id not in (1, 2, 3) In other words, you want to delete rows in table modnaprrecord_zone where the modnaptrrecord_id IS 1, 2 or 3 AND where the mondaptrrecord_id IS NOT 1, 2, or 3 at the same time. That doesn't make sense. Now, if the values in the lists are not in ranges and can't easily be obtained via SELECT queries, you may want to consider modifying your tables to simplify this query. Let's say that those long IN lists are just trying to specify customers who are in good standing with you, i.e. customers who don't owe you any money and are prompt to pay their accounts. You might find it a lot easier to add a column to your customer table that indicates the customer's status and then simply search on the value of that status flag. For example, let's say that a customer could have three statuses: "Excellent", "Good" and "Poor". You could have a process that determines the proper status for that customer; that might be a batch job that runs once a day or once a week. Then, your query could simply search for the status like this: delete from modnaptrrecord_zone where modnaptrrecord_zone.modnaptrrecord_id in (select id from customer where status = 'Poor') and modnaptrrecord_zone.modnaptrrecord_id not in (select id from customer where status = 'Good') -- Rhino - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 20, 2006 11:22 AM Subject: Sql optimization Hi All, I am usingINNODB.I have a delete quetry something like this : delete from modnaptrrecord_zone where modnaptrrecord_zone.modnaptrrecord_id in (593536 ,593537 ,593538 ,593539 ,593540 ) and modnaptrrecord_zone.modnaptrrecord_id not in (593536 ) This is taking a verylong time to execute..somewhere around 15mins. The values in the "id in"and "not in" of the where clause are around 1. I am sending the "SHOW INNODB STATUS also. Can somebody let me know, why this is taking such a long time ? And how to optimize this query ? Thanks Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should
Re: Sql optimization
2006/6/20, [EMAIL PROTECTED] [EMAIL PROTECTED]: Hi All, I am using INNODB.I have a delete quetry something like this : delete from modnaptrrecord_zone where modnaptrrecord_zone.modnaptrrecord_id in (593536 ,593537 ,593538 ,593539 ,593540 ) and modnaptrrecord_zone.modnaptrrecord_id not in (593536 ) I hope this is just some quick copy/paste, but if in your query you have : IN (A, B) AND NOT IN (A,B), I don't think it will return much data... Is there any index on modnaptrrecord_zone.modnaptrrecord_id ? ALTER TABLE modnaptrrecord_zone ADD INDEX(modnaptrrecord_id); Where did you get the list of ids ? you may be better off using a temporary table and doing an inner or left join on it. -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL optimization problem
You might have to tweak the following a bit for syntax: SELECT a.name, COUNT(b.id) as votes FROM poll_options as a LEFT JOIN poll_votes as b ON a.id=b.oid WHERE b.pid='poll_id' GROUP BY b.oid Also, you can refer to: http://www.mysql.com/doc/en/JOIN.html Bhavin. - Original Message - From: Blaster [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 11, 2003 12:35 PM Subject: SQL optimization problem Hey, (this post is pretty long, a short version of the problem is listed at the bottom if you don't like reading long emails :P) I'm currently going through all my SQL queries for my webpage to see if there is anything I can do to optimize them. On my webpage, i have this poll where people can give their opinion in various subjects by casting a vote. To begin with, I'd like to tell you how I created my tables for this task, the poll uses in total 3 different tables as following: poll_list (this table contains the actual question of each poll) === id (int) | stamp (datetime) | question (varchar 255) | active (tinyint) Id is simply an autoincrementing ID for each poll, Stamp is the creation date of the poll, Question holds the actual question (duh :P) If Active is 1, it means that this is the active poll right now. Only one poll can be active at the same time. poll_options (this table holds the valid answers for each poll. You may use any number of answers in your poll) id (int) | pid (int) | name (varchar 255) id is again, autoinc field for this answer pid is a pointer to which poll this particular answer belongs to, i.e pid = poll_list.id name holds the actual answer string poll_votes (this table holds all the casted votes, one row is one vote) = id (int) | pid (int) | oid (int) | uid (int) id, autoinc pid, pointer to poll_list.id, tells me which poll this vote belongs to oid, pointer to poll_options.id, tells me which option this user voted uid, pointer to user account. I won't include the user table, just think of this as a unique identifier for the users, prevents the same user from voting twice in a poll.. AND now! to the problem! Prior to my optimzation checking began, the code to display the results of a poll was something like this: 1) Fetch the active poll: SELECT * FROM poll_list WHERE (active 0) ORDER BY stamp DESC LIMIT 1 2) Fetch the answers for the poll ID we received from the prior query: SELECT * FROM poll_options WHERE pid='id_from_prior_query' 3) For each option received in step 2, I did: SELECT * FROM poll_votes WHERE pid='poll_id' AND oid='option_id' 4) Output HTML formated code to web visitor. Now, I thought, it MUST be possible to make step 2 and 3 using 1 single query, because using this old system (as shown above), it requires 1 + n queries, where n is number of answers in that particular poll. So, I simply replaced it with: 1) Fetch the active poll: SELECT * FROM poll_list WHERE (active 0) ORDER BY stamp DESC LIMIT 1 2) Fetch the answers votes in the same query: SELECT a.name, COUNT(b.id) as votes FROM poll_options as a, poll_votes as b WHERE b.pid='poll_id' AND b.oid=a.id GROUP BY b.oid 3) Print the results. PROBLEM BEGINS HERE ## However! Here comes the problem, if no vote is cast on an option, it will not show up in the list! I want it to print 0% for any options that havn't received a vote, like it would with my old query system. This is basically what I want to do: Select all options from poll_options and, in the same query, count the number of rows in poll_votes which has that particular options id as oid. Pretty hard to explain, but ideally, I'd like to do SELECT a.name, COUNT(b.id) as votes FROM poll_options as a, poll_votes as b WHERE b.pid='poll_id' AND b.oid=a.id GROUP BY b.oid With one exception, if votes = 0, it should be listed in the result aswell! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/doc/ (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL optimization problem
LEFT JOIN On Sat, 11 Jan 2003 18:35:44 +0100, Blaster used a few recycled electrons to form: | Hey, | | (this post is pretty long, a short version of the problem is listed at the | bottom if you don't like reading long emails :P) | | I'm currently going through all my SQL queries for my webpage to see if | there is anything I can do to optimize them. | On my webpage, i have this poll where people can give their opinion in | various subjects by casting a vote. To begin | with, I'd like to tell you how I created my tables for this task, the poll | uses in total 3 different tables as following: | | poll_list (this table contains the actual question of each poll) | === | id (int) | stamp (datetime) | question (varchar 255) | active (tinyint) | | Id is simply an autoincrementing ID for each poll, | | Stamp is the creation date of the poll, | | Question holds the actual question (duh :P) | | If Active is 1, it means that this is the active poll right now. | Only one poll can be active at the same time. | | poll_options (this table holds the valid answers for each poll. You may use | any number of answers in your poll) | | id (int) | pid (int) | name (varchar 255) | | id is again, autoinc field for this answer | | pid is a pointer to which poll this particular answer belongs to, i.e pid = | poll_list.id | | name holds the actual answer string | | poll_votes (this table holds all the casted votes, one row is one vote) | = | id (int) | pid (int) | oid (int) | uid (int) | | id, autoinc | | pid, pointer to poll_list.id, tells me which poll this vote belongs to | | oid, pointer to poll_options.id, tells me which option this user voted | | uid, pointer to user account. I won't include the user table, just think of | this as a unique identifier | for the users, prevents the same user from voting twice in a poll.. | | | AND now! to the problem! Prior to my optimzation checking began, the code | to display the | results of a poll was something like this: | | 1) Fetch the active poll: | SELECT * FROM poll_list WHERE (active 0) ORDER BY stamp DESC LIMIT 1 | | 2) Fetch the answers for the poll ID we received from the prior query: | SELECT * FROM poll_options WHERE pid='id_from_prior_query' | | 3) For each option received in step 2, I did: | SELECT * FROM poll_votes WHERE pid='poll_id' AND oid='option_id' | | 4) Output HTML formated code to web visitor. | | Now, I thought, it MUST be possible to make step 2 and 3 using 1 single | query, because using this old | system (as shown above), it requires 1 + n queries, where n is number of | answers in that particular poll. | | So, I simply replaced it with: | | 1) Fetch the active poll: | SELECT * FROM poll_list WHERE (active 0) ORDER BY stamp DESC LIMIT 1 | | 2) Fetch the answers votes in the same query: | SELECT a.name, COUNT(b.id) as votes FROM poll_options as a, poll_votes as b | WHERE b.pid='poll_id' AND b.oid=a.id GROUP BY b.oid | | 3) Print the results. | | PROBLEM BEGINS HERE ## | | However! Here comes the problem, if no vote is cast on an option, it will | not show up in the list! I want it to print 0% | for any options that havn't received a vote, like it would with my old | query system. This is basically what I want to | do: | | Select all options from poll_options and, in the same query, count the | number of rows in poll_votes which has that | particular options id as oid. Pretty hard to explain, but ideally, I'd | like to do | | SELECT a.name, COUNT(b.id) as votes FROM poll_options as a, poll_votes as b | WHERE b.pid='poll_id' AND b.oid=a.id GROUP BY b.oid | | With one exception, if votes = 0, it should be listed in the result aswell! | | | - | Before posting, please check: |http://www.mysql.com/manual.php (the manual) |http://lists.mysql.com/ (the list archive) | | To request this thread, e-mail [EMAIL PROTECTED] | To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] | Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php | - Before posting, please check: http://www.mysql.com/doc/ (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL optimization problem
Taking a blind stab, how about.. SELECT a.name, minimum(0,COUNT(b.id)) as votes FROM poll_options as a LEFT JOIN poll_votes as b on a.id=b.oid WHERE b.pid='poll_id' GROUP BY b.oid Ryan Fox - Original Message - From: Blaster [EMAIL PROTECTED] However! Here comes the problem, if no vote is cast on an option, it will not show up in the list! I want it to print 0% for any options that havn't received a vote, like it would with my old query system. This is basically what I want to do: SELECT a.name, COUNT(b.id) as votes FROM poll_options as a, poll_votes as b WHERE b.pid='poll_id' AND b.oid=a.id GROUP BY b.oid With one exception, if votes = 0, it should be listed in the result aswell! sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Search SQL optimization
Let us say I have two tables tab1 and tab2. tab1: col1 col2 tab2: col3 col4 For my search, a user can enter space delimited words to search, e.g., microsoft windows xp i have to search for EACH of these words, and join the 2 tables too, so my sql query looks somewhat like this: select * from tab1, tab2 where tab1.col1 = tab2.col3 and ( tab1.col1 = microsoft or tab1.col2 = microsoft or tab1.col1 = windows or tab1.col2 = windows or tab1.col1 = xp or tab1.col2 = xp ) ; now my question is how can i optimize this query? apart from the sql, can i set some indices which will help me make this faster? thanks/erick __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL optimization
Hi, MySQL seems to really under perform when using joins. Am I doing something wrong? I have the following query: SELECT DISTINCT Product.ProductID, Product.Title, ProductFormat.Price, ProductFormat.TradePrice, ProductFormat.ProductCode, ProductFormat.Format, ShoppingCart.Quantity FROM Product LEFT JOIN ProductFormat ON ProductFormat.ProductID = Product.ProductID LEFT JOIN ShoppingCart ON ProductFormat.ProductCode = ShoppingCart.ProductCode WHERE ShoppingCart.PersonID = 13; Product has about 1000 rows ProductFormat has about 2000 rows ShoppingCart has about 20 rows Thing is, this query takes on average 3 seconds to return about 4 records!! my other queries seem to be in the tens of milliseconds. Thing is, all my queries with JOINS in them exhibit this poor performance. PersonID is the primary key in ShoppingCart and the big table Product. ProductFormat has ProductCode as its primary key. Is there anything I can do about this? Thanks in advance Dean Ware Web Developer http://www.readingroom.com Winner : Best Business to Business Website 2000-01 (Internet Business Awards sponsored by ntl) Reading Room Ltd. 77 Dean Street Soho London W1D 3SH UK Tel: +44 (0) 20 7734 9499 Fax: +44 (0) 20 7439 4190 The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, re-transmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
