| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12
Comments:
<0> explain select * from table group by id; ... shows possible_keys = null, keys = id ... why?
<1> id isn't being used to narrow the rows returned
<2> but is used for group by
<0> firewire, but in the end it uses the key anyways ...
<1> it's using the key for group by
<0> firewire, i'm trying to get the query not to show up in mysql.slow_log (not using an index)
<1> you would have to add a where statement then
<2> xlx: disable printing queries that don't use indexes;-)
<1> or that
<0> domas, i wanna see the queries that are not using indexes
<0> :)
<0> but i wanna get rid of the ones i've optimized.. i'll add in a where
<2> this one isn't optimized.
<2> because it returns all rows.
<2> =)
<0> i want all rows returned heh
<2> hey, then HAVE IT IN SLOW LOG
<3> :)
<2> anyway, you should use explain for all queries
<2> then look at slow log.
<2> for slow queries.
<2> and you should profile all queries anyway
<3> muhahahaha!
<0> domas, we're getting off topic here
<2> I AM THE TOPIC
<4> morning guys, which one is better in performance ? MyISAM or InnoDB ?
<2> oh wait :)
<2> slamdunk: depends. :)
<4> domas: on what ?
<0> slamdunk, heavy writes = innodb
<2> on what you do with it.
<2> myisam is faster for scans and appends
<2> innodb is faster for concurrency
<2> at all other places they're quite equal ;-)
<2> in environment where you have just 16MB ram myisam is better for performance
<2> in environment where you have 16GB, I'd vote for innodb ;-)
<2> ah, and GIS/Fulltext searches are faster on myisam
<4> basically, it(the db that im working at) will update the data very frequently, like stock in/out
<2> this is called OLTP
<2> this definitely calls for InnoDB
<0> slamdunk, it's all about locking.. myisam is table level locking. So if you run an update that takes forever that doesn't append, the whole table is blocked. Whereas, innodb is row level locking.. meaning it only holds on to the rows it's modifying and the selects aren't blocked, they see the old data in real time.
<2> it's all about transactions. it's all about durability
<2> it's all about isolation
<2> it's all about..
<0> he's asking about performance
<2> he's talking about stock in/out.
<2> if performance only would matter, HEAP IS OUT THERE!
<2> or even... BLACKHOLE
<0> <4> morning guys, which one is better in performance ? MyISAM or InnoDB ?
<4> xlx , domas : thanks for the info.
<5> domas: which JOIN should i use if i want to find the entries which are not shared by two tables?
<2> loiic: outer.
<5> domas: thanks :)
<2> archivist: \o/
<2> I'd continue working on my engine!
<6> not so good for stock out
<2> it silently cries in cloned branch
<2> archivist: RAND() would come to ***istance!
<7> is it correct that a join between two tables with indices on both join attributes has linear time complexity?
<7> i.e. running time proportional to the number of rows in the result table
<2> nope.
<2> should be log(n) complexity
<5> can't make it to work :/
<2> even if there're no indexes, mysql optimizes that with join buffer
<2> um, it is linear then. not exponential though :)
<7> linear without indexes?
<5> domas: Let's say i have two one-column tables, table A with the entries (1, 2, 3) and table B with the entries (2, 3, 4, 5, 6) i would like the result of my join to be (1), is a tableA LEFT OUTER JOIN ON tableB suitable for that?
<2> yes.
<2> WHERE tableB.id IS NULL
<2> and then you get 1
<2> :)
<2> left join by itself is outer one ;-)
<2> multi_io: yes :)
<5> i dont get the last part
<5> why tableB.id IS NULL ?
<2> because that would select only rows in A which do not exist in B :)
<7> I thought it would do a "merge" join in the presence of indexes, which would give linear performance imho
<5> worth a try
<5> doesnt work domas :(
<2> !tell us about doesn't work
<8> domas asked me to tell you this: Look buddy, doesn't work is a strong statement. Does it sit on the couch all day? Does it want more money? Is it on IRC all the time? Please be specific! Give us the FULL EXACT error message. Tell us what it does and/or does not do.
<2> multi_io: there's no need for merge join if you have two indexed tables joined.
<2> multi_io: you just run a nested loop.
<7> maybe we'Rre using differing terminology here
<5> SELECT t.name FROM tmp t LEFT OUTER JOIN ON club c WHERE c.name IS NULL
<2> now if you do not have indexes, mysql would use join buffer to read bunch of rows from one table, then build a small index, and scan another table with that index
<5> can you detect anything wrong?
<2> loiic: where's your join condition?
<7> two nested loops seem have quadratic time complexity, or not?
<2> you're doing cartesian product here
<2> multi_io: single nested loop
<2> for every row in table A, check for entry on table B
<7> that would be n*log(n)
<2> kind of.
<7> (iterate over all in A takes n, finding corresponding entries in B takes log(n))
<2> so usual optimization is to put smaller table on the left side
<2> in theory - yes
<5> i dont really what is wrong domas
<2> (if only algorithm complexity is taken into account)
<5> that is 1 column tables
<2> loiic: LEFT OUTER JOIN club c USING (column)
<5> actually it is 1 column tables
<2> loiic: whatever.
<2> lok.
<2> look
<2> http://p.defau.lt/?VIwIoXeoFiF94qXBSYWkXA
<2> took me several seconds to prepare this for you :)
<5> i do appreciate :)
<9> what does AS do in a MySQL query?
<6> !man alias
<8> (Problems with Column Aliases) : http://dev.mysql.com/doc/mysql/en/Problems_with_alias.html
<2> =))
<2> he didn't want problems! he wanted description!!!!!!11
<6> i know dumb bot
<2> ;-D
<2> there was a very nice bot in the works
<5> domas: thank you so so much
<6> espionage see http://dev.mysql.com/doc/refman/5.0/en/select.html
<6> domas why "was"
<2> archivist: it is not in works probably now
<2> somewhere else :)
<6> Ive had some fun getting a bot running
<6> I read the php doc xml to feed it
<2> the new one would be using lucene-indexed docs..
<2> and java instead of twisted
<6> lucene borks at some of the reserved words
<6> try any search engine for the in() syntax
<6> or for that matter AS
<2> eh, I try to avoid stopwords :)
<2> there're various side effects related to stopwords
<6> its size as well!
<6> i have no restrictions for my site search
<2> did you try plugin parsers? :)
<6> no not yet
<10> hey, im trying to run a query that "select * from table1" WHERE table2.user_id isnt table1.id
<10> was wondering roughly what i'd need to do that, not sure what to search for in google :]
<5> domas, to perform the join we have been talking about, comparing two lists of 7000 entries, how long do you think it would take with an average computer?
<2> if indexed - miliseconds
<5> if indexed?
<5> because actually it takes ages
<6> quicker than you can type the query
<5> more than 10 minutes and still not finished
<2> if right column is indexed.
<2> shouldn't take that long
<2> miliseconds...
<2> I can try with 200k rows
<5> actually my lists a row of strings
<5> maybe it make it more difficult to compare?
Return to
#mysql
or
Go to some related
logs:
#css
mpg123 pipe aplay
gentoo uninstall screensaver
ati=rage=pro acceleration xorg etch
#ubuntu
alsa VT82
#perl
fedora openswan hostkey.secrets
tigredesiberie
#linuxhelp
|
|