On Thu, Feb 23, 2012 at 5:50 PM, Daevid Vincent wrote:
> Anyone have any thoughts on how I might optimize this query?
As always, it's all about the indexes. The index it chose on your
main table looks pretty weak. You probably should move those
non-joining columns out of your join condition in
- Original Message -
> From: "Daevid Vincent"
>
> d.`date_release`!='-00-00' AND sd.`scene_id`=s.`scene_id` GROUP
> BY sg.`scene_id` ORDER BY num DESC, sd.`count_dl_monthly` DESC LIMIT 30;
Could you put an index on sg.scene_id? Not sure, but it might help the grouping
be more effici
272
-- why do I still hit the filesort and temp table? how can I get rid of
that?
id select_type TABLE TYPEpossible_keys
KEY key_len ref ROWS Extra
-- --- -- --
-- --
--- - --
------
1 SIMPLE d ref
PRIMARY,date_release,STATUS,status
iment with
various index setups.
PB
-
On 5/31/2011 1:27 PM, Daevid Vincent wrote:
I sent this Friday, but it never made it to the list?!
-Original Message-
From: Daevid Vincent [mailto:dae...@daevid.com]
Sent: Friday, May 27, 2011 12:27 PM
To: mysql@lists.mysql.com
Subject: Usi
I sent this Friday, but it never made it to the list?!
-Original Message-
From: Daevid Vincent [mailto:dae...@daevid.com]
Sent: Friday, May 27, 2011 12:27 PM
To: mysql@lists.mysql.com
Subject: Using where; Using temporary; Using filesort
I'm trying to optimize a query that doesn&#
s 2011/05/27 12:26 -0700, Daevid Vincent
[a] the EXTENDED keyword doesn't seem to do anything different? I get the
same columns and results??!
"show warnings"
2011/05/27 12:26 -0700, Daevid Vincent
In other words, am I wasting my time trying to tweak my
query and indexe
status,status_release
key: status_release
key_len: 1
ref: const
rows: 1976
Extra: Using where; Using temporary; Using filesort
*** 2. row ***
id: 1
select_type: SIMPLE
table: s
type:
.
>
> Those queries
> Regards,
> Gavin Towey
>
> -Original Message-
> From: Ciaran Lee [mailto:ciaran@gmail.com]
> Sent: Tuesday, September 22, 2009 1:32 PM
> To: mysql@lists.mysql.com
> Subject: query optimization question (my struggle against 'using temp
.
Those queries
Regards,
Gavin Towey
-Original Message-
From: Ciaran Lee [mailto:ciaran@gmail.com]
Sent: Tuesday, September 22, 2009 1:32 PM
To: mysql@lists.mysql.com
Subject: query optimization question (my struggle against 'using temporary;
using filesort')
Hi,
I hope th
than 2ms), but
selecting the latest listing within a city is REALLY slow (10-20 seconds)
despite being almost a subset of the event query.
I have been working on this for about a day, and have tried all sorts of
tweaks to the indexes but to no avail. I always seem to end up with 'using
temp
Not really, the query took 4-5 seconds. The query runs through 13910 rows
according to explain, that isnt alot is it?
SELECT COUNT(*) antal,ad.ad_id FROM ad
INNER JOIN tag_ad_map tm ON tm.ad_id=ad.ad_id
WHERE
tm.tag_id IN (99, 10807, 20728, 21, 135)
AND ad.is_removed = 0 AND ad.is_active=1 AND
One optimization I see quickly is changing the left join to an inner join.
You always look for records that exist in tag_ad_map (by checking for
tm.is_active) so the left join is not necessary. That should at least speed
this query up considerably.
Walter
Need MySQL advice?
OlinData.com is the pl
I have a query that runs very slow and using Using temporary; Using
filesort. Is there a way to avoid them using current table structure?
Goal with the query is to find ads (ad_id) that have tags (tag_id) connected
to them, order by "antal" is used to get the most relevant first. ( th
Rich Brant schrieb:
> Is there anyway to prevent the temporary and filesort?
>
> SELECT
> t1.sourceID as sourceID,
> count(t1.sourceID) as clicks,
> [...]
> ORDER BY clicks desc, conversions desc;
>
> When using EXPLAIN:
>
> [...] Using where
On 6/29/07, Rich Brant <[EMAIL PROTECTED]> wrote:
Hello all. I'm looking for help with the query below. Is there anyway
to prevent the temporary and filesort?
The filesort is caused by either the ORDER BY or the GROUP BY. There
are sections in the manual about how to get it to use indexes for
Hi,
Could someone please help me to get rid of "Using temporary; Using filesort"
from the following SQL statement?
SELECT a.document_id AS id, attr_name AS name, attr_value AS value, attr_order
AS ord FROM attributes a INNER JOIN status s ON (a.document_id = s.document_
ble_keys | key |
key_len | ref | rows | Extra|
++-+---+---+---+--+-+--+--+--+
| 1 | SIMPLE | t1| range | idx_date | idx_date |
3 | NULL | 4612 | Using where; Using
| possible_keys | key |
key_len | ref | rows | Extra|
++-+---+---+---+--+-+--+--+--+
| 1 | SIMPLE | t1| range | idx_date | idx_date |
3
Here is the explain data:
> +---++---++-
> +-+--+-+
> | table | type | possible_keys | key| key_len | ref
> | rows | Extra|
> +---++--
Thanks Shawn, I'm going to give the temporary table idea a try.
I did omit the 'category' table while testing but when I used EXPLAIN the # of rows
for the thread_link join increased from 105 to 16326 so I decided to leave the
category table in. But I agree, it isn't needed.
My other idea I ha
gt; Especially if there are a lot of threads for that particular category.
>
> Here is the explain data:
> +---++---++-
> +-+--+-----+
> | table | type | possible_keys | key| key_len | ref
> | rows | Extra |
> +---++--
--++-+-+--+-+
| table | type | possible_keys | key| key_len | ref | rows |
Extra |
+---++---++-+-----+-
PRIMARY KEY
REF_ID INTEGER
NUMBER FLOAT(7,2)
No matter, how many entries I have in A and B and no matter what indexes
I create, I always get a USING TEMPORARY, USING FILESORT when EXPLAINing
the following simple statement:
SELECT a.id, MIN(b.number) AS low FROM a JOIN b ON (a.id = b.ref_id
will have
- a row for each appointment in the given range
- a row for each patient who doesn't have an appointment
Then the where clause throws away the ones with appointments.
regards,
Bill
> From: "lorenzo.kh" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED
Hi,
I have 2 tables.patientrecall_table
and appointment_table.The patientrecall_table used to store what is the
nextrecall date for the patient while the appointment_tablewill store
all the appointments made.Now, I'd like to get through this:"Patients
Due for Recall, but No Scheduled Appo
> Probably cause your table ends up being bigger than:
> SHOW VARIABLES LIKE 'tmp_table%';
I don't think so. As I stated before (sorry I didn't include my first post),
I have :
tmp_table_size = 512M
max_heap_table_size=512M
>From my calculation, I would have ~2000 rows in the result of my query,
Hi All!
I think I am getting close to the solution.
In fact, mysql creates tmp tables beacause I "GROUP BY" a query that joins
tables. According to the manual, group bys on joins always creates a
temporary table.
Now the question is, why is this table a temporary disk table???
Thanks in advance.
Hi Matt, thanks for answezring!
> A disk-based temp table is used if you're SELECTing a column [that can
> be] longer than 255 characters (BLOB/TEXT to MySQL). This is because the
> in memory HEAP tables don't currently support variable length rows.
> Using something like LEFT(text_col, 255), if f
-
From: "Arnaud"
Sent: Wednesday, November 19, 2003 2:18 AM
Subject: using temporary / using filesort and disk tables
> Hi!
>
> I have a query that allways creates temporary tables to disk (the
ratio
> created_tmp_disk_tables/created_tmp_tables is close to 0.8, but I
thi
analyze it, I get "where used, using temporary, using filesort" for
the first table of the join (whatever the order of the join), and "where
used" for the other ones.
I have only 2000 rows scanned forthe first table, and 1 for the 3 other
ones.
The variables tmp_table_size and max
|
Extra |
+---++---+-+-+-++---
--+
| mi| ALL| PRIMARY | NULL|NULL | NULL| 740053 |
Using temporary; Using filesort |
| m | eq_ref | PRIMARY | PRIMARY | 15 | mi.nick | 1 |
I asked this a couple days ago, but never got an answer, so I'll try to
include some more detail:
MySQL 4.0.12 on linux 2.4.18...
I'm doing a join on 2 tables and trying to optimize it but I always end up
with "Using temporary; Using filesort" and the query takes way too lo
Andrey Mishenin <[EMAIL PROTECTED]> wrote:
> I've got a problem using temporary tables. Tring to make one (mysql> CREATE
> TEMPORARY TABLE ... ) I recieve an error message:
> ERROR 1044: Access denied for user: '[EMAIL PROTECTED]'
It's probably a matter of granting CREATE TEMPORARY TABLE privilege for the
"hardware" user.
HTH,
Lian
> -Original Message-
> From: Andrey Mishenin [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 03, 2003 8:26 PM
> To: [EMAIL PROTECTED]
> Subject:
I've got a problem using temporary tables. Tring to make one (mysql> CREATE TEMPORARY
TABLE ... ) I recieve an error message:
ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'hardware'
At the mean time all other
I've got a problem using temporary tables. Tring to make one (mysql> CREATE TEMPORARY
TABLE ... ) I recieve an error message:
ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'hardware'
At the mean time all other sql
ess and files.
And, of course, you could add some indexes, on the filename and the
file columns.
I rewrote the column=integer parts but cant really see any
speedimprovement there.
I also tried to define a lot of different indexes, but none of them
seemed to get rid of the "using temporary"
Eric,
> I have a query which I did several months ago, it recently caught my
> attention since it seems to be very slow when done a lot of times, which
> causes a very loaded server.
> The query I used looks like this:
> SELECT files.file_id, filename FROM access, files WHERE
> directory_id="0
oup_id IN (1) AND access.class_id="4" AND class_tbl="file" )
group by file_id order by filename\G
*** 1. row ***
table: access
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
Eric Persson wrote:
CREATE TABLE access (
class_id int(10) unsigned NOT NULL default '0',
group_id int(10) unsigned NOT NULL default '0',
class_tbl char(10) NOT NULL default '',
conn_id int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;
CREATE TABLE files (
file_id int(10) unsigned NO
tax
easily.
It should run way faster then..
Cheers,
Andrew
-Original Message-
From: Eric Persson [mailto:[EMAIL PROTECTED]
Sent: Tuesday 01 April 2003 19:02
To: MySQL List
Subject: Optimize query, avoid 'using temporary'
Hi,
I have a query which I did several months ago, i
roup_id IN (1) AND access.class_id="4" AND class_tbl="file" )
group by file_id order by filename\G
*** 1. row ***
table: access
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8958
Hi,
Bennett Haselton wrote:
--snip
>
> select * from user left join MYTEMP on user.ID = MYTEMP.userid where
> MYTEMP.userid IS NULL;
I bet that this approach will fail because you want the join to show you
records that aren't there. But it will show you something, and it will
show you more if y
ss than a week ago.
If sub-selects were allowed, the query would be:
select * from user where ID not in (select user.ID from user,
clickable_ad_link where user.ID = clickable_ad_link.userid AND
clickable_ad_link.time_sent > DATE_SUB(NOW(), interval 1 week));
Using temporary tables, what
NULL
|| 13 | Using temporary; Using filesort |
| Design_Issue | ref| status_type_id | status_type_id | 3 | Status_Type.id
|| 102 | |
| Person | eq_ref | PRIMARY| PRIMARY| 4 |
|Design_Issue.auth
> (begins: http://ep33.tp4.ruhr-uni-bochum.de/mlists/MySQL/Mar.2000/1029.html )
By chance, this points me to an archive of this list. I already
looked for a hint at the trailer and on the website, but, as I
didn't find any, assumed that there wasn't any. Wouldn't it be
helpful to add links at bot
We are currently working with a table containing ~650MB of data in
700,000 rows. In attempting to select from this, we would typically find
long holdups with process states listed as creating temporary tables.
By using SQL_SMALL_RESULT we were able to greatly reduce the time taken
for select oper
Hello,
I've got a problem:
My MySQL-Server gets signal 11 almost every day arround 0:00 am ... I have a
nice
mrtg - graph showing this ... ;o)
In fact every time the server crashes the statement shown in the server.log
(or a
similar one with other memID) is executed.
topsitesTEMP is a tempora
48 matches
Mail list logo
