When EXPLAIN gets very wrong number of estimated lines or chooses wrong index

hrm.
how would that happen?
how/when/why?
what usually updates index statistics?

hey friends

heya.

is there an alternative to 'or die' like 'or error' or something? so the query continues but outputs the error?
hang on, think ive done it

let me know…seriously.
because I was wondering this myself.

im going to use a php or statement i think

why are you trying to change it?

a php host IF statement, so I can say, 'IF SQL UPDATES {} if not {echo error}
O10xz http://pastebin.com/m78167914

hi guys, how do I know what binlog format a replication connection is using?
the master was set to "ROW", but I get a lot of replication errors with temporary tables

in an ordered table consisting of integer entries, is there any way to say "in each entry, do foo if the preceding value is larger than this one"?
so, in the where-part of the update statement, i need to evaluate data from 2 entries (instead of one)

and
or

you could use 2 variables and compare them. One holding the current value and the other one storing the last one

yes
and then, i would use some sort of loop instead of the update - where - statement

how many entries does your table have?
maybe its easier to do that outside mysql

about 50.000

you can use user variable, but you have to order the result first, so WHERE is no good
you can use a join, with update

ok ill look for that

hmm, maybe still can with single table update
set @:=null; update table set col=if(@acheckcol,newvalue,col), checkcol=(@a:=checkcol) order by whatevercol

Is there a way to see how much ram preloading indices would use?

hi all - i have just read about the on duplicate key update - are there any disadvantages to this function?

ok how to check how many connections are using mysql?
on a web server
oi!
heh
elow? mysql is using 30-40% of my server's CPU i wanna know how many open connections to mysql

more likely you have bad slow querires

archivist ok maybe but my server always worked fine until this morning

try show processlist; for a start

when i do SET @a:=0; SELECT IF(@a=0,1,2) prints "2". it should print "1" though, i think. whats wrong?

hello guyz
select phpbb_users.user_id,sum(mt_user_session.score) as s from mt_session,mt_user_session,phpbb_users where mt_session.id=mt_user_session.sessionid and mt_user_session.uid=phpbb_users.user_id and to_days(now())-to_days(mt_session.date)=0 group by mt_user_session.uid order by s desc limit 1

are you sure! its printing 1 here

i bet it s the to_days… stuffs that is slow

mt_session.date is a DATE ?

datetime

with hours, minutes, seconds = 0 ?

with real hours min sec values

yes, sure

it s game, we log the score of each game
and the date the game was made
and we want to select the user that made the max score today

if you use functions on a field no index can be used for this field. That would be the reason i think
if your tables not to big in row size i would recommend to add a date field

ah

any function on the where side stops an index unless it can be evaluated once

i have no idea why this is happening! its is suppossed to print a "1" as it does here on my side

version 14.12, distrib 5.0.38, access via mysql hosting query browser 1.2.5beta

hitescape, make sure of case @a@A

is there a nifty way to convert all tables in a database from latin1 to utf-8?

INSERT INTO newtable (*) (SELECT * FROM oldtable WHERE ctrln='$id')

!man insert select

see http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

guys, does mysqld need to be restarted after a change of the binlog format?

it doesn't work… is there another way to copy an "entire" row to a new row in a different table?

what is the fastest way to select * from a table where the entries are less than 24h
with a datetime

how can I sort my email domain?

ace, 'where dtime = date_sub(now(), interval 24 hour)'

is it really the fastest? isn't it better using unixtime?

ace see first example http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
yes because its evaluated once

okay
i ll try

try to extract the domaina part and order by it
domain*

and if you have the correct indexes in place for the query

Just split at '@' and sort with the result?

from the top of my head it should be something like substring_index(email,'@',-1) as fld … and then order by fld
but, I cannot test it right now

if I wanted to update every column in my table, would I do WHERE `blg_article_art`.`id_art` ='' ??

leave out there where
the where

thanks
this one always gets me, and because it's so simple google doesn't know what's going on, what's the command to work a DB? I thought it was set DBNAME;

you have not read the tutorial yet
use dbname;

that's it HEHE
i try to avoid it

Ive noticed

lol

sorry for asking an annoying question, but can someone easily spot a syntax error in this? i keep going over it but cannot see the problem: http://dpaste.com/17305/

CREATE TABLE foo(id INT) — won't work inside a procedure, why
i know why that if statement didnt work: mysql dropped the variable just after i set it, must use procedures

??? why would it do such a thing
did you use delimiters?

Change the DELIMITER first and then look at the syntax error you have with TIMEDIFF function

hello

i changed the delimiter to | before running it
but i do see what is going on with the timediff, thanks

By the way TIME is reserved word so you better avoid using it as column name

thanks again.

which interface do you use for mysql I/O ?

!perror 1030
!perror 134

Record was already deleted (or record file crashed)

normal mysql client

can anyone suggest a script that would do a mysqldump, name it based on the date, and send it to another server?

easiest would be write your own script

$x && scp $x host

or something

Do the dump from the remote server host so you don't need to "send" it

thanks, is there a simply copy to smb share command?

smbclient

!man old client

see http://dev.mysql.com/doc/refman/5.0/en/old-client.html

hi there, im trying to give a user privilages to create databases but i do not want them to be able to see other database tables on the mysqld. Is this possible?

ON dbname.*