[PLUG] mysql performance questions

[Bruce, Tim]

Jeff,

Pinning a table in memory can be a "bad" thing.  If the table gets too large
at some point in the future, it could cause a lot of thrashing of the
database.  If you are convinced that it won't get that large, then it should
be ok.   

An article that explains it more in depth is:
    http://www.databasejournal.com/features/mysql/article.php/3077531

As far as joins go, mixing both integer and char (as an example) in the
where clause is fine.  Changing the order (joining on a char before joining
on an Int or vice versa) may have an impact.  However, joining a CHAR field
to an INT field is a no-no because a conversion has to be done before the
join can be accomplished.  Even adding a conversion to the join will
decrease performance (Example below).  (NOTE: This is a poor design - only
for example!)

Give the tables as below:

Table: Client
Id   INT
First VARCHAR (20)
Last VARCHAR (15)
Address1 VARCHAR (20)
Address2 VARCHAR (20)
City  VARCHAR (25)
State VARCHAR (20)
ZipInt INT
ZipId   INT

Table: Zip
ZipId   INT
ZipCode CHAR (5)
ZipInt  INT    //DON'T DO THIS when it's identified as CHAR 5 also!


Example
SELECT c.name, c.address, c.city, c.state, z.zip FROM client as c 
    LEFT JOIN zip AS z ON z.ZipCode = c.ZipInt  <-- BAD  Conversion
Required!

SELECT c.name, c.address, c.city, c.state, z.zip FROM client as c 
    LEFT JOIN zip AS z ON z.ZipId = c.ZipId  <-- BAD  Conversion Required!
    
NOTE:  Both assume that ZipId is indexed, as is ZipCode.  ZipId is a PK and
Identity.

Tim

Timothy J. Bruce
SQL Application Support Specialist
(Database Administrator)

Fiserv EFT
4550 SW Macadam Ave
Portland, OR 97239

Phone:  503-274-6750 (W)
Fax:  503-274-6619
tim.bruce at fiserv.com

This e-mail is confidential and may well be legally privileged.   If you
have received it in error, you are on notice of its status.   Please notify
us immediately by reply e-mail and then delete this message from your
system.   Please do not copy it or use it for any purposes, or disclose its
contents to any other person.   To do so could violate state and Federal
privacy laws.   
Thank you for your cooperation.   Please contact me if you need assistance.

-----Original Message-----
From: Jeff [mailto:freyley at gmail.com] 
Sent: Saturday, September 18, 2004 5:31 PM
To: plug at lists.pdxlinux.org
Subject: [PLUG] mysql performance questions

hi,

I'm looking to optimize queries on a really really big dataset in mysql.
I've heard rumors that you can force MySQL to push a table into memory (if
you've got enough space) and think I have a way to do it, but am not sure
after reading the documentation if it really does what I want or if there's
actually a better way. What I'm trying is

create table tblheap type=heap select * from tbl;

The queries include multiple joins, where clauses, and order by criteria.
I've also attempted to create indexes which help the queries go faster, and
have succeeded to some extent, but I'm still confused about how to deal with
the fact that there are very different fields in the joins, where clauses,
and order by. Which of these need the fields included in the index, and is
there any way that order can use a different index than the rest? I
understand explain and use it extensively, but if someone has a good
suggestion for reading to better understand the art of designing indexes
around queries, I'd appreciate that too.

Thanks,

Jeff

_______________________________________________
PLUG mailing list
PLUG at lists.pdxlinux.org
http://lists.pdxlinux.org/mailman/listinfo/plug