[PHP-DB] Re: Table optimization ideas needed
Shelley wrote: Hi all, I made a post a week ago to ask for the idea of the fastest way to get table records. Fyi, http://phparch.cn/index.php/mysql/35-MySQL-programming/126-fastest-way-to-get-total-records-from-a-table Hi Shelly, I question your mysql database setup. I have a log table with about 2 million records which I used for comparison. Here are the queries you mentioned in your link above: SELECT COUNT(*) FROM test_table; SELECT COUNT(id) FROM test_table; SELECT COUNT(1) FROM test_table; The results goes here: mysql SELECT COUNT(*) FROM test_table; +--+ | count(*) | +--+ | 20795139 | +--+ 1 row in set (1 min 8.22 sec) A count(*) against the entire table does not scan all rows so this should be very fast. In my case, a full table count was about .06 seconds. mysql SELECT COUNT(id) FROM test_table; +---+ | count(id) | +---+ | 20795139 | +---+ 1 row in set (1 min 1.45 sec) This query counts all the rows where id is not null. This DOES require a scan of all your table rows and so will be much slower. In my table, this type of query ran about 6.6 seconds. Scaling to 10x (for 20 million records), this query would take about a minute which compares to your results. mysql SELECT COUNT(1) FROM test_table; +--+ | count(1) | +--+ | 20795139 | +--+ 1 row in set (56.67 sec) This query was very fast in my table as well. Since 1 is never null, there is no full table scan. Then you can imagine how much time sql such as select a,b from table_name where c='d' will take. If c is indexed and the number of resulting rows is small then this will be fast. BUt if you need to retrieve several hundred thousand rows, this will take time to process. In my log table, a count(*) of rows with a restriction: select count(*) from log where username = 'test'; returned 104777 in ~ .4 seconds, but retrieveing all those records (from a separate mysql db host) took 15 seconds. Roberto -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] numeric string to single digit array
- Original Message - From: Chris [EMAIL PROTECTED] Date: Wednesday, March 26, 2008 2:18 am Subject: Re: [PHP-DB] numeric string to single digit array Richard Dunne wrote: Sorry for the top-posting, it's my mail client, not my design. I honestly have not even looked at myphpadmin much, using the CLI mostly. It's easy enough to click to another place in your mail client ;) So what happens when you run that query manually? -- Postgresql php tutorials http://www.designmagick.com/ I ran this $query =Select answer from answers where studentID ='A123456789'; $result = mysql_query($query,$connection); $resultArray = str_split($result,1); $count = count($resultArray); As I have two rows in my table, count should, at least I hope, return a value of two, but it doesn't. It returns 1. From what I can assertain, str_split is seeing a two digit number and not splitting it into two single digit numbers. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] numeric string to single digit array
I ran this
$query =Select answer from answers where studentID ='A123456789';
$result = mysql_query($query,$connection);
$resultArray = str_split($result,1);
$count = count($resultArray);
Where's the fetch?
|$result = mysql_query(SELECT answer FROM answers WHERE studentID =
'A123456789';);
$count = 0;
while ($row = mysql_fetch_assoc($result)) {
$count++;
}
|Of course you should count in the query if the result itself is not used:
|$result = mysql_query(SELECT COUNT(answer) AS nr_of_results FROM
answers WHERE studentID = 'A123456789';);
$row = mysql_fetch_assoc($result);
$count = $row['||nr_of_results||'];||
|
Evert
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] numeric string to single digit array
- Original Message -
From: Evert Lammerts [EMAIL PROTECTED]
Date: Wednesday, March 26, 2008 3:12 pm
Subject: Re: [PHP-DB] numeric string to single digit array
I ran this
$query =Select answer from answers where studentID ='A123456789';
$result = mysql_query($query,$connection);
$resultArray = str_split($result,1);
$count = count($resultArray);
Where's the fetch?
|$result = mysql_query(SELECT answer FROM answers WHERE studentID
=
'A123456789';);
$count = 0;
while ($row = mysql_fetch_assoc($result)) {
$count++;
}
|Of course you should count in the query if the result itself is
not used:
|$result = mysql_query(SELECT COUNT(answer) AS nr_of_results FROM
answers WHERE studentID = 'A123456789';);
$row = mysql_fetch_assoc($result);
$count = $row['||nr_of_results||'];||
|
Evert
PHP is telling me that the resource I am using for mysql_fetch_assoc is invalid:
$query =Select answer from answers where studentID ='A123456789';
$result = mysql_query($query,$connection);
$count=0;
while($row = mysql_fetch_assoc($result));
{
$count++;
}
echo $count;
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] numeric string to single digit array
- Original Message -
From: Evert Lammerts [EMAIL PROTECTED]
Date: Wednesday, March 26, 2008 3:39 pm
Subject: Re: [PHP-DB] numeric string to single digit array
PHP is telling me that the resource I am using for
mysql_fetch_assoc is invalid:
$query =Select answer from answers where studentID ='A123456789';
$result = mysql_query($query,$connection);
$count=0;
while($row = mysql_fetch_assoc($result));
{
$count++;
}
echo $count;
Are you sure your database connection has been made?
$connection = mysql_connect('localhost', 'mysql_user',
'mysql_password')
or die(mysql_error());
etc..
If the connection is made alright, try:
$query =Select answer from answers where studentID ='A123456789';
$result = mysql_query($query,$connection) or die(mysql_error());
$count=0;
while($row = mysql_fetch_assoc($result));
{
$count++;
}
echo $count;
This is my code. The only error is at line 15 as I stated above.
1 ?PHP
2 DEFINE (host,localhost);
3 DEFINE (user,root);
4 DEFINE (password,password);
5 DEFINE (database,questions);
6
7 $connection=mysql_connect(host,user,password) or die ('Could not connect'
.mysql_error() );
8
9 $dbConnect=mysql_select_db('questions',$connection);
10 if (!$dbConnect) {die ('Could not connect to database' . mysql_error() );}
11
12 $query =Select answer from answers where studentID ='A123456789';
13 $result = mysql_query($query,$connection);
14 $count=0;
15 while($row = mysql_fetch_assoc($result));
16 {
17 $count++;
18 }
19 echo $count;
20 ?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] numeric string to single digit array
PHP is telling me that the resource I am using for mysql_fetch_assoc is invalid:
$query =Select answer from answers where studentID ='A123456789';
$result = mysql_query($query,$connection);
$count=0;
while($row = mysql_fetch_assoc($result));
{
$count++;
}
echo $count;
Are you sure your database connection has been made?
$connection = mysql_connect('localhost', 'mysql_user', 'mysql_password')
or die(mysql_error());
etc..
If the connection is made alright, try:
$query =Select answer from answers where studentID ='A123456789';
$result = mysql_query($query,$connection) or die(mysql_error());
$count=0;
while($row = mysql_fetch_assoc($result));
{
$count++;
}
echo $count;
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] numeric string to single digit array
This is my code. The only error is at line 15 as I stated above.
1 ?PHP
2 DEFINE (host,localhost);
3 DEFINE (user,root);
4 DEFINE (password,password);
5 DEFINE (database,questions);
6
7 $connection=mysql_connect(host,user,password) or die ('Could not connect'
.mysql_error() );
8
9 $dbConnect=mysql_select_db('questions',$connection);
10 if (!$dbConnect) {die ('Could not connect to database' . mysql_error() );}
11
12 $query =Select answer from answers where studentID ='A123456789';
13 $result = mysql_query($query,$connection);
14 $count=0;
15 while($row = mysql_fetch_assoc($result));
16 {
17 $count++;
18 }
19 echo $count;
20 ?
Turn line 13 into
$result = mysql_query($query) or die(mysql_error());
, so leave out the connection parameter and append the die() function,
and see what error that produces.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] numeric string to single digit array
Evert Lammerts wrote:
This is my code. The only error is at line 15 as I stated above.
1 ?PHP
2 DEFINE (host,localhost);
3 DEFINE (user,root);
4 DEFINE (password,password);
5 DEFINE (database,questions);
6
7 $connection=mysql_connect(host,user,password) or die ('Could not
connect' .mysql_error() );
8
9 $dbConnect=mysql_select_db('questions',$connection);
10 if (!$dbConnect) {die ('Could not connect to database' .
mysql_error() );}
11
12 $query =Select answer from answers where studentID ='A123456789';
13 $result = mysql_query($query,$connection);
14 $count=0;
15 while($row = mysql_fetch_assoc($result));
remove the semi-colon at the end of line 15
16 {
17 $count++;
18 }
19 echo $count;
20 ?
Turn line 13 into
$result = mysql_query($query) or die(mysql_error());
, so leave out the connection parameter and append the die() function,
and see what error that produces.
--
Jason Gerfen
I practice my religion
while stepping on your
toes...
~The Ditty Bops
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] numeric string to single digit array
- Original Message -
From: Evert Lammerts [EMAIL PROTECTED]
Date: Wednesday, March 26, 2008 4:04 pm
Subject: Re: [PHP-DB] numeric string to single digit array
This is my code. The only error is at line 15 as I stated above.
1 ?PHP
2 DEFINE (host,localhost);
3 DEFINE (user,root);
4 DEFINE (password,password);
5 DEFINE (database,questions);
6
7 $connection=mysql_connect(host,user,password) or die ('Could
not connect' .mysql_error() );
8
9 $dbConnect=mysql_select_db('questions',$connection);
10 if (!$dbConnect) {die ('Could not connect to database' .
mysql_error() );}
11
12 $query =Select answer from answers where studentID
='A123456789'; 13 $result = mysql_query($query,$connection);
14 $count=0;
15 while($row = mysql_fetch_assoc($result));
16 {
17 $count++;
18 }
19 echo $count;
20 ?
Turn line 13 into
$result = mysql_query($query) or die(mysql_error());
, so leave out the connection parameter and append the die()
function,
and see what error that produces.
OK. Tried that and count comes back as 1.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] HTML Entity Decode
I found the following function at http://us.php.net/html_entity_decode. This
looks exactly like what I need to filter out information in a text field that's
been copied from Word. The endash or #8211 is not being accepted and my
processing page is halting. My question to you is where in my page do I call
this function and how .. where do I get $number?
In answer to laurynas dot butkus at gmail dot com and [EMAIL PROTECTED] and
their great code2utf-function I added the functionality for entries between
[128, 160[ that are not ASCii, but equal for all major western encodings like
ISO8859-X and UTF-8 that has been mentioned before.
Now, the following function should in fact convert any number (table-entry)
into an UTF-8-character. Thus, the return-value code2utf( number ) equals
the character that is represented by the XML-entity #number; (exceptions:
#129, #141, #143, #144, #157).
To give an example, the function may be useful for creating a UTF-8-compatible
html_entity_decode-function or determining the entry-position of
UTF-8-characters in order to find the correct entity-replacement or similar.
function code2utf($number)
{
if ($number 0)
return FALSE;
if ($number 128)
return chr($number);
// Removing / Replacing Windows Illegals Characters
if ($number 160)
{
if ($number==128) $number=8364;
elseif ($number==129) $number=160; // (Rayo:) #129 using no
relevant sign, thus, mapped to the saved-space #160
elseif ($number==130) $number=8218;
elseif ($number==131) $number=402;
elseif ($number==132) $number=8222;
elseif ($number==133) $number=8230;
elseif ($number==134) $number=8224;
elseif ($number==135) $number=8225;
elseif ($number==136) $number=710;
elseif ($number==137) $number=8240;
elseif ($number==138) $number=352;
elseif ($number==139) $number=8249;
elseif ($number==140) $number=338;
elseif ($number==141) $number=160; // (Rayo:) #129 using no
relevant sign, thus, mapped to the saved-space #160
elseif ($number==142) $number=381;
elseif ($number==143) $number=160; // (Rayo:) #129 using no
relevant sign, thus, mapped to the saved-space #160
elseif ($number==144) $number=160; // (Rayo:) #129 using no
relevant sign, thus, mapped to the saved-space #160
elseif ($number==145) $number=8216;
elseif ($number==146) $number=8217;
elseif ($number==147) $number=8220;
elseif ($number==148) $number=8221;
elseif ($number==149) $number=8226;
elseif ($number==150) $number=8211;
elseif ($number==151) $number=8212;
elseif ($number==152) $number=732;
elseif ($number==153) $number=8482;
elseif ($number==154) $number=353;
elseif ($number==155) $number=8250;
elseif ($number==156) $number=339;
elseif ($number==157) $number=160; // (Rayo:) #129 using no
relevant sign, thus, mapped to the saved-space #160
elseif ($number==158) $number=382;
elseif ($number==159) $number=376;
} //if
if ($number 2048)
return chr(($number 6) + 192) . chr(($number 63) + 128);
if ($number 65536)
return chr(($number 12) + 224) . chr((($number 6) 63) +
128) . chr(($number 63) + 128);
if ($number 2097152)
return chr(($number 18) + 240) . chr((($number 12) 63) +
128) . chr((($number 6) 63) + 128) . chr(($number 63) + 128);
return FALSE;
} //code2utf()
Trish
~
Patricia Van Buskirk
Florida State University, Office of Telecommunications
644 W. Call Street
Tallahassee, FL 32306-1120
(850) 644-9247
~
Life may not be the party we hoped for, but while we're still here we may as
well dance.'' Life may not be the party we hoped for, but while we're still
here we may as well dance.' ' Life may not be the party we hoped for, but while
we're still here we may as well dance.'
Trish
~
Patricia Van Buskirk
Florida State University, Office of Telecommunications
644 W. Call Street
Tallahassee, FL 32306-1120
(850) 644-9247
~
Life may not be the party we hoped for, but while we're still here we may as
well dance.'' Life may not be the party we hoped for, but while we're still
here we may as well dance.' ' Life may not be the party we hoped for, but while
we're still here we may as well dance.'
--
PHP Database Mailing List (http://www.php.net/)
To
Re: [PHP-DB] numeric string to single digit array
OK. Tried that and count comes back as 1. So your query returns only one record. Try $query =Select answer from answers; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] numeric string to single digit array
On Mar 26, 2008, at 12:30 PM, Evert Lammerts wrote: OK. Tried that and count comes back as 1. So your query returns only one record. Try $query =Select answer from answers; Why not do a var_dump() on $result to verify that it is a mysql result resource and then verify the count of rows with: mysql_num_rows($result); -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] numeric string to single digit array
- Original Message - From: Evert Lammerts [EMAIL PROTECTED] Date: Wednesday, March 26, 2008 4:30 pm Subject: Re: [PHP-DB] numeric string to single digit array OK. Tried that and count comes back as 1. So your query returns only one record. Try $query =Select answer from answers; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Tried that as well and got the same result. I tried Select count(answer) as total from answers where studentID='A123456789'; from the CLI and got total = 2 as a result. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] numeric string to single digit array
Tried that as well and got the same result. I tried Select count(answer) as total from answers where studentID='A123456789'; from the CLI and got total = 2 as a result. So, we got rid of the Invalid Resource error and we know that the student id you use occurs in both rows in your table and that your query works fine. Did you get rid of the semicolon @ line 15 while($row = mysql_fetch_assoc($result));, as Jason suggested? Also, an: error_reporting(E_ALL); at the top of your code might help in backtracing. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] numeric string to single digit array
- Original Message - From: Evert Lammerts [EMAIL PROTECTED] Date: Wednesday, March 26, 2008 5:22 pm Subject: Re: [PHP-DB] numeric string to single digit array Tried that as well and got the same result. I tried Select count(answer) as total from answers where studentID='A123456789'; from the CLI and got total = 2 as a result. So, we got rid of the Invalid Resource error and we know that the student id you use occurs in both rows in your table and that your query works fine. Did you get rid of the semicolon @ line 15 while($row = mysql_fetch_assoc($result));, as Jason suggested? Also, an: error_reporting(E_ALL); at the top of your code might help in backtracing. The semi-colon is gone, although I didn't even notice it! I am using two different queries, one for count and the other to access the data itself. After running mysql_fetch_assoc, is foreach ok for accessing array members, or is there a more subtle approach? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Fwd: Re: [PHP-DB] numeric string to single digit array
Using this extract from
http://ie.php.net/manual/en/control-structures.foreach.php
Amaroq
09-Mar-2008 06:40
Even if an array has only one value, it is still an array and foreach will run
it.
?php
$arr[] = I'm an array.;
if(is_array($arr))
{
foreach($arr as $val)
{
echo $val;
}
}
?
The above code outputs:
I'm an array.
-
So if I use:
$query = Select answer from answers where studentID='A123456789';
$result = mysql_query($query,$connection) or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{
foreach($row as $answer)
{
echo $answer . \n;
}
}
I thought I would be able to print out each array element, but I am not getting
any output. Has anyone got a better idea?
---BeginMessage---
Tried that as well and got the same result.
I tried Select count(answer) as total from answers where studentID='A123456789';
from the CLI and got total = 2 as a result.
So, we got rid of the Invalid Resource error and we know that the
student id you use occurs in both rows in your table and that your query
works fine.
Did you get rid of the semicolon @ line 15 while($row =
mysql_fetch_assoc($result));, as Jason suggested? Also, an:
error_reporting(E_ALL);
at the top of your code might help in backtracing.
---End Message---
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: Re: [PHP-DB] numeric string to single digit array
Not sure if this is relevant anymore, but...
i.e. 1223123 into ['1','2,','2','3','1,'2','3'] ?
$num = 1223123;
$nums = array_filter(preg_split('//', $nums));
Or you can use this function.
It's probably better since the array_filter will probably get rid of any 0's
in the string.
function string_to_array($str) {
$arr = preg_split('//', $str);
array_shift($arr); // removing leading null from split
array_pop($arr); // remove training null from split
return $arr;
}
Or, if you just want the numbers from your student id (and you wanted only
numbers):
$num = A123456789;
$nums = string_to_array(preg_replace(/[^0-9]+/, , $nums));
- Jon L.
On Wed, Mar 26, 2008 at 1:35 PM, Richard Dunne [EMAIL PROTECTED]
wrote:
Using this extract from
http://ie.php.net/manual/en/control-structures.foreach.php
Amaroq
09-Mar-2008 06:40
Even if an array has only one value, it is still an array and foreach will
run it.
?php
$arr[] = I'm an array.;
if(is_array($arr))
{
foreach($arr as $val)
{
echo $val;
}
}
?
The above code outputs:
I'm an array.
-
So if I use:
$query = Select answer from answers where studentID='A123456789';
$result = mysql_query($query,$connection) or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{
foreach($row as $answer)
{
echo $answer . \n;
}
}
I thought I would be able to print out each array element, but I am not
getting any output. Has anyone got a better idea?
-- Forwarded message --
From: Evert Lammerts [EMAIL PROTECTED]
To: Richard Dunne [EMAIL PROTECTED]
Date: Wed, 26 Mar 2008 18:22:53 +0100
Subject: Re: [PHP-DB] numeric string to single digit array
Tried that as well and got the same result.
I tried Select count(answer) as total from answers where
studentID='A123456789';
from the CLI and got total = 2 as a result.
So, we got rid of the Invalid Resource error and we know that the
student id you use occurs in both rows in your table and that your query
works fine.
Did you get rid of the semicolon @ line 15 while($row =
mysql_fetch_assoc($result));, as Jason suggested? Also, an:
error_reporting(E_ALL);
at the top of your code might help in backtracing.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Table optimization ideas needed
Roberto Mansfield wrote: Shelley wrote: Hi all, I made a post a week ago to ask for the idea of the fastest way to get table records. Fyi, http://phparch.cn/index.php/mysql/35-MySQL-programming/126-fastest-way-to-get-total-records-from-a-table Hi Shelly, I question your mysql database setup. I have a log table with about 2 million records which I used for comparison. Here are the queries you mentioned in your link above: SELECT COUNT(*) FROM test_table; SELECT COUNT(id) FROM test_table; SELECT COUNT(1) FROM test_table; The results goes here: mysql SELECT COUNT(*) FROM test_table; +--+ | count(*) | +--+ | 20795139 | +--+ 1 row in set (1 min 8.22 sec) A count(*) against the entire table does not scan all rows so this should be very fast. In my case, a full table count was about .06 seconds. You're assuming she's using a myisam table - which will indeed be fast. Switch to an innodb table (or falcon if you're feeling adventurous) and you'll have this issue because they support transactions and are prone to MVCC issues. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Fwd: Re: [PHP-DB] numeric string to single digit array
Richard Dunne wrote:
Using this extract from
http://ie.php.net/manual/en/control-structures.foreach.php
Amaroq
09-Mar-2008 06:40
Even if an array has only one value, it is still an array and foreach will run it.
?php
$arr[] = I'm an array.;
if(is_array($arr))
{
foreach($arr as $val)
{
echo $val;
}
}
?
The above code outputs:
I'm an array.
-
So if I use:
$query = Select answer from answers where studentID='A123456789';
$result = mysql_query($query,$connection) or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{
foreach($row as $answer)
{
echo $answer . \n;
}
}
I thought I would be able to print out each array element, but I am not getting
any output. Has anyone got a better idea?
Instead of using a foreach inside the while loop, just access the array
key directly. The name of the key is the name of the column (or alias)
from your query.
It should be simply:
$query = Select answer from answers where studentID='A123456789';
$result = mysql_query($query,$connection) or die(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
print_r($row);
echo Answer is , $row['answer'], \n;
}
If a result set has no results (ie your query does not return anything -
there are no matching rows), then php won't actually get into the while
loop.
You can see how many rows are returned by using:
$query = Select answer from answers where studentID='A123456789';
$result = mysql_query($query,$connection) or die(mysql_error());
$number_of_results = mysql_num_rows($result);
Though I suggest only doing this for small result sets - otherwise mysql
has to actually process all of the query results which are then stored
in memory on the server.
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Fwd: Re: [PHP-DB] numeric string to single digit array
I did var_dump on the result resource and I got resource(5) of type (mysql result). ---BeginMessage--- On Mar 26, 2008, at 12:30 PM, Evert Lammerts wrote: OK. Tried that and count comes back as 1. So your query returns only one record. Try $query =Select answer from answers; Why not do a var_dump() on $result to verify that it is a mysql result resource and then verify the count of rows with: mysql_num_rows($result); ---End Message--- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Table optimization ideas needed
Yes, Chris. You are right. I think I mentioned in the archive that the table is Innodb engined. Maybe Roberto didn't notice that. On Thu, Mar 27, 2008 at 7:26 AM, Chris [EMAIL PROTECTED] wrote: Roberto Mansfield wrote: Shelley wrote: Hi all, I made a post a week ago to ask for the idea of the fastest way to get table records. Fyi, http://phparch.cn/index.php/mysql/35-MySQL-programming/126-fastest-way-to-get-total-records-from-a-table Hi Shelly, I question your mysql database setup. I have a log table with about 2 million records which I used for comparison. Here are the queries you mentioned in your link above: SELECT COUNT(*) FROM test_table; SELECT COUNT(id) FROM test_table; SELECT COUNT(1) FROM test_table; The results goes here: mysql SELECT COUNT(*) FROM test_table; +--+ | count(*) | +--+ | 20795139 | +--+ 1 row in set (1 min 8.22 sec) A count(*) against the entire table does not scan all rows so this should be very fast. In my case, a full table count was about .06 seconds. You're assuming she's using a myisam table - which will indeed be fast. Switch to an innodb table (or falcon if you're feeling adventurous) and you'll have this issue because they support transactions and are prone to MVCC issues. -- Postgresql php tutorials http://www.designmagick.com/ -- Regards, Shelley
[PHP-DB] Re: Table optimization ideas needed
Does that mean MySQL is slow? Currently one practice I am using is: get $m = $userId%256, then store $userId's information in table_$m. Then the table with more than 20, 000, 000 records is split into 256 tables, and that can speed up the query. I want to listen to your opinion about that. Actually, I wonder how facebook is dealing with this matter. Somebody knows? On Wed, Mar 26, 2008 at 10:05 PM, Roberto Mansfield [EMAIL PROTECTED] wrote: Shelley wrote: Hi all, I made a post a week ago to ask for the idea of the fastest way to get table records. Fyi, http://phparch.cn/index.php/mysql/35-MySQL-programming/126-fastest-way-to-get-total-records-from-a-table Hi Shelly, I question your mysql database setup. I have a log table with about 2 million records which I used for comparison. Here are the queries you mentioned in your link above: SELECT COUNT(*) FROM test_table; SELECT COUNT(id) FROM test_table; SELECT COUNT(1) FROM test_table; The results goes here: mysql SELECT COUNT(*) FROM test_table; +--+ | count(*) | +--+ | 20795139 | +--+ 1 row in set (1 min 8.22 sec) A count(*) against the entire table does not scan all rows so this should be very fast. In my case, a full table count was about .06 seconds. mysql SELECT COUNT(id) FROM test_table; +---+ | count(id) | +---+ | 20795139 | +---+ 1 row in set (1 min 1.45 sec) This query counts all the rows where id is not null. This DOES require a scan of all your table rows and so will be much slower. In my table, this type of query ran about 6.6 seconds. Scaling to 10x (for 20 million records), this query would take about a minute which compares to your results. mysql SELECT COUNT(1) FROM test_table; +--+ | count(1) | +--+ | 20795139 | +--+ 1 row in set (56.67 sec) This query was very fast in my table as well. Since 1 is never null, there is no full table scan. Then you can imagine how much time sql such as select a,b from table_name where c='d' will take. If c is indexed and the number of resulting rows is small then this will be fast. BUt if you need to retrieve several hundred thousand rows, this will take time to process. In my log table, a count(*) of rows with a restriction: select count(*) from log where username = 'test'; returned 104777 in ~ .4 seconds, but retrieveing all those records (from a separate mysql db host) took 15 seconds. Roberto -- Regards, Shelley
Re: [PHP-DB] Table optimization ideas needed
Thank you very much, Chris. :)
Fyi,
On Wed, Mar 26, 2008 at 1:27 PM, Chris [EMAIL PROTECTED] wrote:
Shelley wrote:
+--+---+--+-+---++
| Field| Type | Null | Key | Default
| Extra |
+--+---+--+-+---++
| id | int(11) | | PRI | NULL
| auto_increment |
| owner_id | int(11) | | MUL | 0
||
| owner_name | varchar(50) | | |
||
| visitor_id | int(11) | | MUL | 0
||
| visitor_name | varchar(100) | | |
||
| visit_time | timestamp | YES | | CURRENT_TIMESTAMP
||
| first_time | int(10) unsigned | | | 0
||
| last_time| int(10) unsigned | | MUL | 0
||
| visit_num| mediumint(8) unsigned | | | 0
||
| status | tinyint(3) unsigned | | MUL | 0
||
+--+---+--+-+---++
That's the table which has more than 20 million records.
And what query are you running?
What does:
explain your_query_here;
show?
mysql explain select count(*) from message;
++-+-+---+---++-+--+--+-+
| id | select_type | table | type | possible_keys | key| key_len |
ref | rows | Extra |
++-+-+---+---++-+--+--+-+
| 1 | SIMPLE | message | index | NULL | status | 1 |
NULL | 23051499 | Using index |
++-+-+---+---++-+--+--+-+
Three queries return the same results.
I can see indexes on at least owner_id, visitor_id, last_time and status,
but at least one of those is across multiple columns ('MUL').
Can you show us the index definitions:
show indexes from table_name;
or
show create table table_name;
and just include the indexes at the bottom.
mysql show indexes from message;
+-+++--+-+---+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-+++--+-+---+-+--++--++-+
| message | 0 | PRIMARY|1 | id | A
|23051499 | NULL | NULL | | BTREE | |
| message | 1 | owner_id |1 | owner_id| A
| 922059 | NULL | NULL | | BTREE | |
| message | 1 | visitor_id |1 | visitor_id | A
| 501119 | NULL | NULL | | BTREE | |
| message | 1 | status |1 | status | A
| 18 | NULL | NULL | | BTREE | |
| message | 1 | last_time |1 | last_time | A
|11525749 | NULL | NULL | | BTREE | |
+-+++--+-+---+-+--++--++-+
5 rows in set (1.09 sec)
--
Postgresql php tutorials
http://www.designmagick.com/
--
Regards,
Shelley
Re: [PHP-DB] Table optimization ideas needed
mysql explain select count(*) from message; ++-+-+---+---++-+--+--+-+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+-+---+---++-+--+--+-+ | 1 | SIMPLE | message | index | NULL | status | 1 | NULL | 23051499 | Using index | ++-+-+---+---++-+--+--+-+ Three queries return the same results. That's never going to be fast because you're using innodb tables. From a previous post: Then you can imagine how much time sql such as select a,b from table_name where c='d' will take. I have a lot of tables like that. So my questions is: What's your practice to optimize tables like that? I thought that's what you needed help with. ? -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Table optimization ideas needed
Actually, I wonder how facebook is dealing with this matter. Somebody knows? There's lots of info here: http://highscalability.com/ about various websites (some using mysql, some using postgres, some using oracle etc). -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Table optimization ideas needed
On Thu, Mar 27, 2008 at 10:03 AM, Chris [EMAIL PROTECTED] wrote: mysql explain select count(*) from message; ++-+-+---+---++-+--+--+-+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+-+---+---++-+--+--+-+ | 1 | SIMPLE | message | index | NULL | status | 1 | NULL | 23051499 | Using index | ++-+-+---+---++-+--+--+-+ Three queries return the same results. That's never going to be fast because you're using innodb tables. Should I change it to MyISAM ones? From a previous post: Then you can imagine how much time sql such as select a,b from table_name where c='d' will take. I have a lot of tables like that. So my questions is: What's your practice to optimize tables like that? I thought that's what you needed help with. ? No. That's only part of it. I have a cron job, which get the total visits often. -- Postgresql php tutorials http://www.designmagick.com/ -- Regards, Shelley
Re: [PHP-DB] Re: Table optimization ideas needed
On Thu, Mar 27, 2008 at 10:06 AM, Chris [EMAIL PROTECTED] wrote: Actually, I wonder how facebook is dealing with this matter. Somebody knows? There's lots of info here: http://highscalability.com/ about various websites (some using mysql, some using postgres, some using oracle etc). Thanks. -- Postgresql php tutorials http://www.designmagick.com/ -- Regards, Shelley
Re: [PHP-DB] Table optimization ideas needed
That's never going to be fast because you're using innodb tables.
Should I change it to MyISAM ones?
It depends.
Do you need or use transactions? You can't change - myisam doesn't
support them.
No. That's only part of it. I have a cron job, which get the total
visits often.
If you are using mysql 5.0+, use a trigger to update a counter:
It'll depend on your data set and what you need to get out of the data
(ie what queries you are running) but if you just need a total or even
total per day, this would be the best.
Might need to do it as a stored procedure so you can see if the date
already exists in that table so you can either do an update or insert,
or maybe you can use replace into without needing to do that check.
See http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html and
http://dev.mysql.com/doc/refman/5.0/en/triggers.html - and look at the
comments:
Triggers can call 'CALL' method.
DROP TRIGGER cinema.TESTTRIGGER;
CREATE TRIGGER cinema.TESTTRIGGER AFTER INSERT ON film FOR EACH ROW
CALL cinema.CHECKFILM('Old boy');
It works. The trigger is perform and do its job. The only request is to
use the word 'AFTER' when you call your trigger. 'BEFORE' doesn't work
(the trigger is called ans work but the insert failed in all case).
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Table optimization ideas needed
On Thu, Mar 27, 2008 at 10:40 AM, Chris [EMAIL PROTECTED] wrote:
That's never going to be fast because you're using innodb tables.
Should I change it to MyISAM ones?
It depends.
Do you need or use transactions? You can't change - myisam doesn't support
them.
I haven't tried transactions so far. :(
No. That's only part of it. I have a cron job, which get the total visits
often.
If you are using mysql 5.0+, use a trigger to update a counter:
It is 5.0+.
It'll depend on your data set and what you need to get out of the data (ie
what queries you are running) but if you just need a total or even total per
day, this would be the best.
Good idea. But I wonder whether calling the trigger each insert will loose
any performance.
Might need to do it as a stored procedure so you can see if the date
already exists in that table so you can either do an update or insert, or
maybe you can use replace into without needing to do that check.
See http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html and
http://dev.mysql.com/doc/refman/5.0/en/triggers.html - and look at the
comments:
Triggers can call 'CALL' method.
DROP TRIGGER cinema.TESTTRIGGER;
CREATE TRIGGER cinema.TESTTRIGGER AFTER INSERT ON film FOR EACH ROW
CALL cinema.CHECKFILM('Old boy');
It works. The trigger is perform and do its job. The only request is to
use the word 'AFTER' when you call your trigger. 'BEFORE' doesn't work (the
trigger is called ans work but the insert failed in all case).
--
Postgresql php tutorials
http://www.designmagick.com/
--
Regards,
Shelley
Re: [PHP-DB] Table optimization ideas needed
Good idea. But I wonder whether calling the trigger each insert will loose any performance. It's going to affect things slightly but whether it'll be noticable only you can answer by testing. Another option I sometimes see is set up a replicated slave and run your reports off that instead of the live system, then: 1) it won't bog the live db down 2) it doesn't really matter how many queries you run 3) it doesn't really matter how long they take to run -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
