speeds up inserts/deletes/select's etc if your using the smallest type
which makes sense
ah, do you make use of staging tables

why doesnt this work Select * FROM foo1, foo2 WHERE foo1.field1=foo2.field1
?

at the moment I dont, I should I think - or plan for it
what error do you get nanotechh?

no error, just keeps returning random data

staging tables? like for ids or the like?

you probably need it to be select foo1.*, foo2.* from …
"random" data?
do you mean the data is unordered
I've read design processes where data is aggregated off other tables and then when it is ready, the live tables are switched out with the new copy
so that clients only do select's on a table and no writes occur on it

Thats neat

eg, the insertion of data is done on another table which replaces the live table which clients query so there are no concurrent inserts/selects
which I can see at the moment performance issues for me, where I notice if stats is doing an aggregation - it can tie up clients trying to render a graph for example

Plasma- like its returning data from any row in the field
so i guess its not ordered
heh

since an insert is in progress or theres a smal lbacklog
nanotechh, that query should be selecting all data from foo1 and foo2 tables where the foo1 field1 == foo2 field1
is that what you really want to do be doing?
Any tips on how big a table should be allowed to get before performance can be an issue (eg maintenance issues or just general inserts/queries)
im thinking 1 mill may be a cutoff point, just pulling that figure out of the air
since I can usually bring up graphs of data searching an 80 mill table within 1 second
not all data is retrieved obviously
only a very minor portion - but its searched well enough w/ indexes

yeah, like in the db 1 row will have alot of info in it from different colums and 1 colum is a reference number, i want the referecen number to then be searched for in foo2.references and give me all the data on that row

nanotechh, then that query looks right
perhaps your random data issue is because you're not ordering results
order by foo1.field1
then have a look and see if the data matches up

foo2.references is not a primary key
nor is foo1.

ah ok so thats probably why you're looking at randomish data as you say - results arent ordered so you may be seeing different matches each time when you notice the same reference #
add the order clause ^ and you should be seeing better results
also ensure you have indexes on those columns

Plasma-: totally depends on the queries, but I would say millions should be fine

Id really like to support adhoc plots as well
for example, being able to place a specific piece of data on a graph and start recording it would be great
(up to the user)
so im thinking I would need an arbitrary table that held something like (entityid, plotid, plotvalue) or something like that
but then you get a few joins to retrieve the stats (namely a left join, as some plots may not exist for a given entity) which may degrade performance

Plasma-: how about just replicating to a read-only db and selecting from there?

yeah thats a good idea
comments on a left join for stats generation/retrieval? Im a bit hesitant, but would be a great feature
eg, you have your standard plots using the entry table and its appropriate columns which are plotted

sounds expensive

but then a custom plot by a user (0 or more) for a given entity
yeah
im just worried the join may cause issues, I doubt it would really if it was indexed though
but I know you denormailize the stats tables to just avoid the overhead of joins
probably something I should look at later once I overhall the current tables

yeah the denormalization make the inserts more complicated
makes

yeah, i need to take that into account - think ill skip doing it for this time around
I want the thing to be lightning fast

Plasma-: sounds like fun stuff

need to solve the issue of concurrent read/writes though; unlike other situations I need stats up to the date 5 minutes ago
so obviously stuff needs to be added every 5 minutes
your idea to replicate to a readonly db sounds good

hope it helps

should be far less of a problem when the table size is down anyway

well, if you're mostly inserting into the new tables, then yeah, the issue isn't gonna come up for querying older tables

even the 5 minute tables, people will be querying
but with the size of the table reduced (archived stats no longer in the same table!) it shouldnt matter

Does anyone understand "group by" ?

Do Create User Exist for Mysql 4.1?
]The CREATE USER statement was added in php mysql web hosting 5.0.2 … The doc says so?

whatcha got?

spackes1, doing a group by only returns the FIRST element of the group..
I wnat the LAST one.
Like, say my column is "id". and I'm doing group by id. And I have rows with id=3,4,5. I want to group by id; and return the row with id=5, not the one with id=3.

can you add something dumb like AND id = (SELECT MAX(id)) ?

instead of the group by?

can you add something dumb like AND id = (SELECT MAX(id) FROM table) ?
in addition to
not sure what you're doing, so hard to say

group by id order by id desc

Plasma-, not working.

table and query?

Do a /j #flood

Do Create User Exist for Mysql 4.1?
]The CREATE USER statement was added in MySQL 5.0.2 … The doc says so?

apparently not according to that statement Mishu

Plasma-, So how i create user in mysql 4.1 Through query

dont know off the top of my head, you'd need to look it up
http://dev.mysql.com search the docs ?
http://dev.mysql.com/doc/refman/4.1/en/adding-users.html Mishu looks appropriate

INSERT INTO user

VALUES('localhost','monty',PASSWORD('some_pass'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

stuff like that by the looks, read that page

Okay!

hrm

what is errno 150 ?
is there a tool that explains error by num?

Can someone please help me figure this out?
I want to do a "group by", but I don't want the first element of the group!

what are you trying to achive
why do you want specific rows, for what purpose

I'm grouping by threadid
If you have a table called messages
threadid, time_added, message_content

I don't see it on the mysql hosting website

I want the message_content out of each unique threadid.
But only the most recent.

http://dev.mysql.com/doc/refman/4.1/en/error-messages-server.html

doing a select group_concat() works, but then I'm going to have to do some PHP parsing to get the last character.

this happens when i'm trying to create a table with FK in table definition

ok I think I get you

not sure on 150

where time_added = (select max(time_added) from messages where threadid = [outer query].threadid) something like that maybe

oh, as for that list, "1005" applies to me…
1005 SQLSTATE: HY000 (ER_CANT_CREATE_TABLE)
Can't create table '%s' (errno: %d)
the errno is 150

Foreign key constraint is incorrectly formed

Plasma-: where did you find this?

Use perror tpool.

yeh, perror tool

thats what i was looking for

Its just on a linux server I use so thats all I know

whats wrong with this? FOREIGN KEY `fk_machine_id` (`machine_id`) REFERENCES `machines` (`machine_id`) ON DELETE CASCADE

Note it explains errors reported at (errno: %d)

yeah, thats what i was looking for precisely forgot the executable name

poncha, using innodb engine yeh?

You can't give nams to foreign keys

sure
manual says theres optional "id" there
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

Also do you have indexes on `machine_id` in both tables?

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …) …
machine_id is PK of machines

perhaps remove the backticks `
ahh actually
perhaps

removing backticks on key id and removing key id completely had no effect
maybe i have to specify on update too?

CONSTRAINT `fk_machine_id` FOREIGN KEY (`machine_id`) REFERENCES `machines` (`machine_id`) ON DELETE CASCADE
try that

Do you have index on `machine_id` in the table you create FK in?
CONSTRAINT `fk_machine_id` — that's wrong

no. should i?

Only database, table and column names can be quoted with backticks

i have a unique key (machine_id, plus couple more columns)

s/should/must

ok, trying

Also columns must be of teh same type, but they probably are

how can i get any rows added comparing two tables (comparing them with 5 different fields)

adding index didnt help; columns are same type.. i'm gonna paste full table definitions for both tables

get rid of “s
CONSTRAINT 'fk_machine_id' FOREIGN KEY (`machine_id`) REFERENCES …

http://pastebin.ca/620116
remove all backticks?

You only need backticks when there are strange characters or you use reserved words as a names
Weird. Works for me:

create table machines (machine_id int unsigned primary key) engine=innodb;

Query OK, 0 rows affected (0.02 sec)

CREATE TABLE m (machine_id int unsigned, CONSTRAINT `fk_machine_id` FOREIGN KEY (`machine_id`) REFERENCES `machines` (`machine_id`) ON DELETE CASCADE) engine=InnoDB;

Query OK, 0 rows affected (0.02 sec)

hm
so what is so different in mine?
oh
stupid me

`machine_id` bigint(20) UNSIGNED vs `machine_id` bigint(20) SIGNED perhaps

i forgot unsigned on the second table on machine_id
apart from that works with the syntax i wrote, too

can someone please help me solve my problem?

got a partial solution, though it isn't all in sql

that's fine..

did you try what I suggested zackattack

Plasma-, ?what did you suggest

forgot your schema, but here you go
select distinct(tid) from table;

where time_added = (select max(time_added) from messages where threadid = [outer query].threadid) something like that maybe

hm

thanks for your help

then you can remove the group by clause
so something like

Does it work now?

then in php loop through a query like this select * from message where id in (select max(id) from message where tid = ?)

no
SELECT m.*
FROM messages m

the point is get the distinct stuff then pull out the biggest id for each distinct thing

WHERE m.time_added = (SELECT MAX(time_added) FROM messages m2 WHERE m2.threadid = m.threadid)
should show the latest entry for each message
thread*
you should have an id/pk field on the messages table btw, if you already do replace time_added with that column value
since timeadded isnt necessarily unique

you understand?

yeah, works fine

poncha, you should try mine as well
you dont want to be issuing O(n) queries depending on the # of threads you have

You can remove KEY `machine_id` (`machine_id`) then

err sorry, that was for zackattack not poncha

Since you have machine_id as first column in user_host index that second index will be neve rused

err
Remote I/O error
i try same create-table on other database, and table is created just fine
and all mysql datadir is on same partition of course
what should i do?

perror 121
Duplicate key on write or update

thats what perror said (OS error code 121: Remote I/O error)

It says both

besides, how can there by duplicate if i am just creating table ?
and same tabledef works in other db

You get that on CREATE TABLE?

yes

Are there foreign keys referencing that table?

no, but there is a fk in that table definition referencing other table

if a table is locked will it generate a mysql error?
when trying to insert

it will wait

oh ok

for table to be unlocked

and throw an error if it timesout (I think)

damn.. are constraint names supposed to be unique ? (in all the db, not just the table)
oh
yeah, it should

o/
i have some problem with mysqld on my gentoo box. i start it with the init script, get "* MySQL NOT started, proceding anyway [ ok ]", check "ps aux" and see mysqld stated "Sl", try to connect with the mysql console client and get ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
any ideas what i can do?

check log files

oh…erm..stupid me
/var is full

that won't help matters!

well, mysqld.err complains about it

I would expect so. /var/lib/mysql is where mysql stores the actual database

what are those localhost-bin.000010 files in /var/lib/mysql/ they are quite big?

binary logs

can i delete them?

probably. Usually they are used for streaming updates to a slave database hosting or to roll forward from backups
you can disable them in the my.cnf config file if you don't need them
and/or probably set them up to be purged after x days

outch, 636mb free now
i guess it's log-bin = 0 what i want?

might work… I just comment out the log-bin line

thanks a lot
o/

I am using QT 4.3.0 When i build using nmake in debug mode it works fine… But while i open a project file(generated using qmake -t vcapp) and try to build it in visual studio it give error

what does that have to do with mysql?

Can someone help!
Sorry!
I thought i was in QT
lol
Hi, How can i remove permission of some user?

still in mysql…

Like i use Grant for Giving permission, what if i want to remove permission..

REVOKE

i don't can find function for analyze table column lenght and type, please help

Hello
Is ORDER exequted before or after the WHERE clause?

after

thanks

SELECT * FROM tbl PROCEDURE ANALYSE()

yes, this, thanks weigon

hello guys, i would like to do sql hosting query's form bash for a spamassassin learning script
how can I do this

echo "SELECT * FROM tbl" | mysql hosting –user=… –password=… …

that simple lets test it, thx

hi… I have one table (node) which keeps articles (their id is in "nid" column) and another table, (url_alias) which keeps urls of those articles. they're "joined" by their "original url", which is in "node/nid" format (for example "node/155" if nid == 155). how would I LEFT JOIN those
tables?

you just answered the question yourself

that "node/" part of the value in url_aliases is my problem

why are you using strings ?
if you have the nid == 155 in both tables anyway

the point is I don't have nid == 155 in both tables
one table has nid == 155, other has src = "node/155"

and you don't think this is a bad idea ?

I'm working on a ready-made database… it's not my design
it's Drupal (which is really cool, btw)

please paste the SHOW CREATE TABLE tbl\G for the two tables somewhere

one sec
LEFT JOIN url_aliases ON node.nid = {'node/'}url_aliases.src

CONCAT("node/", url_aliases.src)
don't expect this too be fast

http://pastebin.com/d52d6b089
I don't need it to be fast…
SELECT node.nid, node.title, url_alias.dst FROM node LEFT JOIN url_alias ON node.nid = CONCAT("node/", url_alias.src) WHERE node.type = 'sentence' // it returns NULL in url_alias.dst

i got the query working form bash (easy) but got a question: when i do this: echo "SELECT path FROM view_users;" | mysql …
the first line of the output is the line with the text "path" how do i remove this?
better formulating how do i prevent mysql from outputting the first line with the text "path"

isn't that what a LEFT JOIN is made for ?
mysql –no-column-names

how can I get values from url_alias then?

thanks

I need values from url_alias.dst column

you know the difference between JOIN and LEFT JOIN ?

not really (sorry :/ )

–skip-column-names (:-D)

time to read the manual. feel free to come back when you read it

all right, thanks for your help

but i saw, mysql used independently execute plan if i had wrong theorethical use of LEFT JOIN

some LEFT JOINs are equivalent to a JOIN and can be rewritten

what would this do, select * from appointments, users WHERE appointments.uname= users.usern, appointments.datee between '7-' AND '8-' order by appointments.datee

it would throw and syntax error

oh
well replace that usern, with usern OR
; )

the second , should be a AND or similar

yeah, it is i just typed it wrong here