Re: Really strange index/speed issues
Chris Hemmings wrote: Baron Schwartz wrote: Hi Chris, Chris Hemmings wrote: Dan Buettner wrote: Chris, a couple of thoughts - First, your index on the section is doing you no good (at this time) since all the values are the same. You may already know that, but thought I'd mention it. Second, my theory on why query #1 is faster - if all your prices range from 1 up, and you're querying for prices greater than 0, then MySQL can just return the first 30 rows after sorting them. The second query, where you are looking for prices greater than 1, MySQL has to sort and then examine a number of rows until it finds enough matching rows (price 1) to satisfy your query. This likely takes a little bit of time. How many rows do you have with price = 1? It would have to scan over that many before it could start satisfying your query, if you think about it. HTH, Dan On 9/10/07, Chris Hemmings [EMAIL PROTECTED] wrote: Hello, I have a table, currently holding 128,978 rows... In this table, I have a section column (int) and a price column (int). Every row has a section of 1 currently, every row has a price, ranging from 1 to 10,000. I have an index on both columns separately. Have a look at these two queries, can someone tell me why there is such a difference in speed of execution? (Note difference in price qualifier) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort Other info: Query cacheing = off MySQL version = 5.0.32 OS = Debian Sarge Sure, the second query returns 29 fewer records than the first, but should that make the difference in time? Hope you can shed some light onto this :-) Ta! Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Thanks Dan, I've got you on the section index... I was going to use that later, when I get somre real data in there. Anyway, I agree with your logic, but, the inverse is happening. The one where it has to actually exclude some rows (because price1) is actually faster. Thats really why this has me baffled, I would presume that the price1 would be slower as it does have to filter rows out first. There's an easy way to find out: FLUSH STATUS, run the query, SHOW STATUS LIKE 'handler%'. Do this on an otherwise quiet server if possible.Or use MySQL Query Profiler -- it does a lot of math for you :-)Baron Thanks Baron! I think you have hit upon something, doing what you said on a 'silent' server, I get the following: SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30; 30 rows in set (0.00 sec) mysql SHOW STATUS LIKE 'handler%'; ++---+ | Variable_name | Value | ++---+ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare| 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 29| | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 14| ++---+ 15 rows in set (0.00 sec) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30; 30 rows in set (0.95 sec) mysql SHOW STATUS LIKE 'handler%'; +++ | Variable_name | Value | +++ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare| 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 128978 | | Handler_read_prev | 0 | | Handler_read_rnd | 30 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 14 | +++ 15 rows in set (0.01 sec) So, the slower query obvisouly
Re: Really strange index/speed issues
Baron Schwartz wrote: Chris Hemmings wrote: Chris Hemmings wrote: Baron Schwartz wrote: Hi Chris, Chris Hemmings wrote: Dan Buettner wrote: Chris, a couple of thoughts - First, your index on the section is doing you no good (at this time) since all the values are the same. You may already know that, but thought I'd mention it. Second, my theory on why query #1 is faster - if all your prices range from 1 up, and you're querying for prices greater than 0, then MySQL can just return the first 30 rows after sorting them. The second query, where you are looking for prices greater than 1, MySQL has to sort and then examine a number of rows until it finds enough matching rows (price 1) to satisfy your query. This likely takes a little bit of time. How many rows do you have with price = 1? It would have to scan over that many before it could start satisfying your query, if you think about it. HTH, Dan On 9/10/07, Chris Hemmings [EMAIL PROTECTED] wrote: Hello, I have a table, currently holding 128,978 rows... In this table, I have a section column (int) and a price column (int). Every row has a section of 1 currently, every row has a price, ranging from 1 to 10,000. I have an index on both columns separately. Have a look at these two queries, can someone tell me why there is such a difference in speed of execution? (Note difference in price qualifier) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort Other info: Query cacheing = off MySQL version = 5.0.32 OS = Debian Sarge Sure, the second query returns 29 fewer records than the first, but should that make the difference in time? Hope you can shed some light onto this :-) Ta! Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Thanks Dan, I've got you on the section index... I was going to use that later, when I get somre real data in there. Anyway, I agree with your logic, but, the inverse is happening. The one where it has to actually exclude some rows (because price1) is actually faster. Thats really why this has me baffled, I would presume that the price1 would be slower as it does have to filter rows out first. There's an easy way to find out: FLUSH STATUS, run the query, SHOW STATUS LIKE 'handler%'. Do this on an otherwise quiet server if possible.Or use MySQL Query Profiler -- it does a lot of math for you :-)Baron Thanks Baron! I think you have hit upon something, doing what you said on a 'silent' server, I get the following: SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30; 30 rows in set (0.00 sec) mysql SHOW STATUS LIKE 'handler%'; ++---+ | Variable_name | Value | ++---+ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare| 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 29| | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 14| ++---+ 15 rows in set (0.00 sec) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30; 30 rows in set (0.95 sec) mysql SHOW STATUS LIKE 'handler%'; +++ | Variable_name | Value | +++ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare| 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 128978 | | Handler_read_prev | 0 | | Handler_read_rnd | 30 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 14 | +++ 15 rows
Really strange index/speed issues
Hello, I have a table, currently holding 128,978 rows... In this table, I have a section column (int) and a price column (int). Every row has a section of 1 currently, every row has a price, ranging from 1 to 10,000. I have an index on both columns separately. Have a look at these two queries, can someone tell me why there is such a difference in speed of execution? (Note difference in price qualifier) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort Other info: Query cacheing = off MySQL version = 5.0.32 OS = Debian Sarge Sure, the second query returns 29 fewer records than the first, but should that make the difference in time? Hope you can shed some light onto this :-) Ta! Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really strange index/speed issues
Jeremy Cole wrote: Hi Chris, Chris Hemmings wrote: Hello, I have a table, currently holding 128,978 rows... In this table, I have a section column (int) and a price column (int). Every row has a section of 1 currently, every row has a price, ranging from 1 to 10,000. I have an index on both columns separately. Have a look at these two queries, can someone tell me why there is such a difference in speed of execution? (Note difference in price qualifier) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort Other info: Query cacheing = off MySQL version = 5.0.32 OS = Debian Sarge Sure, the second query returns 29 fewer records than the first, but should that make the difference in time? Hope you can shed some light onto this :-) Did you run both queries multiple times and average the time taken? Otherwise, it seems likely that in one instance the data was cached, and in the other it was not. The query_cache being off does not affect caching in this sense. Regards, Jeremy Jeremy, Thanks for the swift reply :-) I have tried the query multiple times... Just to double check, here are the timings for each, 5 times: SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9605 sec) Showing rows 0 - 29 (128,978 total, Query took 0.9506 sec) Showing rows 0 - 29 (128,978 total, Query took 0.9556 sec) Showing rows 0 - 29 (128,978 total, Query took 0.9614 sec) Showing rows 0 - 29 (128,978 total, Query took 0.9474 sec) SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Showing rows 0 - 29 (128,949 total, Query took 0.0009 sec) Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Wierd huh? Would you like any of the mysqld runtime settings? Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really strange index/speed issues
Dan Buettner wrote: Chris, a couple of thoughts - First, your index on the section is doing you no good (at this time) since all the values are the same. You may already know that, but thought I'd mention it. Second, my theory on why query #1 is faster - if all your prices range from 1 up, and you're querying for prices greater than 0, then MySQL can just return the first 30 rows after sorting them. The second query, where you are looking for prices greater than 1, MySQL has to sort and then examine a number of rows until it finds enough matching rows (price 1) to satisfy your query. This likely takes a little bit of time. How many rows do you have with price = 1? It would have to scan over that many before it could start satisfying your query, if you think about it. HTH, Dan On 9/10/07, Chris Hemmings [EMAIL PROTECTED] wrote: Hello, I have a table, currently holding 128,978 rows... In this table, I have a section column (int) and a price column (int). Every row has a section of 1 currently, every row has a price, ranging from 1 to 10,000. I have an index on both columns separately. Have a look at these two queries, can someone tell me why there is such a difference in speed of execution? (Note difference in price qualifier) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort Other info: Query cacheing = off MySQL version = 5.0.32 OS = Debian Sarge Sure, the second query returns 29 fewer records than the first, but should that make the difference in time? Hope you can shed some light onto this :-) Ta! Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Thanks Dan, I've got you on the section index... I was going to use that later, when I get somre real data in there. Anyway, I agree with your logic, but, the inverse is happening. The one where it has to actually exclude some rows (because price1) is actually faster. Thats really why this has me baffled, I would presume that the price1 would be slower as it does have to filter rows out first. Still confused. Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really strange index/speed issues
Baron Schwartz wrote: Hi Chris, Chris Hemmings wrote: Dan Buettner wrote: Chris, a couple of thoughts - First, your index on the section is doing you no good (at this time) since all the values are the same. You may already know that, but thought I'd mention it. Second, my theory on why query #1 is faster - if all your prices range from 1 up, and you're querying for prices greater than 0, then MySQL can just return the first 30 rows after sorting them. The second query, where you are looking for prices greater than 1, MySQL has to sort and then examine a number of rows until it finds enough matching rows (price 1) to satisfy your query. This likely takes a little bit of time. How many rows do you have with price = 1? It would have to scan over that many before it could start satisfying your query, if you think about it. HTH, Dan On 9/10/07, Chris Hemmings [EMAIL PROTECTED] wrote: Hello, I have a table, currently holding 128,978 rows... In this table, I have a section column (int) and a price column (int). Every row has a section of 1 currently, every row has a price, ranging from 1 to 10,000. I have an index on both columns separately. Have a look at these two queries, can someone tell me why there is such a difference in speed of execution? (Note difference in price qualifier) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort Other info: Query cacheing = off MySQL version = 5.0.32 OS = Debian Sarge Sure, the second query returns 29 fewer records than the first, but should that make the difference in time? Hope you can shed some light onto this :-) Ta! Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Thanks Dan, I've got you on the section index... I was going to use that later, when I get somre real data in there. Anyway, I agree with your logic, but, the inverse is happening. The one where it has to actually exclude some rows (because price1) is actually faster. Thats really why this has me baffled, I would presume that the price1 would be slower as it does have to filter rows out first. There's an easy way to find out: FLUSH STATUS, run the query, SHOW STATUS LIKE 'handler%'. Do this on an otherwise quiet server if possible.Or use MySQL Query Profiler -- it does a lot of math for you :-)Baron Thanks Baron! I think you have hit upon something, doing what you said on a 'silent' server, I get the following: SELECT * FROM `table1` WHERE price 1 AND section =1 ORDER BY price LIMIT 0 , 30; 30 rows in set (0.00 sec) mysql SHOW STATUS LIKE 'handler%'; ++---+ | Variable_name | Value | ++---+ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare| 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 29| | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 14| ++---+ 15 rows in set (0.00 sec) SELECT * FROM `table1` WHERE price 0 AND section =1 ORDER BY price LIMIT 0 , 30; 30 rows in set (0.95 sec) mysql SHOW STATUS LIKE 'handler%'; +++ | Variable_name | Value | +++ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare| 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 128978 | | Handler_read_prev | 0 | | Handler_read_rnd | 30 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 14 | +++ 15 rows in set (0.01 sec) So, the slower query obvisouly has the larger
What order is a mysql query executed?
Hello there!
If I have a table with 100,000 records in and I have two columns in the
table, one an ID field (int) numbered 1 to 100,000 that is indexed etc. The
other field is a text field with say 100 words in each row.
What would produce the fastest search if I wanted to search for all entries
that had a id50,000 and contained the word 'CAT'.
If this produced a result of 25,000 records, would it be quicker to do
either:
SELECT * FROM table WHERE idfield 5 AND MATCH textfield AGAINST('CAT'
IN BOOLEAN MODE}
or
SELECT * FROM table WHERE MATCH textfield AGAINST('CAT' IN BOOLEAN MODE} AND
idfield 5
The reason I ask this is because it would obviously be quicker to do a MATCH
over 50,000 records rather than the complete 100,000 records.
Thanks!
Chris..
[query]
http://www.exchangeandmart.co.uk
IMPORTANT. Any views or opinions are solely those of the author and do not
necessarily represent those of United Business Media. The information
transmitted is intended only for the person or entity to which it is
addressed and may contain confidential and/or privileged material. If you
are not the intended recipient of this message, please do not read, copy,
use or disclose this communication and notify the sender immediately. It
should be noted that any review, retransmission, dissemination or other use
of, or taking of any action in reliance upon, this information by persons
or entities other than the intended recipient is prohibited.
-
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
