#mysql
08 October 2007
Total 24 pages. You are browsing page 1/24.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
--- Log opened Mon Oct 08 00:00:33 2007
--- Day changed Mon Oct 08 2007
00:00 <****> Rince: you sure that is how I connect to a socket? Doesnt seem to work
00:00 <****> i'm getting "DBI connect failed : Access denied for user ''@'localhost' to database 'mysql'"
00:02 <****> doesn't work
00:02 <****> Doesn't work is not a helpful statement. Was there an error? Unexpected results? Does it sit on the couch all day eating all your cheetos and ignoring the classifieds? Be specific!
00:02 <****> tomin ^^
00:02 <****> oh sorry
00:02 <****> I have two mysql-servers running
00:03 <****> so my interpretation from this is that its trying to connect to the one not running on the socket
00:03 <****> ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
00:03 <****> this is what I ran
00:03 <****> mysql -u root -p -h localhost :/Applications/MAMP/tmp/mysql/mysql.sock
00:04 <****> does it work if you take out the -p ?
00:05 <****> ToeBee: same but using password: NO
00:05 <****> Im sure Im supposed to use something like -P or something to connect to a socket, but cant remember what letter it was
00:06 <****> hmm I've never tried specifying socket path... have you checked the man page/
00:07 <****> looks like --socket= or -S
00:08 <****> i have a column in my mysql database that is an auto_increment, is there any way to run a query to see what the next value will be when something is added?
00:09 <****> are there any table limitations with MySQL?
00:09 <****> airj1012, SELECT MAX(id) FROM table;
00:09 <****> airj1012, select max, and +1. but why do you want to know that before the insert?
00:09 <****> SpNg, what sort of table limitations are you thinking of?
00:09 <****> airj1012: not that I know of but you can get the id of the last ID that was inserted after you insert it
00:10 <****> Well, the # of tables
00:10 <****> can I do millions?
00:10 <****> that may not work it there is a deleted column
00:10 <****> say i had 2 and 3, and deleted 3
00:10 <****> the next value would be 4, but that solution would give me 3
00:10 <****> Simetrical: we are creating several tables, but keeping them small
00:10 <****> SpNg: if you are creating millions of tables then you are doing it wrong :)
00:11 <****> the reason is because i need that value on the page as it's getting entered into the table so people can see their post without having to reload the page
00:11 <****> kind of confusing
00:11 <****> SpNg, you do not want to create millions of tables, period. For any reason.
00:11 <****> At all.
00:12 <****> It is not how relational databases are meant to be used and it will be horribly slow and difficult to deal with.
00:12 <****> ToeBee: We are creating several tables for each user. Is it better to have one table/user or break down the user's content to several tables?
00:12 <****> Is the best way to check for an empty string: FIELD='' ?
00:12 <****> airj1012, thats bullshit, review your db/program code
00:12 <****> give it a boolean of active/inactive
00:12 <****> what
00:12 <****> Simetrical: What can MySQL process quicker. Lots of little tables, or less big tables?
00:13 <****> SpNg: usually you have one table for all users...
00:13 <****> SpNg, FEWER BIG TABLES. VERY EMPHATICALLY.
00:13 <****> SpNg, YOUR DATABASE WILL GRIND TO A HALT IF YOU HAVE THOUSANDS OF TABLES INSTEAD OF A FEW BIG TABLES.
00:13 <****> I use tables with 10s of millions of rows in my website
00:13 <****> SpNg, I hope it's clear now?
00:13 <****> :)
00:13 <****> hmmm
00:14 <****> interesting
00:15 <****> SpNg, the basic rule should be that you should never have to create or delete tables in normal operation. Ever. You should only have to do that if you're adding/removing features or otherwise changing the application itself.
00:15 <****> SpNg, trying to dynamically rebuild your database instead of deciding ahead of time how it's to be laid out will end in tears.
00:15 <****> ToeBee: when querying a table that has that may rows, how does MySQL sift through it? Does it do it laterally (starting at the top?)
00:15 <****> . . . of blood.
00:16 <****> SpNg, it uses indexes.
00:16 <****> yes. indexes are crucial
00:16 <****> SpNg, with correct indexes, it can retrieve exactly the rows it needs without having to scan through anything it doesn't need to retrieve.
00:16 <****> are the tables indexed when created, or do we need to build an index to reference?
00:17 <****> without indexes my queries would probably take minutes on such large tables. Obviously not practical. With indexes, I can run the 30 queries I need in maybe a second
00:17 <****> really
00:17 <****> SpNg, you need to specify what indexes you want when you create the table. You can add or remove them later if your needs change.
00:18 <****> ok, let me check this out
00:18 <****> tutorial
00:18 <****> sounds like what I need to do
00:18 <****> MySQL Tutorial: http://www.mysql.com/doc/en/Tutorial.html SQL Tutorial: http://www.sqlcourse.com and tut.php-q.net/mysql.html
00:18 <****> SpNg, simple queries like retrieving the full info for the user named "Bob" will take maybe a millisecond if there's an appropriate index.
00:18 <****> Even if the table is ten million rows.
00:18 <****> I would look at some of these tutorials. It seems like you need a lesson in basic relational database design :)
00:19 <****> I'm not the engineer, but he told me it was better to have several small tables then it was to have 1 big table
00:19 <****> :)
00:19 <****> SpNg, several small tables, yes, definitely. Thousands, no.
00:19 <****> well it depends...
00:19 <****> we are talking thousands
00:20 <****> SpNg, you should not have only one table, usually. You should never have more than a hundred (and that's really pushing it). Generally, you should have one table for each logically distinct sort of thing.
00:20 <****> lots and lots, eventually millions
00:20 <****> usually tables will follow some logical grouping
00:20 <****> One table for users, one table for products, one table for sales, one table for sessions, one table for user groups, etc., etc.
00:20 <****> I see
00:21 <****> Well good thing I checked into this before we got too deep in :P
00:35 <****> is it possible to index on a function instead of the table data?
00:35 <****> Normalization is wrong.
00:35 <****> No
00:35 <****> :(
00:35 <****> That doesn't really make much sense.
00:36 <****> well, like
00:36 <****> I've been told pgsql can do it, provided the function is a function only of the data (if it's a function of anything else as well then you're on shaky ground).
00:36 <****> Yeah sime, thats what I was thinking
00:36 <****> It can also use a different index for select and sort, apparently (although maybe not as efficiently as using the same index?).
00:36 <****> create index blah on mytable ( id, upper(somefield) );
00:37 <****> prakriti, well, that you can do with collations.
00:37 <****> ?
00:37 <****> that went over my head
00:37 <****> prakriti, if it's a typical VARCHAR or TEXT or whatever, then searches will be case-insensitive anyway.
00:37 <****> And indexes, etc.
00:37 <****> how is that possible?
00:38 <****> are you saying upper() hits the index?
00:38 <****> I'm saying that SELECT name FROM foo WHERE name='Bob'; will return 'BOB' if that exists.
00:38 <****> If `name` is some kind of non-binary column.
00:38 <****> wtf...
00:39 <****> So you don't need UPPER(), which is probably too sophisticated for the optimizer to realize it's pointless.
Total 24 pages. You are browsing page 1/24.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last