[PLUG] mysql performance questions

[Kirk Markovitz]

Gary,

This came across on the linux users group.

Kirk
At 11:48 AM 9/20/2004 -0700, you wrote:
>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
>
>
>_______________________________________________
>PLUG mailing list
>PLUG at lists.pdxlinux.org
>http://lists.pdxlinux.org/mailman/listinfo/plug