SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

!Klams: Dec 25, 2005; Squid Squad

I have an incredibly irritating problem, in that this seems like it should be so easy but I just cant fathom it.

Basically, I have a column, birth_date, and I want to get the youngest one for each year. So far, I've not had much luck, mostly because I suck, but this is my general idea:

code:

 
SELECT
            MAX(youngest)
            FROM
            (
                  SELECT
                        max(a.birth_date) youngest                        
                  FROM
                              agents a
                        INNER JOIN
                              agents b
                        ON
                              a.agents_id = b.agents_id
                  GROUP BY
                        to_char(a.birth_date, 'yyyy')
            ) dates
;

# ? Sep 19, 2008 20:36

Adbot: ADBOT LOVES YOU

# ? May 15, 2024 07:55

Aredna: Mar 17, 2007; Nap Ghost

What version of SQL are you using? This problem is ripe for analytical functions if your version supports them.

vvvv I just wanted to use the fancy min ... over capabilities

Aredna fucked around with this message at 00:51 on Sep 20, 2008

# ? Sep 19, 2008 21:09

!Klams: Dec 25, 2005; Squid Squad

I'm using Oracle, also I don't know what those are, so I shall go and investigate!

# ? Sep 19, 2008 22:31

MoNsTeR: Jun 29, 2002

No analytics needed:

code:

select to_char(trunc(birth_date,'YY'),'YYYY') as YEAR
     , min(birth_date) as YOUNGEST
  from agents
 group by to_char(trunc(birth_date,'YY'),'YYYY')

Unless you want a null for years that have no birth_date values, that's a bit trickier...

# ? Sep 19, 2008 23:28

Risita: Dec 8, 2005

Stephen posted:

I'm creating a views table that tracks which items on my site were viewed/clicked etc. and what time. The table has become far too large to efficiently query and results, so I'm creating a view count column in the item table to do efficient sorts by number of views. I guess my real question is about Triggers. Will it be viable/good practice to create a MySQL Trigger that will update the item view_count column on every insert into the views table? Or is there a better way to accomplish this?

It's certainly possible to create a trigger. Another way is to create an SQL stored procedure that inserts into the views table and then increments view_count in the same transaction. Then call that procedure instead of directly inserting into the views table.

I don't know which way is better, though. My co-workers say to avoid triggers in general because they can complicate things. But that might be because we're using an object-relational thing over the database. If you're just using plain SQL, I'm not sure.

# ? Sep 20, 2008 02:17

!Klams: Dec 25, 2005; Squid Squad

MoNsTeR posted:

No analytics needed:
code:
select to_char(trunc(birth_date,'YY'),'YYYY') as YEAR
     , min(birth_date) as YOUNGEST
  from agents
 group by to_char(trunc(birth_date,'YY'),'YYYY')
Unless you want a null for years that have no birth_date values, that's a bit trickier...

Oh amazing, thankyou!

# ? Sep 20, 2008 03:01

smith7800: Sep 25, 2004

I need to come up with a query in mysql 5:

I've got a table called tblvotes with fields: voteID, storyID and genre.
I need to query the table to return a storyID field containing a list of unique storyID's and another column containing the genre that occurs the most number of times for the corresponding storyID

I realize this is probably very simple, but I am new to this. Can someone help?

# ? Sep 21, 2008 12:02

Pento McGreno: May 4, 2004; Cheese.

smith7800 posted:

I've got a table called tblvotes with fields: voteID, storyID and genre.
I need to query the table to return a storyID field containing a list of unique storyID's and another column containing the genre that occurs the most number of times for the corresponding storyID

code:

SELECT 
    storyID, genre 
FROM
    (
     SELECT 
        storyID, genre, COUNT(*) AS votes
     FROM
        tblvotes
     GROUP BY
        storyID, genre
     ORDER BY
        votes DESC
    ) AS a
GROUP BY 
    storyID;

# ? Sep 21, 2008 17:07

smith7800: Sep 25, 2004

Pento McGreno posted:

code:

SELECT 
    storyID, genre 
FROM
    (
     SELECT 
        storyID, genre, COUNT(*) AS votes
     FROM
        tblvotes
     GROUP BY
        storyID, genre
     ORDER BY
        votes DESC
    ) AS a
GROUP BY 
    storyID;

Thanks so much. Worked perfectly.

# ? Sep 21, 2008 22:46

thefncrow: Mar 14, 2001

I'd like to ask here to see if any of you guys have any idea on what I might be able to do to speed up an operation I'm working on.

In SQL 2005, I've got a table with a large amount of data, millions of records. The table has a primary key that's a single ID column that's a uniqueidentifier. These ID attributes have been filled, to this point, using newid(). The data I'm really interested in, though, is the data inside an untyped XML column.

The column I have has data something like this:

code:

<ElementA>
  <ElementB>
    <DataToBeRetainedA>147875233</DataToBeRetainedA>
    <ElementToBeWipedOut>12345678</ElementToBeWipedOut>
    <DataToBeRetainedB>1234589</DataToBeRetainedB>
  </ElementB>
</ElementA>

The operation I'm trying to do is to scrub out some data that exists inside the XML column after a given period of time passes. In a generalized form, my query is something like:

code:

UPDATE LargeTable
SET XmlColumn.modify('replace (/ElementA/ElementB/ElementToBeWipedOut/text()) with ""')
WHERE Date <= @CutoffDate AND XmlColumn.exists('/ElementA/ElementB/ElementToBeWipedOut') = 1

That .exists() function in the where clause is there because, with the non-XML column data in the row, I still can't separate out some data that doesn't have this element, and so I need it to prevent causing an error when it pulls a record and we don't have the /ElementA/ElementB/ElementToBeWipedOut element in the XML.

Now, that's not exactly what I have(I'm running the operation in batches, I'm doing a lookup on two other columns that aren't nearly as significant, and I just rewrote that xquery off the top of my head), but that's the gist of it. I've got an index on the Date column, so that's not really the big issue.

The query as it stands now is just entirely too slow. I'm batching in groups of 10,000 records, and removing 1 batch of 10,000 records takes about 15 minutes to run. This isn't really acceptable, because this bit of code is going to have to run, alongside several other routine maintenance and cleanup operations inside of a short window of scheduled downtime. Outside of that window, that table requires high availability and I can't be tying it up trying to white out large numbers of rows.

I've placed an XML Index on the table to try to speed up my .exists() function, but in order to get that XML index on the table, I had to change the existing primary key index from a non-clustered index to a clustered one(since it's required for the XML index), and I've been reading about how clustered indexes on GUID columns are a bad idea if the GUIDs have been created using newid(). I can move to where we'll begin utilizing newsequentialid(), but it'll be several years before the old random data is all gone. However, if I'm going to have an XML index, which seems like it's going to be necessary for any sort of decent speed, I'm going to need to have this.

I'm out of ideas for what I can do to speed this up. It's unacceptably slow now, but if it's as fast as it's going to get without significantly restructuring the table somehow, then it's all I can do. I'm just hoping that there's some avenue here that I've missed that I can use to increase performance.

I've got some options for potentially doing different behavior in the hopes that I could do something less costly, but I'd like to be sure first that I haven't missed an avenue for increasing performance while doing this is the right way, especially since all of those other options get into having to do a system impact to see how other bits of the application riding on this database would react.

Any thoughts?

# ? Sep 22, 2008 07:44

Jargon: Apr 22, 2003

Argh this query is killing me... Basically I want to find the 10 most popular scenes (a line item == a purchase of a scene):

SELECT scenes.*, count(*) as popularity FROM "scenes" JOIN line_items ON line_items.scene_id = scenes.id GROUP BY line_items.scene_id ORDER BY popularity DESC LIMIT 10

I'm getting this error in postgres though (works perfectly in my local sqlite development db):

ERROR: column "scenes.id" must appear in the GROUP BY clause or be used in an aggregate function

Jargon fucked around with this message at 10:01 on Sep 22, 2008

# ? Sep 22, 2008 09:57

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

thefncrow posted:

Stuff

Make a stored function that you pass the XML data. That function will then check the XML string to see if it has the section you want to remove, then it will remove it and return the new XML, otherwise it will return the old XML, so your new query would be something like this.

code:

UPDATE LargeTable
SET XmlColumn = MyXmlModifyFunction(XmlColumn)
WHERE Date <= @CutoffDate

This might do an update on every column it touches, so if the XML column is indexed it may slow it down, but you might be able to remove that index since you would no longer need to have exists in your where clause.

You could probably also do this with an IIF or CASE and that would keep it from updating every single row. In that solution you would write two functions, one to see if the xml column exists that returns boolean, and a second that returns the modified xml.

# ? Sep 22, 2008 13:09

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

Jargon posted:

Argh this query is killing me... Basically I want to find the 10 most popular scenes (a line item == a purchase of a scene):

SELECT scenes.*, count(*) as popularity FROM "scenes" JOIN line_items ON line_items.scene_id = scenes.id GROUP BY line_items.scene_id ORDER BY popularity DESC LIMIT 10

I'm getting this error in postgres though (works perfectly in my local sqlite development db):

ERROR: column "scenes.id" must appear in the GROUP BY clause or be used in an aggregate function

Try removing line_items.scene_id and replacing with scenes.id, then switch scenes.* to scenes.id in your select clause and see what happens.

You should look at the GROUP BY clause documentation for postgres. That clause works different depending on the distribution and sometimes every single column that is selected must be included in the group by clause, sometimes not.

# ? Sep 22, 2008 13:17

Jargon: Apr 22, 2003

Begby posted:

Try removing line_items.scene_id and replacing with scenes.id, then switch scenes.* to scenes.id in your select clause and see what happens.

You should look at the GROUP BY clause documentation for postgres. That clause works different depending on the distribution and sometimes every single column that is selected must be included in the group by clause, sometimes not.

Ok this worked:

SELECT scenes.id, count(*) as popularity FROM "scenes" JOIN line_items ON line_items.scene_id = scenes.id GROUP BY scenes.id ORDER BY popularity DESC LIMIT 10

It returned a list of scene IDs and the popularity. However, I'd like to return scenes.* if possible since I have to get that information anyway and I'd like to avoid doing two queries. I tried this:

SELECT scenes.*, count(*) as popularity FROM "scenes" JOIN line_items ON line_items.scene_id = scenes.id GROUP BY scenes.id ORDER BY popularity DESC LIMIT 10

And I got this error:

ERROR: column "scenes.filename_base" must appear in the GROUP BY clause or be used in an aggregate function

So it does seem like every field has to be either in GROUP by or an aggregate clause. I'm not really sure how I would do that though.

# ? Sep 22, 2008 17:24

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

Jargon posted:

Ok this worked:

SELECT scenes.id, count(*) as popularity FROM "scenes" JOIN line_items ON line_items.scene_id = scenes.id GROUP BY scenes.id ORDER BY popularity DESC LIMIT 10

It returned a list of scene IDs and the popularity. However, I'd like to return scenes.* if possible since I have to get that information anyway and I'd like to avoid doing two queries. I tried this:

SELECT scenes.*, count(*) as popularity FROM "scenes" JOIN line_items ON line_items.scene_id = scenes.id GROUP BY scenes.id ORDER BY popularity DESC LIMIT 10

And I got this error:

ERROR: column "scenes.filename_base" must appear in the GROUP BY clause or be used in an aggregate function

So it does seem like every field has to be either in GROUP by or an aggregate clause. I'm not really sure how I would do that though.

All you have to do is GROUP BY scenes.id, scenes.otherField, scenes.otherField2, scenes.otherField3 etc...

You can probably select the scene id and count as a subquery then join that to the rest of the fields as well.

I am not all that familiar with postgres though, you should look at the group by documentation, I am sure there is a solution if you really don't want to have to list all the fields.

# ? Sep 22, 2008 17:35

thefncrow: Mar 14, 2001

Jargon posted:

Ok this worked:

SELECT scenes.id, count(*) as popularity FROM "scenes" JOIN line_items ON line_items.scene_id = scenes.id GROUP BY scenes.id ORDER BY popularity DESC LIMIT 10

It returned a list of scene IDs and the popularity. However, I'd like to return scenes.* if possible since I have to get that information anyway and I'd like to avoid doing two queries. I tried this:

SELECT scenes.*, count(*) as popularity FROM "scenes" JOIN line_items ON line_items.scene_id = scenes.id GROUP BY scenes.id ORDER BY popularity DESC LIMIT 10

And I got this error:

ERROR: column "scenes.filename_base" must appear in the GROUP BY clause or be used in an aggregate function

So it does seem like every field has to be either in GROUP by or an aggregate clause. I'm not really sure how I would do that though.

I don't know postgres, and I'm coming at this from a SQL 2005 background, so I can't claim to know that this works, but it's what I'd suggest.

Do the group by on the line_items table only, and then LEFT OUTER JOIN scenes to your grouped line_items table. Something like

code:

SELECT Scenes.*, ISNULL(SC.Count, 0) AS 'SceneCount'
FROM Scenes
LEFT OUTER JOIN 
(
  SELECT scene_id, count(*) AS 'Count' 
  FROM line_items 
  GROUP BY scene_id
) SC ON SC.scene_id = Scenes.ID

The downside here is that the inner query we're using here will only include scenes that appear in the line_items table, so they'll all have a count(*) >= 1. So, LEFT OUTER JOIN the inner query, which will give you all scenes, and then use your Postgres equivalent to ISNULL() to resolve the NULL away for scenes that don't appear in your line_items table so they have a 0 count.

I know SQL Server is also picky like this, where a group by query requires that all columns in the select list be either in the group statement, or in an aggregate.

Your other option here, if the "grouping" is really only on 1 item like it seems you have here(as I'm assuming you don't have multiple Scenes with the same ID), you can defeat this by using the aggregate functions, like, say, selecting MAX(scenes.filename_base), which will work, because it's an aggregate function, and there's only 1 value for filename_base that's repeated several times in the join, so it's also MAX(filename_base).

Again, though, I haven't used Postgres, so my suggestions are probably best taken with a strong dose of documentation.

thefncrow fucked around with this message at 18:24 on Sep 22, 2008

# ? Sep 22, 2008 17:46

Mo Hawk: Jul 17, 2006; HEADPHONE JACK

Is it possible to sort/select by a value given by a function?

I've got a table with customers (students) and their Dutch bank account numbers, which can be validated with this function:

code:

CREATE FUNCTION fnElfProef (accountNr INT)
    RETURNS BOOLEAN
BEGIN
    DECLARE i, totaal INT DEFAULT 0;
    WHILE i < 9 DO
        SET i = i+1;
        SET totaal = totaal+(i*(accountNr%10));
        SET accountNr = FLOOR(accountNr/10);
    END WHILE;
    RETURN (totaal%11) = 0;
END

Now I would like to only select the customers which have invalid bank accounts, basically like so:

code:

SELECT * from `students` WHERE ( fnElfProef(`vDutchBankAccount`) = 1 )

Is it possible, and if not, what would you recommend as a work-around?
I could load the whole table into PHP arrays first, and filter afterwards, but that is very slow and inconvenient...

# ? Sep 22, 2008 20:12

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Mo Hawk posted:

Is it possible to sort/select by a value given by a function?

I've got a table with customers (students) and their Dutch bank account numbers, which can be validated with this function:
code:
CREATE FUNCTION fnElfProef (accountNr INT)
    RETURNS BOOLEAN
BEGIN
    DECLARE i, totaal INT DEFAULT 0;
    WHILE i < 9 DO
        SET i = i+1;
        SET totaal = totaal+(i*(accountNr%10));
        SET accountNr = FLOOR(accountNr/10);
    END WHILE;
    RETURN (totaal%11) = 0;
END
Now I would like to only select the customers which have invalid bank accounts, basically like so:
code:
SELECT * from `students` WHERE ( fnElfProef(`vDutchBankAccount`) = 1 )
Is it possible, and if not, what would you recommend as a work-around?
I could load the whole table into PHP arrays first, and filter afterwards, but that is very slow and inconvenient...

I don't guarantee my syntax, but

code:

SELECT * FROM (
SELECT *,
       fnElfProef(`vDutchBankAccount`) as `validAccounts`
  from `students`) AS `studentAccounts`
 WHERE ( `validAccounts` = 1 )

is probably the way to go.

# ? Sep 22, 2008 20:16

Mo Hawk: Jul 17, 2006; HEADPHONE JACK

Jethro posted:

I don't guarantee my syntax, but

code:

SELECT * FROM (
SELECT *,
       fnElfProef(`vDutchBankAccount`) as `validAccounts`
  from `students`) AS `studentAccounts`
 WHERE ( `validAccounts` = 1 )

is probably the way to go.

Thanks, I was looking for something just like that. Quite sadly I just discovered that the Function did not work as expected.
I use this

code:

$db->sql_query('
		CREATE FUNCTION fnElfProef (accountNr INT) RETURNS BOOLEAN
		BEGIN
			DECLARE i, totaal INT DEFAULT 0;
			WHILE i < 9 DO
				SET i = i+1;
				SET totaal = totaal+(i*(accountNr%10));
				SET accountNr = FLOOR(accountNr/10);
			END WHILE;
			RETURN (totaal%11) = 0;
		END;');

but I only get

code:

 	You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(accountNr INT) RETURNS BOOLEAN
		BEGIN
			DECLARE i, totaal INT DEFAULT 0;
			W' at line 1

# ? Sep 23, 2008 00:11

benisntfunny: Dec 2, 2004; I'm Perfect.

SQL Server 2005...

I have a query, that is far too long to paste here. Basically we have a large content database and I wrote a query to audit on all the potential issues we have with content on a daily basis... The Title equals the Description... odd characters finding their way into the content etc...

One of the items I have in there is a hits count based on a summary table with about 3,000,000 rows give or take a million. The hits table has an index on the date.

As a query this all completes in about 20 minutes.

Using the exact same query a stored procedure version takes over 5 hours before I stopped it because it was annoying me.

When the stored procedure executes it chooses NOT to use the index on my hits table. I've tried recompiling and giving the procedure table hints but it doesn't seem to matter.

Any ideas?
This sucks.

benisntfunny fucked around with this message at 20:28 on Sep 23, 2008

# ? Sep 23, 2008 20:26

Roseo: Jun 1, 2000; Forum Veteran

I'm trying to create a mySQL trigger that'll zero out some of the data for that row when the value of the status field for the row has changed. I've got the following code, which the server accepts and creates a trigger. However, it's not functioning properly, and I can't find any decent references for trigger syntax. Can anyone shed light on why this isn't working properly?

code:

DELIMITER |

CREATE TRIGGER clrData BEFORE UPDATE ON table
  FOR EACH ROW BEGIN
    IF NEW.status != OLD.status THEN SET
      NEW.field1 = '', 
      NEW.field2 = 0, 
      NEW.field3 = 0, 
      NEW.field4 = '';		
    END IF;
  END;
|

It allows me to make updates to the table with no problem. However, when changing the status, none of the data fields get wiped out, they contain the old data. I'm zeroing out the data in the actual code that utilizes the database, but I'd like the database to be smart enough to zero these fields out if I update a record manually and forget to zero out the data.

Roseo fucked around with this message at 21:50 on Sep 23, 2008

# ? Sep 23, 2008 21:41

!Klams: Dec 25, 2005; Squid Squad

Im trying to create a table in ORACLE that has a list of location descriptions as the rows, and then as the columns a breakdown of boundaries that information can cost, < 1, between one and 2.99, etc. What I want to do is, tally up the number of times that a target in a given location generates a cost of that boundary. So for example, if in Dartmouth three targets generated a cost of 0.4, 2 and 2.5 I would have a one in the first column, and a two in the second column. Also I only care about locations that have more than 3 targets in them.

Heres what I have so far.

code:

SELECT 
		SUM(decode(results.cost, < 1, 1, 0)) AS less_than_one,
		SUM(decode(results.cost, (BETWEEN 1 AND 2.99), 1, 0)) AS one_to_two_point_nine,
		SUM(decode(results.cost, (BETWEEN 3 AND 4.99), 1, 0)) AS three_to_four_point_nine,
		SUM(decode(results.cost, (BETWEEN 5 AND 6.99), 1, 0)) AS five_to_six_point_nine,
		SUM(decode(results.cost, > 7, 1, 0	)) AS more_than_seven
	FROM
		(
			SELECT	
				l.location_id, t.target_id, ac.cost AS cost
			FROM
				locations l 
			INNER JOIN
				targets t
			ON
				l.location_id = t.location_id
			INNER JOIN
				information i
			ON
				t.target_id=i.target_id
			INNER JOIN
				access_cost ac
			ON
				i.security_level 
			BETWEEN
				NVL(ac.lower_bound_level,i.security_level) 
			AND
				NVL(ac.upper_bound_level,i.security_level)
			
		)	results							
	), 	
	locations l
INNER JOIN
    targets t
ON
    l.location_id=t.location_id   
GROUP BY
      l.description
--Only 'main' locations 	  
WHERE t.location_id IN
	(
		SELECT COUNT(targets.target_ID), t.location_id
		FROM 
			targets t
		GROUP BY 
			t.location_id
		HAVING COUNT(targets.target_ID) > 3
	) 
;

I think I'm on the right track, but I'm worried that I haven't explicitly linked the subquery 'results' with the locations, so I'm going to get the same results for each row ? At the moment the trouble I'm having is that it doesn't like the way I've formed my DECODE statements, claiming its 'missing an expression' so I cant even get that far. Any ideas?

!Klams fucked around with this message at 18:33 on Sep 24, 2008

# ? Sep 24, 2008 18:31

var1ety: Jul 26, 2004

!Klams posted:

At the moment the trouble I'm having is that it doesn't like the way I've formed my DECODE statements, claiming its 'missing an expression' so I cant even get that far. Any ideas?

I don't have any advice on the joins. Use CASE instead of decode here.

code:

count(case when cost < 1 then 1 end) AS lt1

# ? Sep 24, 2008 19:06

!Klams: Dec 25, 2005; Squid Squad

var1ety posted:

I don't have any advice on the joins. Use CASE instead of decode here.
code:
count(case when cost < 1 then 1 end) AS lt1

Ok awesome, thankyou! for whatever reason CASE works when DECODE doesn't. I am having the problem I thought I would though, gonna have to wrap my noggin round some bizzarre inner joins.

# ? Sep 24, 2008 19:32

MrHyde: Dec 17, 2002; Hello, Ladies

I need to combine the results of two select statements into a single one. Both select statements select identical data. I tried using the UNION command but it results in a distinct type result set and I want to keep the repeated results. Is that possible?

edit: is UNION ALL what I'm looking for?

MrHyde fucked around with this message at 23:16 on Sep 24, 2008

# ? Sep 24, 2008 23:10

Victor: Jun 18, 2004

imBen posted:

SQL Server 2005...

I have a query, that is far too long to paste here. Basically we have a large content database and I wrote a query to audit on all the potential issues we have with content on a daily basis... The Title equals the Description... odd characters finding their way into the content etc...

One of the items I have in there is a hits count based on a summary table with about 3,000,000 rows give or take a million. The hits table has an index on the date.

As a query this all completes in about 20 minutes.

Using the exact same query a stored procedure version takes over 5 hours before I stopped it because it was annoying me.

When the stored procedure executes it chooses NOT to use the index on my hits table. I've tried recompiling and giving the procedure table hints but it doesn't seem to matter.

Any ideas?
This sucks.

Do you request data from this 3mil record table via subquery or derived table/CTE expression?

code:

select  p.*,
        children = (select count(*) from Child where parent_id = p.id)
from    Parent p

vs.

code:

select  p.*,
        children = isnull(c.count, 0)
from    Parent p
left join (
    select  parent_id,
            count = count(*)
    from    Child
    group by parent_id
) c on c.parent_id = p.id

Try the derived table version if you haven't already.

# ? Sep 25, 2008 04:52

Nomikos: Dec 31, 2003

What's a good way (in MySQL) to impose an arbitrary order on the rows in a table, and still be able to use optimized index-range selects?

What I have right now is a single table with data and an extra column containing a unique integer giving that row's position. However, if I do something like:

code:

 select * from table
    where latitude >= ?
      and latitude <= ?
      and longitude <= ?
      and longitude >= ?
    order by position
    limit 30

MySQL uses a filesort and the query takes seconds, no matter what indexes I use. Well, no wonder, because the documentation states that in order to optimize an ORDER BY clause, the rows must be selected by the WHERE clause using the same column that's in the ORDER BY clause.

Is there some clever way, maybe involving multiple tables or sparsely-documented trickery, to get around this?

# ? Sep 25, 2008 09:07

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

Nomikos posted:

What's a good way (in MySQL) to impose an arbitrary order on the rows in a table, and still be able to use optimized index-range selects?

What I have right now is a single table with data and an extra column containing a unique integer giving that row's position. However, if I do something like:
code:
 select * from table
    where latitude >= ?
      and latitude <= ?
      and longitude <= ?
      and longitude >= ?
    order by position
    limit 30
MySQL uses a filesort and the query takes seconds, no matter what indexes I use. Well, no wonder, because the documentation states that in order to optimize an ORDER BY clause, the rows must be selected by the WHERE clause using the same column that's in the ORDER BY clause.

Is there some clever way, maybe involving multiple tables or sparsely-documented trickery, to get around this?

Where position > 0?

# ? Sep 25, 2008 13:41

MoNsTeR: Jun 29, 2002

In Oracle anyway, you could optimize that query by creating an index on (latitude,longitude,position). Even though you're not selecting based on position, because the index is sorted it will retrieve rows already ordered by position and thus save you the in-memory/on-disk sort you would otherwise suffer.

Of course I have no idea if MySQL works this way...

# ? Sep 25, 2008 15:44

Nomikos: Dec 31, 2003

MoNsTeR posted:

In Oracle anyway, you could optimize that query by creating an index on (latitude,longitude,position). Even though you're not selecting based on position, because the index is sorted it will retrieve rows already ordered by position and thus save you the in-memory/on-disk sort you would otherwise suffer.

Of course I have no idea if MySQL works this way...

I just tried this and it didn't work. However, when I dropped all indexes on the table and removed the ORDER BY clause, performance improved by a factor of 10. :confused:

# ? Sep 25, 2008 21:01

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

What are the results of running 'explain' on your query?

Maybe try making an index for 'position, longitude, latitude' and then do 'order by position, longitude, latitude'

# ? Sep 25, 2008 21:05

Nomikos: Dec 31, 2003

Begby posted:

What are the results of running 'explain' on your query?

Maybe try making an index for 'position, longitude, latitude' and then do 'order by position, longitude, latitude'

Success! I made an index for (position, latitude, longitude) and now the original query no longer says "Using filesort" and runs quickly. If I remove the ORDER BY clause the results are still sorted correctly and it's even faster. So it looks like MoNsTeR's idea was basically correct, but I had to reverse the index order.

Thanks for the help.

# ? Sep 25, 2008 21:19

Pento McGreno: May 4, 2004; Cheese.

Roseo posted:

I'm trying to create a mySQL trigger that'll zero out some of the data for that row when the value of the status field for the row has changed. I've got the following code, which the server accepts and creates a trigger. However, it's not functioning properly, and I can't find any decent references for trigger syntax. Can anyone shed light on why this isn't working properly?

I just tested your trigger on a quick test database, and it worked for me. Here's the test I wrote. For reference, I also tested with status being a TEXT, same result. Can you a) see if this works for you, and b) point out any differences in your code?

code:

USE test;
DROP TABLE IF EXISTS foo;

CREATE TABLE foo (
    status INT,
    field1 TEXT,
    field2 INT,
    field3 INT,
    field4 TEXT
);

DELIMITER | 
CREATE TRIGGER clrData BEFORE UPDATE ON foo 
    FOR EACH ROW 
    BEGIN 
        IF NEW.status != OLD.status THEN SET 
            NEW.field1 = '', 
            NEW.field2 = 0, 
            NEW.field3 = 0, 
            NEW.field4 = ''; 
        END IF;
    END; 
|
DELIMITER ;

INSERT INTO foo VALUES(1,'a',1,1,'a');
UPDATE foo SET status=1;
SELECT * FROM foo;
UPDATE foo SET status=2;
SELECT * FROM foo;
UPDATE foo SET status=2, field1='a', field2=1, field3=1, field4='a';
SELECT * FROM foo;
UPDATE foo SET status=3, field1='b', field2=2, field3=2, field4='b';
SELECT * FROM foo;

# ? Sep 26, 2008 09:29

Pento McGreno: May 4, 2004; Cheese.

MrHyde posted:

I need to combine the results of two select statements into a single one. Both select statements select identical data. I tried using the UNION command but it results in a distinct type result set and I want to keep the repeated results. Is that possible?

edit: is UNION ALL what I'm looking for?

Indeed it is.

# ? Sep 26, 2008 09:31

benisntfunny: Dec 2, 2004; I'm Perfect.

Victor posted:

Do you request data from this 3mil record table via subquery or derived table/CTE expression?
code:
select  p.*,
        children = (select count(*) from Child where parent_id = p.id)
from    Parent p
vs.
code:
select  p.*,
        children = isnull(c.count, 0)
from    Parent p
left join (
    select  parent_id,
            count = count(*)
    from    Child
    group by parent_id
) c on c.parent_id = p.id
Try the derived table version if you haven't already.

Here's what I run.

code:

INSERT INTO @Hits (TMP_ContentId,TMP_Hits) --temp table
SELECT CNT1.CNT_CONTENT_ID,
       SUM(ISNULL ( EVENT_TYPE_CNT, 0 ) )
FROM   dbo.EVENT_TABLE
INNER JOIN dbo.BUT_CONTENT AS CNT1
	ON CONVERT( INT, ISNULL ( TEXT, 0 ) )  = CNT1.CNT_CONTENT_ID
WHERE  EVENT_TYPE_ID = @Hit --code value for hits
       AND TEXT = CNT1.CNT_CONTENT_ID
       AND USER_ROLE_ID IN      (
                                  SELECT item 
                                  FROM parse_comma_delimited_list_in(@Roles, ',' )
                                 )
       AND CNT1.ACTIVE_DATE > GETDATE()

I'll play around with your version and see how it goes. Thanks

# ? Sep 26, 2008 17:34

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

imBen posted:

Here's what I run.

code:

INSERT INTO @Hits (TMP_ContentId,TMP_Hits) --temp table
SELECT CNT1.CNT_CONTENT_ID,
       SUM(ISNULL ( EVENT_TYPE_CNT, 0 ) )
FROM   dbo.EVENT_TABLE
INNER JOIN dbo.BUT_CONTENT AS CNT1
	ON CONVERT( INT, ISNULL ( TEXT, 0 ) )  = CNT1.CNT_CONTENT_ID
WHERE  EVENT_TYPE_ID = @Hit --code value for hits
       AND TEXT = CNT1.CNT_CONTENT_ID
       AND USER_ROLE_ID IN      (
                                  SELECT item 
                                  FROM parse_comma_delimited_list_in(@Roles, ',' )
                                 )
       AND CNT1.ACTIVE_DATE > GETDATE()

I'll play around with your version and see how it goes. Thanks

You are joining two tables on a key that is stored in a text field that you are converting to an integer. I can't see how that could ever be fast, I can't imagine it using any index at all since you are using convert.

Secondly, is @Roles a string? If so can't you do AND USER_ROLE_ID IN ( @Roles )? That alone should save some processing time you would think. I am no SQL Server expert, but I would assume that parse function gets run for every record.

I would assume the same thing for GETDATE(). It seems like you should pass in the current date as a value, or store it in the stored procedure in a variable. That way it doesn't run the function for every record (again, this may not be the case, I don't know how its getting optimized).

Fourthly, you are doing an inner join on CONVERT( INT, ISNULL ( TEXT, 0 ) ) = CNT1.CNT_CONTENT_ID, then in your where clause you have AND TEXT = CNT1.CNT_CONTENT_ID. I may be missing something, but that seems redundant and I imagine its using an automatic cast of some sort.

Perhaps you should try casting CNT1.CNT_CONTEND_ID into a string then comparing that to TEXT instead of converting TEXT to an int?? That is kind of a guess, but wortha shot.

# ? Sep 26, 2008 18:19

Roseo: Jun 1, 2000; Forum Veteran

Pento McGreno posted:

I just tested your trigger on a quick test database, and it worked for me. Here's the test I wrote. For reference, I also tested with status being a TEXT, same result. Can you a) see if this works for you, and b) point out any differences in your code?

Thanks. The code wasn't the problem, the entry of it into the database is what was causing the problem. phpmyadmin must not like the use of delimiter commands, because when I used DELIMITER | in the SQL code to run it created the trigger, but it didn't function properly. Once I dropped the DELIMITER commands and just set it as | in the web UI, the trigger installed properly and runs fine.

Figured it out when I ran your code and the server started spitting back syntax not understood errors.

Roseo fucked around with this message at 19:15 on Sep 26, 2008

# ? Sep 26, 2008 19:12

MoNsTeR: Jun 29, 2002

Begby posted:

Secondly, is @Roles a string? If so can't you do AND USER_ROLE_ID IN ( @Roles )?

You can't do this in Oracle, or rather, you can only do it with some Clever Tricks. Does it work in other implementations? This would be very handy for a personal project.

# ? Sep 26, 2008 20:07

thefncrow: Mar 14, 2001

Begby posted:

Secondly, is @Roles a string? If so can't you do AND USER_ROLE_ID IN ( @Roles )? That alone should save some processing time you would think. I am no SQL Server expert, but I would assume that parse function gets run for every record.

SQL Server will let you do "column IN ( @StringVar )", but when you do it, it's equivalent to "column = @StringVar". If you have a comma delimited list, you have to write something to parse out each item individually, which is what he's doing there.

# ? Sep 26, 2008 20:24

Adbot: ADBOT LOVES YOU

# ? May 15, 2024 07:55

Flea110: Apr 3, 2006

I'm making a simple forum using PHP. The following code will show topics in the order of when their first post was made. I need it to list the topics in the order of when their last post was made. How can I do this?

code:

$info = mysqli_query($link, 
"SELECT t.id, t.title, f.name AS forum_name,
 u.name AS user_name, p.date AS op_date, COUNT(p.id)
FROM thread AS t
JOIN forum AS f
ON t.forum_id = " . (int) $_GET['forum_id'] . "
JOIN post AS p
ON (t.id = p.thread_id)
JOIN user AS u
ON (t.id = p.thread_id) and (p.user_id = u.id)
WHERE f.id = " . (int) $_GET['forum_id'] . "
GROUP BY (p.thread_id)
ORDER BY op_date DESC");

If it's necessary for me to give copies of the original tables, ask and I'll provide them.

# ? Sep 27, 2008 03:38

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »