Re: prob on NT

[e c h o p l u s A T w o r k]

Hi Victoria,

>> echoplusAT> *I can't run a 16 bit program  the file setup.exe is
damage. Try to
>> echoplusAT> reinstall* (!?!?!?)
>>
>> echoplusAT> I've never seen this error ... anyone can help me in order to
solve this
>> echoplusAT> prob?
>
> May be you have a corrupted installation package. Try to download the
MySQL 3.32
>release again from : http://www.mysql.com/downloads/mysql-3.23.html

This seems do not solve my problem ... before I wrote my msg to the list,
I've tried to download 3 different package from 3 different mirrors and they
always give the same error ... well, now I'll try to install a previous
version of the server, but I think that is very strange to have 3 different
install. package damaged ...

anyway, thanks for your help
max




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

prob on NT

[e c h o p l u s A T w o r k]

Hi,
I've a problem in order to install Mysql server 3.23.47 on NT 4 box 
when I try to run the file setup.exe, I get an error msg like this:
*I can't run a 16 bit program  the file setup.exe is damage. Try to
reinstall* (!?!?!?)

I've never seen this error ... anyone can help me in order to solve this
prob?

thanks in advance
max




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Where is Win32 binary NON-debug 3.23.39 ? Debug builds much slower ?

[S A]

I'd rather not fire up Viz C just to build MySQL 3.23.39 NON-DEBUG.

Is there much of a performance difference between DEBUG & NON-DEBUG ?

Also with the Windows binary builds how does one NOT use MySQL MAX, is that a runtime 
option or do you need a different build entirely ? 

Is there much of a performance difference between MAX & regular MySQL ?

- Sam.



-
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/

Index names chosen for CREATE INDEX etc. matter to MySQL ?

[S A]

Do the names of indexes have any significance to MySQL internals or is the name merely 
a convenience for the DB operator ?

For now I try to match the index names to the column names used but I'm wondering if 
the name matters at all.

thanks,

- Sam.



-
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/

Re: Join with LIMIT faster in 2 queries than 1 query, why ?

[S A]

> Did you read about optimizing joins? 
Yes I've read the manuals & docs.
I see in the manual a section entitled "Optimising LEFT JOIN and RIGHT JOIN" but my 
JOIN in question is neither LEFT or RIGHT.
Can you please point me at some specific URLs ?
thanks,
- Sam.
  Werner Stuerenburg <[EMAIL PROTECTED]> wrote: Did you read about optimizing joins? 
It is discussed in detail in
the manual. Recently, we had a similar discussion here (thread
Left Join very sl..) where it turned out that all the
tricks in the manual applied. You can analyze your query and get
detailed info on where and how to change your indexes, syntax
etc.

S A schrieb am Dienstag, 24. Juli 2001, 01:08:31:


> I have standard search functionality on my site where a user can see NN items per 
>screen of a much longer list of search results. I've found that MySQL returns small 
>result sets out of a list of
> hits faster when queried in 2 parts than queried once. Am I doing something wrong ?

> I have a fairly standard join query on a few tables with about 20,000 rows.

> SELECT g.poster_id, g.poster_name, p.file_name, 
> p.file_size, p.post_subject, p.post_id, 
> DATE_FORMAT(p.post_date,'%b-%d-%y') AS date, p.file_extension 
> FROM users_to_files u, posts p, posters g
> WHERE p.poster_id = g.poster_id AND 
> p.post_id = u.post_id AND u.user_id = $folder_owner_id AND u.folder_id = $src_folder
> ORDER_BY g.poster_name, p.post_subject
> LIMIT 0,10

> When I do a SELECT on that join to get just 10 rows using an ORDER BY and a LIMIT 
>0,10 it takes about 2 seconds per set of 10. 

> As an experiment I broke up this query into 2 parts & it only takes about 0.2 
>seconds or less per set of 10. 

> #1 The first query gets just the post_id's that I care about. 

> SELECT p.post_id 
> FROM posts p, posters g $POSTS_TO_GROUPS
> WHERE p.poster_id = g.poster_id 
> AND p.file_size > 0 
> ORDER BY g.poster_name, p.post_subject

> #2 Then I do the joined query on all the columns on just the small list of post_ids 
>to act as a LIMIT.

> SELECT g.poster_id, g.poster_name, p.file_name, 
> p.file_size, p.post_subject 
> DATE_FORMAT(p.post_date,'%b-%d-%y') AS date, p.file_extension 
> FROM posts p, posters g
> WHERE p.post_id IN ($post_ids_in) AND p.poster_id = g.poster_id
> ORDER BY g.poster_name, p.post_subject

> Shouldn't MySQL be as fast or faster to do this join in the 1 query than the 2 
>queries ?

> Does the MySQL optimizer not recognize that only the few rows specified by the LIMIT 
>actually need to be retrieved ?

> If so is there a way to help MySQL optimize the join ?

> Displaying a page full portion at a time of a long list of results is a very common 
>web thing for MySQL to do so hopefully I'm just doing something wrong.

> thanks,

> - Sam.



> -
> Do You Yahoo!?
> Make international calls for as low as $.04/minute with Yahoo! Messenger
> http://phonecard.yahoo.com/


-- 
Herzlich
Werner Stuerenburg 

_
ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen
Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409
http://pferdezeitung.de




-
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/

PS : Join with LIMIT faster in 2 queries than 1 query, why ?

[S A]

PS : I forgot to add that as a further optimization I cache the list of IDs from the 
first query in the 2 part approach& I reuse that list as users advance through it.

 



-
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/

Join with LIMIT faster in 2 queries than 1 query, why ?

[S A]

I have standard search functionality on my site where a user can see NN items per 
screen of a much longer list of search results. I've found that MySQL returns small 
result sets out of a list of hits faster when queried in 2 parts than queried once. Am 
I doing something wrong ?

I have a fairly standard join query on a few tables with about 20,000 rows.

SELECT g.poster_id, g.poster_name, p.file_name, 
 p.file_size, p.post_subject, p.post_id, 
  DATE_FORMAT(p.post_date,'%b-%d-%y') AS date, p.file_extension 
FROM users_to_files u, posts p, posters g
 WHERE p.poster_id = g.poster_id AND 
   p.post_id = u.post_id AND u.user_id = $folder_owner_id AND u.folder_id = 
$src_folder
   ORDER_BY g.poster_name, p.post_subject
  LIMIT 0,10

When I do a SELECT on that join to get just 10 rows using an ORDER BY and a LIMIT 0,10 
it takes about 2 seconds per set of 10. 

As an experiment I broke up this query into 2 parts & it only takes about 0.2 seconds 
or less per set of 10. 

#1 The first query gets just the post_id's that I care about. 

SELECT p.post_id 
   FROM posts p, posters g $POSTS_TO_GROUPS
WHERE p.poster_id = g.poster_id  
 AND p.file_size > 0 
   ORDER BY g.poster_name, p.post_subject

#2 Then I do the joined query on all the columns on just the small list of post_ids to 
act as a LIMIT.

SELECT g.poster_id, g.poster_name, p.file_name, 
 p.file_size, p.post_subject 
  DATE_FORMAT(p.post_date,'%b-%d-%y') AS date, p.file_extension 
FROM posts p, posters g
 WHERE p.post_id IN ($post_ids_in) AND p.poster_id = g.poster_id
  ORDER BY g.poster_name, p.post_subject

Shouldn't MySQL be as fast or faster to do this join in the 1 query than the 2 queries 
?

Does the MySQL optimizer not recognize that only the few rows specified by the LIMIT 
actually need to be retrieved ?

If so is there a way to help MySQL optimize the join ?

Displaying a page full portion at a time of a long list of results is a very common 
web thing for MySQL to do so hopefully I'm just doing something wrong.

thanks,

- Sam.



-
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/

MySQL get COUNT(*) on # rows AND return LIMITed results in 1 query ?

[S A]

Is there some way to take the following 2 queries & make them into one ?

I want to be able to in one query get the # of overall rows and do a LIMITed result 
set to get the first NN rows.

Is it possible to to do this in one query ? Presumable MySQL has to figure out what 
the overall # is to return the limited result set so it'd be more efficient to do this 
all at once.

thanks,

- Sam.

SELECT COUNT(*) FROM posts WHERE group_id=1;

SELECT post_subject FROM posts WHERE group_id=1 LIMIT 10,10;

 



-
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35 a year!
http://personal.mail.yahoo.com/

Re: GROUP BY how to control ordering of non-grouped fields ?

[S A]

 Hello Benjamin.
I forgot to mention that there could be items from multiple posters in each folder so 
the query you sent worked on those cases just like the old query. However looking at 
your nicely formatted example made me realize what I needed to do.
By concatenating the poster_name in front of the post_subject with a CONCAT & then 
doing a MIN I was able to get the desired query because now that column is sorted by 
Poster_Name,Post_Subject. With PHP I will need to split the Poster_Name from before 
the Post_Subject but that's fine.
So you're right nice query formatting can make all the difference !
thanks,
- Sam.
New Query :
 
SELECTf.folder_name AS Folder,
  COUNT(g.post_id) AS Amount,
  MIN(concat(n.poster_name,"\n",p.file_name)) AS Poster_Photo
FROM  users u, folders f, users_to_files g, posts p, posters n
WHERE u.user_id = 1 AND
  f.user_id = u.user_id AND
  g.folder_id = f.folder_id AND
  p.post_id = g.post_id AND
  p.poster_id=n.poster_id
GROUP BY  f.folder_name
ORDER BY  f.folder_name
 
- Sam.
  Benjamin Pflugmann <[EMAIL PROTECTED]> wrote: Hello Sam.

On Fri, Jun 15, 2001 at 03:54:55PM -0700, [EMAIL PROTECTED] wrote:
> 
> When I do the following query I get seemingly arbitrary results for most of the 
>columns.
> 
> SELECT f.folder_name, COUNT(g.post_id), n.poster_name, p.file_name
> FROM users u, folders f, users_to_files g, posts p, posters n
> WHERE f.user_id = u.user_id AND u.user_id = 1 AND g.folder_id=f.folder_id 
> AND p.post_id=g.post_id AND p.poster_id=n.poster_id
> GROUP BY f.folder_name

This is the expected and documented behaviour:

http://www.mysql.com/doc/G/r/Group_by_functions.html

> Is it possible to give MySQL ordering commands for the columns that
> are collapsed together by a GROUP BY ?
>

> The above query gives me the below where the ordering of the
> selected Poster & Photo seems arbitrary when what I want is Poster
> by alpha order ASC and Photo by alpha order ASC and one row per
> Folder.
> 
> | Folder | COUNT(g.post_id)| Poster | Photo |
> | Animals | 15 | WildAnimalsFan | Zebra.jpg |
> | Cars | 34 | TheBMWFan | Z3_BMW.jpg |
>

Just use MIN(n.poster_name), MIN(p.file_name).

>
> What I really want is this where the Poster is alpha ASC and the
> Photo alpha ASC & there's only one row per Folder :
>
> | Folder | COUNT(g.post_id)| Poster | Photo |
> | Animals | 15 | Animalia | Bear |
> | Cars | 34 | AlfaFan | Ferrari |
[...]

The complete SELECT would look like this:

SELECT f.folder_name AS Folder,
COUNT(g.post_id) AS Amount,
MIN(n.poster_name) AS Poster,
REPLACE(MIN(p.file_name),'.jpg','') AS Photo
FROM users u, folders f, users_to_files g, posts p, posters n
WHERE u.user_id = 1 AND
f.user_id = u.user_id AND
g.folder_id = f.folder_id AND
p.post_id = g.post_id AND
p.poster_id=n.poster_id
GROUP BY f.folder_name
ORDER BY f.folder_name

Bye,

Benjamin.


PS: To all: Presenting a pretty-formatted query increases the chance
that someone cares to read it.



-
Do You Yahoo!?
Yahoo! Buzz Index - Spot the hottest trends in music, movies,and more.

GROUP BY how to control ordering of non-grouped fields ?

[S A]

When I do the following query I get seemingly arbitrary results for most of the 
columns.

SELECT f.folder_name, COUNT(g.post_id), n.poster_name, p.file_name
 FROM users u, folders f, users_to_files g, posts p, posters n
  WHERE f.user_id = u.user_id AND u.user_id = 1 AND g.folder_id=f.folder_id 
   AND p.post_id=g.post_id AND p.poster_id=n.poster_id
  GROUP BY f.folder_name


Is it possible to give MySQL ordering commands for the columns that are collapsed 
together by a GROUP BY ?

The above query gives me the below where the ordering of the selected Poster & Photo 
seems arbitrary when what I want is Poster by alpha order ASC and Photo by alpha order 
ASC and one row per Folder.

| Folder  | COUNT(g.post_id)| Poster |Photo  |

| Animals | 15  | WildAnimalsFan |Zebra.jpg  |

| Cars| 34  | TheBMWFan  |Z3_BMW.jpg |

What I really want is this where the Poster is alpha ASC and the Photo alpha ASC & 
there's only one row per Folder  :

| Folder  | COUNT(g.post_id)| Poster  |Photo |

| Animals | 15  | Animalia|Bear  |

| Cars| 34  | AlfaFan |Ferrari   |

Is this possible in one query & if so how ? I've tried various combos of GROUP BY 
poster, photo etc. with & without HAVING but the best that gives me is this which is 
close but I only want one line per Folder, not one row for every Poster within every 
Folder.

| Folder  | COUNT(g.post_id)| Poster |Photo  |

| Animals | 15  | Animalia   |Bear   |

| Animals | 15  | WildAnimalsFan |Zebra.jpg  |

| Cars| 34  | AlfaFan|Ferrari|

| Cars| 34  | TheBMWFan  |Z3_BMW.jpg |

 

thanks,

- Sam.



-
Do You Yahoo!?
Yahoo! Mail Personal Address - Get email at your own domain with Yahoo! Mail.

beta-log version speed difference ?

[S A]

How much if at all slower should a BETA-LOG build of MySQL be than one that doesn't 
log ?

We have on BSD 3.23.26-beta-log which is MUCH slower than 3.23.32 on Linux.

- Sam.



-
Do You Yahoo!?
Yahoo! Auctions $2 Million Sweepstakes  -  Got something to sell?

FULLTEXT built for 2 char words OK ?

[S A]

We need FULLTEXT searching on 2 character length words. 

If we recompile MySQL to do FULLTEXT indexes on 2 char length words is it going to 
work OK or will such short words undo the advantages of FULLTEXT ?

Also will the data files be useable by other standard MySQL binary builds or will we 
have to use MySQLs built for 2 char length FULLTEXT words to access the data files ?

thanks,

- Sam.



-
Do You Yahoo!?
Yahoo! Auctions $2 Million Sweepstakes  -  Got something to sell?

ORDER BY slow down, bug ?

[S A]

I have 3 variations of a fairly simple aggregate query done on a medium sized table of 
2 million rows.

Doing an ORDER BY makes the query go from less than a second to often taking 30 
seconds.

Is this a bug ? 

We're running on Free BSD 4.2 with Dual PIII 700mhz & 1GB RAM.

- Sam.

 

A. The original query which caused the dreaded filesort.

mysql> explain SELECT m.metro_name, sum(a.td_num), a.metro_id FROM album_by_metro a, 
usa_metro_ref m WHERE a.metro_id= m.metro_id AND a.same_album_id = 10 AND a.rpt_dt >= 
'2001-01-01' AND a.rpt_dt <= '2001-05-14' GROUP BY a.metro_id ORDER BY m.metro_name;

+---+--+-+-+-++--+-+

| table | type | possible_keys | key | key_len | ref | rows | Extra |

+---+--+-+-+-++--+-+

| m | ALL | PRIMARY | NULL | NULL | NULL | 108 | Using temporary; Using filesort |

| a | ref | PRIMARY,album_by_metro_idx1,album_by_metro_idx3 | album_by_metro_idx3 | 2 
|| m.metro_id | 131 | where used |

+---+--+-+-+-++--+-+

2 rows in set (0.01 sec)

 

B. The next query which seemed faster but could be sometimes just as slow as the above.

mysql> explain SELECT m.metro_name, sum(a.td_num), a.metro_id FROM album_by_metro a, 
usa_metro_ref m WHERE a.metro_id= m.metro_id AND a.same_album_id = 10 AND a.rpt_dt >= 
'2001-01-01' AND a.rpt_dt <= '2001-05-14' GROUP BY m.metro_name,a.metro_id ORDER 
BYm.metro_name;

+---+--+-+-+-++--+-+

| table | type | possible_keys | key | key_len | ref | rows | Extra |

+---+--+-+-+-++--+-+

| m | ALL | PRIMARY | NULL | NULL | NULL | 108 | Using temporary || a | ref | 
|PRIMARY,album_by_metro_idx1,album_by_metro_idx3 | album_by_metr

o_idx3 | 2 | m.metro_id | 131 | where used |

+---+--+-+-+-++--+-+

2 rows in set (0.01 sec)

 

C. The simplest form of query with no ORDER BY at all which is never slow now.

mysql> explain SELECT m.metro_name, sum(a.td_num), a.metro_id FROM album_by_metro a, 
usa_metro_ref m WHERE a.metro_id= m.metro_id AND a.same_album_id = 10 AND a.rpt_dt >= 
'2001-01-01' AND a.rpt_dt <= '2001-05-14' GROUP BY a.metro_id;

+---++-+-+-++---+-+

| table | type | possible_keys | key | key_len | ref | rows | Extra |

+---++-+-+-++---+-+

| a | ref | PRIMARY,album_by_metro_idx1,album_by_metro_idx3 | album_by_metro_idx1 | 4 
|| const | 10677 | where used; Using temporary |

| m | eq_ref | PRIMARY | PRIMARY | 4 | a.metro_id | 1 | where used |

+---++-+-+-++---+-+



-
Do You Yahoo!?
Yahoo! Auctions $2 Million Sweepstakes  -  Got something to sell?

Bug with MySQL GROUP BY or just loose SQL?

[S A]

I have a query that works fine until my date range goes one day over some kind of data 
size threshold & then it mysteriously goes from 0.34 seconds to 30 seconds. My DBA 
pointed out that my SQL syntax was loose & that Oracle would've flagged it as an error.

I did a GROUP BY based on an numeric ID field & then afterwards did an ORDER BY on a 
string name field.

When I adjusted the query to GROUP BY name and numeric ID then ORDER BY name it's much 
faster even past the threshold date.

I thought that doing the GROUP BY only on a numeric ID would be faster than doing it 
by string and then ID.

Should MySQL have flagged this as an error, should it've optimized this by itself, or 
is my SQL too loose ? 

- Sam.

 

 

Slow :

SELECT m.metro_name, sum(a.td_num), a.metro_id FROM album_by_metro a, usa_metro_ref m 
WHERE a.metro_id= m.metro_id AND a.same_album_id = 1029 AND a.rpt_dt >= '2001-01-01' 
AND a.rpt_dt <= '2001-05-14' GROUP BY a.metro_id ORDER BY m.metro_name;

Faster :

explain SELECT m.metro_name, sum(a.td_num), a.metro_id FROM album_by_metro a, 
usa_metro_ref m WHERE a.metro_id= m.metro_id AND a.same_album_id = 1029 AND a.rpt_dt 
>= '2001-01-01' AND a.rpt_dt <= '2001-05-14' GROUP BY m.metro_name,a.metro_id ORDER BY 
m.metro_name;



-
Do You Yahoo!?
Yahoo! Auctions $2 Million Sweepstakes  -  Got something to sell?

Can someone post up their FreeBSD .cnf file ?

[S A]

My DBA & I can't find a .cnf file to start customizing our Free BSD MySQL setup.

Could someone please post one up to start from ?

thanks,

- Sam.



-
Do You Yahoo!?
Yahoo! Auctions $2 Million Sweepstakes  -  Got something to sell?

FreeBSD 4.2 vs. Linux query speeds ?

[S A]

I have some complex multi-step queries that take over 60 seconds on Free BSD 4.2. They 
seemed much faster on Linux.

Should FreeBSD 4.2 be just as fast as Linux on comparable hardware ?

When I simulate concurrent users the query times go up oddly.

1 user 66 seconds

2 users 157 seconds

3 users 232 seconds

Does this seem odd ?

- Sam.



-
Do You Yahoo!?
Yahoo! Auctions $2 Million Sweepstakes  -  Got something to sell?

UTF-8 supported, compatible or plain won't work ?

[S A]

Can you use UTF-8 with MySQL ?

Is UTF-8 supported now, just compatible or plain won't work ?

I've read conflicting discussion on this topic.

thanks,

- Sam.



-
Do You Yahoo!?
Yahoo! Mail Personal Address - Get email at your own domain with Yahoo! Mail.

Does FULL TEXT speed up LIKE matches also ?

[S A]

I find the fuzzy matches from MATCH and AGAINST to be too unpredictable.

Does using a FULL TEXT index also speed up simple LIKE searches ?

 



-
Do You Yahoo!?
Yahoo! Mail Personal Address - Get email at your own domain with Yahoo! Mail.

Solaris tuning vs. Linux SHOW VARIABLE logs

[S A]

Turns out the Linux table_cache was 457 to Solaris 64.

Could that have been all the speed difference ?

- Sam.

 

Solaris

mysql> show variables;
+-+-
--+
| Variable_name   | Value
  |
+-+-
--+
| ansi_mode   | OFF
  |
| back_log| 50
  |
| basedir | /export/home/mysql/app/mysql-3.23.33/
  |
| binlog_cache_size   | 32768
  |
| character_set   | latin1
  |
| character_sets  | latin1 cp1251
  |
| concurrent_insert   | ON
  |
| connect_timeout | 5
  |
| datadir | /export/home/mysql/app/mysql-3.23.33/var/
  |
| delay_key_write | ON
  |
| delayed_insert_limit| 100
  |
| delayed_insert_timeout  | 300
  |
| delayed_queue_size  | 1000
  |
| flush   | OFF
  |
| flush_time  | 0
  |
| have_bdb| NO
  |
| have_gemini | NO
  |
| have_innobase   | NO
  |
| have_isam   | YES
  |
| have_raid   | NO
  |
| have_ssl| NO
  |
| init_file   |
  |
| interactive_timeout | 28800
  |
| join_buffer_size| 131072
  |
| key_buffer_size | 67104768
  |
| language| /export/home/mysql/app/mysql-3.23.33/share/mysql/eng
lish/ |
| large_files_support | ON
  |
| locked_in_memory| OFF
  |
| log | OFF
  |
| log_update  | OFF
  |
| log_bin | OFF
  |
| log_slave_updates   | OFF
  |
| long_query_time | 10
  |
| low_priority_updates| OFF
  |
| lower_case_table_names  | 0
  |
| max_allowed_packet  | 1048576
  |
| max_binlog_cache_size   | 4294967295
  |
| max_binlog_size | 1073741824
  |
| max_connections | 100
  |
| max_connect_errors  | 10
  |
| max_delayed_threads | 20
  |
| max_heap_table_size | 16777216
  |
| max_join_size   | 4294967295
  |
| max_sort_length | 1024
  |
| max_tmp_tables  | 32
  |
| max_write_lock_count| 4294967295
  |
| myisam_recover_options  | OFF
  |
| myisam_sort_buffer_size | 8388608
  |
| net_buffer_length   | 16384
  |
| net_read_timeout| 30
  |
| net_retry_count | 10
  |
| net_write_timeout   | 60
| open_files_limit| 0
  |
| pid_file| /export/home/mysql/app/mysql-3.23.33/var/devel2.pid
  |
| port| 3306
  |
| protocol_version| 10
  |
| record_buffer   | 131072
  |
| query_buffer_size   | 0
  |
| safe_show_database  | OFF
  |
| server_id   | 0
  |
| skip_locking| OFF
  |
| skip_networking | OFF
  |
| skip_show_database  | OFF
  |
| slow_launch_time| 2
  |
| socket  | /tmp/mysql.sock
  |
| sort_buffer | 4194296
  |
| table_cache | 64
  |
| table_type  | MYISAM
  |
| thread_cache_size   | 0
  |
| thread_concurrency  | 10
  |
| thread_stack| 65536
  |
| timezone| PST
  |
| tmp_table_size  | 1048576
  |
| tmpdir  | /var/tmp/
  |
| version | 3.23.33
  |
| wait_timeout| 28800


Linux :

mysql> show variables;
+-+-

+
| Variable_name   | Value

|
+-+-

+
| ansi_mode   | OFF

| back_log| 50

| basedir | /home/mysql/mysql-3.23.32-pc-linux-gnu-i686/

| binlog_cache_size   | 32768

| character_set   | latin1

| character_sets  | latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis
 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 esto
nia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert   | ON

| connect_timeout | 5

| datadir | /home/mysql/mysql-3.23.32-pc-linux-gnu-i686/data/

| delay_key_write | ON

| delayed_insert_limit| 100

| delayed_insert_timeout  | 300

| delayed_queue_size  | 1000

| flush   | OFF

| flush_time

Solaris much slower than Linux ? tuning tips ?

[S A]

We've got our MySQL DB on comparable Linux & Solaris hardware but Linux beats Solaris 
by about 6 times.

Anyone have Solaris tuning tips ?

 



-
Do You Yahoo!?
Yahoo! Mail Personal Address - Get email at your own domain with Yahoo! Mail.

Unique ID's Alphanumeric to save digits ?

[S A]

Can MySQL help with generation of ALPHANUMERIC unique identifiers ?

ID's that use the full ASCII char range create more possible combinations with the 
same # of characters which can be important when squeezing into cookie storage space.

4 character numeric ID has 10,000 combinations.

4 character (6 bits per char) alphanumeric id has 16,777,216 combinations

If MySQL can't help then I can do it with Perl/PHP I suppose but just curious.

 



-
Do You Yahoo!?
Yahoo! Mail Personal Address - Get email at your own domain with Yahoo! Mail.

How to get MAX of each group after GROUP BY ?

[S A]

 
How do I do a single query to group a table by one column & then get the row of say 
the highest of each group ?

Data is like so :  
 Album, City, Plays, Date

Britney, NYC, 103, 1-10

Britney, SF, 101, 1-10

Eminem, NYC, 100, 1-11

Eminem, SF, 102, 1-11

So I want to GROUP BY Album, then get the row with the highest # of Plays to get this 
result :

Album, City, Plays, Date

Britney, NYC, 103, 1-10

Eminem, SF, 102, 1-11

When I use a GROUP BY I keep getting arbitrary album records instead of the row I want 
( highest plays). I've tried various combos of GROUP BY & HAVING but am stumped.

thanks,

 

- Sam.



-
Do You Yahoo!?
Yahoo! Mail Personal Address - Get email at your own domain with Yahoo! Mail.

Re: load large data files?

[S A]

How do I do a single query to group a table by one column & then get the row of say 
the highest of each group ?

Data is like so : 
 Album, City, Plays, Date

Britney, NYC, 103, 1-10

Britney, SF, 101, 1-10

Eminem, NYC, 100, 1-11

Eminem, SF, 102, 1-11

So I want to GROUP BY Album, then get the row with the highest # of Plays to get this 
result :

Album, City, Plays, Date

Britney, NYC, 103, 1-10

Eminem, SF, 102, 1-11

When I use a GROUP BY I keep getting arbitrary album records instead of the row I want 
( highest plays). I've tried various combos of GROUP BY & HAVING but am stumped.

thanks,

 

- Sam.

 



-
Do You Yahoo!?
Yahoo! Mail Personal Address - Get email at your own domain with Yahoo! Mail.

Re: ranking album plays by city, help me beat Oracle

[S A]

I will respond in more detail later but just quickly now : 


> Hm. Sorry, I don't understand your argument. By the for loop, your

>query does the same, doesn't it (selecting the rank of one album in

>each metro is the same as selecting for all metros the rank of one album)?


The subtle difference is that the query I'm using ( I didn't actually write it ) 
calculates the rank of the single album in question by figuring out how many albums 
have greater play totals & not caring what the more popular albums or what their 
totals were. Perhaps ignoring albums with smaller play totals using the HAVING & 
focusing on just one album's position helps a lot.

$query = "select same_album_id, sum(td_num) from album_by_metro

where metro = 'New York'

and rpt_dt = to_date('2001-01-31', '-mm-dd')

group by same_album_id

having sum(td_num) > v_sum"

 
> I am really curious, whether my arguments hold, and therefore what

> your test results are. Thank you in advance.


If you are sufficiently curious I could send you a little Perl script that will create 
the ALBUM_BY_METRO table, populate it with test data & allow you to more easily 
analyze the problem. Would you like that ?

thanks for your continued help,

- Sam.

 
  Benjamin Pflugmann <[EMAIL PROTECTED]> wrote: 
Hello.

On Tue, Feb 20, 2001 at 03:27:08PM -0800, [EMAIL PROTECTED] wrote:
> > Huh? You got it 7 times faster by only extracting that query from the
> > for loop and changing it to a group by? Or did you do anything else?
> 
> Sorry there were some other unnecessary queries that were mistakenly
> in the measurement..

Ah, okay. Never mind, but good to know.

> Here is the missing EXPLAIN.
> 
> mysql> EXPLAIN SELECT same_album_id, SUM(td_num) FROM album_by_metro
> -> WHERE metro = 'Atlanta' AND rpt_dt >= '2001-01-01' AND
> -> rpt_dt <= '2001-02-14'
> -> GROUP BY same_album_id HAVING SUM(td_num) > 500
> -> ;
> ++---++-+---
> --+--+--+-+
> | table | type | possible_keys | key | ke
> y_len | ref | rows | Extra |
> ++---++-+---
> --+--+--+-+
> | album_by_metro | range | rpt_dt,album_by_metro_idx2 | album_by_metro_idx2 |
> 103 | NULL | 8925 | where used; Using temporary |
> ++---++-+---
> --+--+--+-+
> 1 row in set (0.00 sec)

"Using temporary" is not good (see also http://www.mysql.com/doc/E/X/EXPLAIN.html).
Hm. I don't know, why a temporary table is used here. Two suggestions:
Make a special key for that query (metro,rpt_dt,same_album_id,td_sum).
And second, force in-memory tables with SELECT SQL_SMALL_RESULT
same_album_id, ...

Could you post the EXPLAIN and the speed measurement after each of the
changes, please.

> Sorry I tried the below suggested query but it it is much slower at
> 1min 30 secs probably because it ranks all albums for all metros
> rather than just working on the rank of one album over the metros.

Hm. Sorry, I don't understand your argument. By the for loop, your
query does the same, doesn't it (selecting the rank of one album in
each metro is the same as selecting for all metros the rank of one
album)?

But, of course, yes, the time is way off.

> mysql> EXPLAIN SELECT metro, same_album_id, SUM(td_num) AS played
> -> FROM album_by_metro
> -> WHERE rpt_dt >= '2001-01-01' AND rpt_dt <= '2001-02-14'
> -> GROUP BY metro, same_album_id
> -> HAVING SUM(td_num) > 1000
> -> ORDER BY metro, same_album_id, played DESC;
> ++---+---++-+--++---
> ---+
> | table | type | possible_keys | key | key_len | ref | rows | Ex
> tra |
> ++---+---++-+--++---
> ---+
> | album_by_metro | index | rpt_dt | rpt_dt | 161 | NULL | 716943 | wh
> ere used; Using index; Using temporary; Using filesort |
> ++---+---++-+--++---
> --+

Hm. rpt_dt used as I guessed and the number of rows seems reasonable
(about 100 times as much as with the query above). "using index" is
fine (key_len indicates that not only rpt_dt is read from the key, but
also all the other column values). But "using temporary" and "using
filesort" is considered bad.

We could use a better key, leaving out the column "region", e.g.
(rpt_dt,metro,same_album_id,td_sum), but I guess, this will give only
a small improvement (would you try it please?).

The use of a temporary table is obligatory when GROUP BY and ORDER BY
differ. One could change ORDER BY to "metro, same_album_id" and do the
sorting on client side, but I doubt this would be a better solu

Re: ranking album plays by city, help me beat Oracle

[S A]

> Huh? You got it 7 times faster by only extracting that query from the

> for loop and changing it to a group by? Or did you do anything else?


Sorry there were some other unnecessary queries that were mistakenly in the 
measurement.. 


Here is the missing EXPLAIN.

mysql> EXPLAIN SELECT same_album_id, SUM(td_num) FROM album_by_metro
-> WHERE metro = 'Atlanta' AND rpt_dt >= '2001-01-01' AND
-> rpt_dt <= '2001-02-14'
-> GROUP BY same_album_id HAVING SUM(td_num) > 500
-> ;
++---++-+---
--+--+--+-+
| table  | type  | possible_keys  | key | ke
y_len | ref  | rows | Extra   |
++---++-+---
--+--+--+-+
| album_by_metro | range | rpt_dt,album_by_metro_idx2 | album_by_metro_idx2 |
  103 | NULL | 8925 | where used; Using temporary |
++---++-+---
--+--+--+-+
1 row in set (0.00 sec)

Sorry I tried the below suggested query but it it is much slower at 1min 30 secs 
probably because it ranks all albums for all metros rather than just working on the 
rank of one album over the metros.

SELECT metro, same_album_id, SUM(td_num) AS played
FROM album_by_metro
WHERE rpt_dt >= '$BeginDate' AND rpt_dt <= '$EndDate'
GROUP BY metro, same_album_id
HAVING SUM(td_num) > $MIN_SUM
ORDER BY metro, same_album_id, played DESC

Here is the explain for the above query :

 

mysql> EXPLAIN SELECT metro, same_album_id, SUM(td_num) AS played
-> FROM album_by_metro
-> WHERE rpt_dt >= '2001-01-01' AND rpt_dt <= '2001-02-14'
-> GROUP BY metro, same_album_id
-> HAVING SUM(td_num) > 1000
-> ORDER BY metro, same_album_id, played DESC;
++---+---++-+--++---
---+
| table  | type  | possible_keys | key| key_len | ref  | rows   | Ex
tra|
++---+---++-+--++---
---+
| album_by_metro | index | rpt_dt| rpt_dt | 161 | NULL | 716943 | wh
ere used; Using index; Using temporary; Using filesort |
++---+---++-+--++---
--+


Can this faster approach below which in a loop queries the # of albums with > sums be 
optimized to be a single query. ?

 foreach ($metros as $metro)
 {
  $V_SUM = $V_SUMS[$metro];

  $query = "SELECT same_album_id, SUM(td_num) FROM album_by_metro
WHERE  metro = '$metro' AND rpt_dt >= '$BeginDate' AND rpt_dt <= '$EndDate'
  GROUP BY same_album_id HAVING SUM(td_num) > $V_SUM";
}

Thanks for all your help & interest.

- Sam.

  Benjamin Pflugmann <[EMAIL PROTECTED]> wrote: 
Hello.

On Fri, Feb 16, 2001 at 03:58:20PM -0800, [EMAIL PROTECTED] wrote:
> 
> This is the latest approach we're using now. It turns out that the
> query to get Total Plays for an Album per City can be taken out of
> the City for loop & done only once as a GROUP BY. The worst case
> time for MySQL is now 16 seconds compared to Oracle's 8 seconds.

Huh? You got it 7 times faster by only extracting that query from the
for loop and changing it to a group by? Or did you do anything else?

Well, because the column "range" is not used in any of both queries,
normally only the indexes "album_by_metro_idx1/2" are used. As I said
last time, maybe the indexes could be more specific for the query.
Your change now uses idx2 and has the advantage, that all metros it
matches are really used, therefore the index is now okay for the new
query. Before, it was reading all metros in, although it was only
using one.

If you want to try, it would be interesting, what the time for the old
query in the for loop is, when you have an index on
(same_album_id,metro,rpt_dt). Anyhow, your new solution surely is
faster (avoiding communition overhead for about 100 queries).

> $query = "SELECT metro, sum(td_num) FROM album_by_metro
> WHERE same_album_id = $album
> AND rpt_dt >= '$BeginDate' AND rpt_dt <= '$EndDate'
> GROUP BY metro"; 
> $start = time(); 
> $result = mysql_query($query) or die ("Can't get $query" . mysql_error()); 
> 
> while ($row = mysql_fetch_array($result)) 
> { 
> $metro = $row[0]; 
> $metros[] = $metro; 
> $V_SUMS[$metro] = $row[1];
> }
> // while 
> mysql_free_result($result); 
> // 
> foreach ($metros as $metro)
> {
> $V_SUM = $V_SUMS[$metro]; 
> $query = "SELECT same_album_id, SUM(td_num) FROM album_by_metro
> WHERE metro = '$metro' AND rpt_dt >= '$BeginDate' AND rpt_dt <= '$EndDate'
> GROUP BY same_album_id HAVING SUM(td_num) > $V_SUM";

This will use ind

Re: ranking album plays by city, help me beat Oracle

[S A]

 This is the latest approach we're using now. It turns out that the query to get Total 
Plays for an Album per City can be taken out of the City for loop & done only once as 
a GROUP BY. 
The worst case time for MySQL is now 16 seconds compared to Oracle's 8 seconds. On 
most cases MySQL is now faster than Oracle but once in a while it's slower. 

 $query = "SELECT metro, sum(td_num) FROM album_by_metro
  WHERE same_album_id = $album
  AND rpt_dt >= '$BeginDate' AND rpt_dt <= '$EndDate'
   GROUP BY metro"; 
$start = time(); 
 $result = mysql_query($query) or die ("Can't get $query" . mysql_error()); 

 while ($row = mysql_fetch_array($result)) 
 { 
  $metro = $row[0]; 
  $metros[] = $metro; 
  $V_SUMS[$metro] = $row[1];
 }
// while 
 mysql_free_result($result); 
// 
 foreach ($metros as $metro)
 {
  $V_SUM = $V_SUMS[$metro]; 
  $query = "SELECT same_album_id, SUM(td_num) FROM album_by_metro
WHERE  metro = '$metro' AND rpt_dt >= '$BeginDate' AND rpt_dt <= '$EndDate'
  GROUP BY same_album_id HAVING SUM(td_num) > $V_SUM";
  
  $result = mysql_query($query, $connection);
  if ($result)
   $rank = mysql_num_rows($result)+1;
  else
   $rank = 0; 
 $ranks[$metro] = $rank; 
 }


Below is everything you asked for except the Oracle stored procedure which I can't 
send.
thanks for your help,
- Sam.
mysql> describe album_by_metro;
+---+--+--+-++---+--
+
| Field | Type | Null | Key | Default| Extra | Privileges
|
+---+--+--+-++---+--
+
| rpt_dt| date |  | MUL | -00-00 |   | select,insert
,update |
| region| varchar(50)  |  | ||   | select,insert
,update |
| metro | varchar(100) |  | MUL ||   | select,insert
,update |
| same_album_id | int(11)  |  | MUL | 0  |   | select,insert
,update |
| td_num| int(11)  |  | | 0  |   | select,insert
,update |
 
mysql> EXPLAIN SELECT same_album_id, SUM(td_num) FROM album_by_metro
WHERE  metro = 'Atlanta' AND rpt_dt >= '2001-01-01' AND
 rpt_dt <= '2001-02-14'
  GROUP BY same_album_id HAVING SUM(td_num) > 500
mysql> show index from album_by_metro;
+++-+--+
---+---+-+--++-+
| Table  | Non_unique | Key_name| Seq_in_index | Column_name
   | Collation | Cardinality | Sub_part | Packed | Comment |
+++-+--+
---+---+-+--++-+
| album_by_metro |  1 | rpt_dt  |1 | rpt_dt
   | A |NULL | NULL | NULL   | |
| album_by_metro |  1 | rpt_dt  |2 | region
   | A |NULL | NULL | NULL   | |
| album_by_metro |  1 | rpt_dt  |3 | metro
   | A |NULL | NULL | NULL   | |
| album_by_metro |  1 | rpt_dt  |4 | same_album_
id | A |NULL | NULL | NULL   | |
| album_by_metro |  1 | rpt_dt  |5 | td_num
   | A |NULL | NULL | NULL   | |
| album_by_metro |  1 | album_by_metro_idx2 |1 | metro
   | A |NULL | NULL | NULL   | |
| album_by_metro |  1 | album_by_metro_idx2 |2 | rpt_dt
   | A |NULL | NULL | NULL   | |
| album_by_metro |  1 | album_by_metro_idx1 |1 | same_album_
id | A |NULL | NULL | NULL   | |
| album_by_metro |  1 | album_by_metro_idx1 |2 | rpt_dt
   | A |NULL | NULL | NULL   | |
+++-+--+
---+---+-+--++-+
 
  Benjamin Pflugmann <[EMAIL PROTECTED]> wrote: 
Hi.

On Thu, Feb 15, 2001 at 09:25:56PM -0800, [EMAIL PROTECTED] wrote:
> 
> > Could you please post the result of "EXPLAIN" for both queries and "SHOW INDEX 
>FROM album_by_city".
> 
> 
> I haven't used EXPLAIN before.

Just put it before the SELECT in your query. I don't know whether this
will work directly in your program (php?), you can do it another way,
too. Let's take your first query:

SELECT SUM(total_plays) FROM album_by_city WHERE album_id = $album_id
AND city = $this_city AND date >= $StartDate AND date <= $EndDate;

You have to replace all variables by some real values. (best if you
simply put an additional echo in your php(?) program).

Assume $album_id=10, $this_city="New York", $StartDate="2001-01-01"
and $EndDate="2001-02-14" would be valid values 

Re: ranking album plays by city, help me beat Oracle

[S A]

> Could you please post the result of "EXPLAIN" for both queries and "SHOW INDEX FROM 
>album_by_city".


I haven't used EXPLAIN before.

There are indexes on each column.

> And, for how much cities does this gets executed?


There are about 100 cities or regions that are used for ranking.

> Additionally, I would be interested, if you used the same method with

> Oracle or another. If so, would you please post it, too.


I didn't write the Oracle queries but they used sub selects and all kinds of not 
available in MySQL optimizations.

Thanks for your reply.

- Sam.
  Benjamin Pflugmann <[EMAIL PROTECTED]> wrote: 
Hi.

Could you please post the result of "EXPLAIN" for both queries and
"SHOW INDEX FROM album_by_city".

And, for how much cities does this gets executed?

Additionally, I would be interested, if you used the same method with
Oracle or another. If so, would you please post it, too.

Bye,

Benjamin.


On Thu, Feb 15, 2001 at 10:47:16AM -0800, [EMAIL PROTECTED] wrote:
> 
> I'm moving over a music play database from Oracle to MySQL. So far MySQL is faster 
>for all reports except this one where Oracle takes 8 seconds & MySQL will take 108 
>with my current method.
[...]
> I want to report the ranking of an album's plays (against all other albums ) for 
>every city over a time period.
> National album ranking for "Beatles #1" between 1/1/2001 and 2/14/2001 :
> New York #1
> Chicago #2
> Boston #1
> Miami #3
> 
> Right now the current approach involves looping over every city & making 2 queries. 
>First query to get the total # of plays for this album for the time period in that 
>city. Next query to figure out how many other albums for that city & for that time 
>have total play sums that are greater, which is the Rank.
> 
> foreach $cities as $this_city
> {
> $this_album_total_plays = "select sum(total_plays) from album_by_city
> where album_id = $album_id
> and city = $this_city
> and date >= $StartDate and date <= $EndDate";
> 
> $rank = "select album_id, sum(total_plays) from album_by_city
> where city = $this_city
> and date >= $StartDate and date <= $EndDate
> group by album_id
> having sum(total_plays) > $this_album_total_plays";
> echo "$this_city $rank";
> }
> Is there a way to do this with fewer queries or perhaps temporary tables for greater 
>speed ?
> 

-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail 
To unsubscribe, e-mail 
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Do You Yahoo!?
- Get personalized email addresses from Yahoo! Mail Personal Address  - only $35 a 
year!

ranking album plays by city, help me beat Oracle

[S A]

I'm moving over a music play database from Oracle to MySQL. So far MySQL is faster for 
all reports except this one where Oracle takes 8 seconds & MySQL will take 108 with my 
current method.

I have a big table of times that an album is played like so :

album_id INT,

total_plays INT,

date DATETIME,

city_id INT

I want to report the ranking of an album's plays (against all other albums ) for every 
city over a time period.

National album ranking for "Beatles #1" between 1/1/2001 and 2/14/2001 :

New York #1

Chicago #2

Boston #1

Miami #3

Right now the current approach involves looping over every city & making 2 queries. 
First query to get the total # of plays for this album for the time period in that 
city. Next query to figure out how many other albums for that city & for that time 
have total play sums that are greater, which is the Rank.

foreach $cities as $this_city

{

$this_album_total_plays = "select sum(total_plays) from album_by_city

 where album_id = $album_id

 and city = $this_city

 and date >= $StartDate and date <= $EndDate";

 

$rank = "select album_id, sum(total_plays) from album_by_city

 where city = $this_city

 and date >= $StartDate and date <= $EndDate

 group by album_id

 having sum(total_plays) > $this_album_total_plays";

echo "$this_city $rank";

}

Is there a way to do this with fewer queries or perhaps temporary tables for greater 
speed ?

 

thanks,

 

- Sam.

 



-
Do You Yahoo!?
- Get personalized email addresses from Yahoo! Mail Personal Address  - only $35 a 
year!

how to get SELECT to return 0 or 1 for WHERE match ?

[S A]

I want to SELECT on one term & have the SELECT return as quickly as possible on the 
first TRUE instance.

Ideally I'd like it to after the first matching case stop looking for other matches ( 
for speed ) and return 1. If there's no matches at all I want it to return 0.

This works OK but returns either the date or an empty set.

SELECT thread_last_post_date FROM threads WHERE thread_last_post_date > '2001-02-01' 
LIMIT 1;

I know from Perl or PHP I can check the number of returned rows but I'd like something 
that just returns 0 or 1 so I don't have to special case.

I'd like something like below but which work (it returns 2 rows of 1 for some reason ) 
:

SELECT 1 FROM threads WHERE thread_last_post_date > '2001-02-01' LIMIT 1;

thanks,

- Sam.



-
Do You Yahoo!?
Yahoo! Auctions - Buy the things you want at great prices.