| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8
Comments:
<0> voltagex: he wins, whether he's right or not. I'm shuttting up on this topic.
<1> Sneaky_Bastard: good idea
<2> what cable isp do you know that has multiple users on the same ethernet segment?
<3> all i know is i got some good reading references tonite :)
<4> hi all
<4> can anyone tell me how to optimize query like this: select * from table1 where field1 like 'crit1%' or field1 like 'crit2%' .... ?
<4> i want to optimize it so it uses index to retrieve rows. if i put only " field1 like 'crit1%'" it all works well, as index on field1 gets used
<4> but adding more criteria on same field makes troubles
<5> is there query to do subtracion of the totals of two columns in one table?
<4> join table on itself and sustract sum() output from two columns of different table instances
<4> or you'd like it by example? :)
<5> an example would be nice :) as i am trying to put what you said into perspective
<5> i dont know how to join a table
<5> would that be all in one query?
<4> yes
<4> SELECT SUM(t1.col1) - SUM(t2.col2) AS 'diff' FROM `table` t1, `table` t2
<4> `table` is your table name, col1 is column you want to substract from, and col2 is one you want to substract with
<5> ah i figured the first part, waht is AS 'diff'
<5> oh
<5> i see
<5> is the AS 'diff' nessicary?
<4> no, but you would get "SUM(.... )" as column name
<5> i think that is what is happeneing
<4> if that fits your needs, then no, it's not
<5> cool
<5> ill give it a shot
<4> it works for me, simple and easy
<4> but
<4> i forgot to mention 2 things
<2> why would you do it that way?
<4> that's what i forgot :)
<2> why not just select sum(col1)-sum(col2) from table?
<4> exactly.
<5> heh
<5> nice
<2> in fact if you do it your way you get an invalid result
<4> that's the second thing i forgot
<4> you need "where t1.id = t2.id"
<5> i guess that verifies to me that , you can use some math in querys
<2> you can use lots of math
<4> of course.
<5> wow ... :}
<5> nice
<4> gleam-: you have any clue about indexing stuff?
<2> a bit
<2> what's up?
<4> scroll up
<5> thanks guys
<4> right before l00pdee joined
<4> "can anyone tell me how to optimize query like this: select * from table1 where field1 like 'crit1%' or field1 like 'crit2%' .... ?"
<4> i want to optimize it so it uses index to retrieve rows. if i put only " field1 like 'crit1%'" it all works well, as index on field1 gets used
<2> paste query that doesn't use index and explain output to a pastebin
<4> sec
<2> if you do 'foo%' it will use an idnex, but '%foo' or '%foo%' wont
<4> i figured that out
<4> actually, '%anything' wont use index, as it needs fixed letter at the beginning
<2> i think i said that
<4> oh, i thought " '%foo' or '%foo%' " was said as if was part of one query
<4> :)
<2> no :)
<4> http://hashmysql.org/paste/viewentry.php?id=1833
<4> here
<3> it is expecially an OR condition will cause a table scan
<3> if if you did type like 'monster% or type like 'beast%' a table scan will happen
<4> o_O
<4> let's try it otu
<4> out*
<4> no, it didnt happen
<2> how many rows match that query?
<4> is there any other way i could do it
<4> well, not much, as whole table has 18 rows, but i'm puzzled about how to do it on table with more than 5000 rows
<2> yes but what %?
<4> how you mean?
<2> what percentage of rows are returned?
<2> 50%?
<2> 10?
<4> sorta
<4> about half of rows
<2> okay
<2> the optimizer won't use an index if it thinks it will save time by going directly to the data
<3> as lng as the there is an OR condition in that case, a table scan is going to happen
<2> if, say, it's going to return half the rows it probably will just go to the datafile
<2> that way it doesn't have to do two seeks
<4> oh
<2> try inserting 3000 rows that don't match that where condition
<2> and see if it uses an index
<4> so it outsmarts my optimization attempts :)
<4> okay
<4> let's try
<4> true, it works on larger data set
<4> where only few % gets returned
<2> and zircu you're wrong
<2> but it's ok :P
<2> anyway yeah dusko that's the problem
<3> it is a good thing your around... i could have swore OR's cause table scans
<2> really with 18 rows, what's the point?
<4> anyway, i got my answer, thanks a lot :D
<2> for a table that small there probably isn't too much of a point
<4> honestly, no point with 18 rows, i could pick them out by hand :)
<2> yeah
<4> but i was wondering if same thing would happen on large data set, then what? :)
<2> yep :)
<2> good to know you're fine
<4> luckily, you were around to prove me wrong.
<3> so you added 3000 rows that dont matche either?
<3> and it resulted with less rows?
<4> no, i used another few-k rows table
<4> ~8000 rows, query resulted with ~150 rows
<4> while using indexes
<3> with using the Or condition, right?
<4> yeah
<4> where name like 'b%' or name like 'e%'
<3> sorry i'm just trying to figure out the threshold of the index stuff with the or, it might come in handy for me
<6> I'm a real noob, is there a better way to insert 50 000 records other than 50 000 insert statements?
<7> Question: Does MySQL have some capacity to do database quota per user?
<7> Or would I need an external system to manage that?
<2> clarify what you mean by database quota
<8> database size (fs or records)
<7> I create a user and give that user full rights to a database of his/her own. I wish to ensure they do not go over, for example, 100MB of usage (seems laughable, appropriate for the environment though).
<2> yeah, you'll need to manage that separately
<4> but how to distinct between users, when mysql doesnt share credentials with outside world?
<4> (interests me also ;) )
<2> get a list of databases the user has privileges to, or just prefix the database name with their username, or whatever.
<4> i bet google will give you an easy answer, that is somewhat of a frequent problem
<4> (or not-so-frequent, if you like it better)
<2> there are programs to manage it
<2> lots of isps have the same problem
<4> my point also ;)
<7> Yeah, just found what looks like a good one. Simple, straight forward PHP script.
<7> http://www.howtoforge.com/forums/showthread.php?t=1944
<4> (ew.)
<7> ew?
<2> eh that'll work but that's kind of a silly way to do it
<2> you should just charge them more if they go over
<4> that's what "ew" is for.
<9> overage :)
<2> also that doesn't even take care of update_priv or alter_priv
<2> both of which could considerably increase the size on disk
<7> Hrm. Unfortunately, this would be a situation where it would be impossible to enforce payment for overage.
<2> rough
<2> i'd just have it notify you if they go over 100 so you can talk to them about it
<4> how does subselect optimization work?
<2> run explain on it and find out dusko :)
<4> yeah :P
<7> No kidding. :) But I'm glad you mentioned the update thing. If I also took away those, then they'd be pretty much stuck until other arrangements were made.
<2> yeah, that would probably handle it
<10> hey guys... i have the following sql ... http://pastebin.com/714920 ... which produces the following error ... >> Unknown column 'DISTANCE' in 'where clause' << , i thought about putting the calculation instead of the distance in the where place but then how do i do the order by? any help is appreciated :)
<9> did you put back quotes around that column name?
Return to
#mysql
or
Go to some related
logs:
#css
#perl
pivot_root: No such file or directory /sbin/init: 432
#osdev
+preseed +partman +primary partitions
configure: error: C++ preprocessor /lib/cpp fails sanity check + fedora core 5
point eip to nop sled
no cdrom + no floppy + install linux
ubuntu php_pdo sqlite3
portage_tmpdir on ramdisk
|
|