[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    
From:       Michael Widenius <monty () monty ! pp ! sci ! fi>
Date:       1999-01-30 15:47:22
[Download RAW message or body]

>>>>> "Andrei" == Andrei V Loukinykh <avl@pb301e.crimea.com> writes:

Andrei> Hello
Andrei>   I wish I didn't ask any questions about Mysql, but I'm eventually
Andrei> forced to do it
Andrei>     There is simple DB :
Andrei> CREATE TABLE  ad   (
Andrei> id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
Andrei> dn VARCHAR(8) NOT NULL,
Andrei> cdt CHAR(8) NOT NULL,
Andrei> ct TIME NOT NULL,
Andrei> cvt SMALLINT(6) UNSIGNED,
Andrei> dig VARCHAR(15) NOT NULL,
Andrei> PRIMARY KEY ( id ),
Andrei> INDEX ii (dn,cdt));

Andrei> The table now has about 800 000 rows.
Andrei>   The file  ad.ISD takes about 26 Mb and ad.ISM takes 27 Mb.

Andrei> Linux- slackware- 2.0.36, 586, 16Mb memory
Andrei> Mysql 3.21.33b


Andrei> May be I haven't done it perfectly, but its perfomance in Selects is not
Andrei> the question
Andrei> to talk about at this time.

Andrei> Today all I wanted to do was just to add an index on 'dig'- its first 6
Andrei> chars.
Andrei>   I created simple script :
Andrei> "alterscript"
Andrei>     use ATS;  /* My Data Base's name */
Andrei>     ALTER TABLE ad ADD INDEX digindx (dig(6));

Andrei>   and started it :  cat alterscript | mysql

Andrei> HERE's my question:  I STARTED IT about 9 a.m and the time I'm writing these
Andrei> lines
Andrei> ( 21 p.m.) it's still working!
Andrei> It converts tables at a speed of about 10kb/min or so.

Andrei> That's all,folks :(

Andrei> I repeat:  Its perfomance in Selects almost meets my demands ( so far ). No
Andrei> questions about it at the moment. The very huge select I can make takes not
Andrei> more 30 c.( I mean smart selects, not stupid ones).

Hi!

If you want 20-40 % more speed on your select, you should change all
your varchar to char.

Under normal normal conditions MySQL should convert rows at about
1000 - 10,000 rows/second with 3 indexes. If not there is some unknown 
faktor (like disk full or swapping) that makes this slower.

With 16M memory, the problem is probably that your keybuffer is too
big. Try different values between 2-8M to find the optimal value for
your machine (mysqld -O keybuffer=#).

Regards,
Monty

-----------------------------------------------------------
Send a mail to mdomo@tcx.se with
unsubscribe mysql your@subscription.address
in the body of the message to unsubscribe from this list.

[prev in list] [next in list] [prev in thread] [next in thread]