Translator (http://sqlfairy.sourceforge.net/)

ALTER TABLE { index } … 45 minutes and counting …

eh, i'm doing an index that's over an hour and counting.
i'm almost wondering if dumping and re-importing would be faster.

how many records, unique values and what kind of data?

how do I do this??? "SELECT group_concat(usage_hr \ ckt_insvc SEPARATOR ' ') from trunks group by city" ????

after i mysql_init(), and mysql_real_connect() fails, do i need to mysql_close() or will it do that for me?
uafter i mysql_init(), and mysql_real_connect() fails, do i need to mysql_close() or will it do that for me?/u

6.3 million rows, non-unique values in the index.

what happens when a myisam table gets to data_length = max_data_length from "show table status like almost_full_table" ?

20M rows … with approx 20K unique values on the index (int)

last time I added an index on a 200 MB table, it took a bit over half an hour…

ouch. this server i'm running on in a PII-400. i keep begging for an upgrade, still haven't gotten one.
s/in/is/

i'll probably make a table which has all those unique values, then have another table JOINED which only has the ID of that first table …

I'm assuming yours is bigger than 200 MB, but it also depends on cpu/hard drive speed

1.4GB of data (ex. indexes)

pizza_biz, this is when you call for OT.

the table is about 1.8GB, looks like.

pizza_biz, do the index creation when everyone has gone home, get extra dough.

i'm salary, no OT option.

sorry g.
its what we do here..but a lot of times i dont even clock in
so im withya

is there a point to stay during the whole procedure?
outside issuing the statement … there isn't much that can go wrong

Hi

not really. i'm futzing around with other non-db stuff while it runs. so unless it loses connection to the server, i'm not too worried.
done. after about 1:13.

hi tomwys

SELECT `id` , `url` FROM `pages` GROUP BY `domainId` (I want select one page form each domain)?

tomwys, if your domainid is indexed that will make it run faster maybe
explain SELECT `id` , `url` FROM `pages` GROUP BY `domainId`;

crap. i don't think the index did anything for this query.

that will tell you

I did something stupid. Yesterday I went to Del Taco for Tuesday Taco Night and bought 14 tacos when I could've had 15 tacos for the same price

arrrgh. it didn't.

off by one count is a problem with programmers

tomwys does it even run?

they count their pinkies from instead of 0
from 1

that makes me hungry

aoirthoir, I was explained it in the bracket

anyone have good reading material on how to determine an index that will benefit a query?

!man explain

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

so… del taco, ranks above or below baja fresh for you ?

Trengo, I'm writing program now and I'm trying to find the best way to write this SQL query

tomwys, no explain is an sql statement
tomwys, if you put explain before your select it will tell you which columns it will be indexed on

but to pick the keys … you'd look at your where clauses and choose from the fields mentioned therein

aoirthoir, ok

which uses less ram on a default install of both postresql or mysql ?

depends on the data involved…

i've read that, and i'm looking at the explain results for the query, but there's some line of logic i must be missing out on, possible_keys is NULL, key is NULL. do i consider the index based on the where clause of that query?

null, Extra: Using temporary; Using filesort - it's probably be very slow with many rows

may depend on packaging

for instance, i have "where ord_date='00000000' and ord_date='20070128' and store_num=1" — does this mean an index (ord_date, store_num) would help?

also any good ways to optimize mysql i really dont want it to use no more then 100 mb or ram here

I have an INSERT query that generate OOM errors on 750MB RAM

most likely

can MySQL be optimized pretty well ?

tomwys i know it doesnt run

define optimum

where my mysql server can take like 500 connection per/sec
very simple small data not alot

Trengo, It run now, but I will have a lot of data it this table, so I need some faster query.

Do you actually have the bandwidth for 500 connections?

tomwys the one you pasted cannot run

Trengo, why?

tomwys because you're mixing plain columns with a group by

What what!

one more (possibly stupid) question. does it matter if it's key (store_num, ord_date) or key (ord_date, store_num)? or is just for human readability to decide which comes first?

what happens when a myisam table gets to data_length = max_data_length from "show table status like almost_full_table" ?
I knew that would work
:-)

Can't insert any more data

pizza_biz, very important, depends on the query

swhat I thought, but wanted to be sure it didn't crater the dbengine or something
suppose I'm gonna have to purge that table down some

if you explain, you'll have 2 listening

Trengo, http://wrzut.tomwys.info/2047318093/a.txt

a table that's 70 rows is using filesort when i sort all rows by last_used, even though last_used is indexed

pizza_biz, well, in the first case, your query will use the index if it uses store_num, or both, but not if it only uses ord_date

You could rebuild the table so that it has a wider pointer
Though for a big table that's potentially quite time-consuming

I don't think I can schedule a service outage that long

tomwys then i must say "i know nothing"
im sorry

possibly better to lock; rename; create table; set index; unlock;

http://pastebin.ca/624876 — i suppose ord_date gets precedence?

hm. if you have a lock on a table and rename it… can you unlock?

Trengo, no problem. The problem is how to write it smarter.

lepine see trengos answer to pizza_biz

ah, cool, note taken
thanks guys

don't forget to thank the bots!

thanks bot.

might as well add women and children while you're at it

no "you're welcome." ingrate!

you said it wrong. try "thanks wench"

http://rafb.net/p/qafXw775.html
help?

http://img.photobucket.com/albums/v88/punkpig/clamp.gif
wtf

Pardon! please mind your Ps and Qs

I wanted to so a speed test on two similar queries, but I am afraid that some temporary tables are spoiling my plans. Executing the same query 2 times in a row makes the 2nd considerably faster. Is there any way to clear these temporary tables manually?

help