On Wed, 2008-01-16 at 20:55 -0800, Michael Tinsay wrote: > --- On Thu, 17/1/08, joebert jacaba <[EMAIL PROTECTED]> wrote:
> > Common queries: <snip> so Michael and Rob were right then. And you won't need that integer month field. Michael has given you a bunch of SQL. That should be sufficient. If you were working with a totally bogus database that didn't understand <, >, =, BETWEEN for dates, then you might need to add a string (YYYYMM or YYYYMMDD) or integer (epoch, as suggested by, I think, Zak) field to help. but mysql does have those operators, so you won't need to bogotify your schema. >> 4. get the account details of an or number well, probably just select * from [or_table] where or_number=[or_no_constant]; although if you need columns in related tables, well, you'd need to do a join there. if the or_table is large, you might want to have an index on or_number. or just try it. if the queries are significantly faster then keep the index, else remove the index. Note: you normally want to have only the minimum number of indexes as are needed by your app. I have worked with a company that had an index on every single column in their database plus a few functional indexes. Normally, an insert or update will require updates to all the indexes on that table also. So it's overhead. The indexes also take space, more overhead. Indexing all columns in a table will lead to doubling disk usage for a table (add a bit more for overhead). depending on the database, an index on account_no and a separate index on due_date will usually perform worse than a compound index on (account_no, due_date). However, you balance that with the flexibility that the separate indexes give you. a query on just account_no will use the single index, an index on just due_date will use the single due_date index. in the compound index only case, a query on just due_date WILL NOT use the index since the index's first column is account_no. By the way, joebert (and list), I strongly recommend lurking on the postgresql pgsql-general (and perhaps on the pgsql-sql) mailing list. It's very mind-expanding. I am not familiar with the mysql mailing lists, but a lot of the knowledge on pgsql-general/pgsql-sql (sql tips, general database concepts) will apply to mysql. warning though, you might want to lurk on some advanced mysql lists instead. learning postgresql and listening to the incredibly knowledgeable discussions on those lists will make you dissatisfied with mysql "The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression." http://dev.mysql.com/doc/refman/5.1/en/data-type-defaults.html but maybe they'll fix that eventually. Or maybe some mysql variant or engine supports that now, I don't know. in postgresql you can, e.g., do: tiger=> create table r(i int, j int default random()*1000); CREATE TABLE Time: 115.453 ms tiger=> insert into r (i) values (1); INSERT 0 1 Time: 15.347 ms tiger=> select * from r; i | j ---+----- 1 | 968 (1 row) Time: 0.626 ms and you can stick any function in the default there, as long as it returns the right data type. at some point (might still be true), mysql does not enforce foreign key constraints. you can *SAY* foreign key ... in your table definition, but it's just some text there, nothing actually happens if you insert a row that does not have a matching row in the parent table. This may be fixed by now (5.0 or 5.1), but I don't care enough to look. tiger _________________________________________________ Philippine Linux Users' Group (PLUG) Mailing List [email protected] (#PLUG @ irc.free.net.ph) Read the Guidelines: http://linux.org.ph/lists Searchable Archives: http://archives.free.net.ph
