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'

<deleted user>

Melraidin posted:

Is there a way to get a running total from MS SQL in the same manner as from MySQL? Or, as I suspect, is getting a running total from MySQL a bad idea to begin with?

Pull the data out of the database as-is, and model the data in your application. Keeping computation out of your database makes it perform better, and promotes a looser coupling between your application and any one database platform.

Just my opinion!

# ¿ Dec 5, 2007 21:28

Adbot: ADBOT LOVES YOU

# ¿ May 7, 2024 10:49

<deleted user>

dest posted:

I need a custom Excel SQL query written for my job. The boss wants it, and I don't know a thing about SQL. If I wanted to pay a goon to write it for me, should I post it in here somewhere, or over in SA Mart?

So you need weekly sums per-vendor? That should be pretty easy to do. You might get more help if you post the relevant parts of your SQL schema though.

# ¿ Feb 29, 2008 03:33

<deleted user>

LightI3ulb posted:

No errors at all, and adding mcl doesn't change anything

Did you connect() with RaiseError? You aren't checking that execute() succeeded. Exceptions and DBI go well together:

code:

my $dbh = DBI->connect($dsn, $user, $pass, { RaiseError => 1 })

eval {
   my $sth = $dbh->prepare("SELECT 1");
   $sth->execute();
}
if($@) {
   warn "a horrible error: $@";
   exit;
}

Also, you probably meant $sth->errstr in your code. And $sth->rows won't do anything useful in a SELECT context.

# ¿ Apr 8, 2008 04:17

<deleted user>

da keebsta knicca posted:

So no one has ever had a problem with MySQL and creating a fulltext index on a table and trying to use it and having MYSQL claim it is not there? although boolean mode works for some reason.

Post the table schema, and the EXPLAIN output for whatever queries are not acting as expected. MySQL fulltext is quite limited, and its possible you are producing a query where the index cannot be used as you are expecting because it is mixing another index in. More than likely, your boolean mode query is not using the index either (they can complete without one, but its slow).

# ¿ Apr 8, 2008 04:27

<deleted user>

da keebsta knicca posted:

I have deleted a bunch of indexes and cleared everything out still no go with this simplified SQL.
code:
SELECT *
FROM job_postings
WHERE MATCH (company_desc,website,position_desc,qualification,employer)
        AGAINST ('html');

And how many rows of data are in the table you are running the query against? Make sure you have enough data in the table that MySQL isn't "optimizing away" your results because of the 50% rule. From here:

quote:

A word that matches half of the rows in a table is less likely to locate relevant documents. In fact, it most likely finds plenty of irrelevant documents. We all know this happens far too often when we are trying to find something on the Internet with a search engine. It is with this reasoning that rows containing the word are assigned a low semantic value for the particular data set in which they occur. A given word may reach the 50% threshold in one data set but not another.

The 50% threshold has a significant implication when you first try full-text searching to see how it works: If you create a table and insert only one or two rows of text into it, every word in the text occurs in at least 50% of the rows. As a result, no search returns any results. Be sure to insert at least three rows, and preferably many more. Users who need to bypass the 50% limitation can use the boolean search mode...

If you're sure you have varied data inserted, run "REPAIR TABLE `job_postings`". Then run a query with EXPLAIN and a suitably-unique word for the AGAINST clause:

code:

mysql> explain select ID from job_postings WHERE match(company_desc,website,position_desc,qualification,employer) against('lawn');
+----+-------------+--------------+----------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table        | type     | possible_keys | key          | key_len | ref  | rows | Extra       |
+----+-------------+--------------+----------+---------------+--------------+---------+------+------+-------------+
|  1 | SIMPLE      | job_postings | [b]fulltext[/b] | company_desc  | company_desc |       0 |      |    1 | Using where |
+----+-------------+--------------+----------+---------------+--------------+---------+------+------+-------------+
1 row in set (0.00 sec)

You're looking for "fulltext" listed in the type column.

Here's what I put in my table for the test:

code:

mysql> select company_desc from job_postings;
+--------------------------------------------------------------+
| company_desc                                                 |
+--------------------------------------------------------------+
| burgeoning bosoms of steel magnolias are on the lawn gnome   |
| cornish hens are attacking my lawn gnomes down by the casino |
| cost plus world market is a sham                             |
| a bucket is a fuckit                                         |
| large marge sent us                                          |
| paintball champions of the world                             |
+--------------------------------------------------------------+
6 rows in set (0.00 sec)

Here's my fulltext result:

code:

mysql> select company_desc from job_postings WHERE match(company_desc,website,position_desc,qualification,employer) against('lawn');
+--------------------------------------------------------------+
| company_desc                                                 |
+--------------------------------------------------------------+
| burgeoning bosoms of steel magnolias are on the lawn gnome   |
| cornish hens are attacking my lawn gnomes down by the casino |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

# ¿ Apr 9, 2008 05:08

<deleted user>

Stabby McDamage posted:

I'm using DBI with Perl against MySQL. I know Perl inside and out, but have very little database experience.

I want to iterate all rows in a very large table (millions of rows). Hence:
code:
my $sql_pagelinks = $dbh->prepare("SELECT * FROM pagelinks");

print "Starting to execute\n";
$sql_pagelinks->execute() or sqldie();
print "Execute done, starting to iterate results\n";

while (my $row = $sql_pagelinks->fetchrow_hashref) {
	print "I found a row\n";
}
When I run it, it stops after printing "Starting to execute", indicating that it's spending time doing the execute(). During this time, perl balloons in memory usage, as if it was streaming in the ENTIRE result (many gigabytes). I abort before I run out of RAM.

I thought that the "prepare, execute, fetch, fetch, fetch, ..." paradigm meant that the database would get ready to start streaming results at execute(), then walk a cursor through the results with each fetch(). Instead, it seems like execute() wants to collect all the results, and fetch() will just iterate them locally.

Am I doing something wrong?

Do I need to use LIMIT to get a subset, and wrap it in a larger loop to walk the LIMIT through the table? That would seem to defeat the purpose of having a database...

The result set is being buffered by DBD::MySQL, like you said. By default, the driver uses "mysql_store_result()" to buffer the result locally. MySQL also provides "mysql_use_result()", which works more like you described, but at a possibly serious performance hit at the server (see here).

Unless you're doing this on an isolated box where you're the only client, I would probably go with using LIMIT as you said. Just remember that for "LIMIT 5000, 20", the server actually reads rows 1-5000 before sending you the next twenty you actually want, so you might want a fully-indexed subquery involved that pulls the keys you need first and passes those to an outer query that gets the full row data.

Server-side prepare (which is probably not on by default, depending on your DBD::MySQL version) will prepare the query on the server, so that the client needs only send the parameters for the query on each execute, rather than sending the entire block of SQL + parameters and doing the query parsing step each time. It's mostly useful for bulk write operations. To use it, you need the "mysql_server_prepare=1" option included in the DSN given to connect(), but read the docs first.

Check out DBD::MySQL docs and source, and the MySQL C API docs and it demystifies a lot of things.

MySQL sucks. Enjoy.

# ¿ Apr 12, 2008 03:35

<deleted user>

da keebsta knicca posted:

Ack... thanks. Requires me to have meetings with 2 different people to discuss possible implications site wide and all this junk.

Changing a character XML file shouldn't effect display of any of these special characters should it? and only effect searches? Right now this will be the first thing we actually use full text search with so it shouldn't be a problem right? Yet we display a lot of special characters and stuff from our databases, because the nature of us having a lot of data from international people.

It won't affect the data at all, just what characters are used to build the fulltext index. Remember that '+' is a boolean operator also, and that increasing the indexed character set will increase the index size and lower performance.

# ¿ Apr 14, 2008 13:49

Adbot: ADBOT LOVES YOU

# ¿ May 7, 2024 10:49

<deleted user>

minato posted:

If adding an index is slowing a SELECT down, then I would guess that the query planner is stupidly using the index when a sequential search would be better (which is the case when the number of rows in the table is low). Not much you can do about that.

Many databases allow for index hints of some kind. MySQL has "USE/IGNORE/FORCE INDEX". Oracle has "SELECT /*+ ... */". I think MSSQL has WITH(INDEX()) or something like that. Last I checked, Postgres does not have hints, because the developers don't like the concept (which I agree with mostly, but I've had lots of instances with MySQL where hints were a must).

# ¿ Apr 15, 2008 16:04

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