I've been working on ways to optimize a web-application to run this query for a long time now …. its down to an 11 second query…. let me ask this way… what do i need to do to cut that down to 1 second ? http://pastie.caboo.se/89479 ?
any little suggestion will do…

i was just saying, that it is not the same logical connection… second please

run your query with EXPLAIN prepended at the front. The result will describe the indexes that will be used. You may be able to add other indexes.

already did
look at the bottom of the paste

Personally, when I have long queries that use temporaries, if caching is AT ALL possible, I create a table to cache the results in. Further queries just access the new table, which already has all the data.

wow! it's a miracle it finishes as quickly as it does.

file.sql ?

that is its best time… 11 seconds — I am not going to play here.. I don't know what to do to make this any faster

as noob as my question is could someone please assist?

somefile.dmp

dinner, later

if you remove the distance calculation field, how long does it take?

"term1 term2 +term3 +term4 -term5 -term6" should give you:
WHERE field LIKE '%term1%' OR field LIKE '%term2%' AND field LIKE '%term2%' AND field LIKE '%term2%' AND NOT field LIKE '%term3%' OR field LIKE '%term4%'
you don't need parentheses at all, because mysql knows that or means less. but logically it would mean:
WHERE (field LIKE '%term1%') OR (field LIKE '%term2%' AND field LIKE '%term2%' AND field LIKE '%term2%' AND NOT field LIKE '%term3%') OR (field LIKE '%term4%')

fies, I think all you need is an INNER JOIN

thanks

btw… the query is so long i can't feed it in usually through the mysql prompt … i have to edit a file and cat file|mysql …. , is there any way to get how long it takes to process a query out that way?
otherwise ..its just a guess

I'm going to make some dummy records and try it out. I'll report back. Thanks

date; cat file | mysql hosting ; date

: ) — creative

tryint to decide whether it's the math or the join that costs so much

15 seconds

or "time mysql file"
_longer_ with the math removed?

11 second is a really quick response… 17 seconds is average.. long is 25 seconds
so when i provided that 11 second benchmark… it was somewhat of an unfair benchmark

the same query run repeatedly should take very close to the same time.

Run it with and without maths back to back.

therefore, something else is screwing with your system, or something is locking the table(s)

man…. hold on — can't remember the original date
ahh… that time thing is great
12.565s

/tmp/output 2&1"

12.719s
13.221s
13.092s

So its mostly the joins

how many rows in the tables?

the biggest tables are loads & locations

Do you mind if I pull you aside so we don't futz with iratik and wwalker?

2085588, locations: 34914

explain looks like there are almost no rows.

no problem

there a something in the order of 50-500 insert/updates/deletes an hour on the loads table
uthere a something in the order of 50-500 insert/updates/deletes an hour on the loads table/u

Do you have any composite keys?

see updated paste @ http://pastie.caboo.se/89479
attached keys to the bottom

Well, I don't have a lot of time to check it out, but just wondering if you have any. Might help

welll… its such a big problem
we considered dishing out the 10,000 to have a mysql consultant actually fly out here and look at the query

Exactly why I don't have the time
You don't need to fly one out

i mean… we (i've) been trying to get some good performance out of this query and similar queries to it for over a year now …..

Talk to either mysql.com or provenscaling.com

its still a generated query

how many rows are returned?

65

How can I substract six hour from a date time field?

so what limits a set of 2M rows to 65?

limit 65
plus the timestamp restraints
yeah there are 2m rows in the table … but only 20,000 at most of those fit the time constraints

"field - interval 6 hour"
so, if you remove limit65, you get less than 25K rows?

for that query… less than 1000

wwalker, the information is already on the DB, how can I set that?

because … there are only that many in georgia

why is the pickup time constraint done twice?

something having to do with the way the query is generated

mysqldump for safety, then "update table set field = field - interval 6 hour" would update every row

ok, but That will update automatically each time a new record is added?

move the pickup = into the first join line instead of the where clause. sometimes the optimizer doesn't guess properly. Sometimes moving things from a ON to WHERE or vice versa have no effect, and other times it is a massive affect.

iratik, I'm no great expert, but there's an issue in that you're referring to fields from two tables in your WHERE clause.

eh?

Well . . . I don't know, maybe I don't know enough to talk.

what do you mean?

that is what I need. That automatically when a new record is added, to substract 6 hours from it

Do things like low or high priority in a query get replicated to a slave from the master? Or is it lost?

But you can't have an index across tables. Your WHERE clause imposes conditions on i.ts, i.pickup, and o.state.
Hmm.

are you trying to workaround a time zone problem?

If you added a key (ts, pickup) to loads, I *think* your situation would be greatly improved.

Adding an index to a table that already has that many is no small matter

Yeah, I know.

the pitfalls of over-indexing are well documented

Don't take my word for anything, I'm not some MySQL genius, but basically, as far as I can see, what it's currently doing is selecting all rows corresponding to Georgia from your database, irrespective of date.

wwalker; I am getting information from some device. I need GMT -6 and it send GMT

Then filtering out the bad dates. Then sorting what's left. Then limiting it to 65 results.

iratik I think it's generating a multimillion row result and then trimming with the where clause instead of trimming the rows before the first join.

Right.

did you try moving the pickup line?

Whereas if you had an index on (ts, pickup), I *think* it would select all the rows corresponding to all states within the desired timeframe, in the correct order, then selecting the first 65 for Georgia.
If it were possible to have o.state in the key, it would be able to just select the correct 65 rows from the index to begin with and not look at anything else at all.
Oh, and I'm forgetting the bit about "deleted".
The key should really be on (deleted, ts, pickup).

??

Overindexing is a lot better than underindexing.
wwalker, I'm pretty sure the MySQL query optimizer would be able to ignore the redundant condition.
wwalker, I'm pretty sure the mysql query optimizer would be able to ignore the redundant condition.

it's not whether it's redundant, it's _when_ it's applied.
I only asked about the redundant in case it was a typo

could someone kindly help me with this — I want to select all the data from the row, but only where the email column is distinct.
I have this:
INSERT INTO $edb1table9 (email_address, first_name, last_name, zip_code, ip_address, unique_id, refererpage) SELECT DISTINCT (email_address), first_name, last_name, zip_code, ip_address, unique_id, refererpage FROM $edb1table8

hold on …. been on hold for 45 minutes with mediatemple

concerning indexes, is high cardinality usually better or low cardinality? is null for cardinality bad?

msbhvn, high vs. low cardinality depends on the data. Low cardinality indicates a lot of repetition or a small table. It's not what I would call "good" or "bad".

i think you are on to something

gotcha thanks,

I've got 6 table joins with multiple 10M row tables. in way less than 10 seconds on crappy hardware, but where I put constraints made a big difference.

its seeming that too many indexes can also be bad

tell me more about how i can apply this concept to this query … how should i move around the constraints

Is it possible a mysql table is corrupt but mysql isn't reporting it?

wwalker, you mean reordering the WHERE clause makes a difference?

0')) from the WHERE clause to the end of the "ON o.id=i.origin_location_id"

msbhvn, each added index slows down every INSERT, in particular. It can speed up SELECTs and has mixed performance with UPDATE and DELETE. It's a tradeoff. Ideally you want all your queries indexed, and that's usually preferable to leaving some uncommon/fast ones unindexed, but it depends on
your application.

reordering the where clause, doubtful, moving things into or out of an ON clause, more plausible.
iratik, is this on a dedicated server or on a VPS?

wwalker, I thought that ON clauses are equivalent to WHERE.

could someone kindly help me with this
I want to select all the data from a row, but only where the email hosting column is distinct.
it does select distinct on all of them

they are roughly equivalent
you need grouping

group by the email? — could you give me an example query perhaps?

select max|min(field1), max|min(field2), …, fieldn from tbl GROUP by fieldn

it's according to which version and other things. it _should_ not make a difference,but it can

well… both — but with what we are talking about …. its on a dedicated server

wwalker, ultimately, there just aren't the right indexes to support that query.

thanks thumbs

de nada

It's going to have to scan a ton of rows without a better index.

according to EXPLAIN, it will only look at 600 rows

i've also got it up on some sort of clustered VPS setup … but its alot slower than our self-hosted dedicated setup as of now

which are you testing on/?
but did you move the constraints?

iratik, it says 723 rows for the first table, 52 for the second. 723*52=37596.

Simetrical:

Thats what i was starting to think about earlier …..
how can i change that to reduce the overall number of rows scanned?

iratik, you need an index.

like I said

That's what they're for. Without indexes you need to scan rows.

0')) from the WHERE clause to the end of the "ON o.id=i.origin_location_id"

hmmm… play with it

then rerun the query. If it makes a significant difference, we're on to soemthing, if not, then Simetrical is right and you need more specialized indexes

By all means, try fiddling with the query first.

on Mega row tables add indexes is a time consumer

Yeah, that's true.
It's why you need to do EXPLAIN before they become large.
For a couple million rows it shouldn't take more than a couple of hours to add the indexes if you do it right, but it would be potentially a bit tricky to do without downtime.

I've had explain take 5 minutes to run before (had a developer that thinks sub-selects are wonderful)

Heheh.

and it could take his app down for the whole two hours.

Yes, as I said, tricky to do without downtime.

table type?

InnoDB

how long did the modified query take?

well…
unfortunately i got the bright idea to run optimize table loads
not realizing that as indexed as that table is … it takes a while
so i've got another copy of the db on a shared setup
but for the same query it takes 1 min 32 seconds

so modify the query and run it on the shared and see if it improves

iratik, with the index I suggested, I bet you five bucks it will be under one second.

pickup

(deleted, ts, pickup)

hmm
why not
i'll set it off… the behnchmark was 1min 32seconds …. hopefully once i check it in the morning.. the index will have helped it
thank you all for your help

how do i find a unique table entry ?
analogous to a perl hash ?

select distinct ?

iratik, i am comint late to this discussion

"The ALL, DISTINCT, and DISTINCTROW options specify whether duplicate rows should be returned. If none of these options are given, the default is ALL (all matching rows are returned). DISTINCT and DISTINCTROW are synonyms and specify removal of duplicate rows from the result set."

iratik, i do not agree with Simetrical that index on (deleted, ts, pickup) would be any good, unless relatively few records have deleted=0

(ts, pickup) are necessary, though, surely?

iratik, also i did not see if anyone has already pointed out that the join to o does not have to be OUTER
Simetrical, yes (ts, pickup) is useful. but the "deleted" as first column will destroy its usefulness for the ORDER BY

Why? ORDER BY can use any suffix of the index used.
It just has to be the last part of the index that's used
..

is there a way to set a global variable , similar to "Set @foo = 'myValue';"

Simetrical, in what universe ?

"The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause."
.. . . SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2"

hello people?

ask

"I have a question", Don't ask: "Is anyone around?" or "Can anyone help?". Just Ask The Question. Also, please read: http://workaround.org/moin/GettingHelpOnIrc

Simetrical, that is more advanced than i expected from mysql

can a composite multivalued attribute contains also a multivalued attribute?
can it contains a multivalued attribute the composite multivalued attribute?
or can composite attribute contains a multivalued attribute?

5000000 AND field2 BETWEEN 2500000 AND 7500000 ORDER BY field1, field2;" does use an index on (field1,field2) for both retrieval and sorting. Yay.

anyone know why i get a syntax error on http://pastebin.ca/664995 mysql administrator generated it…

(gnari disses mysql but uses it.)

my gosh, people here doesnt know Entity Relationship Diagram…..

is there a way to comment each field in a mysql table?

threnody, not at all. i was genuinely impressed by this

giva a description

you can add a comment to each field

ah that would be what I need
I am up to 77 tables and it is getting easy to get lost

let me doucle check, cannot really recall of I am correct
table comment is possible, let me check the field

As a DBA with a few months' experience, the major thing I wish I had is cross-table indexes. And also the ability to ALTER TABLE live in the background, and backup live in the background without shelling out for InnoDB Hot Backup.

anyone?

!m ccherrett create table

ccherrett see http://dev.mysql.com/doc/refman/5.0/en/create-table.html

I
?

search for comment, field level comment possible too

NemesisD, the foreign key looks dodgy

gnari, how so?

innodb live backup is possible without hotbackup

NemesisD, wait let me look up syntax

that is for Simetrical
innodb live backup is possible without hotbackup

kimseong, okay, that's true.

gnari, wait should it be `projects` (`Project ID`), that being the column that is referenced

Fair enough, mysqldump works.
It's just really slow to restore.

restore in advance
and generate the tablespace

So to clarify, hot backup of some kind for all table types that can be immediately restored with mv or something similarly fast, on a per-table basis.

even if you use innodb hotbackup, make sure you know the correct procedure, I come across a user who pay for it but does not know how to use it correctly

For instance, this could be done by having MySQL make every write to copies of two tables, as if it were slaved to itself (is that possible?), and then you could stop writing to the storage copy while it's copied, copy over all changes in the interim and proceed.

replication

LVN snapshots?

Yes, if you have more than one server. Unless mysqld can be slaved to itself.

NemesisD, usually, i would ADD CONSTRAINT `constraint name` FOREIGN KEY (columnlist) REFERENCES table

m

seekwill, that's what I'm probably going to be using.

Simetrical:replication works on single server hosting too

But it took a lot of searching.

NemesisD, or ADD CONSTRAINT `constraint name` FOREIGN KEY (columnlist) REFERENCES table(targetcolumnlist)

kimseong, I have to run two servers then, using up a ton of extra memory when really I want their caches to be shared.

gnari, wait why is target column list after references, wouldn't that be the source columns, the 'referenced' cols

is there an sql statement to INSERT only if an existing value doesn't already exist?
i currently SELECT first, then INSERT

NemesisD, yes

but because it's not atomic, a value can be inserted inverveningly…

insert ignore, if you have a unique key on the column

NemesisD, they are optional, and default to the primary key of the referenced table

gnari, oh ok

jablko, nothing is atomic unless you're using transactions, in which case SELECT then INSERT is atomic.
(for a given definition of "atomic")

not necessary

NemesisD, i have never used the optional indexname after FOREIGN KEY before the localcolumnlist

Hello, I got a table with following fields (thread,userid). i want to get duplicated results which has same thread,userid. any idea ?

even if the select and isnert is atomic, that does not prevent another user to do the same at the same time

gnari, but how does it know which local col is going to be the foreign key

CalvadoS, select thread,userid from yourtable group by thread,userid having count(*)1;
NemesisD, that is the columnlist after FOREIGN KEY

gnari thanks let me check

NemesisD, ADD CONSTRAINT constraintname FOREIGN KEY (thistablescolumnlist) REFERENCES othertable(othertablecolumnlist
)

gnari but it returns only duplicated item. i want to see all duplicated items.
gnari, i mean i want to see duplicated items with default ones ( sometime duplication occurs more than one time

CalvadoS, so join this back to table

hm

gnari, can't create table './pricing/#sql-33fc_11.frm' (errno: 150)

gnari it may look abit lazy but can you make sql sentence for me

!perror 150

NemesisD, paste your query again, along with tabledefinition of referenced table

Foreign key constraint is incorrectly formed

gnari, ok how do i get the table definition of the other one

NemesisD, show create table foo ?

NEVER!

show drop table foo

i am just now realizing foo is a bot

Oh, I'm a bot alright.
Could a bot do that?

foo, that is exactly what a bot would do

You would *want* us to think you're not a bot.

at least, that is what i would do if i were a bot

I appreciate the clever delays.

gnari, http://pastebin.ca/665019

At intervals of exactly 14, 5, and 6 seconds.

i think you are the bot

Let's play a game of Mafia!
Let's have a town vote, who are we going to lynch as the bot?

Could a bot do this?

lynch me! lynch me!

NemesisD, i assume you have already successfully added the column `Project ID` to the table prices

intelligent bot

gnari, yep
foo has a neuronet processor, a learning machine

Is there any mysql equivalent to the 'intersection' concept?

AHA!

IN (a) AND IN (b) ?
or w/e.. don't know why I even tried answering that

NemesisD, did you make it UNSIGNED ?

doh, that did it
gnari, good catch

someone actually uses ZEROFILL

yes i think it looks snazzy

CalvadoS, sorry, i missed your request in all the talking
CalvadoS, select y1.* FROM yourtable as y1 JOIN (select select thread,userid from yourtable as y2 group by thread,userid having count(*)1) as foo USING (thread,userid)

NOPE

IN (a) AND IN (b)? I'm not sure how this would work. If I have a table with field values (1, 2, 3, 4) and a table with (2, 3, 4, 5) and a table with (2, 4, 6), I would want the result set to look like (2, 4)

CalvadoS, minus the extra select

gnari ok thanks

Sapph42, select x from t1 where x in (select x from t2) AND x in (select x from t2)
Sapph42, select x from t1 where x in (select x from t2) AND x in (select x from t2)

you might want to turn that script off
bah
you might want to turn that script off

good night, foo

hey guys i am having trouble to connect to mysql 5 from delphi2007 using the odbc driver could anyone help me

what is the error

catastrofic error
says nothing else

try the odbc test connection

it worked
but from delphi it wont connect

i can't help, never use delphi

ok thanks anyways

anone here?

ask

"I have a question", Don't ask: "Is anyone around?" or "Can anyone help?". Just Ask The Question

How can I authenticate mysql users using pam?

now DARKGirl is in here…
watch out for silly questions.

if i have a monetary value, can say mediumint or something handle cents or should I go with something like decimal(7,2) ?

DECIMAL works well

Is DECIMAL any slower than INT?

it consumes more space.

In 5.1, it seems only slightly so. decimal(7,2) will use 5 bytes instead of 4.
(6,2) will be 4 bytes, apparently.

eh theres not going to be a whole lot of data on here, one user

(7,2) should be 4 bytes right?

!man adding new user

see http://dev.mysql.com/doc/refman/5.0/en/adding-users.html

Apparently it's 5, in MySQL 5.1. The pre- and post-decimal parts are stored separately.

it is always separate even in 5.0
5 digits is 3 bytes, 2 digits is 1 byte, so 4
7 includes the 2 decimal

"SELECT groups.id, name, level, relations.userid FROM groups LEFT JOIN relations ON groups.id = relations.groupid GROUP BY nombre , how can i make that when it groups the results, it shows on userid the user i want instead of the 1st user ?

hey guys

lol

oh no.

(scrambling for the exit)
'Is quark soup salty?' 'Are indexes left handed?'

Is there a way to authenticate mysql users using pam?

hahaha

Hi, someone can tell me where can found mysql documentation

www.mysql.com

lol

thanks, i am new on this area and want learn about this issues

o_O

exists some certificated support for Mysql

hello
need some advice

some site or company for get support about implementation or customs error

Serch-Net: you can buy support from MySQL AB
Serch-Net: http://www.mysql.com/company/

i am working on this db where i will have a form that will save radio button choices should the save be one field with comma seperated items or each item in its own field/

excelent, my company want change their database to mysql hosting from postgres and they need some kinf support for make this

Serch-Net: tell them threnody sent you. you'll get a 0.0% discount.

It's probably a better idea to save each value in it's own field

theres going to be about a dozen fields if i do

anytime it occurs to you to use comma separated values in a text field, stop! Seek help!

ok

really, thats sound good,

ty for the advice ill follow as suggested

normalization

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html and http://datamodel.org/NormalizationRules.html and http://mysqldump.azundris.com/archives/20-Nermalisation.html and some here http://www.keithjbrown.co.uk/vworks/mysql/

I see no problem with having a dozen or so columns… you could also have a related table with key'd by the autonumber of the main table and the option key

Serch-Net: you need consultancy for migration, not support

asdf3:^^

ty

!tell asdf3 about normalization

asdf3 http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html and http://datamodel.org/NormalizationRules.html and http://mysqldump.azundris.com/archives/20-Nermalisation.html and some here http://www.keithjbrown.co.uk/vworks/mysql/

Serch-Net: Why do you want to change from postgreSQL to MySQL?

So, (so I can quick asking/searching) is there no way to auth mysql users against pam? my users are kerberos authenticated, so it would be nice if when their kerb pass changes, their mysql pass does as well

what´s wrong? (SELECT name FROM customers ORDER BY name LIMIT 1), (SELECT COUNT(0) FROM orders)

yes, i need support for migration process by some bring up problem during the process

what are you trying to do?

Is there an existing SQL statement that combines "INSERT" and "UPDATE" into one? That is, "Here are some values. If the row exists, update it to these values; if it does not, create it with these same values."

Vlet 2 SELECTS in the same query

!m apetrescu insert on duplicate key

Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/insert on duplicate key

I did it one time but i dont rember how..

does anyone have the compiled version of mysqldb?

!m apetrescu insert

The manager want change because someone advice that mysql has more support and feature that postgres

i am unable to compile it

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

I've never heard of using PAM to authenticate mysql users, who are distinct from system users. Of course, anything's possible.

and no one is able to help me with my issue
I have been searching on google for hours
with nothing that works

Serch-Net: Ah okay.

so you would want the result to be a one line result set consisting of "someone's name, 234" where 234 is orders?

http://dev.mysql.com/downloads/

Vlet yes
you are right

insert on duplicate

and does your order table have a customer id field?

no. MYSQLDB

Ah! Thanks guys
That looks very promising.

not mysql

What is MYSQLDB?

insert on duplicate update

don;t know whats that

Do you think that is a correct decision??

Vlet yes.. but i want in this query the number of all orders
not just the customer name of first query

it binds python to mysql

just a test

You want to look for "connectors"

Serch-Net: Depends on your own requirements.
Maybe yes, maybe no.

see near the bottom, got api for python

http://www.google.com/search?hl=en&q=mysql+vs+postgresql

What search terms did you put into Google?

yes
i did
gods i did

oh, but if you limit 1, you realize that you're not specifying which customer, so you'll only be showing the first customer alphabetically

In your experience what recommend me?? which is best??? mysql or postgres

select customers.name, count(orders.*) from customers join orders on orders
oops

(SELECT COUNT(0) FROM customers) as customers_count, (SELECT COUNT(0) FROM orders) as orders_count;

none of those will compile
I need the compiled version for os x

Serch-Net: They both have different strengths and weaknesses, it depends on your application and individual need.

i don;t use python so don't know

"which is better, linux or windows?" - Depends on what you need.

and i don't use os x

oooh.. if you want them in the same row like that, you could do: select x.value, y.value from (select count(*) from customers) as x, (select count(*) from orders) as y

Vlet oh yes! thank you! i'll test it

Thats right, i believe that we need make some analysis before make this change, the main strengths is the avalability, replication or fail tolerance functionality,

Yep, lots of analysis is needed.

Mysql has some of this features (replication or fail over mechanics)

Vlet thank you friend, it works!

yay - glad to help

how do i stop root access remotely?

Run mysql_secure_installation

Do you have multiple records for root in your user table?
assuming you do, I would just wipe the record where user = 'root
' and host = '%'
then flush privileges
but that's me, and I do things backwards sometimes

i dont think i have more than onc record

thanks a lot, see you later
bye

see ya

anyone can see if my installation has db?
# ls /var/lib/mysql/mysql/*.frm
/var/lib/mysql/mysql/columns_priv.frm /var/lib/mysql/mysql/procs_priv.frm
/var/lib/mysql/mysql/db.frm /var/lib/mysql/mysql/tables_priv.frm
/var/lib/mysql/mysql/func.frm /var/lib/mysql/mysql/time_zone.frm
/var/lib/mysql/mysql/help_category.frm /var/lib/mysql/mysql/time_zone_leap_second.frm
/var/lib/mysql/mysql/help_keyword.frm /var/lib/mysql/mysql/time_zone_name.frm
/var/lib/mysql/mysql/help_relation.frm /var/lib/mysql/mysql/time_zone_transition.frm
/var/lib/mysql/mysql/help_topic.frm /var/lib/mysql/mysql/time_zone_transition_type.frm
/var/lib/mysql/mysql/host.frm /var/lib/mysql/mysql/user.frm
/var/lib/mysql/mysql/proc.frm

Do you see the db.frm?

seekwill, no
this is mysql5
seekwill, how to initialise?

?
Not sure what you're trying to do

seekwill, i don't have any DB

When I asked if you saw db.frm, I was asking if you saw the db.frm file, because I do.

seekwill, mysql -p doens't work

doesn't 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!

# mysql -p
Enter password:
Access denied for user 'root'@'localhost' (using password: YES)

You have the wrong password

ls db.*
No match.

Use the right one, or reset it if you forgot it
No
Leave that alone

i have just reset it

Well, if you did, you didn't type it in right
How did you reset it?

# mysqld_safe –init-file=~/mysql-init
cat ~/mysql-init
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('XX');

how do I fix tables that have this error when I run myisamchk? myisamchk: warning: 9 clients are using or haven't closed the table properly

That's not the purpose of the –init-file option.

Xgc, http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

Ignore that comment.

ok

Did you see any errors?

no
this is fresh mysql5 install
the only error i see is

What behavior is the problem?

l# /etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.
Xgc, see the 3rd line
ohhhhh
it works now

That doesn't sound like a (successful) clean install.

previously, i get

Hmmm… unless that's just an informational message.

'Checking…'

Xgc, seems it's an informational message
previously, i get
# mysqld_safe –init-file=~/mysql-init
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[1837]: started
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[1859]: ended
so it's a failure

might need to delete a leftover pid file.

now

show databases;

+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
+——————–+
2 rows in set (0.00 sec)
is that correct? healthy?

That's fine.

mysql5 ships only with two dbs now?
no test?

You can quite easily create one

That could be optional.

mysql does not start when the computer does!
how do I remedy this?
btw, how do I start mysql manually

Depends on the OS.

Unix (os x)

ok real quick refresher
insert into table (field1, field2) VALUES (val1, val2)?

Yes, what about it?

just making sure my memory still serves me right
some of these guis make a brotha lazy

hey guys.

hey girls.

(sigh)
Can't connect to local MySQL server through socket '/usr/local/mysql/run/mysql_socket' (61)

what is the "approved" way for storing passwords in a DB?

md5 them.

md5 hashes, probably.

sha1

…

Excellent source of fiber

MD5 or SHA1 with a salt.

A salt?

Yes.

Pepper works better
Salts don't do a thing

Like, MD5( CONCAT(userid, '-', password) ) .
That way you can't use rainbow tables as easily.

No real need
What's a rainbow table?

A map of the MD5 values of common words.

Oh.
Problem is, you don't need to know the password. Just the hash

What do you mean?

Nevermind.

The point of a hash is to be irreversible. Salted hashes are a lot harder to reverse than unsalted.

a rainbow table is a way to steal your password.

No, it's a way to decode a hash.
It doesn't help with stealing passwords to start with, only decoding a password hash you've already stolen.
Something like John the Ripper works well for decoding hashes too.

I'm developing a PHP web application and I have a query like this "SELECT SQL_CALC_FOUND_ROWS … LIMIT …", so that with a "SELECT FOUND_ROWS()" after I can see the number of total results, as it would be without LIMIT. Now I changed my code and I use a prepared statement for the first
query, and the second query does not work anymore. Any suggestions, please?
Probably I have to do the query on the same statement, but I don't know how can I do it.
doing the same thing using JDBC in another application I developed, worked.

is there any problem with changing the drive letter when migrating a drupal installation from one server to another (win2003, root access)?
(I'm getting page not founds for everything on the new server)

How many characters does the password() function produce?

kaje1 41 bytes

thanks!

am i missing something with this?
insert into table (field1, field2) VALUES (val1, val2)?
should it be ('field1','field2')?

no, it is ok. are you getting a syntax error?

how to create a user for a certain db?

qiyong http://dev.mysql.com/doc/refman/5.0/en/adding-users.html

NiLon, thanks

no
$query = "INSERT INTO bet (week, user, $pick score, date, modified) VALUES ($cleanweek, $user, $pickval $cleanscore, ". date("Y-m-d H:i:s"). ", ". date("Y-m-d H:i:s"). ")";
its not syntax erroring and its not intserting

drop your dick

drop your lsm

Hi

heres the value output of the actual insert i am trying
http://pastebin.com/m2ada940f

haha

We have a server that crashed (unknown reason). When brought back up, mysqlcheck returns an error:
Incorrect information in file: './actinic_production/projects.frm'
Does this look like I need to restore the data directory or is this fixable?

and theres no error….?

fraid not

have you checked?

Hello

hey
where could I find information on mysql's query cache

Can anyone give me information on the "incorrect information in frm file" error?

!man query cache

see http://dev.mysql.com/doc/refman/5.0/en/query-cache.html

!man table full

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

should I be thanking seekwill or the_wench

You should be thanking the community effort here at #mysql

Can I use a prepared statement, but fetch an array for a result instead of binding variables to separate columns?

I just discovered searching on google that doing "SELECT SQL_CALC_FOUND_ROWS … LIMIT …" and then "SELECT FOUND_ROWS ()" is slower than doing "SELECT … LIMIT" and "SELECT COUNT () …". So I would like to change in an application I am developing the first approach with the second one,
but I have a doubt. Should I lock tables or disabling auto-commit to be sure to get the correct COUNT? What will happen if some data will be inserted or
removed between the two queries?
well, probably it is not a problem. I am just thinking now that it is in a webpage, and I do the SELECT COUNT () for the pagination, and after the page is loaded, the user can click to see a page, that maybe will not exist anymore, if the table changed. I guess I can do it without locking
anything.

GionnyBoss how about limit?

grant all on db to usr1 identified by 'password';

No database selected
what's wrong?

what do you mean?

what do i use for a mysql boolean data type?

int(1)
or tinyint(1)
(not null, and unsigned)

hmm
i remember hibernate generating something else
like bit

I dunno that's what I use
i am pretty sure there isn't a boolean type

yea tinyint(1) looks good

snoop-, you could use CHAR(0) NULL.

screw char
:-)

'' = true, NULL = false.

haha
dam ineed to pee now that was funny

I bet that's more efficient than tinyint.
domas was the one who suggested it to me.

theres no reason an insert ignore would be limited to 52000 rows right?
wait nm i proved to myself that this insert is working properly
and past row 52000

char(0) null for boolean? it save some space since this is really 1 bit
there is bit(1) now in mysql 5.0

there is bit(1) in mysql ??
what's this useful for?
and btw… what's "key distribution" in MySQL?

hello i am currently doing a query like SELECT *,count(id),MAX(id) FROM channelpollvote WHERE channelpollid=2 GROUP BY vote; but is it possible for MAX to show the max results for the WHOLE table, and not just the increment of the group bys ?

erm?

MAx doesnt show the whole table count untill it gets to the last group.

I don't understand what you're asking.

ok say if i use a group and it brings back 5 results, for example the count(id) would be 2 | 5 | 3 | 5 | 6 well then the MAX(id) on each result would be 2 | 7 | 10 | 15 | 21
i want it to be 21 for ALL of them

schnoodles, you could do it like SELECT *, COUNT(id), (SELECT MAX(id) FROM channelpollvote WHERE channelpollid=2) FROM channelpollvote WHERE channelpollid=2 GROUP BY vote;
You might also consider GROUP BY vote WITH ROLLUP (I think that's the right syntax).

well…I'm still confused

oh that multi select works perfect Simetrical do you know if its slow at all ?

how can i check if mysqldb was installed properly??

…
that's pretty vauge.
what are you looking for exactly?

MySQLdb (a Python DBAPI 2.0 module)
blah, nvm
it works

But of course it does

i was using wrong syntax

In a PHP application I am developing, I have a BIGINT value that I want to insert in a database with a prepared statement. I can only see integer, double, string and blob as datatypes when I want to bind_parameter() my prepared statement. Which one can I use for BIGINT, please? Sorry if I am
OT.

SELECT uname FROM user WHERE (SELECT puname FROM posting WHERE posting.pu

name = user.uname);
I'm getting the error "Subquery return more then one result"
why would this happen?
a subquery can only return one result?

In that context
Use a JOIN instead

hrm, well I'm curious though, why would this happen?
I'd like to be well versed on subqueries here.

SELECT uname FROM user WHERE pname = (SELECT pname FROM posting WHERE …)
Or maybe it was pname IN (SELECT …)
It's all about syntax

the = will still error
unless it does not return more than 1 row

I prefer my derived tables and joins

much better with joins

Yup

Hi there, Im trying to do an ALTER TABLE, I want to make a table column UNIQUE, but I cant get the query right.. I tried "ALTER TABLE all_extensions ADD UNIQUE extension" but it gives a query error.. how do I do this?

!m phoenixz alter table

phoenixz see http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

kimseong, already checked the documentation, but it lacks a clear example.. thats the problem..

read the syntax
you need (colname)

schnoowork, it's basically just two queries written as one, is all. It shouldn't be remarkably slow.

kimseong, The table name is all_extensions, column name is extension.. It tells there is an error at ""… so I don't think the problem is the column name, is it??

add index (colname) you probably left the ( )

THAT did the trick

a subquery can't return multiple rows?

can

why am I getting this error then?

there is restriction depends to usage

weird.

at the location, only a true or false is allowed, since WHERE expects to see a true or flase
*false
!man subquery

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

ahhhh

depends to where you use the subquery

yea.

hi
sorry i have a few noob questions =[
ummm
how do i access the mysql CLI in ubuntu
to start making db's

mysql -u root -p

mysql

thanks simetrical
how do you set the root password?

mysqladmin -u root password, if it's not set already, IIRC.

hi
is it possible to delete duplicate keys in a table from Mysql directly instead of usin php for example?

andrew@andrew-laptop:/var/www$ mysql -u root pass
Unknown database 'pass'
did i do somethign wrong?

yes, you dont specify the password on the cmd line

ecaandrew, you need mysql -u root -p. It will then ask for your password.

ohhh

(that's what -p does)
Otherwise anyone running ps could see your password.

crap
its denying my password
hehe
im a huge time noob when it comes to this
trying tolearb
trying to learn
mysql -u root -p
Enter password:
Access denied for user 'root'@'localhost' (using password: YES)

ecaandrew, the password is wrong.

it just let me in without entering a password
how do i set a password?
mysql -u root -p
i hit enter and it worked
without a pass
thanks for your help btw

how do you rename columsn in MySQL?
I thought it was AS ?
oh yea…it is ..

can I do this in mysql 4.024? NSERT INTO bad_table(id,name) SELECT id,name FROM bad_temp;

yea
er wait
4.1 +
see http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html

I was afraid that
Athfar I can use it

how do you rename a table?

drop and create?

i think it works for certain types of sub queries

but all the data in the table will be lost

Athfar, yes. But the one I would like is also supported
backup?

airj1012, http://www.google.com/search?q=MySQL+rename+table

TO new_name

and yes google shit

Athfar, http://www.google.com/search?q=shit

http://www.google.com/search?q=smartass

anyone have experience installing phpmyadmin on ubuntu
i cant figure it out…. =[

a "standard" join is usally an outer join, correct?

ecaandrew, sudo apt-get install phpmyadmin ?

tried it
lol
didnt work

I thought it was INNER

O10xz, JOIN = INNER JOIN = CROSS JOIN.

interesting.

OUTER JOIN = LEFT JOIN = LEFT OUTER JOIN.

sweet I win a million dollars

yeap. okay.

Or so I infer from the pretty chart on http://dev.mysql.com/doc/refman/5.0/en/join.html

LEFT JOINS are usually the standard
I win

uh?? define standard

LEFT JOINs are the standard if you like slow queries with lots of extra rows that are probably meaningless for your application.

me.
lol

is it possible to compare two tables and remove in one every row with the same id than the second one?

I'm just trying to be a jerk.

by mysql I mean

I'm going to be now, g'night all

O10xz, you need to try harder.

lol, note taken.
nighty night all.

I can guess in the mysql version 5 you can find something but not for 4.024

wfq, the first way that occurs to me uses three queries, and assumes you have a unique key somewhere. There are probably better ways.
Hmm.

i msised the question

Simetrical but you mean make a join and pass it to a third query to remove them form the table

More or less.

but I can't do that in 4.024
I mean in one statement

Not sure if it's possible.
Hmm.
Well, no, DELETE FROM . . . JOIN works, doesn't it?

Hmm I really don't know

DELETE FROM table1 JOIN table2 ON table1.field1=table2.field1 AND table1.field2=…….

!man delete

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

that will syntax error

Hmm, seems not.
Rats.

it should work with the correct syntax

Simetrical,yes you can do that but the manual specifies for version 5.0

!man41 delete

see http://dev.mysql.com/doc/refman/4.1/en/delete.html

wfq, multi-table delete syntax works for 4.0, but it's not what you need anyway.

i thought that should work
with a left join

Kimseong, even so I just have 4.024

if multi table delete is in 4.0, whynot

it could be

hello. question. I have an "internet" db filled in online. I need it localy to edit docs. But my website host doesn't accept direct connection to the db. How can i import it localy without having to use phpmyadmin (i actualy export the db and create it localy)?

How can I spit out the ignored tables? I want to make sure they're being read from my.cnf

what's wrong?

grant all on db to usr1 identified by 'password';

No database selected

!man grant

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

morning

kimseong, should I create a user first then grant?

qiyong no difference

how can i look up column names in a particular table?
just names, no data

describe table

oh that command i didn't know at all.. many thanks!

kimseong, should I select a db before grant a user?

no need, please read the syntax carefully

no need, the db you grant rights on is mentioned in the grant statement
and never grant more rights than absolutely needed

but if that is ignored, it will use the default database
and usually that happen as a mistake

hm i'm having a trouble when writing subquery.. what is wrong with this line?
select name from table where id = (select id from table2 where id2 = 10);
i also tried to replace = ( with IN ( but it didn't help
yesyesyes! fixed

robboplus, you tried this?: select name from table where id IN (select id from table2 where id2 = 10);

is there anyway to look a for a keywork throughout the table
?

gnari many thanks! just fixed that at the same moment

well I know if I use each field as field1= or field2= or… or it is ok

it was another problem btw - the query didn't work without table name for some mean reason
but yes, IN was also needed - thank you again gnari

Hi guys. I have an one question. I need mysqld. then I build to mysql source(ver 4.1.22). But i can't completed and i see error msg to 'item_func.cc:2174: error: `gettimeofday' was not declared in this scope'. anyone. I want help. please tell me answer.

wfq, usually if you need to do that, it is a sign that you need to normalize

gnari do you know why it didn't like select from table but it did like select from db.table? why would it matter?
table name was character btw - it is a reserved name and that's why?

mysql databasename ?

cat database.sql|mysql databasename

i know about mysql database.sql but i'm trying to get the database from a stdout
just like that?

use the pipe Luke !

so i could also echo "drop database bla" |mysql

yup

cool

I sometimes do mysqldump -h localhost|mysql -h otherserver
to transfer databases;

robboplus, yes

i'm doing the same
but trough ssh

when i specify int(11) is that 11 digits or 11 bits?

robboplus, you could have quoted the name, also

11 digits

Bheam, even better, bo not specify it
Tini_cpn, do you have a reason to build such an old version?

*g*

ERROR 1006 (HY000): Can't create database 'web17_db1' (errno: 13)
this database was created by ISPconfig before…i never heard about a way to force a DB to be not createable.is there something under mysql

errno 13? wasn't that about rights?

ah.. current ver is 4.1, but I wil up to version.

iam root..so not.

something filesys related i believe, not sure

wow.

nobody cares what you are, question is, what mysqld is allowed to do

any way to check the structure of mysql ?
iam root nder mysql..i have full right

no
i mean, what mysqld is allowed to do on your drive

ah !
one second

good day lads
have a question about updating a field in a table, which i'm using to select pull up values from for a subquery
i get an error that i can't select from the same table
i think i need to create a copy of a table…
how do i do tha?

perfectly…found it
thank you

is it possible to use subquery in SELECT part of query? e.g. select name,age,(subquery) from table; ?

yes

xored oh wow, so that really works?

yes, thats what the word subquery is for.

xored but i only used it in WHERE part so far

depending on your version of mysql

all the other ways would rather be joins
just check the docs

so "mapping" certain field with other table would be possible then.. damn got to try it

one second
better look for "LEFT JOIN"

hm
that i had no clue about at all he he

to "link" to tables by an ID or filed,you use joins

hm

%s/filed/field
its like

could someone help me rewrite this query please http://pastebin.com/m587be655

i'm looking up some data with select but one of those columns is "cryptic" - just id, and then i want to look it up in another table to "map" that id to a real name

select * from table1, table2 where table1.id=table2.id;

check syntax for insert into for copying tables

why need to rewrite?

hm… that's really interesting example
anyway it doesn't apply to me in this case

you would get all field of both table in "a row" linked by the ID. so this would be something like the user tabel and getting the user details out of the user_detail table identifieg by user_id

thewizkid, do you have a link?
kimseong, fix? ^_^

customize

http://dev.mysql.com/doc/mysql/en/insert.html

the only thing that comes up on google for "mysql insert into copy table"
is copying tables

xored many thanks - got to think it over and cut some leet query

El_Neng, your problem is that the subquery is not correlated to the updated table

thewizkid, i am not inserting tho…

8) El_Neng: i think i need to create a copy of a

xored joins are so simple that i just don't get it lol
xored still can't understand how that works

gnari, could you elaborate on that please?

xored just trying to write the where part

El_Neng, explain in words what you want as the sql is not obvious(wrong)

gnari, does it have to be like 'UPDATE prblm_credit SET c.TVBV_ID=(…'

UPDATE prblm_credit TVBV_ID=( SELECT cc.channel_id FROM prblm_credit_channels as cc WHERE prblm_credit .KOD_KANALU=cc.channel_number AND prblm_credit .KANAL_REPLIKACII=cc.channel_replication AND prblm_credit.MFO=cc.mfo)

xored what i don't get is what do i select - if i go for column from table1 then i won't get that value mapped onto the other table, right?
mapped == linked
select name,age,(this other thing that has id here and name in the other table) from table1,table2

El_Neng, what you had before, is called an unconstrained join

El_Neng update will set every rows to the same value, seldom anyone do this

UPDATE prblm_credit SET TVBV_ID= (…

kimseong, you're right, i tried doing that yesterday and it set every row to the same value

good morning

is that what you want?

no
i don't understand why it does it

you need to learn more then, we cannot help when we do not know what you want to do

El_Neng, did you try my query?

can i try and explain?
gnari, it's doing it now - there are about 500,000 records

I have a little problem. In my query I am using a LIKE expression. However if I say for instance WHERE LOWER(name) LIKE '%rudolf%' it still would not match anything unless i capitalize the r in rudolf… anyone knows how i can make a case in-sensitive LIKE search?

El_Neng gnari setting every row to the same value?

UPDATE prblm_credit SET TVBV_ID=( SELECT cc.channel_id FROM prblm_credit_channels as cc WHERE prblm_credit .KOD_KANALU=cc.channel_number AND prblm_credit .KANAL_REPLIKACII=cc.channel_replication AND prblm_credit.MFO=cc.mfo)

hi guys!

hi, i am thinking whether to use an INT or TIMESTAMP to store the time in mysql. what would you suggest?

does anyone know how to keep an application with a single connection to mysql for a very very long time?
this is because i have developed an application with the MYSQL API, and it seems to stop working after some period of time

use timestamp

cjk, if you store it in INT you won't be able to use mysql's date comparison functions

ok, thank you guys

though, i need to keep the program running for a extremely long time, like 6 months or more

kokozedman, just open and close the connection as needed, or check and reopen if needed
kokozedman, I open and close

archivist, how do i check whether it's open or not?

kokozedman, Im running 24/7 the channel bot in here
the connection times out

yes, that quite probable
but i have already set the auto-reconnect option, and it still doesn't seem to make it

gnari, kimseong did you manage to come to a consensus?
i can explain what i'm doing here…

Hi! I want to rename a database, I was thinking to just recreate it and import it again using mysqldump. But what are the 'advised' options for mysqldump?

El_Neng, i can explain what went wrong in your query

kokozedman, by disconnecting and reconnecting on the next qry you can garantee the state (connecting has a low overhead in mysql)

yeah, go on. in the one i'm running now?

!man update

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

El_Neng, no , your original query first

oh
what is it?

archivist, how often do you recommend that i close and reconnect?

!man delete

i'm having around 60 queries per seconds here

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

UPDATE foo sel bar=(subqueryinvolvingajoinbutnotcorrelatedtofoo)
UPDATE foo sel bar=(plainsubquerycorrelatedtofoo)

kokozedman, then Im suprised it times out

I have a little problem. In my query I am using a LIKE expression. However if I say for instance WHERE LOWER(name) LIKE '%rudolf%' it still would not match anything unless i capitalize the r in rudolf… anyone knows how i can make a case in-sensitive LIKE search?

Hey guys, anyone knows where MySQL keeps its logs?

archivist, so am i

This is RHEL

gnari, ok i see. however, i thought, that the subquery would just pull up the correct id of the bank branch, based on its "channel_number" and "replication_number"
as a value

justmozzy, learn to use fulltext indexes

somewhere in /var/log would make sense

(Red Hat Enterprise Linux)
yeah, looking there, not seeing much…

justmozzy, looks like you have a case sensitive collation set

El_Neng, it would if you had has any reference to prblm_credit in it. unfortunately it only had recerences to c
references

what's best, executing queries with transaction, or without it?

kokozedman, depends on your app

archivist, this is because i'm having 32 threads, each doing around 10 queries

what logs, query logs?

collation: utf8_general_ci

kokozedman,are you doing updates?

mostly, but preferably I'd like to know about any logs.

yes, i'm doing A LOT OF UPDATES

kokozedman, use transactions

gnari, that BEGIN … COMMIT, right?
that's*

kokozedman, yes, or whatever your preferred language libraries use

query logs are off by default. not much else to log, is there?

gnari, i mean the SQL syntax

I'm pretty sure there's some form of MySQL logs clogging the hard drive…

gnari, would it optimize the performance of the Mysql Server? because right now, it's consuming around 3% of my CPU

find 'em with 'du'
or locate, or find

gnari, that's for around 60 queries per seconds

how would du help? can I get a list of the top 10 largest directories?
(on the entire hard drive?)

sure

justmozzy, your query cannot use an index for two reasons lower() has to convert each row and like '%blah%' cannot use an index, it will be sloaw as it scans the entire table