List:General Discussion« Previous MessageNext Message »
From:Rudy Metzger Date:July 14 2003 8:42am
Subject:RE: Can mysql handle this load?
View as plain text  
Just take care that you do not cast and or modify your datecolumn in the where clause, no
matter what data type you use. Because then you would loose the index on it. So if you
use int in you applications it might indeed be wise to use int in the DB too. However
when starting a new application, I would use DATETIME on both sides (PPHP/perl,... and
DB).

Example:

...
WHERE unix_timestamp(somedate) = 4858453
...

BAD, as MySQL cannot use the index anymore!

...
WHERE date_add(somedate, 5 INTERVAL days) = '2003-05-23'
...

BAD, again you loose the index

...
WHERE somedate = date( "%Y-%m-D", mktime() )
...

(not sure about correctness of PHP syntax, but hope you get the meaning)
GOOD, because you leave the MySQL date variable unchanged

...
WHERE somedate = date( "%Y-%m-D", mktime()+5*60*60*24 )
...

GOOD, as you move the modification to the "constant" (not indexed) part of the equation.

Same counts for all other data type formats, of course.

Cheers
/rudy



-----Original Message-----
From: Adam Nelson [mailto:adam@stripped] 
Sent: vrijdag 11 juli 2003 17:19
To: nospam@stripped; mysql@stripped
Subject: RE: Can mysql handle this load?

Certainly datetime is the way to go.  It takes up 8 bytes per row, as
opposed to 4 bytes for int.  But, even if there are 10 million rows
(over 27 years of data), that's only a 4 MB difference.

> -----Original Message-----
> From: nospam@stripped [mailto:nospam@stripped] 
> Sent: Thursday, July 10, 2003 5:13 PM
> To: mysql@stripped
> Subject: Re: Can mysql handle this load?
> 
> 
> why use INT for a date?
> i am used to do this with my bulletin board, since i need a 
> 1-second resolution and so i can easily use the time() 
> function in php and format the output string with date(), 
> which is also using unix timestamps.
> but for applications that only need a resolution of 1 day, 
> something like DATE would be better, i think. for client 
> side, it's more processing to get the date displayed and to 
> do some arithmetics with it (calculate time spans etc.), right?
> 
> correct me if i'm wrong, since i had some chaotic encounters 
> with DATE and TIMESTAMP values at the beginning of my 'mysql 
> time', and i'm using INT unix timestamps since then...
> 
> -yves
> 
> 
> -----Ursprüngliche Nachricht----- 
> Von: "Rudy Metzger" <rudy.metzger@stripped>
> An: <nospam@stripped>; <mysql@stripped>; "Adam 
> Gerson" <agersonl@stripped>
> Cc: <benchmarks@stripped>
> Gesendet: Mittwoch, 9. Juli 2003 17:19
> Betreff: RE: Can mysql handle this load?
> 
> 
> Why using int for date? Better suited would be DATE or 
> DATETIME (or even TIMESTAMP, depending how you want to use it).
> For studentid, SMALLINT or MEDIUMINT would maybe suffice, esp 
> when you make them UNSIGNED.
> For status I would choose CHAR(1), you can put a lot of 
> information into that, which also stays (a bit) human 
> readable. Also enums would be ok but are a mess to change 
> later (in the application). Do yourself a favor and use a 
> master detail relation for this, eg:
> 
> CREATE TABLE student_status (
>   Status CHAR(1) NOT NULL,            /* short status flag, eg. A */
>   Verbose VARCHAR(20) NOT NULL,       /* verbose description, 
> e.g. ABSENT */
> PRIMARY KEY(status)
> )
> 
> Maybe keep 'verbose' on char to force fixed line size and 
> thus faster access.
> 
> Cheers
> /rudy
> 
> -----Original Message-----
> From: nospam@stripped [mailto:nospam@stripped] 
> Sent: woensdag 9 juli 2003 16:42
> To: mysql@stripped; Adam Gerson
> Cc: benchmarks@stripped
> Subject: Re: Can mysql handle this load?
> 
> i think this should be no problem...
> 
> i'd think of some table layout like this:
> date         int      PRIMARY
> student_id   int      PRIMARY
> status       int
> extra_data   what-you-want
> 
> then you should get about 360,000 records per year.
> i saw people on this list reporting about millions of records 
> etc... and i guess they had a little greater tables than you 
> should get here.
> 
> but why would you want to move any previous records to 
> another table all the time? just keep it in one table and 
> back up anything older than 5 years or so. that keeps your 
> table at, say 50 MB, and you can run real-time queries anytime :)
> 
> -yves
> 
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Access deniedAsterix11 Mar
  • Re: Access deniedChristian Mack11 Mar
  • Re: Can mysql handle this load?nospam9 Jul
  • RE: Can mysql handle this load?Andy Eastham9 Jul
  • Re: Can mysql handle this load?Krasimir_Slaveykov10 Jul
  • Left Join - Revistedvernon10 Jul
RE: Can mysql handle this load?Mike Hillyer9 Jul
RE: Can mysql handle this load?Rudy Metzger9 Jul
RE: Can mysql handle this load?Rudy Metzger9 Jul
RE: Can mysql handle this load?Rudy Metzger9 Jul
RE: order of table joins or where clauses relevant?Rudy Metzger10 Jul
RE: Left Join - RevistedRudy Metzger11 Jul
RE: Can mysql handle this load?Rudy Metzger14 Jul