Re: SELECT with 1,000,000 ROWS
Un beau jour, Victoria Reznichenko a écrit: > "Arno" <[EMAIL PROTECTED]> wrote: >> >> I have aproximately 1,000,000 rows and I would like to do some >> query. The >> first one is to get the number of row so I do : >> >> mysql> SELECT COUNT(*) FROM `Log`; >> +--+ >>> COUNT(*) | >> +--+ >>> 969129 | >> +--+ >> 1 row in set (0.00 sec) >> >> mysql> SELECT COUNT(*) AS `Nb` FROM `Log` WHERE `ID` = 49; >> ++ >>> Nb | >> ++ >>> 969129 | >> ++ >> 1 row in set (1 min 20.99 sec) >> >> But like you can see it, it take a long with the WHERE clause. I use >> Pentium >> III at 650 Mhz with 48 Mb of ram. I think that the probleme come >> from the >> computer but I'm not shure (I need more RAM ?). > > SELECT COUNT(*) without WHERE clause for only one MyISAM/ISAM table > works very quickly. Because number of rows for this table is stored. > > As to the second query, do you have an index on column 'ID'? > There are any index or key for this table. It's table for log so there are any ID. -- Arno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT with 1,000,000 ROWS
"Arno" <[EMAIL PROTECTED]> wrote: > > I have aproximately 1,000,000 rows and I would like to do some query. The > first one is to get the number of row so I do : > > mysql> SELECT COUNT(*) FROM `Log`; > +--+ > | COUNT(*) | > +--+ > | 969129 | > +--+ > 1 row in set (0.00 sec) > > mysql> SELECT COUNT(*) AS `Nb` FROM `Log` WHERE `ID` = 49; > ++ > | Nb | > ++ > | 969129 | > ++ > 1 row in set (1 min 20.99 sec) > > But like you can see it, it take a long with the WHERE clause. I use Pentium > III at 650 Mhz with 48 Mb of ram. I think that the probleme come from the > computer but I'm not shure (I need more RAM ?). SELECT COUNT(*) without WHERE clause for only one MyISAM/ISAM table works very quickly. Because number of rows for this table is stored. As to the second query, do you have an index on column 'ID'? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT with 1,000,000 ROWS
Un beau jour, Nick Gaugler a écrit: >> Hello, >> >> I have aproximately 1,000,000 rows and I would like to do some >> query. The first one is to get the number of row so I do : >> >> mysql> SELECT COUNT(*) FROM `Log`; >> +--+ >>> COUNT(*) | >> +--+ >>> 969129 | >> +--+ >> 1 row in set (0.00 sec) > > MyISAM tables keep a specific count of the number of rows in the > table, that is why this query is extremely fast. > > >> mysql> SELECT COUNT(*) AS `Nb` FROM `Log` WHERE `ID` = 49; >> ++ >>> Nb | >> ++ >>> 969129 | >> ++ >> 1 row in set (1 min 20.99 sec) > > This query is slow, presumably, because MySQL must read all 969129 > rows off of the disk and count them, which will take some time > depending on the size of the rows and the speed of the system. Ok, I thought that but I was not sure. >> But like you can see it, it take a long with the WHERE clause. I use >> Pentium >> III at 650 Mhz with 48 Mb of ram. I think that the probleme come >> from the computer but I'm not shure (I need more RAM ?). > > Yes, your ram will make a difference because the file system will do > caching. You may want to read up on Indexes in MySQL and see how they > are used to optimize queries and how they can also be cached in the > key_buffer. > > http://www.mysql.com/doc/en/MySQL_indexes.html > Thanks for the link, I go to see it immediately. PS: For information, I had forgotten to say that I used MySQL 4.1.0-alpha on Linux Slackware 9.0 -- Arno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT with 1,000,000 ROWS
> Hello, > > I have aproximately 1,000,000 rows and I would like to do some query. > The first one is to get the number of row so I do : > > mysql> SELECT COUNT(*) FROM `Log`; > +--+ > | COUNT(*) | > +--+ > | 969129 | > +--+ > 1 row in set (0.00 sec) MyISAM tables keep a specific count of the number of rows in the table, that is why this query is extremely fast. > mysql> SELECT COUNT(*) AS `Nb` FROM `Log` WHERE `ID` = 49; > ++ > | Nb | > ++ > | 969129 | > ++ > 1 row in set (1 min 20.99 sec) This query is slow, presumably, because MySQL must read all 969129 rows off of the disk and count them, which will take some time depending on the size of the rows and the speed of the system. > But like you can see it, it take a long with the WHERE clause. I use > Pentium III at 650 Mhz with 48 Mb of ram. I think that the probleme > come from the computer but I'm not shure (I need more RAM ?). Yes, your ram will make a difference because the file system will do caching. You may want to read up on Indexes in MySQL and see how they are used to optimize queries and how they can also be cached in the key_buffer. http://www.mysql.com/doc/en/MySQL_indexes.html > Thanks in advance. > > -- > Arno nickg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
