#mysql
10 October 2007
Total 18 pages. You are browsing page 3/18.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
05:35 <****> so i can get a ratio, in query
05:36 <****> fireangel: subquery is going to be the only way
05:36 <****> ok
05:36 <****> will do
05:36 <****> thanks for the tip :)
05:37 <****> now my SELECT LAST_INSERT_ID() keeps giving the same ID after a while.
05:37 <****> what's the matter with this function??
05:38 <****> why are you using it?
05:38 <****> to reference the ID to my search engine.
05:48 <****> SELECT * FROM `xbt_files_users` LEFT JOIN torrents ON fid = id WHERE name IS NULL
05:48 <****> how to delete all those rows
05:48 <****> where name is null
05:49 <****> DELETE FROM ?
05:49 <****> DELETE FROM `xbt_files_users` LEFT JOIN torrents ON fid = id WHERE name IS NULL
05:49 <****> syntax error
05:49 <****> you forgot ;
05:49 <****> i didn't
05:50 <****> or paste the exact error msg
05:50 <****> #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN torrents ON fid = id WHERE name is null' at line 1
05:50 <****> hm it may like IN () better
05:51 <****> which tracker engine is that btw? :)
05:52 <****> tbdev + xbt + mystuff
05:52 <****> damn nice
05:52 <****> you can't left join a delete
05:53 <****> chadmaynard not even with iN ()?
05:53 <****> chadmaynard: how to do that right?
05:53 <****> well it's no longer a left join if you rewrite as a subquery so my comment wouldn't apply
05:54 <****> you can use comma joins all day
06:09 <****> hi, can anyone tell me how do i optimize IN () ?
06:09 <****> the query goes something like this :
06:09 <****> select ids from tablename where text='hello' and ids IN (select ids from anothertable where x=10 and y=7 and z like 'somthing_(%')
06:09 <****> indexing x, y and z did help but still the query is awfully slow when the selection in IN() reaches records in 200k+ records :(
06:10 <****> and alternatively .. joining the tables causes the response to come in count of minutes! :(
06:11 <****> isaidnosmoking: you have *200K+* IDs in a single IN() clause?
06:11 <****> depends on x, y and z.. sometimes yes
06:11 <****> that is a lot
06:11 <****> probably more than is reasonable
06:12 <****> any work around to speed this up?
06:12 <****> this is causing a lot of damage to my app
06:17 <****> isaidnosmoking: you should brea it out so that it doesn't have so many items in the list!
06:17 <****> the query by itself must be absolutely enormous
06:17 <****> seekwill: I did a decent maze, disappointing score :/
06:17 <****> seekwill: http://www.handdrawngames.com/DesktopTD/ConfirmScore.asp?id=3407404&md5=dcd69d64e4aff6463ce1e9edbe74edb4
06:19 <****> thumbs: lol
06:19 <****> I am trying different strategies...
06:19 <****> That's cool
06:19 <****> breaking it any further would end up in multiple IN ()
06:19 <****> litheum: i think perhaps there has been a misunderstanding. isaidnosmoking is saying that the SELECT used in his IN can return 200+k records, not that there are 200+k literal items physically included in the IN clause
06:19 <****> seekwill: what do you think of it?
06:20 <****> litheum: is that right?
06:20 <****> litheum: i asked him if he has 200K IDs in the IN() list, and he said "yes", so i am assuming that this is what is actually happening
06:20 <****> if not, then there has been quite a communication breakdown :)
06:22 <****> litheum: what i meant is it really depends on the fields in the where clause (which are taken programmatically from the user)
06:22 <****> isaidnosmoking: try an inner join, something like: select ids from tablename t INNER JOIN anothertable a ON t.ids = a.ids WHERE t.x = 10 AND t.y = 7 AND t.z LIKE 'somthing_(%'
06:22 <****> litheum: the x, y and z in the sample
06:23 <****> thoughtful: will give it a try, thanks
06:32 <****> i want to do an insert and get the id (auto increment) of the inserted row, is it possible with one sql statement ? anyone ?
06:37 <****> get id into sql or some language?
06:40 <****> vjvjvj: in straight up SQL you can issue a query after your insert: select last_insert_id()
06:41 <****> vjvjvj: but if you are using mysql/java/whatever you can usually get it out of the mysql api without issuing another query
06:41 <****> ToeBee: I am using php with mysql, i donot want 2 queries, bcos that will slow the script
06:42 <****> ToeBee: I think the select last_insert_id, should work, thnx
06:42 <****> vjvjvj: http://us2.php.net/manual/en/function.mysql-insert-id.php
06:42 <****> the last_insert_id gives wrong numbers for me after processing many insert
06:43 <****> vjvjvj: you can just call that from php
06:44 <****> ToeBee: cool
06:46 <****> another question, this is php/mysql specific, i want to do join statements in php, is there something special to do, or usual query with ; in between statements ?
06:49 <****> will deleting records from a DB increase InnoDB usage?
06:50 <****> taking up free space that is
06:50 <****> CharlieSu: you can optimise the table after, if you wish.
06:50 <****> If I have an array and I want to perform a SELECT query and access only items where id IN $array, is there a way to order the query by the ids in the array?
06:50 <****> even optimize doesn't reclaim space unless you're using seperate innodb files per table
06:51 <****> at least it doesn't for the main ibdata* files.. not for me at least ;)
06:51 <****> Tapout: good point.
06:51 <****> progolferyo: no.
06:51 <****> thumbs: so it would then? deleting records actually takes up space?
06:51 <****> Can you suggest a better way to do this than to perform a query on each item in the array, or do I have to do that?
06:52 <****> progolferyo: use subqueries.
06:52 <****> what do you mean?
06:53 <****> select (select.... ) as f1, (select ... ) as f2, ...
06:53 <****> ahh, ok
06:53 <****> it's not optimal, but it should work for you.
06:54 <****> is that more efficient than just performing a separate query for each item in the array?
06:54 <****> thumbs: did i understand that correctly?
06:55 <****> CharlieSu: you did.
06:55 <****> thumbs: sorry.. trying to make sure, deleting things does take up space?
06:55 <****> progolferyo: yes, running one query, even with subqueries, is always more efficient than running multiple queries.
06:55 <****> sequentially, that is.
06:56 <****> thumbs: ok thanks a lot for the advice.
06:56 <****> the sql engine is initiated less often
06:56 <****> heh. remarkably on-topic for this channel: http://xkcd.com/327/
06:59 <****> its from some bash.org quote iirc
06:59 <****> ToeBee: lol
07:01 <****> I think the bash.org quote is something along the lines of "sometimes I want to change my legal name to drop table; and see what happens"
07:01 <****> thumbs: so i need to optimize to get that space back?
07:01 <****> CharlieSu: that's what I do for tables.
07:02 <****> thumbs: can i see how much space would be freed if i did optimize?
07:02 <****> I barely know how to do it, someone else might be a better reference.
07:04 <****> thumbs: how do I do a query with subqueries if all i am getting is values from the subqueries? (SELECT (SELECT * FROM TABLE) AS value1, (SELECT * FROM TABLE) as value2)
07:04 <****> what is the proper syntax for that, i get a 'operand should contain 1 column(s) error;
Total 18 pages. You are browsing page 3/18.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last