#mysql
29 October 2007
Total 28 pages. You are browsing page 3/28.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
00:51 <****> nkbreau: in really large database apps, a decision will be made to use innodb masters for data entry, and myisam slaves to handle select queries. myisam is faster under those conditions.
00:51 <****> ok, but for a table with 12 million records if you need to bring back 5-6 fields and cant index them all, if the index fits in memory the query will still have to go to disk correct, unless it's cached ?
00:51 <****> benefits from an index on (b,a), as only the index needs to be accessed at all
00:51 <****> ki77a77: hmm, but when it replicate, the slave lock the whole table
00:51 <****> nkbreau: yes but once it knows where to look that's great
00:52 <****> 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:52 <****> ki77a77: I was actually wondering if you could do that... is it easy to create that type of setup with standard mysql clustering ?
00:52 <****> nkbreau for any database that doesn't fit in memory, yes, disk access is unavoidable
00:52 <****> kimseong: load balance the requests to multiple slaves.
00:52 <****> ki77a77: updates go to all slaves
00:52 <****> and the slaves can have a different table type than the master ?
00:52 <****> nkbreau: yes
00:52 <****> I have a table that is 30 million rows. I only have indexes on the fields that I filter by and it is plenty fast...
00:52 <****> nkbreau: true
00:53 <****> ToeBee: do you query fields without where clauses?
00:53 <****> ToeBee that's because you are accustomed to the speed you get.. trust me, when you inlude ALL fields you retrieve in the index it'll explode in speed - provided, of course, that the entire index fits in memory
00:53 <****> I do select * from table where ....
00:54 <****> if the master is innodb and the slaves are myisam when the records get written to the master do the same inserts then get performed on the slaves ? would the slaves not be able to keepup with the master's inserts ? I guess i need to readup on clustering more
00:54 <****> for any non-trivial database this means the entire database must fit in memory, as you obviously *need* all fields
00:54 <****> ToeBee: indexing will speed 'select somefield from tablename;'
00:54 <****> ToeBee yes, but there's also the buffer cache - why do I need to prove the basic working of a SQL database to you ? :)
00:56 <****> add an index on a text column and select it to make it faster.
00:56 * chadmaynard proves generalization that is going on is bad for people listening
00:56 <****> the correct answer to all of these questions is "it depends"
00:57 <****> ToeBee: by the way, mysql is written to optimize 'select count(*) from table;' that have no where clauses.
00:58 <****> how does data getten written from the innodb master to the myisam slaves ?
00:58 <****> or should i say replicated
00:58 <****> Uh-oh, time to watch Colorado Rockies lose the World Series.
00:58 <****> ha
00:58 <****> who said anything about count??
00:58 <****> MEEEE
00:58 <****> ToeBee: sorry, justing noting a bit of trivia.
00:58 <****> i watched about 1/2 an inning the other night and couldnt stand the boredom
00:59 <****> i think we should switch the channel topic to physics on sundays
00:59 <****> nkbreau: cramming 30 minutes of action into 3 hours...
00:59 <****> 30 minutes ? that much ?
00:59 <****> well I guess this page contains the relevant details... nkbreau, you might want to take a look. http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
00:59 <****> !man how mysql uses indexes
01:00 <****> (How MySQL Uses Indexes) : http://dev.mysql.com/doc/mysql/en/mysql-indexes.html
01:00 <****> : _
01:00 <****> : )
01:00 <****> "If a query uses only columns from a table that are numeric and that form a leftmost prefix for some key, the selected values may be retrieved from the index tree for greater speed"
01:00 <****> $_
01:00 <****> would datetime be considered numeric ?
01:00 <****> ha!
01:00 <****> so ok... I guess data CAN be retrieved from an index... but only in certain cases
01:01 <****> ToeBee: I just said that.
01:01 * chadmaynard slaps toebee with a tuna
01:01 <****> nkbreau: is datetime *stored* as an integer?
01:01 <****> yes
01:02 <****> but it isn't indexed as one
01:02 <****> will this script run despite the comments if I type this at the command line: mysql -u root -p databasename < script.sql http://pastebin.com/d7055008f
01:02 <****> so... that's a no ?
01:02 * ToeBee dodges chadmaynard's tuna
01:02 <****> chadmaynard: I thought indexes were stored as hashes...
01:02 <****> I was off RTFMing :)
01:02 <****> ki77a77: i mean it isn't indexed like an integer is
01:03 <****> most indexes are b-trees
01:03 <****> can someone look at this script and tell me if it will run despite the fact that it has comments? will the comments interfere with the script? http://pastebin.com/d7055008f
01:04 <****> hashes in the memory
01:04 <****> is there any way to find out how much memory an index is using up, or to know if the entire index is in memory ?
01:04 <****> crackintosh: i can tell you it isn't normalized
01:04 <****> or anything along those lines ?
01:04 <****> chadmaynard: why is it not normalized?
01:04 <****> crackintosh: other than that TIAS
01:05 <****> holy hell that is one fat table
01:05 <****> you have 140 columns too, that's never good for life
01:05 <****> but "2007 price" "2006 price" .....
01:06 <****> the list goes on
01:06 <****> oh yeah, normalized. no the database is not normalized.
01:06 <****> you put every possible attribute as a column
01:06 <****> its a legacy database that used to be used with access, it was started back in 1996.
01:06 <****> i'm assuming most rows won't use most columns
01:07 <****> chadmaynard: It is a very redundant database.
01:07 <****> are you getting an error?
01:07 <****> I havent run it yet, I wrote that sql script last night.
01:07 <****> scary
01:07 <****> try it
01:07 <****> what do you have to lose?
01:08 <****> pride of it working on the first try!
01:08 <****> alright, i guess ill try it.
01:08 <****> ToeBee: I can bet it won't work the first time
01:08 <****> it'll probably disconnect him from irc...
01:09 <****> heh
01:09 <****> and probably leave his toilet lid up
01:10 <****> thol
01:11 <****> ok thanks for all the info guys, I'll have to look into clustering possibly...
01:11 <****> what's a 'toilet lid'?
01:11 <****> sorry, batchelor talk
01:11 <****> chadmaynard: This script draws from multiple databases should I use this syntax? `databasename`.`tblname` instead of just saying which table to use?
01:12 <****> crackintosh: either is fine
01:12 <****> i'd use db.table
01:12 <****>= NOW()) END
01:12 <****> chadmaynard: I agree
01:12 <****> so I have a really slow db and I want to know how to use explain to figure out the problem
01:13 <****> it's used to set a WHERE criteria in a query
01:14 <****> hey all http://paste.ubuntu-nl.org/42521/
01:14 <****> when i change $number to actuall number ie 4448 it works..
01:14 <****> but with $number wont work
01:14 <****> !m SiliconG explain
Total 28 pages. You are browsing page 3/28.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last