Optimization Suggestion

This is not actually a bug report but is just an optimization suggestion.
Practically I found out that in queries like this

Select * From SomeTable Where Condition_Using_An_Index

the index is only used when the number of scanned rows is less than some
value (the value depends on the total number of rows in the table), and when
it is greater than the value, the index is not used. I guess this is done
consciously, but I don't get why.

How-To-Repeat:
We use version 3.23.38 on FreeBSD, but I find the same behavior with the
version 4 for Windows.
1) I have a dictionary table Words

CREATE TABLE `Words` (
`Id` mediumint(8) unsigned NOT NULL auto_increment,
`Word` varchar(40) NOT NULL default '',
PRIMARY KEY (`Id`),
UNIQUE KEY `Words` (`Word`)
) TYPE=MyISAM

As your qmail-send program at web.mysql.com doesn't accept messages larger
than 30000 bytes, I made a zip available at http://www.rlsnet.ru/Words.zip.
The archive is a small part of the table, it's big enough to
demonstrate what I mean.

2) the query

explain select * from Words Order By Id

outputs

+-------+------+---------------+------+---------+------+---- ----+-----------
-----+
| table | type | possible_keys | key | key_len | ref | rows | Extra|
+-------+------+---------------+------+---------+------+---- ----+-----------
-----+
| Words | ALL | NULL | NULL | NULL | NULL | 191556 | Using
filesort |
+-------+------+---------------+------+---------+------+---- ----+-----------
-----+

It's obvious that in order to get "Order By Id" it's enough to scan through
the whole table using the primary key, instead, the table is output to a
temporary file, where it is sorted by Id, which noticeably increases
execution time.

3) the query

explain select * from Words where id>180000 and Id<185000

doesn't use the index, while the query

explain select * from Words where id>180000 and Id<181000

does.

Why refuse from using an index?! No matter how many rows are to be scanned,
finding the first and the last rows in the range using a key is always
faster than scanning the whole table "using where", especially with huge
tables.

4) Also,

explain select * from Words Order By Id Limit 16555

outputs that now the index is used (16555=the number of rows in the table-1)
!








______________________________________
Scanned and protected by Inflex



------------------------------------------------------------ ---------
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 bugs-thread12250@lists.mysql.com
To unsubscribe, e-mail

Re: Optimization Suggestion

> the index is only used when the number of scanned rows is less than some
> value (the value depends on the total number of rows in the table), and
when
> it is greater than the value, the index is not used. I guess this is done
> consciously, but I don't get why.
Row-by-row table scanning works much faster than reading through the index.
If you have more than 30% of table records in your selection reading using
index is slower.

> explain select * from Words Order By Id Limit 16555
>
> outputs that now the index is used (16555=the number of rows in the
table-1)
I think it's natural - usind index you don't have to sort selection.

Regards.
A.


------------------------------------------------------------ ---------
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 bugs-thread12253@lists.mysql.com
To unsubscribe, e-mail

Linux IA 64 version doesn"t seem to use memory as requested in the cfg file

Hi all,

We just picked an IA64 (Itanium) box with 2 cpus and 32 gigs of memory.
However, I can't seem to get MySql to use more than 529 megs of memory.
I've used MySql extensively on Linux, Solaris and Win32 (in 32 bit versions)
and never had this problem before.

First, versions:
mysql Ver 11.18 Distrib 3.23.51, for unknown-linux-gnu (ia64)
On a Mandrake system

I've been using the key_buffer entry to look at memory settings.

When I ask for 1 to 7,129M of memory, the mysql uses 81 meg
When I ask for 7,130M to 14,259M, mysql uses 145 meg
another change at 28,520M to use 529 megs.

When I request more than 42M, the system won't start.

So, I have a memory jump at 7,130M requested. It occurs again at 2X and 4X
7,130. Before I get to 8X, the system won't start. However, the memory
requested doesn't seem to have any relation to memory used.

btw, I have confirmed that there are no other my.cfg files on the box, so I
don't think the configurations are getting overwritten.

Does anyone have any suggestions? Thanks in advance!

Jared


------------------------------------------------------------ ---------
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 bugs-thread12257@lists.mysql.com
To unsubscribe, e-mail

Re: Linux IA 64 version doesn"t seem to use memory as requested in the cfg file

Hi all,

Is there a list that would be better for this question?

Thanks!

Jared

----- Original Message -----
From: "Jared Richardson"
To:
Sent: Wednesday, July 31, 2002 8:05 AM
Subject: Linux IA 64 version doesn't seem to use memory as requested in the
cfg file


> Hi all,
>
> We just picked an IA64 (Itanium) box with 2 cpus and 32 gigs of memory.
> However, I can't seem to get MySql to use more than 529 megs of memory.
> I've used MySql extensively on Linux, Solaris and Win32 (in 32 bit
versions)
> and never had this problem before.
>
> First, versions:
> mysql Ver 11.18 Distrib 3.23.51, for unknown-linux-gnu (ia64)
> On a Mandrake system
>
> I've been using the key_buffer entry to look at memory settings.
>
> When I ask for 1 to 7,129M of memory, the mysql uses 81 meg
> When I ask for 7,130M to 14,259M, mysql uses 145 meg
> another change at 28,520M to use 529 megs.
>
> When I request more than 42M, the system won't start.
>
> So, I have a memory jump at 7,130M requested. It occurs again at 2X and
4X
> 7,130. Before I get to 8X, the system won't start. However, the memory
> requested doesn't seem to have any relation to memory used.
>
> btw, I have confirmed that there are no other my.cfg files on the box, so
I
> don't think the configurations are getting overwritten.
>
> Does anyone have any suggestions? Thanks in advance!
>
> Jared
>
>
> ------------------------------------------------------------ ---------
> 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 bugs-thread12257@lists.mysql.com
> To unsubscribe, e-mail
>


------------------------------------------------------------ ---------
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 bugs-thread12266@lists.mysql.com
To unsubscribe, e-mail

Re: Linux IA 64 version doesn"t seem to use memory as requested in the cfg file

Jared Richardson writes:
> Hi all,
>
> Is there a list that would be better for this question?
>
> Thanks!
>
> Jared

You will soon have answer from our Ithanium maintainer.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.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 bugs-thread12268@lists.mysql.com
To unsubscribe, e-mail

Linux IA 64 version doesn"t seem to use memory as requested in the cfg file

Hi!

>>>>> "Jared" == Jared Richardson writes:

Jared> Hi all,
Jared> We just picked an IA64 (Itanium) box with 2 cpus and 32 gigs of memory.
Jared> However, I can't seem to get MySql to use more than 529 megs of memory.
Jared> I've used MySql extensively on Linux, Solaris and Win32 (in 32 bit versions)
Jared> and never had this problem before.

Jared> First, versions:
Jared> mysql Ver 11.18 Distrib 3.23.51, for unknown-linux-gnu (ia64)
Jared> On a Mandrake system

Jared> I've been using the key_buffer entry to look at memory settings.

Jared> When I ask for 1 to 7,129M of memory, the mysql uses 81 meg
Jared> When I ask for 7,130M to 14,259M, mysql uses 145 meg
Jared> another change at 28,520M to use 529 megs.

How have you verified this ?

I just tested this on an alpha system with the MySQL 3.23 and got:

(/my/mysql-3.23) ./sql/mysqld -O key_buffer=2G --help | grep key_buffer
key_buffer_size current value: 2147479552
(/my/mysql-3.23) ./sql/mysqld -O key_buffer=6G --help | grep key_buffer
key_buffer_size current value: 6442446848
(/my/mysql-3.23) ./sql/mysqld -O key_buffer=32G --help | grep key_buffer
key_buffer_size current value: 34359734272

Which seams to indicate that this works as expected.

Note also that MySQL 4.0 has a totally new command line argument
parsing, which is better to handle 64 bit numbers on 32 bit machines.
(This should not apply to you, but you may anyway want to try out 4.0
on Itanium as this has more 64 bit optimizations than 3.23)

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.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 bugs-thread12301@lists.mysql.com
To unsubscribe, e-mail