#mysql
11 November 2007
Total 18 pages. You are browsing page 1/18.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
--- Log opened Sun Nov 11 00:00:33 2007
00:14 <****> Xgc, i guess i made some progress
00:14 <****> Xgc, http://pastie.caboo.se/116387
00:15 <****> just still need to get the class the student was on each semester
00:20 <****> on a cross join, if one of the queries returns 0 results, the whole query returns 0 results? (0 X N)
00:20 <****> yup
00:21 <****> ok
00:31 <****> Can somebody explain cardinality of primary keys to me?
00:31 <****> 1
00:32 <****> lol
00:32 <****> lol
00:35 <****> dampjam, cardinality of a primary key, or any UNIQUE key on a NOT NULL column, should be equal to the number of rows in your table.
00:36 <****> what is its purpose?
00:36 <****> cardinality is more useful for non unique indexes
00:36 <****> dampjam, . . . what is the purpose of what?
00:36 <****> it gives the estimated number of unique values in the tables
00:37 <****> Right. Basically, it tells you the size of the BTREE, or hash table or whatever.
00:37 <****> my whatever is bigger than your whatever
00:37 <****> whatever
00:37 <****> indeed
00:37 <****> okay
00:37 <****> thanks
00:38 <****> This is relevant to things like index scans: a B-tree with a cardinality of 10 is fast to scan even on a very large table, whereas a B-tree with a cardinality of 10,000,000 is not.
00:38 <****> Also, select and insert time for B-trees is logarithmic in the cardinality, AFAIK.
00:38 <****> So it will get a bit slower as it gets larger.
00:39 <****> I need a hybrid INSERT/UPDATE command... if col1 = 'abc' and col2='def' exists, then update col3 = 'xyz', else insert a new col with col1/2/3... is there such?
00:39 <****> pathfindr, INSERT ... ON DUPLICATE KEY UPDATE?
00:39 <****> Simetrical, weeeeeeee!
00:39 * Simetrical wants ARRAY indexes. What's the point of using a B-tree for a tinyint column? Or a densely-packed primary key or something?
00:39 <****> that's plain awesome
00:40 <****> but it requires I rid of the artificial key and make a composite one
00:40 <****> workaround for non-keys?
00:40 <****> pathfindr, REPLACE no good either? Then probably, uh, do two queries.
00:40 <****> I'll check replace
00:41 <****> You shouldn't have to get rid of the artificial key.
00:41 <****> You should be able to add a UNIQUE composite key, no?
00:41 <****> ah yea, REPLACE says identical PK or Unique key
00:41 <****> I just need unique composite
00:41 <****> :D
00:41 <****> Simetrical, thanks, exactly what I wanted
00:41 <****> I assume INSERT ... ON DUPLICATE KEY UPDATE is the same, but REPLACE is easier if it works for you.
00:42 <****> I have a question about SQL, I have a statement like this: SELECT buecher.id FROM books, genre WHERE genre.bookid = books.id AND (genre.name = 'novel' OR genre.name = 'fantasy')
00:42 <****> but I want the genres connected with AND
00:42 <****> x1jmp, each book can only have one genre, correct? You can't require that a book be in two genres at once.
00:43 <****> there are multiple rows in genre with the same bookid but different genres
00:43 <****> Oh, I see, sorry.
00:43 * Simetrical misread the query
00:44 <****> You could do it with some really messy subqueries pretty easily, but I'm not sure offhand how to do it nicely.
00:46 <****> it looks like a simple 1:n, but I don't know a lot about these and especially not about how to do such a query
00:47 <****> x1jmp FGS use a JOIN
00:48 <****> SELECT id FROM books JOIN genre ON bookid = id WHERE name IN ('novel','fantasy')
00:48 <****> but really, you should use a table for the genre names
00:48 <****> literals are ugly
00:49 <****> I'm doing this, I just simplified it ...
00:49 <****> adaptr, the problem is that he only wants it if it's in both the "novel" and "fantasy" genres.
00:49 <****> name IN ('novel','fantasy') would be OR
00:49 <****> but its better to parametrize as much as possible
00:50 <****> so you dont have to change the codebase when you remember you also need horror in there
00:50 <****> I meant I simplified for posting in IRC
00:50 <****> Simetrical true, this is why splitting out the genre details is more an application task
00:50 <****> Maybe: SELECT id FROM books JOIN genre AS g1 ON books.id=g1.bookid JOIN genre AS g2 ON books.id=g2.bookid WHERE g1.name='novel' AND g2.name='fantasy'?
00:51 <****> sure, TIAS
00:51 <****> but for arbitrary numbers of genres, you need a real solution
00:53 <****> hey guys when creating a db for a web app, should it be referenced or could i make only tables with no refence to the other ones
00:53 <****> Is it possible to use variables in subqueries?
00:54 <****> x1jmp, kinda
00:54 <****> jmoncayo if you mean foreign key relations, sure, but you need InnoDB to fully utilize that
00:54 <****> jmoncayo, use references, but make sure your hosts support them
00:55 <****> hosts ?
00:55 <****> what do "hosts" have to do with it ? what are "hosts" ?
00:55 <****> webhost
00:55 <****> some servers have innodb disabled
00:56 <****> so ? enable it
00:56 <****> right, if you're the server
00:57 <****> I am
00:57 <****> I always am
00:58 <****> after a crash, does mysql check every table? or just the ones that had transactions in the log?
00:58 <****> o
01:00 <****> it checks all of them, but good luck repairing MyISAM errors...
01:02 <****> Guys, I have a question if you don't mind. I'm coding a game, in this game there are multiple buildings etc...I have a friend who suggests I should store the building names within the database rather than static php vars...what do you guys think? Thanks
01:02 <****> adaptr: all innodb here
01:02 <****> From my point of view it seems silly to store static things which arn't going to change within a database?
01:03 <****> oktanouc that depends on ho wmany things you have :)
01:03 <****> 10
01:03 <****> well, yes - that IS silly
01:03 <****> Thanks :)
01:03 <****> Where would it become an advantage?
01:03 <****> but using a database allows for easy extension
01:03 <****> After how many buildings?
01:03 <****> everywhere you have data that may change
01:04 <****> I cannot see that, I have a simple config file where I can edit building names easily
01:04 <****> if you don't know, them perhaps above 1
01:04 <****> ah, well, then it's rather up to you - how many entities have properties that would benefit from relational storage ?
01:04 <****> I'll give you the full view
01:04 <****> if it's more than 2 or 3 you could use a database to increase versatility
01:04 <****> I have an isles table, this table contains cols such as building_1 etc
01:05 <****> oh, you already use a database ?
01:05 <****> up to building_10
01:05 <****> Yes...of course
01:05 <****> then everything goes in the database
Total 18 pages. You are browsing page 1/18.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last