Can anyone help with this I have a server running Invision Power Board using mysql The forums keep dying with

CheapWebHosting.co.nz

Ok Im accessing the backend of an ecommerce solution - I have about 500 tables its not a very good solution I

Can anyone help with this? I have a server hosting running Invision Power Board using mysql. The forums keep dying (with no reponse when you try to connect). Restarting mysqld fixes it for a couple of minutes, but it eventually climbs to 25% CPU with heavy memory usage and then the forums die again. What the heck is causing this?

I think not, as the table would be locked

hmm
cause im getting a really strange problem (i think its the api hosting im using, but i cant seem to get an answer about that)

Say MyISAM uses table locking http://www.hashmysql.org/index.php?title=Storage_Engines

essentially, seemingly randomly, not all the columns get returned to me

not all the COLUMNS?
what is you query?

a really simple "SELECT * FROM Playlist LIMIT 1;"
where the Playlist table has two columns

[OmegentooX]: Have you modded the IPB? If not, contact them. Is mysqld the high cpu user, or is php?

mysqld

how many columns are you getting?

most of the time two
but every once in a while i only get one back

are you sure the other column isn't null or something?

yes
definately isnt

you just want the first entry of Playlist?

yeah

hey guys, how do i change the time in mysql?

isn't it the time of the server?

i dont quite get your question

SELECT CURDATE();

+------------+
| CURDATE() |
+------------+
| 2007-06-11 |
+------------+
this is what i get
whereas the date is 2007-06-12

[kiki]

is python's cms mysql hosting bindings known to be unstable or anything?
is there an easy way to delete everything from all tables in a database (but keep the tables there)

run delete from table on every table

i dont really understand what the difference between myISAM and InnoDB
what does full text searching and transaction safe mean?
that is the difference according to the sites i have been reading, but i am not sure what that means exactly

whats the difference between a primary key and a unique key (and a plain old key)?

i'd like to know that too actually

no shares

!man transactions

see http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html
see a href="http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html"http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html/a

indeed it appears to be

!man key

see http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

!man unique

see http://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html

1 table may have multiple candidate keys , 1 is chosen as the primary key
unique key is a constraint, not exactly a key, but since it is unique it is almost like a candidate key
with exception of the null values

the reason i ask is i'm graphing our database setup in graphviz and want to know if its worth it to make them different colors
graphviz is awesome btw, but if anyone else has a recommendation on a tool for doing that, lemme know.

you should differentiate them, pk, unique, index

ok
thanks

could someone kindly tell me the best hosting way to count the number of duplicates and then insert the number in a field called count on each row, corresponding, of course, to that entry

then do your inserts for each id

that's one way

awesome! thank you very much

what exactly is a BLOB?

I have a table Line which has a non-NULL FK to another table Identity with a corresponding constraint
SELECT COUNT(*) FROM Line; gives 113520 which is correct

!m UltraVi01 blob

UltraVi01 see http://dev.mysql.com/doc/refman/5.0/en/blob.html

SELECT COUNT(*) FROM Line NATURAL JOIN Identity; gives only 325 however (there are 416 entries in identity)

natural join? how many col the same name?

mm I thought it worked if there was a constraint there
that's probably it
whoops

i am really having trouble understanding the differences between indexes, keys, and primary keys, could someone point me to a good tutorial or explanation somewhere, i am having trouble finding a clear article covering this...

it use all columns having the same name
pk 1 per table, no null, no duplicates

Hmm, if I have a table where I need to have an index (numeric, format non-negotiable), and a few text-fields...
few being 5 total

ok, do i need to declare it not null or is that assumed if it is primary key?

unique no duplicates, allow null, can have multiple per table
_Quintus_ index allows duplicates

UPDATE LOGS a INNER JOIN logs_cnt b USING ( term ) SET a.count = b.count_term
could someone kindly tell me what the problem here is?

pk has to declare not null

1 can't be longer than 10 chars, two no longer than about 50 chars, one ~150 chars, and the last no more than 300 chars

kimseong, do you mean that a unique *can't* have multiple per table? unique sounds like it would not be able to...

would I be best for performance reasons to do varchar or a basic non-var string and space-pad-it?

unique can have multiple per table

kimseong, multiple per table but not multiple per column?

why want the same index multipl times?

kimseong, i dont , i just want to understand the details

depends

kimseong, are any of those best to use in terms of speed, or are they all roughly equal in speed but just have different requirements?

of course different
use what you need depending to the requirement of your data

kimseong, pk is fastest i assume?

_Quintus_ usually

kimseong, ok, but they are all faster then columns i would never have to lookup based on

_Quintus_ you index columns need for lookup
or constraint with unique and/or pk

kimseong, ok, so if i have a table with two columns, they both pair users, (eg user1, user2) and there can be repeats in each column, then i would use index?
kimseong, does index have any contraints, or just optimizes that column at the cost of HD space...?

can u1,u2 appear multiple times?

kimseong, yeah

i mean the combined u1,u2, not u1,u2 and u1,u3

kimseong, maybe u1,u2 has other data too, its not important, but it might represent a relationship of some kind between two users
kimseong, oh no
kimseong, the PAIR would be unique, but not each

_Quintus_ then you can have a pk on (user1,user2) that will prevent multiple

so "primary key (u1, u2)" maybe?
kimseong, ok
kimseong, great thanks
kimseong, one last question, none of this effects how i form the calls to get that data does it? It is just important when the table is created....?

it may speed up the select
slow down insert, update and delete

kimseong, ok great, select is more common so, probably more important in my case
i actually have another separate question
more of a clarification actually, ... i read that searches are not case sensitive... so would "select * from users where name=john;" turn up a row if there is a user 'JOhN'?

alldatabases.sql then run on new server mysql -u root -p alldatabases.sql but no go

any pointers

kimseong, would searching by username like that be case insensitive like I want?

could someone tell me how to rewrite this so it is readable in mysql 3.22
UPDATE logs a INNER JOIN logs_cnt b USING (term)
SET a.count = b.count_term;
any enlightened help would be greatly appreciated

Using php, how can I update a value in a table? I want to change the value of "DownCount" in the table Stats of the database "downstat". Currently I'm creating a row every time, which I do not want to do

cooner750, "update table status set downcount = newvalue"

2 AND g 5

#1064 - You have an error in your sql host syntax near 'a INNER JOIN logs_cnt b USING (term)
ON a.count = b.count_term' at line 1
any help?

ultra, paste your entire sql on http://pastebin.ca/

okay
http://pastebin.ca/560658
thanks

I'm using a case statement in a select statement as a column, I used a alias to call it "group" that works fine, but I'd like to count the rows in each group, but when I used COUNT(group) it says that column doesnt exist.
the group by with the alias works fine

Ultra, I don't get it, what are you trying to do with that update, its not updating anything

Ricoshady, doesn't count(*) along with the group by do the job?

plus you can use either ON or USING (not both) - see the examples http://dev.mysql.com/doc/refman/4.1/en/join.html

I'm trying to count all of the duplicates and insert that number in a "count" column next to each term
I meant to put SET instead of the ON
UPDATE logs a INNER JOIN logs_cnt b USING (term)
SET a.count = b.count_term;

hey all

I don't think you can update join, do you have an index on term in the logs table?
update logs a set count=(select count(term) from logs b where a.term=b.term);
without temporary tables and all

how would I go about checking if one of the words in a field is 2 chars in length... i.e "I love to admin a php mysql web hosting database"... it would then see that the word 'to' is 2 chars and then I wanna UPPER() it

http://pastebin.ca/560668

ah
thank you very much!
actually, that doesn't work

why not?

i don't know.. syntax error.. maybe it's because it's ver 3.23

do you have a field named count in the logs table?

yup

is there a function in mysql that will convert "A TITLE STRING" to "A Title String" ?

You have an error in your SQL syntax near 'a set count=(select count_term b from logs_cnt where a.term=b.term)' at line 1

anybody got an idea of how to do this?
quite easy... I'll show you quick...

was just looking at your problem actually

you running mysql 5* or mysql 4* ?

live is running 4.1 but i could move it to 5.0

ok, 2 very different ways... lemme show you in 4*... gimme a sec...

can't see how to solve your problem without significant looping or something

this is strange, when I group by using SELECT column AS "age_group" and group by "age_group" things work fine, but AS "Age Group", Group By "Age Group" doesnt work.

oh maybe a regex
did u try using `` instead of "" ?
`...` vs "..."

mysql_query("UPDATE table `Stats` set `DownCount` = $newdownload"); ?

quickly hacking up my version which upper cases the first word... to suit all words

thanks phpboy

I don't suppose you're using PHP?

cooner750, there's no "table" there. and does Stats have only one row?

i am but not in this import process

lemme get you started with my version for first char in MySQL 4

Stats only has one row, yes

kk thanks phpboy - i can do the heavy lifting then!

CONCAT(UPPER(SUBSTRING(fieldname, 1, 1)), LOWER(SUBSTRING(fieldname FROM 2)))

cooner750, have a look here http://www.w3schools.com/sql/default.asp for general sql basics. and then http://dev.mysql.com/doc/refman/5.0/en/update.html for further details. it might help you

that'll convert "A TITLE STRING" to "A title string"

i see that. great, thanks!

it's a good start
well, if you find a solution... I think yours might help me
in fact lemme help you help me
it's just a case of spilting up the strings
that'll help us both

yeah it would require looping through stuff

well... in a sense yes
but not necessarily

or tons of case statements

hmm o_o

I think I've got the answer
gimme a sex to play with it

find the index of spacecharacter

Think I sort of have this down but
$sql = 'UPDATE `downstat`.`Stats` SET `DownCount` = `$newdownload` WHERE `Stats`.`DownCount` = 5 LIMIT 1;';
Apparently I cannot just insert the variable $newdownload in there.
Or I'm doing it wrong

a 30 million row mysql database, I did a group by order by count(*) desc limit 25; (get most frequent item in a certain field). how long should it take?

you need to interpolate the php value in
$sql = "UPDATE `downstat`.`Stats` SET `DownCount` = `$newdownload` WHERE `Stats`.`DownCount` = 5 LIMIT 1;";
$sql = 'UPDATE `downstat`.`Stats` SET `DownCount` = `' . $newdownload . '` WHERE `Stats`.`DownCount` = 5 LIMIT 1;';

hmm o_o

I'm kinda on a role... I think SUBSTRING_INDEX might be our bitch
what type of server is it on?

how do i change the variable system_time_zone ?

I would just create a row for each download but that would eat space like crazy
if I do it row by row, can I count the total rows somehow?
Using an SQL query ?

wtf group by order by count(*) desc
count(*) is always going to be the same number
no?

UltraVi01, that syntax is apparently not supported in mysql, you need to use the one you tried before

yes
1 number
unless you're doing a union or something like that
but traditionally 1 number

http://pastebin.ca/560714

when you do a union, how does the ordering of the results put together, in the exact order of the queries?

(select ..) union (select ..) order by ..

kimseong, perfect

ops not what you ask

is that what you want? i though you ask what is the order if no order by

you answered my question, i just wanted to know how to order all the unioned results

ok, the ( ) is important

right ok, thanks

the problem is mysql 3.23

this doesn't look all that easy

can I do something like - "select blah blah from ( (select...) union (select...) )"
as long as the columns are correct?
almost like a view?

yes, but need to alias the table
from ( () union () ) as t

is anyone familiar with a way to incorporate a spell check with fullindex search

can add a fulltext plugin

like what?

C program
write yourself

how does one set a DATE field to null, if its value is set to 0000-00-00 currently.. the logical solution doesnt seem to work, previously the field was declared as not null, now it allows null.. but UPDATE table SET datefield=NULL WHERE datefield='0000-00-00' does not work

why use fulltext search with spell check? check first before running the fulltext search
maybe the field does not allow null

I want a feature like google's "did you mean..."

is the spell check? more like fuzzy logic

well perhaps I didn't articulate it correctly
my sincere apologizes.

but google could be something else too, probably based on its own database of words

I'd like to base mine off my own database

assuming I enter "aabbcc" what do you expect it to return?
and you have no "aabbcc" in your data

"no results"

what is the do you mean word you expct to see?

i'd like to use my log table as the dictionary
because it has all of the commonly misspellings that people use

you have dictionary? then it will not be difficult

i'd like to use the terms in my log file as the dictionary

do you have the mispelled words and the actual word data?

that's the problem
I don't know how to relate them

if you don;t how to match a mispelled word?

exactly.

it is not the process, it is the information that you need

well surely there is a way to relate misspellings with words that are similar

that is language and culture specific probably, you expect mysql to do that for you?
maybe take a look at sql server 2005, it has some fuzzy logic feature even though i don;t know exactly what it can do

I think you're looking too much into it
I only want to compare the small amount of data in my database

sure, assuming I enter "aabbcc" and it does not exist, how to suggest what I want to mean

it should only return a suggestion if the term they enter is similar to a term in the database

define similar
if you can, you should have a way to do it

I guess I'm gonna have to do this in PHP :T

Hi
how to ORDER by
in descending order?

order by dsc
i think

desc ok
thx

hey
who's got nothing to do?
anyone got any ideas why connecting to mysql is so slow when it is not on localhost?

Because the internet isn't instant, just nearly instant?

lol but the difference is huge

what, 50 ms as opposed to 1ms?

like 1 sec vs 6 sec
no
im wondering if im doing something wrong

have you tried pinging your mysql server?

same script runs in less than a second
it has imcp turned off

dns server

hmm

try resolving the ip, or ping your dns server
are other sites slow, or just this mysql one?

well see the site is mysql, php, apache
when i run the php code internally its 1 sec
or less
otherwise it takes a long time

internally = ?

locahost

localhost

localhost and 127.0.0.1 are the same, but in MySQL localhost triggers magic and causes the Unix C-Client library to use Unix Domain Sockets connections.

on YOUR pc, or on theirs?

what u mean

are you running the php script on your computer, or is it uploaded to a server and running on their's?

the script is uploaded on a host

Do you remember installing apache web server on your computer? =P
ok

gimmie a sec
let me try running it from my computer

if the mysql server is on the same host, why would you not use localhost?

its not
im running a script on a diff server than the mysql one

I'm sufficiently confused. kimseong you understand this?

http://century21banner.info/test.php this is running localhost

quite fast!

i assume connect from localhost is fast, from remote host isslow

http://temp.century21banner.com/test.php
this is running on a diff webserver

the issue could be the dns, try skip-name-resolve and use ip address in the grant tables

how do i do that
in the my.cfg?
cnf

yes, windows?

no linux
i know how ot get to it

usually cnf

I noticed him the url to use kimseong

still slow
hicker

yes, it is
hiker, but continue

so im in my.conf
cnf
# skip-networking
should i enable it?

i was wondering if anybody here can help with rc configuration of mysql for freebsd. the startupt script seems to fail and i have no idea how to troubleshoot or which log to look in

Are you leasing the server tha page is hosted on?

yes
its shared

then you can't go around messing with their configs I don't think

i seem to have this problem http://www.thescripts.com/forum/thread141742.html

so why do you have two mysql servers, you prefer to use the one offsite?

well the mysql's computer I own
its a server i own

and the apache/php server you just want for the php files, gotcha
well, try pinging the remotemysql server

so if i run the script on the mysql server
it works fast
if i run it anywhere else
its slow as heck
i can't my imcp is turned off
lol

have you tried other hosts?
connecting to the remote mysql server that is
or try the php script from your computer and see if you get the same results

you know /etc/hosts file

kinda

add the machine you are connecting from into the hosts file
with its IP address
hopefully this solve it

k let me try

hey guys... is it possible to do a if statement (not if function) in MySQL 4.1
?

how should i add it

!man where

see http://dev.mysql.com/doc/refman/5.0/en/extended-show.html

just the ip?
74.220.204.26

you're terrible the_wench...

covrigel you need the ip and the hostname
it is a mapping table

what are you trying to accomplish?

kimseong this server doesn't have DNS
i didnt set it up

it probably get the dns from elsewhere, thats why slow

perhaps
how can i fix that
i just want to run this server as a simple IP

either turn off name resove in mysqld or try to add an entry into /etc/hosts

no domains necessary when i connect to the sql i connect with the IP

then try add skip-name-resolve to my.cnf under [mysqld]

kimseong gimmie a sec

if search then resulting query
instead of if(condition, true result, false result)

k so i turned on skip-name-resolve

do that in you php or whatever language/scripting

now when i try to connect

restart mysql server

i did
now it gives me lost connection
mysql_connect() [function.mysql-connect]: Lost connection to MySQL server during query

try mysql cli

cli?

mysql
mysql client

if you use hostname in the permission table, then it may fail

i dont have mysql clinet
i just use php to connect
but kimseong i didnt add it in the ip table
should i add the server?

connect from localhost and check the mysql.user table

UPDATE `table` SET `val` = CASE `key` WHEN 'foo' THEN 'option1' WHEN 'bar' THEN 'option2' ELSE `val` END

can i use phpmyadmin?

!man if statement

see http://dev.mysql.com/doc/refman/5.0/en/if-statement.html

yes

k im in
what am i lookin at users?

select user,host from mysql.user
check the host column, do you see hostname of ip address of your remote client

no
its set to %

then it is fine

should i add the ip in the ip tables?
i mean

no need since % is more generic

lol /etc/host
in there i meant

no need since you use skip-name-resolve

k then how come i can't connect?

no idea

i get Lost connection to MySQL server during query
http://temp.century21banner.com/test.php
im using the ip address of the mysql server
any ideas

comment the skip-name-resolve and try again after restart of mysqld

maybe undo what you did in /etc/hosts if you made any change

meh, i suck at sql... i have 2 tables i want to join via an id col, one has dupe rows but i only want to join on the first. what's the, uh, 'portable' way to do this? (translation, i'm actually using *cough*mssql*cough*, _blush_)

works now again
i didnt do anything in /etc/hosts

the skip-name-resolve ?

yeah but its still slow

weird, try add ip (of the remote client) into the mysql.user table and turn on skip-name-resolve again

instead of %

yes

k sec
same problem
kimseong it wont even allow me to open a mysql connection locally
nevermind

what?

due to a hardware failure of our backup server, I managed to save a database only in "file form" (/var/lib/mysql/dbname). I succesfully managed to phisical copy the files into a virtual machine with the same configuration of the old broken machine. However, if I try to make a dump of that database, the blob photos inside it don't works (I get a table with 9 photos instead of 602). How should I resolve the problem? I w
I discard to say that the blob photos are well readable with our application, so the data is inside the files. The problem is in the dump
Now I want to dump all and put it into the new server, but I'm only having nearly empty tables. Is there a way to make an integrity check...?

I need it in if form... IF 1 + 1 = 2 THEN 'YEAH' ELSE 'MySQL Can't count :'
that doesn't seem to be working though :T
I suppose I'll have to use CASE :T

SELECT IF (1+1=2, 'yes', 'no')

hi! When I do an insert via a stored procedure and the insert succeeds, will i be able to query "SELECT LAST_INSERT_ID()" and get the insert-id for the row the procedure inserted?

anyone got any ideas how i can fix this slowness?

what kind of slowness?

mysql is eating 185% of my cpu all the time. i've never had a problem like this. is there somewhere obvious to start?

SHOW PROCESSLIST;

MalibuStacy when i run mysql query from a different server than localhost

thx

it takes 100x more to display

i guess it's the connection and not mysql
if you access them from the same local network there should be no problem

yeah

maybe connection-pooling can help a bit

look at the difference
fast - http://century21banner.info/test.php
slow - http://temp.century21banner.com/test.php

i get 10k records over the world wide web in about 7 seconds

MalibuStacy this searches 500,000 records, but only returns 20
there's no reason for this to be this slow

i didn't recognize any significant difference

really? i have

nah

quick question.

about the same speed
maybe 1 second difference

MalibuStacy the info is instant

how would i go about using a third int in a sql table?

the second one is a bit slower
but not more than a second

mine takes 5 seconds

well, prolly your connection

more like 2.5 or so
lol

how would i go about using a third int in a sql table?

i could capture it on video. no significant difference at all
i wouldn't mind it

hmm thats weird

select IF 1 + 1 = 2 THEN 'YEAH' ELSE 'MySQL cant count :' END IF; --- in theory this should work, right?

because http://century21banner.info/test.php runs really fast for me
its instant

I think if then else like you have are for functions only
I already mentioned how to do it with plain select statements, read up ^^

SELECT IF (1+1=2, 'yes', 'no')

got shortcut don;t want to user
*user
*use

select IF 1 + 1 = 2 THEN 'YEAH' ELSE 1 + 3 = 2 THEN 'oh hi' END IF; --- in theory this should work, right?

no it don't

kimseong do u have any other ideas

no, usually it is the connect time due to dns lookup

I need a final result with many contions :/

you have CASE

kimseong there is no dns lookup, i connect to the IP

yes, but mysqld does a reverse dns lookup on the dns

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

ag ok.... just wantewd to confirm that case was the best for this

kimseong skip-name-resolve
i turned it on

unless it is disabe with skip-name-resolve

i did
it works now

ok
so still slow?

yes

as bad as previously?

http://temp.century21banner.com/test.php
yes
maybe a bit faster
but still very slow

look normal to me

i want it instant
not 2.5 seconds

it is a web page, what does 1 and 2.5 sec difference to a human

select CASE WHEN 1 + 1 = 2 THEN 'YEAH' ELSE 'MySQL cant count' END CASE; --- this should work in theory?

end only no end case

repeating yourself doesn't make things work

it's a different example :T
thanks kimseong

it takes me 1.61s to load the slow page and 15 ms to load the fast one
thats a huge difference

servers on the same subnet?

no
working on WAN

what is the latency between the serves?
and where is your browser?

what u mean
pinging from my mysql server to my php server takes 40 ms

you need to send the req from browser to web server, then to mysqlserver and then back all the way to the browser, if your broswer is on the same subnet of the web and mysql server, then of course much faster

its not on the same submet
subnet
i dont own the php server

your browser on the same net of the fast web/mysql server

nope
but i dont think that should cause this much of a dela
delay

try ping the 2 web server from your browser
maybe the web server is slower

let me turn on icmp

hi lads

i can't seem to be able to ping it

what's the purpose of the flush privileges command?

!man flush privileges

Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/flush privileges

!man flush

see http://dev.mysql.com/doc/refman/5.0/en/flush.html

thanks, that was all i needed

alrighty
so it shouldn't do no harm, just to refresh the rights

thank goodness
gives me more time to not buy servers

hah
0 rows affected
?

i'm way happier at 26% than 185%

Where are the user inffo of mysql stored?
I transfered yesterday some clients from my old server but their mysql user info doesn't work
it's a bit different because I'm using vhcs

how do you check if a string is_numeric?

concat(str+0)=str

ah, so there's no is_numric funtion in mysql?

there is regexp

I want to filter out a series of rows that have certain data. I can get the first condition to work but none of the others. WHERE a.client_name NOT LIKE 'Contract Account' OR a.client_name NOT LIKE '%CUSTOMER GONE%' OR a.client_name != '%ACCOUNT CLOSED%'
Any help appreciated

what is the problem?
do you get an error?

No it is not filtering the results out that contain the second and third conditions.

well .. you are using OR .. so it only needs to match one criteria to pass
maybe you should use AND?

Hi there, I did try AND as well and that didnt work
There are loads of asterisks in these fields before and after, would they have an effect
It is having no effect at all with AND or OR after the first condition
******CUSTOMER GONE***********

i got a sql backup file and just want to import one table...how to do that?
are there facilities that can do that?
kinda urgent ^
the backup file is a few gigs in size

you want all those 3 condition not in the result?

Yes, and there could be more

easier to do NOT (cond1 or cond2 or cond3)

actually - any of those 3 conditions
sorry

easier to do NOT (col like ' ' or col like ' ' or col =' ')

I've a few practical questions, 1) What would be more efficient for a way of storing properties of e.g. furnature. every property as field or a table with e.g. 3 columns and put it as row with value and an object id (= id of e.g. furnature)

Thanks I havent tried that, I will have a go

easier to see the logic this way

Do I substitute the WHERE for NOT or is it WHERE NOT

where not ( )

Cheers

1) additional note, that I don't expect properties to change often (obviously)

how would I go about using a 'var' I created in MySQL for display? i.e. select 'oh hi' as the_var, the_var; effectively that would reflect the same thing twice

the alias appear as the header of the col

so I can't use a 'string' I create with mysql for a condition?

the_var only usable in the group by , having , order by

I did EXPLAIN SELECT COUNT(id) from table and got "Select tables optimized away" what does optimized away means ?

what you have in mind? show it

ah, so I need to apply the condition I need everywhere?

the alias is a literal constant
very fast
what is the type?

i.e: CASE WHEN pdf.pdfaddressedto like '%@vax.co.za' THEN SUBSTRING_INDEX(concat('0', substr(pdf.pdfaddressedto, 3, 9)),'@',+1) WHEN concat(pdf.pdfaddressedto+0) != 0 THEN pdf.pdfaddressedto END as the_numba;
ok, now that creates the var... in this case 'the_numba' Then

kimseong, bigint

the result will store in the_numba
the explain has a type column

CASE WHEN address_book_mobilen = the_numba or address_book_workn = the_numba or address_book_faxn = the_numba THEN 'oh hi' END;

kimseong, SIMPLE

eeddadssad
askfjhd
ASD

simple?
not this col
don;t think it work that way

kimseong, lemme put the result in hashmysql

the alias usable only in Group by, Having and Order by, not again in the select

what is the easiest way to check if a row exists in a table, though I don't actually want the row

hey; i have a innodb table with a varchar(200) which i do "select where col='mymatch' " on (quite often); im kinda scared to create a index since each row in the index will allocate 200 bytes. i can't guarantee that each row will be unique either - so what should i do?

http://pastebin.ca/561002

That worked perfectly, thanks a lot.

hello everyone.
I got a problem, when i try to dump a data base. mysql tries to dump a table, which doesn't exist or at least I don't see the table.... mysqldump: Got error: 1146: Table 'foo.bar' doesn't exist when using LOCK TABLES
Anyone has an idea how to solve this? Creating a table with the same name doesn't work since mysql complains that the table already exists

I want to redo a machine which has about 570GB of mysql data in innodb tables
Dumping this out with mysqldump and restoring it later is not an option
Copying the files for the stopped service would be much quicker.
What exactly do I have to copy
a. full data directory, b. /etc/my.cnf
what else?

I got a problem using fairly simple joins.. can someone please help? Take a look at http://phpfi.com/241129

Fanty, put the conditions in a where
not the ON
in ON, you simply put the JOIN "rules"
in WHERE, you put the filters

LEFT JOIN table WHERE conditions?

SELECT pages.pid, pages.uid, tx_mochtracking_pagestatus.completed FROM pages LEFT JOIN tx_mochtracking_pagestatus ON tx_mochtracking_pagestatus.page_id = pages.uid WHERE pages.uid = '14' AND tx_mochtracking_pagestatus.user_id = '1'

ohh.. WHERE comes *AFTER* the LEFT JOIN.. I've been trying to put it in front of the LEFT JOIN
thanks fifo

does anyone have an opinion about my previously asked question? (columns or rows for 'properties')

I don't know what the question was, but the answer is "rows"
Scales much better

scales?

What's Signal 11 and why is MySQL getting it and terminating it when I start the service? (MySQL 5.0, Windows Service)

My initial question was about an item with properties. e.g.: furnature with a series of properties like, color, fabric, etc

If all the innodb files are under datadir nothing else

TerrorB1te, it Segmentation fault

then I wonder what would be more efficient, use fields as different properties, or rows per product

salle, i read on the Mysql web site that you need the configuration file, and I would think so especially since my innodb file sizes are custom

Keep it in mind you have to put them in exactly same directories on the new machine

fifo_, segfault on Windows?

Dynom, how about a composite type?

If tomorrow you need to add one more property it is trivial to do with normalized table

TerrorBite, yes. maybe it crashes and it's error messages is simply "signal 11". I am not sure. but that would be my interpretation
Dynom, salle has a very good point

but I won't be adding/changing much once determained

Dynom, i don't think composite types support that

Also it saves space when not each items has values for all properties

Dynom, not much or not at all... not ever... guarenteed

It leaves a message in the system Application log, "DProgram Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Got signal 11. Aborting!"

but with large amounts of data you require joins
which can be slower

not much, it could be that a property is added along the lines ('never say never')

"once determined" in practice never ends

Dynom, then go with salle.

I'm eager about speed, I want that above all, speed and efficiency

Followed by two more, the first says "Aborting" and the next "Shutdown Complete"

one more point then

TerrorBite, check what google says about signal 11 on windows. add mysql to the keywords

which also leads to the proper way of designing a database. What are you going to seacrh in that database most often?

you man data type?

If you always get all properties at once several columns might be more efficient, but in case you want to seacrh by property then it willbe way more efficient to have separate row for each item, property

ok, well on a 'detail' page you want all properties, on most cases however it's just searching which product meets certain properties, like you can imagine on most sites
so a typical search phrase could be 'wood' 'leather' in which all furnature with those two properties will be displayed in a list (quite detailed, but nothing fancy)
and the 'big list' will be shown on the detail page after clicking on a certain product
so search overhead is fairly minimized I think

Well ... thats where you have to start. Define the output first, then try to define what kind of queries you will need and just then decide the structure
And just finally decide what data do you need to store

well I think i reached that point now, but there where a few questions I want to take into consideration before working it out
like what would be faster/efficient, using rows or columns for properties :-]
but seems like there is no 'general' awnser to that

1 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't
9 [ERROR] Default storage engine (InnoDB) is not

Relational databases are optimized for tables with few columns and millions of rows. Having few rows with lot of columns is always inefficient.

ah
I see
ok, that is interesting

TerrorBite, did you compile yourself?

fifo_, no
I'm running mysqld.exe --console atm
changed the default storage enging

SHOW VARIABLES LIKE "have_innodb";

salle, can't run any SQL queries if the server won't start
...wow, that was interesting

How is varchar(40) stored. Does it always store 40 characters, or does it store only the amount it has as a value. I remember there is one character field which saves static length always, and one which saves on varying length depending on the value. which is which.

VARCHAR means variable sized

I figured that. But which one is static?

CHAR(20)

ah. obviously :/

Note that with multi-byte charset even CHAR is variable sized

hi, is there anything wrong with this connection string? http://rafb.net/p/PEG4xd49.html

I wonder if the VAR part might give a clue on what type it is

what var part?

VAR as in VARCHAR

hey all

http://rafb.net/p/Jo5YMq31.html
that worked... delete the database files and rerun

is it possiable to have have 3 active int's in a table?

define "active"

like
hang on
ok
right
say
i have recnum as a int
and hten
i have another thing say this time added as an int
but i want another int in the table
so you have 3 int's in a table

that is still no definition of active
but it is very possibe to have 3 int fields in a table

yes but once you insert data into that table, it wont insert

tell me more
it is likely a syntax error in the insert query

...
the insert works fine with two int's
but if i add a third it wont insert data

explain more
show the query

How can I change the type of a column from int to float and preserve the values?

just ALTER the table

is there anyone that can help me... ?
due to a hardware failure of our backup server, I managed to save a database only in "file form" (/var/lib/mysql/dbname). I succesfully managed to phisical copy the files into a virtual machine with the same configuration of the old broken machine. However, if I try to make a dump of that database, the blob photos inside it don't works (I get a table with 9 photos instead of 602). How should I resolve the problem? I w
The blob photos are well readable with our application, so the data is inside the files. The problem is in the dump
Now I want to dump all and put it into the new server, but I'm only having nearly empty tables. Is there a way to make an integrity check...?

[noob response] a `repair` won't do the job?

ppl i have Mysql hosted on our server can i give different databases access privilege to directories under a specific domain , lets say domain.com/userA only access databaseA and domain.com/userB only access database2 and so on... , any comment ?

Nokao, recover from backup.
f00bar80, no
i mean, yes
its just not clear to me what you mean