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

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

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

Re: LIKE sql optimization

Hi Zhigang, On Feb 11, 2014, at 8:48 PM, Zhigang Zhang 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

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

Re: LIKE sql optimization

smaller index data >> > has >> > better performance than to scan the whole table data. >> > >> > >> > >> > >> > >> > zhigang >> > >> > >> > >> > _ >> > >> > From: Mathieu Desharnais [mai

Re: LIKE sql optimization

ailto: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 % .

Re: LIKE sql optimization

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 doe

Re: LIKE sql optimization

t; _ > > 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 start

RE: LIKE sql optimization

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

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 : > For example: > > > > Select

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 i

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 op

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

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.

Re: Need sql optimization help

TECTED] 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-

Re: Need sql optimization help

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

RE: Need sql optimization help

day, 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

Re: Need sql optimization help

Bryan, A 'Not Exists' query 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 ... SELE

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? SELEC

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_i

Re: Sql optimization

trrecord_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 using INNODB.I have a delete qu

Sql optimization

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 )   This is

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='' GROUP BY b.oid Ryan Fox - Original Message - From: "Blaster" <[EMAIL PROTECTED]> > However! Here comes the problem, if no vote is

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 webpa

Re: SQL optimization problem

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

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 thi

Re: SQL optimization

Dean Ware writes: > Excellent! > > By changing the order of the tables being joined I reduced the execution > time from 2800ms to 20ms! > > Very useful command. > > Thanks very much! > Keep in mind you can help MySQL a lot by setting STRAIGHT_JOIN, to make it sure that MySQL honors the table

Re: SQL optimization

Excellent! By changing the order of the tables being joined I reduced the execution time from 2800ms to 20ms! Very useful command. Thanks very much! At 14:20 29/06/2001 -0300, you wrote: >Are the fields been JOINed indexed? If not, they should. > >Have you tried an EXPLAIN command? Try it li

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 FRO