Re: Slow query times
Balazs Rauznitz wrote: However when the 'sex' column is involved: mysql select count(*) from sex where id459000 and id =46 and sex = 'M'; +--+ | count(*) | +--+ | 504 | +--+ 1 row in set (5.09 sec) Any way to make this faster ? Well, MySql can only use 1 index per table to optimize a query. It's apparently using the index on ID, so it then needs to examine all records in the right ID range to see if they meet the sex='M' condition. You could build an index on both fields as one index, and MySql should be able to use it to resolve both parts of the query. create index id_and_sex_index on sex (id,sex); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query times
Chuck Gadd said: Balazs Rauznitz wrote: mysql select count(*) from sex where id459000 and id =46 and sex = 'M'; +--+ | count(*) | +--+ | 504 | +--+ 1 row in set (5.09 sec) Any way to make this faster ? Well, MySql can only use 1 index per table to optimize a query. It's apparently using the index on ID, so it then needs to examine all records in the right ID range to see if they meet the sex='M' condition. You could build an index on both fields as one index, and MySql should be able to use it to resolve both parts of the query. create index id_and_sex_index on sex (id,sex); Does that really matter? We are talking about 5 byte rows (+ overhead). What is the minimum size you get back from a disk read? 512 bytes? 8192 bytes? How many records will there be in 1 disk read? What is the chance that all records are M or F and thus the read would not be necessary? RAM might be cheap nowadays, but wouldn't you loose more by crowding out the key bufer as you gain by reducing in-memory comparisons (I seriously doubt it will save you any disk I/O)? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query times
ps: 'optimize table' seems to have no effect. I was also able to reproduce this on a different mysql server... Balazs On Mon, Jan 19, 2004 at 10:39:26AM -0500, Balazs Rauznitz wrote: While doing some benchmarks the other day, I saw surprisingly slow query results on columns that were indexed. Here's the table definition: create table sex ( id integer, sex char(1)); create index id_index on sex (id); create index sex_index on sex (sex); Then I loaded a million rows, id was from 1 to 1_000_000, sex was randomly 'F' or 'M'. When searching on 'id' everything is snappy: mysql select count(*) from sex where id459000 and id =46; +--+ | count(*) | +--+ | 1000 | +--+ 1 row in set (0.00 sec) However when the 'sex' column is involved: mysql select count(*) from sex where id459000 and id =46 and sex = 'M'; +--+ | count(*) | +--+ | 504 | +--+ 1 row in set (5.09 sec) Any way to make this faster ? I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM. Insert jokes about sex making MySQL slow here ;-) Thanks, Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow query times
I wouldn't imagine that creating an index on a column with only two possible values could make things any faster. You only get a maximum 50% reduction in row scans, rather than the normal log based reduction with a random value distribution. In addition, you contend with the overhead of using the index. What is the runtime without the index? Matt -Original Message- From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] Sent: Monday, January 19, 2004 12:53 PM To: [EMAIL PROTECTED] Subject: Slow query times While doing some benchmarks the other day, I saw surprisingly slow query results on columns that were indexed. Here's the table definition: create table sex ( id integer, sex char(1)); create index id_index on sex (id); create index sex_index on sex (sex); Then I loaded a million rows, id was from 1 to 1_000_000, sex was randomly 'F' or 'M'. When searching on 'id' everything is snappy: mysql select count(*) from sex where id459000 and id =46; +--+ | count(*) | +--+ | 1000 | +--+ 1 row in set (0.00 sec) However when the 'sex' column is involved: mysql select count(*) from sex where id459000 and id =46 and sex = 'M'; +--+ | count(*) | +--+ | 504 | +--+ 1 row in set (5.09 sec) Any way to make this faster ? I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM. Insert jokes about sex making MySQL slow here Thanks, Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow query times
-Forwarded by Peter J Milanese/MHT/Nypl on 01/20/2004 02:37PM - To: [EMAIL PROTECTED] From: Peter J Milanese/MHT/Nypl Date: 01/20/2004 02:34PM cc: [EMAIL PROTECTED] Subject: RE: Slow query times You may also want to try : count(1) instead of count(*) count(*) pulls back the data while count(1) does not. Peter J. Milanese -Matt Griffin [EMAIL PROTECTED] wrote: - To: [EMAIL PROTECTED] From: Matt Griffin [EMAIL PROTECTED] Date: 01/20/2004 02:33PM Subject: RE: Slow query times I wouldn't imagine that creating an index on a column with only two possible values could make things any faster. You only get a maximum 50% reduction in row scans, rather than the normal log based reduction with a random value distribution. In addition, you contend with the overhead of using the index. What is the runtime without the index? Matt -Original Message- From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] Sent: Monday, January 19, 2004 12:53 PM To: [EMAIL PROTECTED] Subject: Slow query times While doing some benchmarks the other day, I saw surprisingly slow query results on columns that were indexed. Here's the table definition: create table sex ( id integer, sex char(1)); create index id_index on sex (id); create index sex_index on sex (sex); Then I loaded a million rows, id was from 1 to 1_000_000, sex was randomly 'F' or 'M'. When searching on 'id' everything is snappy: mysql select count(*) from sex where id459000 and id =46; +--+ | count(*) | +--+ | 1000 | +--+ 1 row in set (0.00 sec) However when the 'sex' column is involved: mysql select count(*) from sex where id459000 and id =46 and sex = 'M'; +--+ | count(*) | +--+ | 504 | +--+ 1 row in set (5.09 sec) Any way to make this faster ? I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM. Insert jokes about sex making MySQL slow here Thanks, Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query times
You are perfectly correct. Without the index on sex query times are lightning fast. I don't quite get you explanation why; where can I read up on how queries are done with and without an index ? Can one make a generalization that unless the number of different values in a column is less than log(number of rows), it does not make sense to use an index ? Thanks, Balazs On Tue, Jan 20, 2004 at 02:33:28PM -0500, Matt Griffin wrote: I wouldn't imagine that creating an index on a column with only two possible values could make things any faster. You only get a maximum 50% reduction in row scans, rather than the normal log based reduction with a random value distribution. In addition, you contend with the overhead of using the index. What is the runtime without the index? Matt -Original Message- From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] Sent: Monday, January 19, 2004 12:53 PM To: [EMAIL PROTECTED] Subject: Slow query times While doing some benchmarks the other day, I saw surprisingly slow query results on columns that were indexed. Here's the table definition: create table sex ( id integer, sex char(1)); create index id_index on sex (id); create index sex_index on sex (sex); Then I loaded a million rows, id was from 1 to 1_000_000, sex was randomly 'F' or 'M'. When searching on 'id' everything is snappy: mysql select count(*) from sex where id459000 and id =46; +--+ | count(*) | +--+ | 1000 | +--+ 1 row in set (0.00 sec) However when the 'sex' column is involved: mysql select count(*) from sex where id459000 and id =46 and sex = 'M'; +--+ | count(*) | +--+ | 504 | +--+ 1 row in set (5.09 sec) Any way to make this faster ? I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM. Insert jokes about sex making MySQL slow here Thanks, Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow query times
I couldn't say without some hard benchmarks. Just keep in mind that an index still only breaks your data up into fairly large compartments which must be scanned. It's like this: rows scanned = # rows / (log2(# of options)) At some threshold of log2(# of options) it's faster to scan the whole table. MySQL usually detects this and ignores the index, especially if another index was already used to break down the number of rows. Matt -Original Message- From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 2:46 PM To: Matt Griffin Cc: [EMAIL PROTECTED] Subject: Re: Slow query times You are perfectly correct. Without the index on sex query times are lightning fast. I don't quite get you explanation why; where can I read up on how queries are done with and without an index ? Can one make a generalization that unless the number of different values in a column is less than log(number of rows), it does not make sense to use an index ? Thanks, Balazs On Tue, Jan 20, 2004 at 02:33:28PM -0500, Matt Griffin wrote: I wouldn't imagine that creating an index on a column with only two possible values could make things any faster. You only get a maximum 50% reduction in row scans, rather than the normal log based reduction with a random value distribution. In addition, you contend with the overhead of using the index. What is the runtime without the index? Matt -Original Message- From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] Sent: Monday, January 19, 2004 12:53 PM To: [EMAIL PROTECTED] Subject: Slow query times While doing some benchmarks the other day, I saw surprisingly slow query results on columns that were indexed. Here's the table definition: create table sex ( id integer, sex char(1)); create index id_index on sex (id); create index sex_index on sex (sex); Then I loaded a million rows, id was from 1 to 1_000_000, sex was randomly 'F' or 'M'. When searching on 'id' everything is snappy: mysql select count(*) from sex where id459000 and id =46; +--+ | count(*) | +--+ | 1000 | +--+ 1 row in set (0.00 sec) However when the 'sex' column is involved: mysql select count(*) from sex where id459000 and id =46 and sex = 'M'; +--+ | count(*) | +--+ | 504 | +--+ 1 row in set (5.09 sec) Any way to make this faster ? I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM. Insert jokes about sex making MySQL slow here Thanks, Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow query times
Ignore that silly equation. I tried to simplify and ended up with something mathematically ridiculous. I'm sure someone can come up with a more accurate simplification. Matt -Original Message- From: Matt Griffin [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 3:46 PM To: 'Balazs Rauznitz' Cc: [EMAIL PROTECTED] Subject: RE: Slow query times I couldn't say without some hard benchmarks. Just keep in mind that an index still only breaks your data up into fairly large compartments which must be scanned. It's like this: rows scanned = # rows / (log2(# of options)) At some threshold of log2(# of options) it's faster to scan the whole table. MySQL usually detects this and ignores the index, especially if another index was already used to break down the number of rows. Matt -Original Message- From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 2:46 PM To: Matt Griffin Cc: [EMAIL PROTECTED] Subject: Re: Slow query times You are perfectly correct. Without the index on sex query times are lightning fast. I don't quite get you explanation why; where can I read up on how queries are done with and without an index ? Can one make a generalization that unless the number of different values in a column is less than log(number of rows), it does not make sense to use an index ? Thanks, Balazs On Tue, Jan 20, 2004 at 02:33:28PM -0500, Matt Griffin wrote: I wouldn't imagine that creating an index on a column with only two possible values could make things any faster. You only get a maximum 50% reduction in row scans, rather than the normal log based reduction with a random value distribution. In addition, you contend with the overhead of using the index. What is the runtime without the index? Matt -Original Message- From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] Sent: Monday, January 19, 2004 12:53 PM To: [EMAIL PROTECTED] Subject: Slow query times While doing some benchmarks the other day, I saw surprisingly slow query results on columns that were indexed. Here's the table definition: create table sex ( id integer, sex char(1)); create index id_index on sex (id); create index sex_index on sex (sex); Then I loaded a million rows, id was from 1 to 1_000_000, sex was randomly 'F' or 'M'. When searching on 'id' everything is snappy: mysql select count(*) from sex where id459000 and id =46; +--+ | count(*) | +--+ | 1000 | +--+ 1 row in set (0.00 sec) However when the 'sex' column is involved: mysql select count(*) from sex where id459000 and id =46 and sex = 'M'; +--+ | count(*) | +--+ | 504 | +--+ 1 row in set (5.09 sec) Any way to make this faster ? I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM. Insert jokes about sex making MySQL slow here Thanks, Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query times
At 11:52 AM 1/19/2004, you wrote: While doing some benchmarks the other day, I saw surprisingly slow query results on columns that were indexed. It's because MySQL won't use the Sex_Index index because the value that you are searching for (Namely F or M) accounts for more than 30% of the data, probably 50%. So if F appeared in say 20% of the data, then the index would be used. MYSQL does this because the overhead in accessing the index for more than 30% of the data is greater than just accessing the data alone. It has to do at least 1 I/O to get the index entry, and another to get the corresponding data record. From the manual: http://www.mysql.com/doc/en/MySQL_indexes.html Note that sometime MySQL will not use an index, even if one is available. One instance of this is when use of the index would require MySQL to access more than 30% of the rows in the table. (In this case a table scan is probably much faster, as it will require many fewer seeks.) However, if such a query uses LIMIT to only retrieve part of the rows, MySQL will use an index anyway, as it can much more quickly find the few rows to return in the result. You can try a LIMIT 100 to force it to use an index. Of course for what you are trying to accomplish, a select count(*) as Num from Sex group by Sex would be much faster. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query times
Hi Balazs, The likely answer is the one that nobody mentioned: it's an optimizer bug in 4.0.16. If you look at the EXPLAIN output for the second query, it's probably using a ref type on the sex column, instead of the more restrictive id index. If so, that's the bug. From http://www.mysql.com/doc/en/News-4.0.17.html Fixed optimizer bug, introduced in 4.0.16, when REF access plan was preferred to more efficient RANGE on another column. If that's the problem, upgrading will fix it. :-) Matt - Original Message - From: Balazs Rauznitz Sent: Monday, January 19, 2004 9:39 AM Subject: Slow query times While doing some benchmarks the other day, I saw surprisingly slow query results on columns that were indexed. Here's the table definition: create table sex ( id integer, sex char(1)); create index id_index on sex (id); create index sex_index on sex (sex); Then I loaded a million rows, id was from 1 to 1_000_000, sex was randomly 'F' or 'M'. When searching on 'id' everything is snappy: mysql select count(*) from sex where id459000 and id =46; +--+ | count(*) | +--+ | 1000 | +--+ 1 row in set (0.00 sec) However when the 'sex' column is involved: mysql select count(*) from sex where id459000 and id =46 and sex = 'M'; +--+ | count(*) | +--+ | 504 | +--+ 1 row in set (5.09 sec) Any way to make this faster ? I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM. Insert jokes about sex making MySQL slow here ;-) Thanks, Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query times
- Original Message - From: Peter J Milanese Sent: Tuesday, January 20, 2004 1:37 PM Subject: RE: Slow query times You may also want to try : count(1) instead of count(*) count(*) pulls back the data while count(1) does not. Completely untrue... Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
