[PHP-DB] Group by
Hi. In a mysql query, it is recommended that GROUP BY fields be indexed? Using EXPLAIN on one of my queries, I noticed no change between having indexed by GROUP BY field and not indexing it. Any thoughts would be appreciated. In this example, should `history_field` be indexed...? SELECT MAX(`timestamp`) AS `max_ts` FROM `history` WHERE `req_id` = 17 AND `history_record_id` = 35 GROUP BY `history_field` Thanks, ~Philip -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Group by
On Wed, Dec 9, 2009 at 12:52 PM, Philip Thompson philthath...@gmail.com wrote: Hi. In a mysql query, it is recommended that GROUP BY fields be indexed? Using EXPLAIN on one of my queries, I noticed no change between having indexed by GROUP BY field and not indexing it. Any thoughts would be appreciated. In this example, should `history_field` be indexed...? SELECT MAX(`timestamp`) AS `max_ts` FROM `history` WHERE `req_id` = 17 AND `history_record_id` = 35 GROUP BY `history_field` always depends on how many records you have, if you have 100-1000 records is very diferent to 10-50 Thanks, ~Philip -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Julio Araya C.Linux User #386141 Memorista de Ingeniería Civil Informática Ubuntu User #14778 Universidd Técnica Federico Santa María Valparaíso - Chile -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Group by
Hi i recommend always use indexes when programming. developers tend not to.. and when the databases grows it's difficult to modify or make them modify the code. El mié, 09-12-2009 a las 14:22 -0300, Julio Araya escribió: On Wed, Dec 9, 2009 at 12:52 PM, Philip Thompson philthath...@gmail.com wrote: Hi. In a mysql query, it is recommended that GROUP BY fields be indexed? Using EXPLAIN on one of my queries, I noticed no change between having indexed by GROUP BY field and not indexing it. Any thoughts would be appreciated. In this example, should `history_field` be indexed...? SELECT MAX(`timestamp`) AS `max_ts` FROM `history` WHERE `req_id` = 17 AND `history_record_id` = 35 GROUP BY `history_field` always depends on how many records you have, if you have 100-1000 records is very diferent to 10-50 Thanks, ~Philip -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Julio Araya C.Linux User #386141 Memorista de Ingeniería Civil Informática Ubuntu User #14778 Universidd Técnica Federico Santa María Valparaíso - Chile -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Group by
Also you may not see a difference because your not even using 'History_field' anywhere in your SELECT statement. I am a little surprised that you didn't receive a error. On Wed, Dec 9, 2009 at 9:31 AM, Juan Pablo Ramirez ramirez.juanpa...@gmail.com wrote: Hi i recommend always use indexes when programming. developers tend not to.. and when the databases grows it's difficult to modify or make them modify the code. El mié, 09-12-2009 a las 14:22 -0300, Julio Araya escribió: On Wed, Dec 9, 2009 at 12:52 PM, Philip Thompson philthath...@gmail.com wrote: Hi. In a mysql query, it is recommended that GROUP BY fields be indexed? Using EXPLAIN on one of my queries, I noticed no change between having indexed by GROUP BY field and not indexing it. Any thoughts would be appreciated. In this example, should `history_field` be indexed...? SELECT MAX(`timestamp`) AS `max_ts` FROM `history` WHERE `req_id` = 17 AND `history_record_id` = 35 GROUP BY `history_field` always depends on how many records you have, if you have 100-1000 records is very diferent to 10-50 Thanks, ~Philip -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Julio Araya C.Linux User #386141 Memorista de Ingeniería Civil Informática Ubuntu User #14778 Universidd Técnica Federico Santa María Valparaíso - Chile -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- a href=http://www.c28.com/?adid=stiid=19467; img src=http://www.c28.com/images/banner_88x31.gif; border=0 width=88 height=31/a
[PHP-DB] Combing PDO with mysql_pconnect connections in one application -- will this degrade performance?
Hi, We are about to convert all of our queries using mysql_pconnect to prepared statements using PDO database connections. It will take some time to convert the hundreds of SQL statements so the plan is to do it in phases. Is is bad idea to have both a mysql_pconnect and a PDO connection open for the same application? Will this doubling of our database connections for every page hit degrade performance? Any tips on the best way to transition to PDO/prepared statements with minimal disruption? -Sara -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Group by
The only SELECT is on MAX('timestamp'). There is really nothing to
Group BY in this query.
Dewey
Philip Thompson wrote:
Hi.
In a mysql query, it is recommended that GROUP BY fields be indexed? Using
EXPLAIN on one of my queries, I noticed no change between having indexed by GROUP BY
field and not indexing it. Any thoughts would be appreciated.
In this example, should `history_field` be indexed...?
SELECT MAX(`timestamp`) AS `max_ts`
FROM `history`
WHERE `req_id` = 17 AND `history_record_id` = 35
GROUP BY `history_field`
Thanks,
~Philip
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Combing PDO with mysql_pconnect connections in one application -- will this degrade performance?
Hi, We are about to convert all of our queries using mysql_pconnect to prepared statements using PDO database connections. It will take some time to convert the hundreds of SQL statements so the plan is to do it in phases. Is it a bad idea to have both a mysql_pconnect and a PDO connection open for the same application? Will this doubling of our database connections for every page hit degrade performance? Any tips on the best way to transition to PDO/prepared statements with minimal disruption? -Sara -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Group by
On Dec 9, 2009, at 12:58 PM, h...@deweywilliams.com wrote:
The only SELECT is on MAX('timestamp'). There is really nothing to Group BY
in this query.
Dewey
Philip Thompson wrote:
Hi.
In a mysql query, it is recommended that GROUP BY fields be indexed? Using
EXPLAIN on one of my queries, I noticed no change between having indexed by
GROUP BY field and not indexing it. Any thoughts would be appreciated.
In this example, should `history_field` be indexed...?
SELECT MAX(`timestamp`) AS `max_ts` FROM `history` WHERE `req_id` = 17 AND
`history_record_id` = 35
GROUP BY `history_field`
Thanks,
~Philip
Well, that was just an example query. My real one is
SELECT `h`.*
FROM (
SELECT MAX(`history_timestamp`) AS `max_ts`
FROM `history`
WHERE `req_id` = 17 AND `history_record_id` = 35
GROUP BY `history_field`
) AS `max`
INNER JOIN `history` `h` ON `max`.`max_ts` = `h`.`history_timestamp`
WHERE `req_id` = 17 AND `history_record_id` = 35
GROUP BY `history_field`
This returns the results I need. The explain (split up) from this query is...
++-++--++
| id | select_type | table | type | possible_keys
|
++-++--++
| 1 | PRIMARY | h | ref |
req_id_history_record_id,history_timestamp |
| 1 | PRIMARY | derived2 | ALL | NULL
|
| 2 | DERIVED | history| ref | req_id_history_record_id
|
++-++--++
--+-+-+--+--+
key | key_len | ref | rows | Extra
|
--+-+-+--+--+
req_id_history_record_id | 8 | const,const |3 | Using temporary;
Using filesort |
NULL | NULL| NULL|2 | Using where
|
req_id_history_record_id | 8 | |3 | Using where; Using
temporary; Using filesort |
--+-+-+--+--+
3 rows in set (0.01 sec)
There's only 10 records in table right now... but the # of rows it's going to
traverse before find the results is very small.
Do I need to include `history_field` in the inner select?
Thanks,
~Philip
Re: [PHP-DB] Group by
Philip Thompson wrote:
On Dec 9, 2009, at 12:58 PM, h...@deweywilliams.com wrote:
The only SELECT is on MAX('timestamp'). There is really nothing to Group BY in
this query.
Dewey
Philip Thompson wrote:
Hi.
In a mysql query, it is recommended that GROUP BY fields be indexed? Using
EXPLAIN on one of my queries, I noticed no change between having indexed by GROUP BY
field and not indexing it. Any thoughts would be appreciated.
You won't with only 10 rows in the table.
In this example, should `history_field` be indexed...?
SELECT MAX(`timestamp`) AS `max_ts` FROM `history` WHERE `req_id` = 17 AND
`history_record_id` = 35
GROUP BY `history_field`
I'd index
req_id, history_record_id, history_field, timestamp
If you're using myisam tables then all the data can be fetched directly
from the index instead of hitting the data table as well. If you're
using innodb, it'll at least use this index to search for req_id and
history_record_id (assuming these fields are normally in your queries).
There's only 10 records in table right now... but the # of rows it's going to traverse before find the results is very small.
In theory. Sometimes databases don't work that way and instead of
choosing a particular index you'd expect it to, it'll pick another one.
Fill up the table(s) and make sure it does what you expect. Mysql isn't
great at subselects either.
Do I need to include `history_field` in the inner select?
No, you don't have to.
You could do a query like:
select count(id) from table group by another_field;
so you get a count per another_field of how many records there are. Not
a great example as normally you would include another_field in the
select, but you don't have to.
--
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] Combing PDO with mysql_pconnect connections in one application -- will this degrade performance?
Sara Leavitt wrote: Hi, We are about to convert all of our queries using mysql_pconnect to prepared statements using PDO database connections. It will take some time to convert the hundreds of SQL statements so the plan is to do it in phases. Is it a bad idea to have both a mysql_pconnect and a PDO connection open for the same application? Will this doubling of our database connections for every page hit degrade performance? Any tips on the best way to transition to PDO/prepared statements with minimal disruption? As long as you test it (hopefully using some sort of automated tests) you should be right. Since you're using persistent connections (make sure you set pdo to do the same), I don't think you're really doubling the number of connections. Check your mysql logs to see if you are doing double connections or not - that way you'll know for sure. -- 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] Combing PDO with mysql_pconnect connections in one application -- will this degrade performance?
As long as you test it (hopefully using some sort of automated tests) you should be right. Since you're using persistent connections (make sure you set pdo to do the same), I don't think you're really doubling the number of connections. Yeah, I think PDO uses the underlying mysql/pgsql functions anyway - it just provides a common API layer across all database types. Therefore if you call mysql_pconnect outside of PDO, then call the same database server with PDO, technically it should realise the connection has already been made to that server and use your previous connection. As Chris pointed out, make sure PDO will also use persistent connections. Regards, Andy
