- Original Message -
> From: "Shawn Green"
> Subject: Re: Query optimizer-miss with unqualified expressions, bug or
> feature?
>
> On a more serious note, indexes with limited cardinality are less useful
> than those with excellent cardinality. Cardinali
Hi Shawn,
On 19.10.15 22.33, shawn l.green wrote:
On 10/19/2015 3:48 PM, Roy Lyseng wrote:
Hi Ben,
On 19.10.15 16.07, Ben Clewett wrote:
Hi Roy,
Thanks for the clear explanation.
I guess (hypothetically) the optimizer could see if it has a key, and
then use
two starts: one on 'a > 0' and
On 10/19/2015 3:48 PM, Roy Lyseng wrote:
Hi Ben,
On 19.10.15 16.07, Ben Clewett wrote:
Hi Roy,
Thanks for the clear explanation.
I guess (hypothetically) the optimizer could see if it has a key, and
then use
two starts: one on 'a > 0' and one on 'a < 0', taking a union of the
result?
Which
Hi Ben,
On 19.10.15 16.07, Ben Clewett wrote:
Hi Roy,
Thanks for the clear explanation.
I guess (hypothetically) the optimizer could see if it has a key, and then use
two starts: one on 'a > 0' and one on 'a < 0', taking a union of the result?
Which might make a significant result to something
Hi Roy,
Thanks for the clear explanation.
I guess (hypothetically) the optimizer could see if it has a key, and
then use two starts: one on 'a > 0' and one on 'a < 0', taking a union
of the result? Which might make a significant result to something?
Ben.
On 2015-10-19 14:19, Roy Lyseng wr
Hi Ben,
On 19.10.15 15.10, Ben Clewett wrote:
I have noticed that an unqualified boolean expression cannot be optimized by
MySQL to use an index in 5.6.24.
For example:
CREATE TABLE t (
i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
a BOOLEAN NOT NULL,
KEY a (a)
) ENGINE=InnoDB;
This wi
I have noticed that an unqualified boolean expression cannot be
optimized by MySQL to use an index in 5.6.24.
For example:
CREATE TABLE t (
i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
a BOOLEAN NOT NULL,
KEY a (a)
) ENGINE=InnoDB;
This will hit key 'a':
SELECT * FROM t WHERE a = TRUE;
Mike,
> Has the query optiimizer improved any for later MySQL versions?
Have a look at http://dev.mysql.com/tech-resources/articles/mysql-54.html.
Peter Brawley
http://www.artfulsoftware.com
-
mos wrote:
I am using MySQL 5.1.30 (MyISAM) and the query optimizer is quite bad
at choos
I am using MySQL 5.1.30 (MyISAM) and the query optimizer is quite bad at
choosing the proper index.
I often have to force it to use the correct index for Select statements,
otherwise takes 50x to 100x slower than it should be.
Has the query optiimizer improved any for later MySQL versions
I am using MySQL under Ubuntu 8.04. In fact, I am very interested in the
query optimization of MySQL. Now I have the source code of MySQL on my hand.
My question is how can I figure out the part of the "query optimizer" from
the source code of MySQL.
his a bug in the query optimizer?
Do you have an index on enames.id ?
Do you have an index on enames.id and enames.displayname ?
I don't know enough about the mysql optimizer but at a (very wild) guess
it could be mysql is trying to order the results first (so it can limit
the results qui
t; index in this case?
-Original Message-
From: Christian Hammers [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 03, 2006 2:54 PM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Glitch in Query Optimizer
On Tue, Oct 03, 2006 at 01:35:01PM -0700, Robert DiFalco wrote:
> Scratch that,
ubject: Re: Glitch in Query Optimizer
On Tue, Oct 03, 2006 at 01:35:01PM -0700, Robert DiFalco wrote:
> Scratch that, the only way to have the optimizer "choose" the correct
> index is to remove all compound indices that start with "NodeID" or
> move NodeID so that it i
On Tue, Oct 03, 2006 at 01:35:01PM -0700, Robert DiFalco wrote:
> Scratch that, the only way to have the optimizer "choose" the correct
> index is to remove all compound indices that start with "NodeID" or move
> NodeID so that it is not the first column specified in the compound
> index. Ugh. Any
bject: Re: RE: Glitch in Query Optimizer
Have you tried using the USE/IGNORE/FORCE INDEX optimizer hints?
http://dev.mysql.com/doc/refman/5.0/en/join.html
Sometimes, MySQL's optimize just doesn't make the best choice.
Somewhat rare in my experience but it happens.
HTH,
Dan
On 10/3/06,
ert DiFalco [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 03, 2006 1:00 PM
To: mysql@lists.mysql.com
Subject: Glitch in Query Optimizer
Here's an odd one.
I have a table called Elements and another table called ElementNames.
The ElementNames table has a unique ID and a VARCHAR display name
Falco [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 03, 2006 1:00 PM
To: mysql@lists.mysql.com
Subject: Glitch in Query Optimizer
Here's an odd one.
I have a table called Elements and another table called ElementNames.
The ElementNames table has a unique ID and a VARCHAR display name.
WHERE Elements.nodeID = ? AND Elements.nameID <> 0
ORDER BY Enames.displayName
LIMIT ?, ?;
It is impossible for nameID to be zero so it doesn't effect the outcome
of the query, it just forces it to use the correct index.
Any idea why this is happening? Is this a bug in the que
On Sat, May 06, 2006 at 12:55:55PM +0100, Peter Rosenthal wrote:
> Out of interest is there any time on the roadmap to improve the query
> optimizer's handling of sub-queries as specified in
> http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html ?
As Timour previewed at his "Speeding
Out of interest is there any time on the roadmap to improve the query
optimizer's handling of sub-queries as specified in
http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html ?
Thanks.
Scott Gifford <[EMAIL PROTECTED]> writes:
[...]
> I think I'm going to take a look at the MySQL source and see if
> there's anything I can tweak to get the effect I want. I'll report
> back my results.
The MySQL source looked a bit too complex for casual hacking, but
here's what I ended up doin
"Jigal van Hemert" <[EMAIL PROTECTED]> writes:
> From: "Scott Gifford"
[...]
>> Right, ALL would be a great plan if it weren't for the LIMIT 1.
>
> The LIMIT 1 will be performed *after* the recordset is sorted :-(
Ah, I think that is the piece I was missing.
[...]
>> I'm a little surprised My
From: "Scott Gifford"
> >> Apparently MySQL's optimizer sees that it can use the primary key for
> >> mirealsource_home_supplemental to do the query, but for some reason
> >> decides not to.
> > This is often the case when the query will probably return more than 30%
of
> > the records in that tabl
Thanks for your response, Jigal. More below...
"Jigal van Hemert" <[EMAIL PROTECTED]> writes:
> From: "Scott Gifford"
[...]
>> Apparently MySQL's optimizer sees that it can use the primary key for
>> mirealsource_home_supplemental to do the query, but for some reason
>> decides not to.
>
> Thi
From: "Scott Gifford"
> mysql> explain
> SELECT mirealsource_homes.mls_num,
> mirealsource_homes_supplemental.listdate,
> mirealsource_images.image1,
> mirealsource_homes_stats.detail_views
> FROM mirealsource_homes,
> mirealsou
I'm having a hard time figuring out why a query in my application is
slow. It seems that MySQL is using a filesort where it's not
necessary, and as a result a query that should be taking a fraction of
a second is taking up to 10 seconds.
Essentially, the query is doing a join of 4 tables, two of
On 11 Sep 2003 at 15:31, Tongprasith, Anan wrote:
> I think you are right. I try adding and deleting data one by one and
> found no particular data that will always "turn off" index in all
> circumstances. myisamchk --analyze doesn't help. Is there a way to
> force using index?
Yes, 'USE INDEX (i
Never mind. New MySQL has "force index" option.
Thanks for your help Brent.
Anan T.
-Original Message-
From: Tongprasith, Anan
Sent: Thursday, September 11, 2003 3:32 PM
To: Brent Baisley
Cc: [EMAIL PROTECTED]
Subject: RE: Query optimizer decision to use index depends on data?
idn't help.
Anan T.
-Original Message-
From: Brent Baisley [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 11, 2003 2:18 PM
To: Tongprasith, Anan
Cc: [EMAIL PROTECTED]
Subject: Re: Query optimizer decision to use index depends on data?
I don't think so. Based on what I'v
I don't think so. Based on what I've read, MySQL always assumes and
equal distribution of data for an index. Based on this assumption it
may determine that it's just not worth it to use the index, like if
your search will return most of the records. You should periodically
optimize your indexes
I have two tables which are exactly the same but have different set of data on them. I
wrote a query and used EXPLAIN to see how it would run on each table.
It turn out that the same query will use index on one table but not the other. So I
delete all data on the "bad" table (the one that won't u
Deer Sir,,
I would like to know which optimizer mode used in MySQL, cost base or rule base?
There was no detail description about MySQL query optimizer in MySQL Reference Manual..
could you let me know about MySQL query optimizer mode?
thank you
32 matches
Mail list logo
