#mysql
29 October 2007
Total 28 pages. You are browsing page 2/28.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
00:27 <****> dayofmonth*
00:27 <****> hardcorelamer: it has to run the function on EVERY row so the index is useless
00:28 <****> hardcorelamer: using an index to select every row is actually slower than a tablescan
00:29 <****> www.john-hunt.com/mysql.txt << This query worked with an older ver of mysql, but now it's saying: ERROR 1054 (42S22): Unknown column 'a.id' in 'on clause' any ideas?
00:29 <****> john hunt? haha
00:29 <****> err, what?
00:29 <****> ever get any jokes or was that intentional?
00:30 <****> JohnRobert: a matter of precedence in joins. Replace your commas with left joins
00:30 <****> no, no one has ever been clever enough to make fun of my name
00:30 <****> JohnRobert: :)
00:30 <****> ki77a77: weird
00:30 <****> JohnRobert: what versions did you use? that changed you know
00:30 <****> JohnRobert: not al all
00:30 <****> the commas seperate the table fields
00:31 <****> meh, I've an idea
00:31 <****> yeah, they're only clever enough to make fun of his brother, Mike
00:31 <****> i get error 404...
00:32 <****> hmm
00:32 <****> JohnRobert: search this page for precendence; http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html
00:32 <****> ah
00:32 <****> thx
00:34 <****> JohnRobert: or precedence, actually
00:36 <****> ki77a77: so I assume the offending bit is FROM activities a, users LEFT ..
00:37 <****> you better believe it
00:37 <****> :/
00:37 <****> I don't have time for this ://
00:37 <****> hello - I have a few questions - I need help with memory usage
00:38 <****> I have been looking at a few things and currently my Max Memory Ever Allocated is 261M
00:38 <****> buy more!
00:38 <****> JohnRobert: you were probably wanting just an inner join ( i didn't read your text file, but i assume a lot)
00:38 <****> oh
00:38 <****> but the Configured Max Per-thread Buffers : 16 G
00:38 <****> !Perror 1054
00:38 <****> no ?
00:38 <****> and my max memory Limit is 17 g - this all seems really high
00:38 <****> how do I fix this?
00:38 <****> adaptr: perrors come at the end of queries
00:39 <****> ah
00:39 <****> SiliconG do you actually HAVE all of that ?
00:39 <****> adaptr: 1054 is unknown column i think
00:40 <****> hi guys =)
00:40 <****> no the machine has 2g of ram that is the problem
00:40 <****> anyone here with some free time, i need help building my web database layout
00:40 <****> how do I set this up correctly.
00:40 <****> SiliconG why is that a problem
00:40 <****> SiliconG increase your buffer cache , up to ~90% of your RAM if it only runs MySQL
00:41 <****> ok
00:41 <****> but I do run other services
00:41 <****> then use less
00:41 <****> that's both tricky and difficult, since you have to guess how much RAM these other services need
00:41 <****> what I figured was to set it to 90% of the free mem
00:42 <****> well, that depends
00:42 <****> is there a way to setup the max memory limit
00:43 <****> I just told you, yes
00:43 <****> any optimization experts kicking around ? we have a table with 12 million records which seems to be getting slow... if i do "select id from table" it comes back very fast, but as soon as i do "select id, field_z from table" the query gets slow, until it is in the cache... would this be because the 2nd field specified causes the query to have to read from disk ?
00:43 <****> but it won't actually reach that if it's not necessary
00:43 <****> nkbreau queries are never "read from disk"
00:43 <****> they can use an index, or not use an index
00:44 <****> hackmysql.com
00:44 <****> if you have an index on "id" then the query only needs to access the index, which is usually all in memory
00:44 <****> but if field_z is not in the index, then that has ot be loaded from disk if the table does not fit in memory
00:44 <****> simple solution: set the index on both id and field_z
00:45 <****> well... he is talking about fields in the SELECT clause... that shouldn't be affected by indexing should it?
00:45 <****> of course it is - it is the ONLY thing that is affected
00:45 <****> ToeBee: ?
00:45 <****> indexing helps to optimize the WHERE clause and joining
00:45 <****> ok, so are all indexed fields loaded into memory ?
00:45 <****> nonsense
00:45 <****> ideally, yes - if you have memory for it and it doesn't get swapped out
00:46 <****> so if i want to select id, f1,f2 from ... where f4='xxxxxx' would i need to do a index based on all those fields ?
00:46 <****> no "need", no.. but 12 M rows is substantial, so chances are not everything will be in memory all the time
00:46 <****> but an index on f1 and f2 wouldn't speed up that query
00:46 <****> as soon as a query goes form all memory to disk it slows down by a factor 1000 or so
00:46 <****> A fast database should fit in the memory
00:47 <****> ToeBee of course it would, you're not reading what I said
00:47 <****> and as a database grows that is eventually unavoidable, correct ?
00:47 <****> true
00:47 <****> which is why index design is a true art
00:48 <****> If I did the query: ALTER TABLE `tblname`.`databasename` would that work? or would I have to be using a specific database prior to running an ALTER TABLE command?
00:48 <****> ToeBee in program code reality an index does only one thing: it stores data in a smaller subset of the table, making it faster to process
00:48 <****> 12M rows, if all 4 fields are integer, then 1 row is about 20 bytes, thats will only be 240MB, not much
00:48 <****> WHY it is processed is irrelevant
00:48 <****> what do you mean a fast database should fit in memory ?
00:48 <****> nkbreau because it would not be fast if it were on disk ?
00:48 <****> adaptr: that's not true.
00:48 <****> factor 1000, at *minimum*
00:49 <****> right, but any good size database will not be able to fit in memory
00:49 <****> adaptr: index could be larger than the data and still useful
00:49 <****> then it should be if data fits in memory, it will be fast :)
00:49 <****> chadmaynard true, but now I am only talking about the data storage part, not the indexing itself
00:49 <****> ok
00:49 <****> obvioulsy indexing serves a purpose, or else why do it at all :)
00:49 <****> adaptr: indexing the fields that only appear in the SELECT clause is silly...
00:50 <****> ToeBee no, it isn't
00:50 <****> adaptr: if you never filter on them it is
00:50 <****> or sort
00:50 <****> select c1 from t order by c1 the index works
00:50 <****> maybe depends
00:50 <****> yea i threw sort in there at last minute
00:51 <****> chadmaynard true, but then it would STILL make sense to include them in the index if the conditions hold true (the key being unique and the index being small enough)
Total 28 pages. You are browsing page 2/28.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last