Re: Optimize question
On Wed, 08 Nov 2006 15:38:27 -0500, Francis wrote: If I do a select to get the log for this date 2006-01-01 : Select * from mytable where mydate = '2006-01-01' and mydate = '2006-01-01'. Do you think is more faster to do on table with have only one date or is the same think if I do it on table whit all day ? That is what indexes are for. And last question, if I do a group by, I think the group by is faster on column as type INT Vs Varchar ? Not necessarily so. -- Leandro Guimarães Faria Corcete DUTRA+55 (11) 5685 2219 http://br.geocities.com./lgcdutra/ +55 (11) 9406 7191 mailto:[EMAIL PROTECTED] +55 (11) 2122 0302 xmpp:[EMAIL PROTECTED] ymsgr:sendIM?lgcdutra BRASIL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimize question
Hi list, I have a table with about 17 millons of records. This table contain log of web for one month and I have an other table but with only 1 day of log. If I do a select to get the log for this date 2006-01-01 : Select * from mytable where mydate = '2006-01-01' and mydate = '2006-01-01'. Do you think is more faster to do on table with have only one date or is the same think if I do it on table whit all day ? And last question, if I do a group by, I think the group by is faster on column as type INT Vs Varchar ? Ty for your reply -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimize Question
Hi all!
I'm trying to optimize the statement below, but I can't figure out why this
query is still doing an ALL join with a filesort.
There are several indexes on both my main table ('t'), and all the tables
I'm trying to join. (I also tried adding an index for t.publish, which
bumped my join type to 'range' but still gave me a 'filesort' and seemed to
produce slower query results.)
What can I do to further optimize?
Thanks,
Patrick
...
**SELECT STATEMENT**
SELECT
t.id,
t.name_present,
t.location_city,
t.photo,
a.name_short,
a.name_long,
b.name as b_name,
c.name as c_name,
d.id as d_id,
d.name as d_name
FROM t
LEFT JOIN a
ON t.a_id = a.id
LEFT JOIN b
ON t.b_id = b.id
LEFT JOIN c
ON t.c_id = c.id
LEFT JOIN d
ON t.d_id = d.id
LEFT JOIN e_t
ON e_t.t_id = t.id
LEFT JOIN e
ON e_t.e_id = e.id
WHERE t.publish = 'Yes' AND e.id = '2'
ORDER BY t.photo, t.name_present, t.location_city
LIMIT 0,25
**EXPLAIN RESULTS FOR THIS QUERY**
+-++-+-+--+--+-+
| tbl | type | key | key_len | ref | rows | Extra |
+-++-+-+--+--+-+
| t | ALL| NULL|NULL | NULL | 4279 | where used; filesort|
| a | eq_ref | PRIMARY | 2 | t.a_id |1 | |
| b | eq_ref | PRIMARY | 2 | t.b_id |1 | |
| c | eq_ref | PRIMARY | 1 | t.c_id |1 | |
| d | eq_ref | PRIMARY | 1 | t.d_id |1 | |
| e | eq_ref | PRIMARY | 1 | e_t.e_id |1 | where used; index |
| e_t | ref| t_id| 2 | t.id |1 | |
+-++-+-+--+--+-+
('possible_keys' was identical to 'key', but i removed it due to space
issues)
**INDEXES ON 't'**
+---+---+
| Key_name | Column_name |
+---+---+
| PRIMARY | id|
| location_city | location_city |
| name_present | name_present |
| a_id | a_id |
| b_id | b_id |
| c_id | c_id |
| d_id | d_id |
+---+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize Question
In the last episode (Aug 25), Patrick Crowley said:
Hi all!
I'm trying to optimize the statement below, but I can't figure out why this
query is still doing an ALL join with a filesort.
There are several indexes on both my main table ('t'), and all the tables
I'm trying to join. (I also tried adding an index for t.publish, which
bumped my join type to 'range' but still gave me a 'filesort' and seemed to
produce slower query results.)
An index on t (publish,photo,name_present,location_city) would be the
most effiecient, I think.
**SELECT STATEMENT**
SELECT t.id, t.name_present, t.location_city, t.photo, a.name_short,
a.name_long, b.name as b_name, c.name as c_name, d.id as d_id, d.name
as d_name
FROM t
LEFT JOIN a ON t.a_id = a.id LEFT JOIN b ON t.b_id = b.id
LEFT JOIN c ON t.c_id = c.id LEFT JOIN d ON t.d_id = d.id
LEFT JOIN e_t ON e_t.t_id = t.id LEFT JOIN e ON e_t.e_id = e.id
WHERE t.publish = 'Yes' AND e.id = '2'
ORDER BY t.photo, t.name_present, t.location_city
LIMIT 0,25
**EXPLAIN RESULTS FOR THIS QUERY**
+-++-+--+--+--+-+
| tbl | type | key |keylen| ref | rows | Extra |
+-++-+--+--+--+-+
| t | ALL| NULL| NULL | NULL | 4279 | where used; filesort|
| a | eq_ref | PRIMARY |2 | t.a_id |1 | |
| b | eq_ref | PRIMARY |2 | t.b_id |1 | |
| c | eq_ref | PRIMARY |1 | t.c_id |1 | |
| d | eq_ref | PRIMARY |1 | t.d_id |1 | |
| e | eq_ref | PRIMARY |1 | e_t.e_id |1 | where used; index |
| e_t | ref| t_id|2 | t.id |1 | |
+-++-+--+--+--+-+
('possible_keys' was identical to 'key', but i removed it due to space
issues)
**INDEXES ON 't'**
+---+---+
| Key_name | Column_name |
+---+---+
| PRIMARY | id|
| location_city | location_city |
| name_present | name_present |
| a_id | a_id |
| b_id | b_id |
| c_id | c_id |
| d_id | d_id |
+---+---+
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize Question
I just tried that, but with mixed results. Like before, when I added an index on publish, I get a 'range' join, but it's still doing filesort. | theater | range | status_publish | 1 | NULL | 4279 | where used; Using filesort Any way around that? Best, Patrick An index on t (publish,photo,name_present,location_city) would be the most effiecient, I think. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize Question
In the last episode (Aug 25), Patrick Crowley said: I just tried that, but with mixed results. Like before, when I added an index on publish, I get a 'range' join, but it's still doing filesort. | theater | range | status_publish | 1 | NULL | 4279 | where used; Using filesort Any way around that? Hm. With a compound index on all those fields, it shouldn't have to sort anything. I wonder why keylen is '1' in what you pasted there. That sounds like it decided it didn't need (or couldn't use) the rest of the index. What version of mysql is this? If you're running 3.23, does 4.0.14 do the same thing? And if you can try it, does 4.1.0? -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize Question
I'm running 3.23.52 locally, but I just noticed my production server is running 4.0.12. Given the complexity of this query, should I use subqueries instead of joins? Would that make any difference? Best, Patrick What version of mysql is this? If you're running 3.23, does 4.0.14 do the same thing? And if you can try it, does 4.1.0? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimize Question
In the last episode (Aug 25), Patrick Crowley said: I'm running 3.23.52 locally, but I just noticed my production server is running 4.0.12. Given the complexity of this query, should I use subqueries instead of joins? Would that make any difference? The subquery equivalent to your joins should be processed exactly the same way (barring optimizer bugs). You would have to use 4.1.0 to get subqueries anyway. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimize question
I have a php/mysql application that allows a user to add/remove/update/delete text and records from various tables. To avoid table overhead that comes when column sizes change etc - I am considering implementing an optimization query that will optimize all tables in the db every time the user logs out of the application. Before I do this - is there anything I need to consider? Any thoughts? thanks MS sql, mysql - 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
