Hi, Isn't there a way to reduce the number of rows that are scanned based on an index - for example the query
SELECT * FROM email WHERE id>1000000 AND subject = 'RE: TEST EMAIL' ORDER BY `id` DESC LIMIT 0,25; will be significantly faster (it will scan less rows) and will most probably return the same results. Using the EXPLAIN command will give you an estimate of how many rows the query will scan before returning a result. Check http://www.mysql.com/doc/en/EXPLAIN.html for more iinformation If you plan to run this query often you should consider indexing the subject field or every time you'll have to wait long time before the query executes. Dobromir Velev [EMAIL PROTECTED] http://www.websitepulse.com/ ----- Original Message ----- From: "Dallas Engelken" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, February 05, 2003 17:30 Subject: sql_big_selects i have a datbase of about 1.5 million log entries that i want to search based on subject (not indexed because it's seldom used). when i try, i get a fatal warning that i cant seem to kick. i have rtfm, and changed max_join_size=2000000 in my.cnf to hopefully make it run without the fatal warning... that didnt work. if i set sql_big_selects=1 and run the query, it takes 10 minutes to return any data. mysql> SELECT * FROM email WHERE 1 AND subject = 'RE: TEST EMAIL' ORDER BY `id` DESC LIMIT 0,25; ERROR 1104: The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok can someone offer some advice to help make this query work. btw, the order by `id` is the primary key. thanks, dallas --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
