Try to remove two 'LIKE' conditions. query 3: SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND Field3 LIKE '%John%' ORDER BY Field6 LIMIT 0,20;
What do you have now??? Mikhail. ----- Original Message ----- From: "David Bordas" <[EMAIL PROTECTED]> To: "Mikhail Entaltsev" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, August 27, 2002 5:16 PM Subject: Re: Slow select query, need some clues to speed it up please ... > ME> In this case... > ME> Can you try again? > > Sure. > > query 1 : > > SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable > WHERE > > Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' > OR Field5 LIKE > > '% John%' ) ORDER BY Field6 LIMIT 0,20; > > query 2: > > SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable > WHERE > > Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' ) > >ORDER BY Field6 LIMIT 0,20; > > Results : > query | time > 1 0.78s > 2 1.20s > 1 0.77s > 2 1.21s > 1 0.78s > 2 1.22s > > > IMHO there are 2 ways: > > 1. It will work much faster. > > 2. It won't change speed of execution significantly. > Euh > Third one ? > Remove a condition slow the query ... > > Perhaps i've a problem somewhere, but where .. > Perhaps with some cache variables ? I don't know. > > This is the my.cnf : > # The MySQL server > [mysqld] > port = 3306 > socket = /tmp/mysql.sock > skip-locking > skip-name-resolve > set-variable = key_buffer=128M > set-variable = back_log=100 > set-variable = record_buffer=1M > set-variable = sort_buffer=2M > set-variable = max_allowed_packet=1M > set-variable = thread_stack=128K > set-variable = max_connections=700 > set-variable = max_connect_errors=100 > set-variable = table_cache=256 > set-variable = net_read_timeout=180 > set-variable = net_write_timeout=180 > set-variable = wait_timeout=3600 > > Server have got 1Go and run only mysql ... > Table have 4M rows and index. > Mytable.MYD : 1109586816 bytes > Mytable.MYI : 93065216 > > Thanks > David > --------------------------------------------------------------------- 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
