Hello Jeremy and List, Thanks for that pointer, altho I am not feeling it will help, i'll try it today. It'll take quite some time to add the index, there are like 12,000,000 rows.
What is also a bit odd is that the same query below, if I change the where clause to "where uri_scheme = 'mailto'", the query has sub-second response time. When I run the query as stated below, it takes 3 minutes. where uri_scheme = 'mailto' takes .5 seconds where uri_scheme = 'http' takes 3 minutes The difference is that there are 4 million rows that have mailto, 8 million that have http. Twice the records equates to 300+ fold performance degredation. I have tried configuring the database to use more memory, but what memory would I increase? Setting various variables very high (this server has 2GB ram) does not help. Perhaps I am tuning the wrong thing. Any further thoughts? Thanks! On Saturday, April 27, 2002, at 01:31 PM, Jeremy Zawodny wrote: > On Sat, Apr 27, 2002 at 08:25:26AM -0400, Richard F. Rebel wrote: >> >> Hello, >> >> Perhaps I don't understand indexes but I was wondering how to improve >> teh >> performance of a query on an indexed column that uses MAX(). >> >> mysql> explain select max(master_mtime) from redirects where >> uri_scheme = >> 'http'; >> >+-----------+------+-----------------------------+----------------+---------+-------+---------+------------+| > >> table | type | possible_keys | key | >> key_len >> | ref | rows | Extra | >> >+-----------+------+-----------------------------+----------------+---------+-------+---------+------------+| > >> redirects | ref | full_uri_idx,uri_scheme_idx | uri_scheme_idx | >> 16 >> | const | 7694050 | where used | >> >+-----------+------+-----------------------------+----------------+---------+-------+---------+------------+1 > >> row in set (0.01 sec) >> >> There is an index on master_mtime but it's not listed by explain??? > > Indexes typically help out in the WHERE clause. You'll notice that > MySQL selected the uri_scheme_idx index because that's the column on > which you've asked it to filter. > > You might get better performance out of a compund index on > (uri_scheme, master_time), but you'll have to be the judge of that. > > Jeremy > -- > Jeremy D. Zawodny, <[EMAIL PROTECTED]> > Technical Yahoo - Yahoo Finance > Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 > > MySQL 3.23.47-max: up 79 days, processed 2,065,294,319 queries > (302/sec. avg) > > --------------------------------------------------------------------- > 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 <mysql-unsubscribe- > [EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- 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
