After a few off-list e-mails with Tim, I issued ALTER TABLE dbmail_messageblks ADD INDEX ( blocksize AND physmessage_id );
which took almost 11 hours to index. Once done, however, my select statement went from a hair over 50 minutes to 15 seconds. (1.69 seconds after the index was cached.) Wow. Thanks for the help, all! -Ken On Wed, June 24, 2009 12:03 pm, Little, Timothy wrote: > To answer your questions in no particular order, YES you can speed it up > with indexing. > > You might want to first create an index on ( blocksize AND > physmessage_id ). > > Why, you might ask, index on physmessage_id? Because then the db won't > have to do a fetch on items from the table since it's in the INDEX itself, > saving any unnecessary reads. > > Realistically, I can't see that taking more than a few seconds, at most, > to execute. However, making the index might take a serious bit of time. > > Please let us all know how it does or does not work. > > > Tim... > > > -----Original Message----- > From: Ken D'Ambrosio [mailto:k...@jots.org] > Sent: Wednesday, June 24, 2009 11:07 AM > To: mysql@lists.mysql.com > Subject: Indexing? (Warning: relative newbie.) > > > Hi, all. I'm a long-time MySQL user who's only recently had to start > learning some administrative stuff, largely because I finally have a > decently-sized database. My database is about 100 GB; I'm using it -- via > dbmail (www.dbmail.org) -- as a mail server for my company. While dbmail > is well-and-good with its IMAP front-end, I'm thinking of writing a > Python > front-end to do some queries directly against MySQL. But some of them take > a l-o-n-g time. As an example, I've got a table with slightly over a > million records; I'd like to be able to show (say) only IDs of messages > under a half-MB. The query would look something like this: > > select physmessage_id,blocksize from dbmail_messageblks where blocksize < > 500000; > > > That query takes 50 minutes. A smidge long to wait. > > > So I said, "Huh. That's impressive." And I tried it without the > physmessage_id: > select blocksize from dbmail_messageblks where blocksize < 500000; > > That took 14 seconds. A bit more in my timeframe. Can I optimize this > with indexing? Should I be using a different DB engine? Is there a > site/book I should be learning DBA fundamentals from that might offer me > direction for stuff like this? > > Sorry for all the newbie questions, but I haven't done serious database > stuff since Foxbase/dBase III days. Things have changed a little since > then. > > Thanks! > > > -Ken > > > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is believed to be clean. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=tlit...@tgrnet.com > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=...@jots.org > > > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is believed to be clean. > > -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
