#mysql
13 October 2007
Total 15 pages. You are browsing page 2/15.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
00:46 <****> im helping out here :)
00:46 <****> Oh well
00:47 <****> im gonna try moving /var/lib/mysql to antoher machine
00:47 <****> yeah, thanks Seekwill!
00:47 <****> have a good one
00:47 <****> It's always someone else that messed it up
00:47 <****> later
00:53 <****> Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' How do I avoid this error?
00:54 <****> Using phpMyAdmin?
00:54 <****> no, i am using a sql builder called sqlyog
00:54 <****> What's a fast, simple way to do a full backup of an entire MySQL database instance?
00:55 <****> Eg: all the tables, not necessarially the mysql binaries (I can get that part).
00:55 <****> If i remove the whereclause : WHERE LOWER(customerprofile.username)=LOWER('uname234') it works fine, but returns all rows
00:56 <****> I need WHERE LOWER(customerprofile.username)=LOWER('uname234') to return only 1 row
01:03 <****> can you have an or statment in your where clause?
01:03 <****> can you have a query: select * from table1 left join table2 where table1ID = table2ID or table1ID = xyz ?
01:07 <****> is there any way to monitor what queries are being executed currently?
01:09 <****> Hi
01:09 <****> if a table hasn't any primary key... will it be slower serving data?
01:10 <****> anyone?
01:11 <****> me but...
01:11 <****> no one answers :(
01:13 <****> Ramattack: you should ALWAYS have a primary key for all your tables
01:13 <****> swimrr: yes.
01:13 <****> karsten: thanks
01:14 <****> swimrr: ... you can have an OR statement in a WHERE clause.
01:14 <****> is there any way to monitor what queries are being executed currently?
01:14 <****> could you take a look at this please? http://www.howtoforge.com/proftpd_mysql_virtual_hosting_debian_etch
01:14 <****> rimad: 'show processlist;' as ordinary user shows user's queries, as privileged user shows all queries.
01:14 <****> here how could be that ftpquotalimits and ftpquotatallies not to use primary keys???
01:16 <****> are this tables made wrong???
01:16 <****> in the home of this ftp server have seen that they don't use primary key too... how could this be??
01:18 <****> rimad, mytop show realtime as the queries are being run
01:18 <****> Cahaan, could you please??
01:20 <****> Ramattack: well, making good designs is not granted to everybody
01:20 <****> you won't die if a table misses a primary key
01:20 <****> but I will do a sequential search?
01:20 <****> but that's not a good practice
01:21 <****> not if one of the field used for your search is indexed
01:22 <****> in the link I passed you as far as I see there are no indexes in table definition are they?
01:23 <****> which table?
01:24 <****> hmm I see
01:24 <****> ftpquotalimit and ftpquotatallie
01:24 <****> well yes, no indexes and no PK's
01:24 <****> so sequential search in them??
01:25 <****> yes, definitely
01:26 <****> and couldn't be something in the code that could avoid that row by row search?
01:26 <****> maybe the design itself
01:26 <****> i.e. if those tables only have 6 records, you wouldn't care
01:26 <****> the design of the table?
01:26 <****> the design of the application
01:27 <****> hi people
01:27 <****> so the app could be ready for avoiding those row by row searchs?
01:27 <****> what's the maximun number in decimal(15,4) ?
01:27 <****> in this case proftpd?
01:28 <****> I don't know Ramattack, I didn't create proftpd :) maybe they know what they're doing, maybe not
01:29 <****> just try to understand the purpose of these tables, and the probable number of records they will receive in a real-life usage
01:29 <****> but then always a table that hasn't primary keys or indexes will be read row by row from the begin of the table??
01:29 <****> one row per user
01:30 <****> ftpquotalimit and ftpquotatallies will always have a row per user
01:31 <****> (because I'll use quota for all of them)
01:31 <****> so always will be done a full table read?
01:32 <****> yes
01:32 <****> that's correct
01:34 <****> this is what I call a damn shit of implementation :)
01:37 <****> Cahaan, I was wondering what could happen if I set a primary key on that tables that haven't...
01:37 <****> Cahaan, could that damage? something?? think not isn't it?
01:37 <****> well it could help, depending how the queries are written though
01:38 <****> not is shouldn't do any damage, except if you set a primary key on a field which won't have unique data per record
01:41 <****> you might want to check how the queries on these tables are made, and possibly add an index where required
01:42 <****> I suppose I have not the enough knowdlege for knowing how index works or when it could be needed... lol
01:43 <****> Ramattack: it's kinda easy, check the fields inside the where clauses for these tables
01:43 <****> have done it
01:44 <****> it's on page 3, you know I have done it for understand queries it does...
01:44 <****> if you have something like select stuff from ftpquotalimit where used_id = xxx; then it would be a good idea to add an index on user_id
01:44 <****> s/used/user/
01:44 <****> sorry whats that last?
01:44 <****> s/used....
01:44 <****> typo correction
01:45 <****> hi
01:45 <****> hi
01:45 <****> typo correction?
01:46 <****> I typed "where used_id = xxx;" instead of " where user_id = xxx;"
01:47 <****> oh lol
01:47 <****> for example here
01:47 <****> SELECT "name, quota_type, per_session, limit_type, bytes_in_avail, bytes_out_avail, bytes_xfer_avail, files_in_avail, files_out_avail, files_xfer_avail FROM ftpquotalimits WHERE name = '%{0}' AND quota_type = '%{1}'"
01:47 <****> this is for obtaining quota of the user
01:48 <****> ok...
01:48 <****> then and index here would be...
01:48 <****> SELECT "name, quota_type, per_session, limit_type, bytes_in_avail, bytes_out_avail, bytes_xfer_avail, files_in_avail, files_out_avail, files_xfer_avail FROM ftpquotalimits WHERE name = '%{0}' AND quota_type = '%{1}'" index on name?
01:50 <****> then I would create an index on (name, quota_type)
01:50 <****> why?
01:51 <****> because these are the fields in the where clause
01:51 <****> they are both used to select your entry
01:52 <****> so you would do index of fields mentioned in where clause?
01:52 <****> Ramattack: yes... it might depend on the context (such as selectivity), but this is the evident way to create indexes :)
01:53 <****> one question... if you can avoid reading all table with indexes what are primary keys used for?
01:54 <****> to have an unique identifier for each record
01:54 <****> your primary key can also be a concatenation of several fields
01:55 <****> the primary key is what makes each record unique
Total 15 pages. You are browsing page 2/15.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last