Dave Hodgkinson wrote:
>
> "mike thomas" <[EMAIL PROTECTED]> writes:
>
> > Greg,
> >
> > Well, I'm doing like "%stuff%" selects on a single column table
> > with 20 million records, so any speed gain would be greatWould
> > I be correct in saying that a select of this type will always do
> > a table scan?
>
> Pretty much.
>
> > I think I'll go with SCSI and increase the RAM so I can use a
> > heap table.
>
> A cheap first step might be to go with a couple of decent 7200 rpm,
> ATA100 (or whatever the hell they're calling themsevles this week)
> disks and RAID0 them.
>
> Striping Good.
This still does not help the seek time of SCSI vs EIDE (ATA100 only
helps the bus speed AFAIR).
Some calc's I did a while back implied that SCSI seek time is 40% better
(or less) - this was comparing IBM SCSI 10k drives to IBM EIDE.
Agreed that striping is good tho.
>
> Also, make sure your IDE is spiffed up to the max. Do an:
>
> hdparm /dev/hda (or whatever).
Redhat is very bad for not tweeking the UDMA - but they do not as they
are afraid of damaging old drives that do not support it.
>
> Make sure it's using DMA, unmasked IRQ, 32 bit i/o and, a multcount of
> 16. Benchmark your drive before and after, with:
>
> hdparm -t /dev/hda
>
> You should see >10MB per second after tuning up.
Once you've tweaked it put the hdparm setting at the end of your
/etc/rc.d/rc.local (if on redhat - your's may be in a different
location) so that it gets loaded at each boot.
You may also wish to mount your disks with no atime (each time your
disks are access the access time of the file is update - if you do not
need this then switch it off) eg an example line from my /etc/fstab:
/dev/hda2 / ext2 noatime,defaults
1 1
>
> However, I can't help thinking for your application There Must Be A
> Better Way. Do you really need to do substring matching all the time?
> Can you pre-build an intermediate results set that can be optimally
> queried?
(Dave sometimes you worry me ;-)
Dave's right - substring matching is bad performance wise.
Greg
>
> Dave.
>
> --
> Dave Hodgkinson, http://www.hodgkinson.org
> Editor-in-chief, The Highway Star http://www.deep-purple.com
> Apache, mod_perl, MySQL, Sybase hired gun for, well, hire
> -----------------------------------------------------------------
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
