Optimize and Order by problems in SCO

Description:
The point is that it doesn't make the rigth selection of the index
When it performs:

explain select nfactura, nauclfe from auxiliar where nfactura like '30%' or=
der by nfactura;

The result we have is:
table type possible_keys key key_len ref rows Extra
auxiliar ALL PRIMARY NULL NULL NULL 1028572 where used;=20
Using filesort
The name of the table is "auxiliar" and the field "nfactura" is "unique
key" not null.
The rigth answer should be in the column "key" "nfactura" but it isn't=20
as you can see. The value is NULL.

The same query works ok with other versions of MySQL 3.23.45 for FreeBsd.


And another problem is (ORDER BY):
-----------------------

I have table of 1,028,572 rows and i do query all, and after i can't do=20
any
more, because this process to blockade all mysql and i can't do query=20
of any table using ORDER BY. (mysql use filesorting).


What i can do ? May be is SCO version 3.23.43 and 3.23.51?=20


>How-To-Repeat:
CREATE TABLE auxiliar (
nfactura char(10) NOT NULL,
nauclfe char(18) NOT NULL,
ifactura double(16,2) NOT NULL,
iva double(16,2) NOT NULL,
vendpto char(16) NOT NULL,
PRIMARY KEY (nfactura),
INDEX nauclfe_idx (nauclfe),
INDEX vendpto_idx (vendpto));

explain select nfactura, nauclfe, fvcto where nauclfe like '130%' ORDER BY =
nauclfe;
>Fix:
For first problem., I don't know.
For second problem --- Not use ORDER BY.

>Submitter-Id:
>Originator: Superuser
>Organization:
La Industrial Mexicana
Av. Convencion Sur 1400
Colonia Caminera
Aguascalientes, Ags, Mexico.
Telf. 52-449-9105006
>MySQL support: [none | licence | email support | extended email support ]
none
>Synopsis: Not selected index and ORDER BY blockade mysql
>Severity: critical
>Priority: high
>Category: mysql
>Class: support
>Release: mysql-3.23.51 (Source distribution)

>Environment:

System: SCO_SV limsa1 3.2 5.0.6 i386


Some paths: /bin/make /usr/local/bin/gcc /bin/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/i486-pc-sco3.2v5.0/2.95/specs
gcc version 2.95 19990728 (release)
Compilation info: CC=3D'gcc' CFLAGS=3D' ' CXX=3D'gcc' CXXFLAGS=3D'-O3' =
LDFLAGS=3D''
LIBC:=20
lrwxrwxrwx 1 root sys 35 Jul 17 19:01 /lib/libc.a -> /opt/K=
/SCO/unixds/5.1.2A/lib/libc.a
lrwxrwxrwx 1 root sys 36 Jul 17 19:01 /lib/libc.so -> /opt/=
K/SCO/unixds/5.1.2A/lib/libc.so
lrwxrwxrwx 1 root sys 39 Jul 17 19:01 /usr/lib/libc.a -> /o=
pt/K/SCO/unixds/5.1.2A/usr/lib/libc.a
lrwxrwxrwx 1 root sys 40 Jul 17 19:01 /usr/lib/libc.so -> /=
opt/K/SCO/unixds/5.1.2A/usr/lib/libc.so
lrwxrwxrwx 1 root root 41 Jun 26 11:48 /usr/lib/libc.so.1 ->=
/opt/K/SCO/Unix/5.0.6Ga/usr/lib/libc.so.1
Configure command: ./configure --prefix=3D/usr/local/mysql --with-extra-cha=
rsets=3Dcomplex CC=3Dgcc CXXFLAGS=3D-O3 CXX=3Dgcc



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

Re: Optimize and Order by problems in SCO

jesilva@limsa.com.mx writes:
> Description:
> The point is that it doesn't make the rigth selection of the index
> When it performs:
>
> explain select nfactura, nauclfe from auxiliar where nfactura like '30%' order by nfactura;
>
> The result we have is:
> table type possible_keys key key_len ref rows Extra
> auxiliar ALL PRIMARY NULL NULL NULL 1028572 where used;
> Using filesort
> The name of the table is "auxiliar" and the field "nfactura" is "unique
> key" not null.
> The rigth answer should be in the column "key" "nfactura" but it isn't
> as you can see. The value is NULL.
>
> The same query works ok with other versions of MySQL 3.23.45 for FreeBsd.
>


The above depends on number of rows in your table. The above EXPLAIN
shows that 1028572 rows satisfy the above like "30%".

How many rows are there in total ??

This could be an error on your part if nfacture is not CHAR-like column.

>
> And another problem is (ORDER BY):
> -----------------------
>
> I have table of 1,028,572 rows and i do query all, and after i can't do
> any
> more, because this process to blockade all mysql and i can't do query
> of any table using ORDER BY. (mysql use filesorting).
>
>
> What i can do ? May be is SCO version 3.23.43 and 3.23.51?
>

The above is not a bug but expected behaviour as MyISAM has locking on
table level only.

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

Re: Optimize and Order by problems in SCO

Hi!

Sorry for the delayed responce, but I have been on vacation...

>>>>> "Sinisa" == Sinisa Milivojevic writes:



>> And another problem is (ORDER BY):
>> -----------------------
>>
>> I have table of 1,028,572 rows and i do query all, and after i can't do
>> any
>> more, because this process to blockade all mysql and i can't do query
>> of any table using ORDER BY. (mysql use filesorting).
>>
>>
>> What i can do ? May be is SCO version 3.23.43 and 3.23.51?
>>

Sinisa> The above is not a bug but expected behaviour as MyISAM has locking on
Sinisa> table level only.

The above is not really the whole truth in this matter.
There is two separate problem:

- If you have a problem with mixing updates / updates then the problem
is MyISAM table locking and can be fixed by using INSERT DELAYED,
the INNODB table type or by optimizing your queries.

- If the problem is with only SELECT queries, the problem is a well
known problem on old SCO boxes that one thread may get all CPU and
the thread switch doesn't happen until the the thread is blocking
for a read. For this there is unfortunately no real solution than to
upgrade to a newer unix version.

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