Hi, when I create a table with engine = memory, does this means when I restart mysql, the table would disappear?

You can't use SELECT * and GROUP BY in a standard way. MySQL does allow that form, but you either shouldn't use it ever or be very very careful you know what you're doing.

just the contents

I have a table with fields `user_id`, `page`, `timestamp`. I am ordering first by user_id, then by timestamp. I want only the last page for each user.

aha! thanks for the clarification;

http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html
groupwise

Eh, bsod

groupwise max

http://jan.kneschke.de/projects/mysql/groupwise-max/ http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

Check that URL.

Is there no way to get it to replicate alter table without stopping the slaves first?

Thanks alot I'll check it right now Xgc

I'm doing a database query and I need a wildcard in my WHERE (i.e. something = ? )… what should I use? And is there a place that I can look up these special characters?

Just don't specify that column in the WHERE clause.

not an option: I hard coded it and I need a variable to replace it
is there not one at all?

something

That'll work for null as well.
No. There is no constant that will do what you want.

darn

If ? can be a column name, the best you can do is something = something, if you can't change the operator.

alright, thanks

something will do exactly what you want.

rails replaces the ?s in my query in order: "YEAR(#{ self.table_name}.created_on) = ? AND MONTH(#{ self.table_name}.created_on) = ? AND DAY(#{ self.table_name}.created_on) = ?", year, month, day…. and I want to allow for a lookup by month without changing the code (as it's nice), but I'll
write two different version

Good evening all.

guys imneeding some help tryign to get someinfo out of the db

Does ? always represent some literal value or can it be an identifier?
Is it the equivalent of a prepared statement?

well, in my application the variable subsituted in for the question mark is always an integer
except sometimes the month and day are missing

Ok. If you could replace the operator with LIKE you could use wildcards.

I hadn't thought of that

field LIKE '%' would match any non-null field value.

im tryign to get al lthe user_name from the user table
http://paste2.org/p/5668
and have them in order by name

that's awesome, that keeps my code pretty

You're welcome.

can anyone tell me if my code is right or even close ?

It's perfect, as far as I can tell. You have fluff in that example that has nothing to do with SQL.

fluff ?

Is there really both a name and user_name field?

hmmm, I could pastebin this query, but it requires a bit of work to anonymise, so will ask w/o a pastebin first….. I have a ELT(FIELD(GREATEST(…..), …….), '….', '….', '…..') - it works very well for sorting (with thanks to the esteemed #mysql from _ages_ ago)

oh well i don't need both

BUT the problem is that it picks the first field, when if all fields are NULL
(when, not if)

if one of those doesn't exist in teh table, the sql host will fail.

i want them ordered alphibetically

ORDER BY user_name

there is a user_name field though

and it really should be NULL

oh i see now lol
and this $c = "SELECT NAME='$type'";
should the be user_name ?

That has nothing to do with sql or MySQL.
You need to ask that in whatever channel handles that language.

I ma looking for suggestion. I have C++ program that downloads some data when program starts. It is putting it into database host as it comes from socket. Now I dod delete all data first before starting download. This is quite bad. So what I am looking
for to start download data to a temp table and when all download completed than drop data from main table and move temp to main table. How can you do that? Can you?

Not sure what you're trying to ask, unless you want to use COALESCE() to convert nulls to some known non-null value.

Xgc, hmmm……sec, will rewrite

no reason why you can't. don't know that it will be any faster though.
how many records are involved?

DROP TABLE tblname; RENAME TABLE tblname to new_tblname;

it is not matter of beeing faster

Ooops.. DROP TABLE tblname; RENAME TABLE newtblname to tblname;
He just wants to be sure the new data is in place before dropping the old table.

ELT(FIELD(GREATEST(product_1, product_2), product_1, product_2), 'product_1', 'product_2') — where the contents of the field is 'when that product was downloaded'

To be really careful, rename the original table, don't drop it.

but sometimes the user doesn't d/l either products so both field are NULL, yet that query returns product_1

it isthat is what I want to do
jsut make sure that data is there before droping

Only drop it after the incoming table is renamed successfully.

else I will be left with empty data
is temp table persistent between stored proc calls?

Import into tmptable … rename table tblname to savetable; rename table tmptable to tblname; drop table savetable;
If you create a concrete table, it will persist.
As an aside, this is a *very* bad way to handle data. Hopefully this is just a maintenance procedure and not done during live/production operation.

how do you create concrete table? and how do you check if it already exist before inserting data. So it is only created on first call
Thank gusy for help

create table …;
Even a temporary table would persist between procedure calls, as long as it's within the same connection.

it is same connection
how do you check if temp table exists

You create it.
Why wouldn't you know it exists? This really doesn't seem like a very sound design. Consider not creating any tables dynamically.

Xgc, got a sec to throw me a bone? :/

What's the easiest way to give a non root user the ability to change his own password?

!m ampex set password

ampex see http://dev.mysql.com/doc/refman/5.0/en/set-password.html

it is always possible to change your own password

are you sure?
no need for mysql table update privilege?

When both fields are null, the above expression returns null, not 'product_1'.

Xgc,

Either your version of MySQL behaves differently or you're misunderstanding something.

yeah, possibly
this is 44.x FWIW
4.x

select ELT(FIELD(GREATEST(null, null), null, null), 'product_1', 'product_2'); Try that.

hmm, I get a syntax error, due to the null
nm, typo

Must be a 4.x bug or you didn't run it properly.
and the result?

I get "NULL", yeah

Well, while you're initial question was faulty, there is some bad behavior you missed.
select ELT(FIELD(GREATEST(1, null), 1, null), 'product_1', 'product_2'); also produces null.
You may need to treat nulls differently.

hmmm….. not here
here, if I replace "null" with "1" (I assume you meant a literal "1", I get product_1, not NULL

In 5.0.37, greatest(1,null) is null.
Ah. Sorry. That was a typo.
No. I meant 1, the number.

heh - I must be contagious

In 5.0.37, greatest(1,null) is null.

yeah, I put in 1
4.1.22 here FWIW

select FIELD(GREATEST(1, null), 1, null); generates 0.
Which of those behaves differently for you?

hmmm, on THIS side, GREATEST(1,null) gives 1
checking field

Ouch. That's the one.
It probably should be null, which is the 5.x behavior.

& F(G( returns "1" too :/

Since null is unknown, the db can't be sure 1 is the greatest.
I suspect that was fixed in 5.x.
You probably shouldn't depend on that 4.x behavior.

ah HA!
Before MySQL 5.0.13, GREATEST() returns NULL only if all arguments are NULL. As of 5.0.13, it returns NULL if any argument is NULL.
ah ha! and another problem with the query - Field() = "If str is NULL, the return value is 0"
oh, nm - that'd be correct

guys

actually, I think FIELD() is the broken one
ignore me, brain's frieed

I read your suggestions. I am still confused a little. What is your suggestion to that I should do. One more time. I want to make sure that do not delete existing data until successful download. I could just update/insert on every net download what is new/missing. But this would grow number
of records over time.

Wow
http://linux.slashdot.org/article.pl?sid=07/08/09/2047231 Time to switch to PostgreSQL!

and spend time relearning
badly phrased title

guys i have a problem
how i searh in a field type date, when the month is 5 and the year 2007 ?

range search for every day in the month

where col"2007-06-01" and col"2007-04-30"

mmm

The title is downright *incorrect*
It's shameful,really.
MySQL (even Enterprise) is obviously still GPL…for the time being.

maybe it could changed too

But even so the recent developments all lead to the conclusion that all the effort in the world to relearn a DB system and switch to PostgreSQL is worth it.

but Community is always there

And I know I wont be alone in doing so.

relearn is one thing, rewrite application is another

True, but most applications I use support either MySQL/PostgreSQL

interesting point of view.

Thank you for respecting my perspective enough to not flame me, even if you may not agree with me.

it is worth looking into, clearly.

No Enterprise Source Tarballs! Alas, MySQL has become the great Satan!
No Enterprise Source Tarballs! Alas, mysql hosting has become the great Satan!

It's still GPL though.

Noooo, it's evil!

Unlike sensasionalistic Slashdot headlines would have you believe.

Isn't it?

I'm not saying it's NOT evil.
It's enough reason for me to switch to postgresql hosting personally.

No, then you play right into their hands!

But Enterprise edition is still GPL so you can still get sources and binaries from other distributors.
Haha why's that?
Play into their hands by not using or recommending the use of their product?

Well, the MySQL MO is to see how much nerd rage they can generate without people switching. You have to scream but not leave.
SCO, the initial split, the removal of the tarballs, it all flows together.

don't forget Area 51, which is Norwegian for 'Master Plan'.

Sssshhhhh, do you want to get us killed?

*loads his rifle*

SubFreeze is Norwegian for Sigmund Freud.

*loads his pistols*
*loads his AK-47*
*equips his grenades*
*straps on his kevlar*
*looks at threnody and winks*

down boy

don't MAKE me pull this planet over!
cheater

*sigh* I wonder where it'll end up…

Is there a way to concatenate the columns that I get with a select * from foo?

CONCAT()

R T F M

select concat(*) from foo doesn't work

TLDR

you need to choose the columns to concat

Of course not

I don't know the column names

w t f

this is blind sql host injection

you did read the documentation on concat(), did you?

yes

don't do SQL injection

I am pen-tester

contact(field1, withfield2)

I like SQL injection
One of my favorite security topics TBVH

anyways, I need to figure out the column names, and I don't have access to information_schema

concat(fieldone, concat(field2, concat(field3, field4)))

My next favorite is arp poisoning

I don't know the column names mate

select * works, it tells you the name-value pairs

The only way I've ever figured out column names is by guessing

or "describe tablename"

I am attacking a web app with blind injection…

….

ahh, brute force! I love it

the only output is the mysql error message
so brute force is the only way?

or SHOW CREATE TABLE
:-)

and we're supposed to help you attack the website?

SELECT * FROM TABLE

that doesn't work in subquery

new query SELECT * FROM table; TRUNCATE table;

php doesn't allow that

Yes, I know, I am quite experienced in what you are doing

Damnit!

Yes, PHP only allows one query per call to mysql_query()

can you do a sub-select?

yes

have anyone considered that what borius is trying to achieve might be illegal?

You can however use subqueries and UNION SELECTs

this is legal pen-testing

pen as in prison pen?

heh

0, 1, 2))

problem is I can't figure out the name of password column

pwd

Error MySQL server has gone away while close stmt

tried 'em all mate

Yea, pwd is a good one, as is password, pass, pw, passwd

How do i fix that ;[

tried them, and then some

close stmt?

so, here I am wondering if there is a way to turn * into a huge string that I can dissect with substring()?

select * will not give you the column names.

yeah I know

Hacker

I don't really need them, I just need the output from the password column. So I figured if I could get everything concatted then I could figure out what's the password.

Just tell your client to fix their injection issue by regexp'ing out anything not numeric and longer than the max id in the db

they are corporate droids, they want proof of concepts

select * ; loop through array; print values;
booya.

He doesn't have access to the code. He's using injection

and how would I do that in a subquery?

o.

normally I guess the names or bruteforce them out of information_schema
but that has failed me here

borius, toss the URL

haha, no way

Are you fortunate enough to get error messages?

awww, why not?
he is fortunate enough to get error messages

yeah I get them
but they just say "unknown column name blah blah blah"

hey all. I want to do something like this: SELECT * FROM table1 LEFT JOIN table2 USING (id) WHERE 'there is nothing in table2'
SELECT * FROM table1 LEFT JOIN table2 USING (id) WHERE table2.id = null?

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

hmmm, guess I am SOOL then

Hmm.

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id WHERE table2.id is null?

If you're getting error messages and the "corporate droids" such as myself can't see the messup in the code, and they can't figure out not to let anything other than an integer pass, then they probably can't afford to pay you.

heh

the fact that you can get some columns should tell them that somehow someone will guess them all and get their data

well, it seems the password field has some really weird name

I'll tell you now that I do not believe you are doing this legally

all the other fields are guessable from looking at the web app's var names

people that do it legally know what they are doing, and don't usually come here asking for help in hacking
or rather, "cracking"

yeah yeah, believe what you want

leave them to their demise if you can't convince them their system is insecure.

Just write it down and keep it in your CYA file.

are there any functions that accept the output of a select * query?
doesn't seem like it, they all say input should be one column only

Your "Cover Your Ass" file, should anything ever come of it and they say "Why didn't you warn us!? Isn't that your JOB?!" you can show them that you did.

I'm not employed there
am consultant

you're contracted, which puts your arse on the line if something does happen after you've told them it won't. So warn them, document it, and let them fail

yeah well…

unless you're really trying to get everyones passwords so you can get system access and not warn them, then, well, yea.

haha, I love this paranoid atmosphere
trust no one

Eh, it's not mis-trust, it's acceptance of all possibilities and their consequences.

Isn't that the primary concept behind infosec?

yeah

Hmm, wow, good way of wording it SubFreeze

It's just how I think

Yeah, well you hit the nail right on the head.

so, borius, good luck getting access and flip off the PHP MySQL module for not allowing multiple queries.

Haha

eek!

Alright you all, I'm outta here. It's home time with a pinch of sleep

yeah well… being a whitehat sucks… if I was evil I could just social engineer them with freebie USB sticks

If the company won't heed your consulting warning based on the evidence you already collected, they won't make it anywhere and you need to find better clients.

cool, I just found out a way to get the number of columns

ORDER BY 1…
ORDER BY 2…
ORDER BY 3…
:-)

hehe
I used (select (select * from users) = (select 1))
the err msg tells me the number of columns
I'm not an sql guru, I'm the first to admit it

Well, post the URL and let us do some dirty work

I need money for guns, women, computers

guns and computers are worth your money

Zing!

I guess

they depreciate and take less. Women just keep taking more, and they don't appreciate.

the money part is for buying peacock attire and seduction books

the gqame ftw

ahh, setting yourself up for failure, got it.

hehe

I'm going to go hit the bar next door, then get in my car and go home. Later all

I've got mates who have gone from wbAFC's to hardcore PUA's… I don't have the time for it myself

Well, if you're driving, don't hit the bar too hard, heh.

driving drunk ftw

Just scoping out available, drunk women

Respect.

s/drunk/coked out/

comes when they sober up
and I tell them I saved them from a horrible mistake by allowing them to make a not so horrible mistake
uand I tell them I saved them from a horrible mistake by allowing them to make a not so horrible mistake/u

Hah

hasta

lat
late*

guess you lot are Americans?
I am euroweenie
it is like 6am here

Ah, yea I'm american

cool
you get to have guns
and be like actual citizens and not just subjects

Yes, and so far I have 3

damn you, don't make me jealous

:-)
Anyways, I know what you mean about not having time for hardcore pickup
I just apply the basic concepts in my day-to-day life, I'm a busy guy, I don't have time for it either

I'm into hacking in all of life's areas
like smart drugs
heard of 'em?
aka nootropics

Nope, never heard of them.

well they're a class of substances that allegedly make you smarter and shit
most known is piracetam
the effects are kinda subtle but I can say that they do work
now, what I really need to get my mittens on is modafinil
aka the new wonderdrug of the DoD

hello!

i have a query which joins the table group to the table release. there is a release_date field in the group table which is currently being used to determine the release_date. however there is also a release_date field in the release table (don't shoot me, i didn't design the db). now what i
need to do is change the FROM clause so that instead of group.release_date, i have something like MAX(webstock.release_date) AS release_date … so basically i want to use

your line was too long. it cut off at "… want to use…"

top

the latest date from the release table instead of the date in the group table… but MAX won't work there because i'm not using a GROUP BY clause
bthe latest date from the release table instead of the date in the group table… but MAX won't work there because i'm not using a GROUP BY clause/b
thanks

why aren't you using a group by clause?

because it's not my query and i want to change it as little as possibly. it's a monst0r of a query that is central to the operation of a production system

I wrote script that restore db but I can't do it quietly. This command: sudo mysqldump –all-databases -u root -pPassword $1 where $1 is first parameter of script

that doesn't restore anything

I don't want that mysqldump is showing on screen progress

hmm I'm not quite clear… do you want the bigger of the two dates? There is a function called "greatest()" that will return the biggest of whatever values you pass to it

file

it should be: sudo mysql -u root -pPassword $1

so greatest(group.release_date, release.release_date) would return whichever date is greater (later)

ups, thx for help.. I didn't good understand docs :/

actually i think what i'm talking about is impossible… because what i'm talking about will occur across multiple rows

then you need a group

yeah it's not going to happen… i think i'll solve this problem at the import side
thanks for your help guys

well… ok

lol
musta been a gentoo guy
go fish

sorry I just took a week of vacation time

have it your way then

Hooray for vacations

Hi

damn. guess I still have over 150 hours of vacation! Crazy state

go fish

i am just wondering i want to select everything out of a table except one colum… any ideas how i can go about it so far i got SELECT * FROM tblnx ?

heh
list 'em all out. There is no " * except blah"

sorry?

you have to list all the columns you want to select

the current topic is vacation. Why'd you have to go and ask a legit. question?

who knows
so you cant just do SELECT *, -Order …?

lol, 150 hours? Its insane

nope

damn ok

SELECT * is evil anyway

yes but o so convident

8 hours every 2 weeks

and they can compound all the way to 150 hours?

higher than that… I think up to 300

well I'll beee.

hihi
buu, We're sorry, but Google Answers has been retired
what a pity
seriously ?

yep. for about 18 months now

I don't want to stay retired

I kind of doubt they would appreciate it if I let it build up to 300 and then say "oh by the way, I'll see you in 3 months. kthxbye"

tias
and let us know

heh well especially now since I might end up being the only developer, DBA, sysadmin and billing person if my coworker leaves…

If I set a timestamp column to current for ON UPDATE, will it be set when the row is first entered? I'd like a "entered" timestamp, and a "last_updated" timestamp.
It seems that I can only have one timestamp column with any kind of CURRENT_TIMESTAMP set

just make the "entered" column a datetime type and insert NOW(). Then the "last updated" column can be a timestamp column

i like triggers

anyone who speak spanish?

try #mysql-es

thanks jbalint

hey guys, why would you use mysqld_safe instead of mysql
?
safe_mysqld vs. mysqld rather.

hey guys… when i clear the mysql.log file (that logs sql query(s)) do i have to restart the server??

wtf, this SQL server is pegged at like 100% CPU, but a 'SHOW FULL PROCESSLIST;' simply shows a lot of sleeping processes.

that's because they're snoring very loudly

Oh hey, that was pretty good.
Well, I just bounced mysqld, and usage went down

maybe it was a lot of very quick queries. When I'm parsing XML and doing massive inserts I rarely catch a query in the process list even though it is inserting 2,000 records per second

Ahh.
I've never had a problem with this particular server. It's a backend for a Postfix mail server, which rarely sees that kind of usage.

good morning

'morning.

too early to tell if it is good yet

well - I wish you all a good morning, I am not sure wether it will be good

Happy yesterday!

Hello

could it be that snmp traps in the dashboard version 1.2 is currently broken?
(okay, the enterprise website too…)

Whats an easy query to duplicate a row in a table, only creating a new unique ID (autoincrement) and timestamp?

hello everyone

is there an easy way to copy one structure of a database to another ?

mysqldump

–no-data
if you only want the structure, not the data itself

hello ?

good morning

is it possible to grant two passwords for the same user?
GRANT USAGE ON *.* TO `user`@`%.domain.com` IDENTIFIED BY 'password',`user`@`%.domain.com` IDENTIFIED BY 'password2',

i dont think there is but i could be wrong

Zap-W: No
Hmm…
Try it and let us know

well it didnt work for me , it took the password from the second one ,`user`@`%.domain.com` IDENTIFIED BY 'password2',

Zap-W: it is treated as a 2nd user with the same priv, overwrites the 1st in this case

Zap-W: As kimseong says in MySQL world user1@host1 and user1@host2 are completely different users

i am set GRANT Insert,Update and i only see Update in columns_priv

hi guys, my feeble mind is giving me problems again i'm trying to construct a select query that would return me a list of 201 people where i have the 'person' i search for as number 100. So basically.. i want the 100 over him, and the 100 after him (ranked by a score)

UNION will help you

4 |
update user set gecos='Hey Hey' where User='sad';
UPDATE command denied to user 'nss-root1'@'localhost' for table 'user'
lol users
never mind

Where can I find a list of all the commands understood by mysql_real_query?
uWhere can I find a list of all the commands understood by mysql_real_query?/u

alguem fala portugues???????

mysql_real_query is a function. It does not understand commands
mysql_real_query sends SQL statement to MySQL server and gets back the result and errors if any

it unterstands "SELECT" to "CREATE"

Which are not commands

Okay, I need the list of SQL statements and the parameters/syntax for them.

dev.mysql.com has the manual

The list is very small actually

What's the latest stable MySQL version?

hello, trying to get a dump of a database that is guaranteed to be consistent withing a database. atm I'm doing "–complete-insert –lock-tables –add-locks –flush-logs" with mysqldump but that doesn't guarantee anything. also mysqlhotcopy according to the manpage does only table locks. so
does anyone have a tool to get a consistent dump of a mysql myisam database?

or if you are more hard-core, check the sql/sql_yacc.yy

However SQL is a language with more than 30 years history You can hardly learn all about it from a single list

dev.mysql.com has that info too

there isn't such a thing SQL being a standard that is usable on different servers is an urban legend…
bthere isn't such a thing SQL being a standard that is usable on different servers is an urban legend…/b

ninguem fala espanhol???

try #mysql-es or #mysql.es

xau

!spanisch

is a row cell limit the 64 characters?
*limited to

depends on the column type
look it up at mysql.com/doc

kk, ty

ok, anyone has some pointer to backup a myisam database in a consistent state on a live server? i looked thru the docs and it seems that only single tables are guaranteed to be consistent and there is no way of getting a consistent database wide dump

Hey all. I have a question about caching in MySQL databases.

ekimus, change to innodb for a start

I'm running a script that used to take about 4 seconds to process each row, but now each query is taking so long that it takes 20 seconds per row. But when I run the script a second time on those rows go by lightening quick. I'm assuming it's because they are now cached.
Is there anyway to speed things up or force certain things into the cache?

do I read that I'm right with my above assumption? and the only way is to switch do innodb?

eyeRmonkey, first find why its slow in the first place use explain
ekimus, myisam does not have transactions

Alright. I did the explain. It didn't really tell me anything I didn't know already. What am I looking for?
1 PRIMARY albums_serialized ALL NULL NULL NULL NULL 90969 Using where 2 DEPENDENT SUBQUERY multidiscs ref PRIMARY PRIMARY 242 func 1 Using where; Using index

use of indexes

morning

which it isnt

the subquery is
i suppose there's probably a way to optimize it

i know that, but I'm not quite sure if that means that i can't do a consistent database backup. (while the server is online)

let me show you the query:

pastebin

try http://pastebin.ca or http://pastebin.mysql-es.org

http://eyermonkey.pastebin.com/m4bcd9a41
albums_serialized is the main table and blz_id is the primary id. multidiscs is a helper table with no keys or indexes
wait. i take that back. multidiscs does have a primary key. It's the "primary_disc_id" column

Hi, can i ask a question releated to Access and SQL

go ahead.

!tell eyeRmonkey about a not in b

eyeRmonkey SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;

is it necessary to have the table names be infront of the column names like that?

i have a tblUser, and a form with a text feild. i am using Environ("username") to get the NT username

no but helps to make it obvious what it means

actually its a combo and not text
so how can i make the combo name display the user name

what kanguage are you using to connect to the DB
language *

*oh, because NT username is usally jtim, if the user realy name is Jim Tim, so i build a function that will check the username and match it to the real name
i am using SQL

thats not a lanuguage to connect to the DB
the Combo box is in Html yes ?

It sounds likes C# or VB.NET
Does anyone know how to list mysql process on windows and kill a specific one that is taking too long?

SHOW PROCESSLIST;

alright. I changed my query. Unfourtunately, it's still going really slow. It went really fast up to the point where I last stopped it. I'm guessing that's becaused those results were all cached. Any other ideas for optimizing it?

well currently its scanning 90K plus records that needs to be stopped

was that directed at me?
oh yea. i guess it is.
i think that was a bad estimate though. it only has 20k
and i kill the processes everytime i stop and start it
Wow. Alright. I found the problem, but this leads me to a new question.
For every row I grabbed form the primary table, I did some processing, then did this query:
SELECT * FROM albums_serialized WHERE blz_id IN (SELECT secondary_disc_id FROM multidiscs WHERE primary_disc_id='blah';

dont quote numbers if primary_disc_id is a number

it's not. but thanks.
the first query I showed you on pastebin grabbed the "primary disc" using the multidiscs table as a reference. This query grabs all the secondary discs. When i commented out this query, every row was processed in a fraction of a second.
So this is obviously what was slowing things down
is there a way I could use a join to get all of them at once?

likely yes

http://eyermonkey.pastebin.com/m5e9b2eba

and likely a lot faster if the correct indexes are in place

if you need me to explain the columns purposes, let me know.
join
I'm reading the manual on joins again so I can try to understand them.

eyeRmonkey is the is a mysql for windows???

Yes.
Do you mean is mysql available for windows? Or were you asking if I was using it on windows?

eyeRmonkey i was asking if there is a mysql for windows
links

http://dev.mysql.com/doc/refman/5.0/en/windows-installation.html

thanks

There is MySQL for windows for almost a decade
Actually more than decade. Since MySQL 3.12
3.21

do you have any idea what i should look into to combine those two queries?

salle thats good, but i only start working with SQL 3 days ago

Welcome to the exciting world of SQL then

and by exciting you of course mean chaotic and senseless.

I see nothign chaotic with it.

i'm kidding.

just remember sql is 'declarative' you declare what you want and not how

It's a language derived straight from the relational algebra and when you derive somethign from pure mathematics the definition of chaos is not what it is in everyday life

Haha
you have a very good point. It took me a while to get used to that. I still am as a matter of fact.
Would anyone be able to help me combine these two queries into one?
http://eyermonkey.pastebin.com/m5e9b2eba

sql sometimes is easier to a person who do not do sturctured programming

Why not UNION?
I'd say always
Some mathematical background helps a lot while experience with procedural languages acutally makes thigns more complicated

Would that off a speed increase from doing two separate queries? The way it is structured it seems like there MIGHT be a more elegant way.

Ask EXPLAIN

Hehe. Will do.

I would rewrite the second one to JOIN. There's no need for subquery there

A left join or a normal join? I'm no good with them.

mysql 4.1 manual, rewriting subquery as join

Instead of SELECT .. FROM a WHERE id IN (SELECT id FROM b); use SELECT .. FROM a, b WHERE a.id = b.id;

http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html
I didn't think they would have a whole manual page just for that.

historical reason, before 4.1 no subquery

that's a good point.

And then you will see you can combine both where clause into single one with OR
We had that manual page long before MySQL 4.1

I bet. I just recently realized how amazing IRC rooms are for getting help. I really appreciate all the help you guys offer.
And you have a good point. And OR would do it. No need to make things complicated. Haha.

hehe irc rulez nearly as much as google for help

If not more so

better acuracy

and no doubt someone has allways encountered the problem before

yep

half the time on google its knowing the correct syntax to ask it
yay just stripped a 67 meg db into an 11meg one

Very true. I'm surprised when I see some of my nerdy friends typing horrible search queries.

just with some better storing methods

Jester-|: enlighten us.

the original had about 36 tables in it, i stripped that into 8

Jeeze.

and got rid of some shit data and cleaned up the structure
was a mess
while at the same time im 75% through writing a webOS for my companies database … i smell a payrise coming for me

you said this: Instead of SELECT .. FROM a WHERE id IN (SELECT id FROM b); use SELECT .. FROM a, b WHERE a.id = b.id
Instead of SELECT .. FROM a WHERE id IN (SELECT id FROM b WHERE b.id='blah');

I preffer select

as opposed to?

select … from a join b on a.id=b.id
both those alternatives

where a.id=b.id
short and sweet

So do I use the ON and WHERE clause or just one or the other?

on is easier for the syntax when having a real condition
you only need one of them

I read in the manual that you should use WHERE in most cases
http://dev.mysql.com/doc/refman/4.1/en/join.html
3rd paragraph

iirc u can select where AS then choose b.is=what.the.as.was
but i could be wrong again

imo, the on syntax is much easier to read if you have real condtions

The join you guys are suggsting just isn't clicking in my mind. I have more than one condition that i'm trying to satisfy.
SELECT * FROM albums_serialized LEFT JOIN WHERE blz_id IN (SELECT secondary_disc_id FROM multidiscs WHERE primary_disc_id=$blz_id_escaped)
SELECT albums_serialized.* FROM albums_serialized JOIN multidiscs ON albums_serialized.blz_id=multidiscs.secondary_disc_id

you are omiting the left join

Why yes I am. Haha.
But it still seems like something is missing.

you know, I have a simple rule for query design

what's that?

designate one, and only one, table who's rows is the main table
the other tables are just there to add info to it
but this one is the core one
for this particular query
say we got a web forum and are getting the posts in a topic. the posts table is the main table then

Alright
SQL is the hardest thing I've ever had to learn. I think it's because I'm so used to doing things procedurally. I've been reading the hashmysql.org page on JOINs for 10 minutes and i'm barely grasping it

Join helps you to understand things in depth. On top of that joins are usually better optimized
Better find a book about mathematical theory of sets

I took discrate mathematics last year in college

Tables in relational model are defined as sets. All operation on them are set operations. Things like intersections, joins etc. even "empty set" come straight from there

I just feel like none of the syntax or keywords are all that intuitive for all the work that is going on behind the scenes

eyeRmonkey, you won't get anything until you do tutorials… once you see how it works it's so easy

I've done tutorials. And I can do basic selects and inserts (after some practice). Everything else is still foreign to me.

I usualy recommend sqlcourse.com for starters …

one you see joins.. it's easy
eyeRmonkey, I guarantee reading up on Normalization will tie joins together in your mind

Think of WHERE clause as "find subset of a set" and inner join which is simplest join as "find intersection between two (or more) sets"

I actually understand normallization pretty well (or at least I think I do). It's the SQL that is the problem, not the design of databases

b.id

or is that not even possible?

Who says it is better?
Who says they do the same?

I made it up. So… Maybe they don't?

tias

Try it and see, its quicker to type it on your system and try it than wait for one of us to tell you its ok

It's kind of strange you say you don't understand SQL well, but you play with outer joins

Play is the key word. I have no idea what I'm doing. You guys said to use joins…. So I am.

hehe

how do I change from myisam to innodb and what are the effects going to be on existing database?
bhow do I change from myisam to innodb and what are the effects going to be on existing database?/b

is it possible to make incremental backups of mysql's database by using mysqdump?

can you split the question please ?
if you split it, you will get a yes and a no

ALTER and there are too many effects that it will take full week course to discuss them

weigon, yes, here we go
I have some databases very big and I was looking for a way to backup only the table's register that are modified from a day to other and not the complete database every night
is that possible by using mysqldump?

mysqldump is for "dumps" aka full backups
for incremental backups check out the binlogs

binglogs? but even so the incremental backups should be done by block or files and not under the mysql's engine?
What usually people do when they have a 30GB database for example. They cannot save the complete one each night

replication to alive backup

archivist, but replication does not involve backup, it is just a replication
you mean people usually use replication and they don't do backup of the database

1 its a live backup and 2 you can backup the slave

With replication you can have full live copy of the data at second server
With some lag indeed because the replication is asynchronous

yes I kno what you mean

Could someone tell me if these two queries are anywhere near being identical:
http://eyermonkey.pastebin.com/m40136e6f
Well… It seems they are the same. Cool.
The queries still take 20 seconds, though.

hi all - is it standard practice to name which fields are to be inserted into on a sql insert. I used to think it was unnessary but then thought if more fields are added later then it means adjusting each php page which has an insert where as if the fields are mentioned then field additions dont
alter anything unless they are compulsary field additions

so use explai and check for index use
explain

live by explain, die by explain

nickthorley, yes it is goog practice to name columns for insert

thanks

should I create an index for all my columns to speed things up?

no only needed columns

so only columns that might be involved in a condition of a query?

yes match indexes to queries

nickthorley, it is also possible to change the column order with an ALTER TABLE, and then a positional insert will fail

and select * then fails …..

it is unlikely i will need to change order of cols but may need to add a field and without having fields identified on insert then i would need to adjust each page with an insert where as naming i wouldnt so that sounds good to me

Sounds good. Do I ever need do anything with indexs in my query, or will they handle themselves after I set them?

99% handle themselves

And I don't need to set an index for something that is already a primary key, correct?

make that 99.99%
yes

Sigh. I added an index to the secondary_disc_id column and it still took 20 seconds. Is there any flush or something that I need to do after adding the index?

hi to all
create view test as select foo.time_id from (select time_id from dim_time where time_id10) as foo;
is the statement is correct or not?

eyeRmonkey, no need they get built automaticaly

no. FROM specifies a table. You are giving it a subquery which will just return rows
but then again. i have no idea what i'm doing. so i might be wrong.

SELECT … FROM (SELECT ..) is quite correct

salle, yes its working in postgresql

Nothing wrong with it. It's another question if it's necessary

yeah. I started to remember that right after I said it. I'll let you guys give the advice for now.

Why do you need subquery there?
No point.
Yours is like SELECT 1 FROM (SELECT 1)

its just a sample

("PRIMARY" must be the name of and only of a primary key!)
I have a column named "primary_disc_id" but it is not a primary key. is that why i'm getting that warning?

cant have two primary keys

so i can use the sub query on the view at the from clause right?

I know. I'm not. It's just the name I chose
primary means it's "disc 1" of a set. secondary means its its "disc 2, 3, …"

Welcome back to the theory

Hi

Primary Key is important concept there

how to load fast text file to mysql with tab delimited columns ?

If you think it's just kind of index you are wrong.

any loader for ascii files ?

!man LOAD DATA

see http://dev.mysql.com/doc/refman/5.0/en/load-data.html

!m mashuuk LOAD DATA

mashuuk see http://dev.mysql.com/doc/refman/5.0/en/load-data.html

again. Primary key I understand. SQL, I don't. Ha.

What is your definition of Primary Key then?

salle, whats the opposite of that LOAD DATA command?

Hi, I'm trying to construct a select query with the UNION clause, but is there a way to refer to a result from the first select, in the second select?

!man export data

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

SELECT … INTO OUTFILE

ahh
gotcha

PRIMARY KEY(id).

thanks

Thumann, a self join

the_wench, thx

not a union

If you care to open the load data page of the manual you will see it there too

the_wench, is the first column PRIMARY KEY ?
the_wench, after loading ?

The definition of a car engine is not "it revolves"
Better read the page please.
It will tell you LOAD DATA will not create the table for you

what i'm trying to do is, to get a list of 200 rows, where #100 is the input of the query, as in.. i want a list of people where i have the 100 above me and the 100 after me (99) based on a score rank

it's a column where no value can appear twice and it's optimized for lookups. as things are inserted, they are put in a sorted order so they can be found with quick search techniques (like binary search) later on.
correct?

Close, but that also defines UNIQUE KEY

salle, LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table

I knew you were going to say that! Haha. So what else makes it special?

PK value identifies the row. The theory dictates that every table must have PK even if it spans across entire row. In practice sometimes it's good to violate that indeed

here's what I have. http://eyermonkey.pastebin.com/m204e946f … should I change the name of the "primary_disc_id" column?
I see.

Why should you change it?

because of the word "primary" being in it. That's what phpMyAdmin was warning me about. I guess it was just being overly catious.
**cautious
Alright. Now that I have it as a join instead of a subquery, how would I combine these: http://eyermonkey.pastebin.com/m5c2887a

I have a database with a long list of (sometimes multiword) tags (14,000) which I am matching against a long list of posts (60,000) using MATCH() AGAINST() and a FULLTEXT index. At the moment I have to query to get the list of tags, and then run one SQL statement for each different tag as you
are not allowed to have non-constant AGAINST() clause - is that right? Is there a more efficient way of doing it? I am doing IN BOOLEAN MODE because I do not want, f
or example, "fish cakes" to match fish and/or cakes, but the phrase "fish cakes". Is there a more efficient way of doing this? (It currently takes around 300 seconds - as I do not do every post, just those that have been updated recently)

jeesus mate, that's one hell of a query

do your own search functions and indexes

archivist, how?

well heres my lookup www.arhivist.info/search/index.php/Sql

Wow! I'm so tired. It's 4AM here.
Salle, archivist, thanks very much for your help. I appreciate it a lot.

I'm so tired too. It's 2pm here …

12pm here

Well. See ya around. I'm sure I'll be back here quite often.
Night all.

It's addictive

Hehe. It is indeed.

is there a cure?

any pointers to my previous "question"

Hello
I have a normal PHP5 website connecting to a MySQL database. I'm uploading it to some Windows hosting - they say they allow PHP5 and MySQL, but I can't connect to the database they set up.
I think they are running MS SQL Server configured to allow MySQL access… how do I go about connecting?

http://us2.php.net/mysql_connect
?

select charater_name,character_score from ranking where character_name = 'Thumann'; would give me, my name and my score.. i need the query also to return the 100 people over me in score, and the 100 people below me in score.. so i get a list with 201 people

salle, why this doesnt work ? create table baza.table_o12_e2_pam20 (REAL index, DOUBLE PRECISION alignment_i, DOUBLE PRECISION kmer);

will need a selfjoin and maybe some other magic for that

flupps, the usual mysql_connect isn't working for me - I can see in cPanel that they have an 'ODBC Datasource' created with my database name, so I'm wondering if there are some different parameters

salle, what is type indefier for double in mysql?

use decimal
is there any patch available for mysql to create view with subqueries?

luckymurali_81, I need double precision
luckymurali_81, with floating point
Any help ?

mysql manual doesn't mention magic anywhere
hehe

FLOAT(7,4)
use float

Thumann, what about the UNION solution ?

gnari, hi

Any help
?
How to create table with floating point records ?
in columns ?

mashuuk, use float

well, how do i refer to the score of the input player in the two other selects?

lymeca, I want double

hi float and double are taking same ranges of value

create table table_o12_e2_pam20(index FLOAT, alignment_i FLOAT, kmeri FLOAT);
doesnt work

Doesn't work is not a helpful statement. Was there an error? Unexpected results? Does it sit on the couch all day eating all your cheetos and ignoring the classifieds? Be specific!

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 'FLOAT, alignment_i FLOAT, kmeri FLOAT)' at line 1

http://www.htmlite.com/mysql003.php

luckymurali_81, ok but this doesnt explain why my request doesnt work

paste your code in paste bin

with the error

Thumann, select * from (select id,score from scoretab where score (select score from scoretab where name='x' ) order by score desc LIMIT 100 UNION ALL select id,score from scoretab where score =(select score from scoretab where name='x') order by score LIMIT 101) as foo ORDER BY score

luckymurali_81, I already pasted it here

where?
gnari, hi

mashuuk, reserved word index

Thumann, possibly you need more parentheses in there
hi lucky

luckymurali_81, http://pastebin.com/d7a850ee

i have a doubt in your query for thumann
mashuk, index is a key word
change the column name to other
it will work
cant we optimize the query
its looking like beginers

Argh, how do you get a single value? A specific field in a specific record in a specific table?

phylo, select [col name] from table name where condition

what is condition?
that's pretty broad

condition to retrive the particular record

that's as far as I got

ok thx
luckymurali_81, thx

ur always welcome

Anythign which can possibly evaluate to boolean TRUE can go to WHERE clause. Yes it is pretty broad

Sorry, it turns out it's become a question of how do I ensure my user will always have a RecordName field, but it's irrelevant to SQL.

i'll give it a shot
Incorrect useage of union and order by
http://www.pastebin.ca/652204

The proper form: (select * from xint order by id limit 1) union all (select * from xint order by id desc limit 1) order by id desc;

is there any way to know how the big are your databases without using a php script that goes to show databases and show tables?

Hm
how to set DOUBLE uninque
?
or as index?

and of course those sample selects inside each set of ()s can be complex. Feel free to add your subqueries within them.
uand of course those sample selects inside each set of ()s can be complex. Feel free to add your subqueries within them./u
Just like any other.
The most common form is: create table blah ( field double primary key );

Thumann, as i said, you have to add some parentheses . try http://www.pastebin.ca/652210

if using 5.0, you can query the INFORMATION_SCHEMA tables to get the sizes

I have been told that but they are older version

Actually, in your case you could have just moved the existing parentheses. You had plenty of them already… and you didn't need the outer select * from () …;

Xgc, I get duplicated entry error http://pastebin.com/d61c252dd

what's this! i need to spell column names correct aswell.. futile language

Just follow the proper form: () union all ();

sloooow learning curve for me

Xgc, while there are 186507 line begining with 0.1… and seq column is cuted off to one integer after point

Greetz. How do I add 14 days to the last field in the following WHERE statement? DATE_FORMAT(`created`, '%Y-%m-%d') = DATE_FORMAT(`timeout`, '%Y-%m-%d')
I would like the fetch all rows where `timeout` is exactly 14 days bigger than `created`

hmm.. http://www.pastebin.ca/652218

Show a full example of the behavior. Use the pastebin.

it's kinda useless with some many people with the same score.. hehe

Xgc, I gave You link
Xgc, http://pastebin.com/d61c252dd

select * from blah where timeout = date_add(created, interval 14 day); or something

I can't use that without a sample text file.
The input is important.

Thanks

and the input 'Molri' is nr. 130 on the list instead of 100 or so..

Xgc, input has 100mb

You can create a test input file that contains 2 or 3 lines to show the behavior.

Xgc, here it is http://cannibal.kaduk.net/~mateusz/input.txt

SELECT id,MATCH (titel,artikeltext) AGAINST ('Tutorial') FROM artikel;
this will order results by relevance. but how can I give out the relevance?

Xgc, ups
Xgc, I know where is the problem
Xgc, can primary key be a string ?

sure

Yes. varchar(n)

usernames make nice primary keys

ok thx

But they don't always make for efficient systems.

prompt?

they appeared to be simply sql scripts

source file.sql;

Xgc, i was wondering how to generate them.

select into outfile …;
mysqldump is the more common approach.