Tag Info

Hot answers tagged

18

I'm having trouble imagining anything where the data model could legitimately contain 2000 columns in a properly normalised table. My guess is that you're probably doing some sort of "fill in the blanks" denormalised schema, where you're actually storing all different sorts of data in the one table, and instead of breaking the data out into separate tables ...


11

The latest draft SQL standard that I could find on the internet (dated 21/12/2011) has the following available for use in a query expression: <result offset clause> ::= OFFSET <offset row count> { ROW | ROWS } <fetch first clause> ::= FETCH { FIRST | NEXT } [ <fetch first quantity> ] { ROW | ROWS } { ONLY | WITH TIES }


10

MySQL 5.0 Column-Count Limits (emphasis added): There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors. Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on ...


8

Why would you need to create a table with even 20 columns, let alone 2000 ??? Granted, denormalized data can prevent having to do JOINs to retrieve many columns of data. However, if you have over 10 columns, you should stop and think about what would happen under the hood during data retrieval. If a 2000 column table undergoes SELECT * FROM ... WHERE, you ...


6

It's a measurement system with 2000 sensors Ignore all the comments shouting about normalization - what you are asking for could be sensible database design (in an ideal world) and perfectly well normalized, it is just very unusual, and as pointed out elsewhere RDBMSs are usually simply not designed for this many columns. Although you are not hitting ...


5

Use correct ANSI group by (not the MySQL abomination extension) and see what happens select sum(score) total,name,gender,dob,country from users join scores on users.id = scores.user_id where date between '2012-01-01' and '2012-01-31 23:59:59' group by name,gender,dob,country having sum(score)>=1000 order by sum(score) desc limit 50 Why? GROUP BY in ...


4

The size of the database is the size of the file. Look at the actual size of the data file (the transaction log doesn't count). Yes indexes count. If you are running out of space consider an upgrade to SQL 2012 Express as that increases the size limit to 10 Gigs.


4

In SQL Server and other systems that support ROW_NUMBER()... WITH GoalsWithGoalNum ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY [In] ORDER BY [Goal ID] ) AS GoalNum FROM Goals ) SELECT * FROM GoalsWithGoalNum WHERE GoalNum <= 2;


4

What is wrong with the (maybe too obvious?): select * from noun n, noun_inflection ni where n.label = 'handlebar' and n.label ilike '%'||ni.label_singular order by char_length(ni.label_singular) DESC limit 1;


3

Ok, it was simple :) I wanted to limit each database to 256MB of space per student. In order to achieve that, I enabled smallfiles in /etc/mongod.conf and ran the mongod process with mongod -f /etc/mongod.conf --quota --quotaFiles 4 which limited the size of the database to 256MB. smallfiles starts preallocating space with 16MB, then 32MB, 64MB and finally ...


3

First some more flaming, then a real solution... I mostly agree with the flames already thrown at you. I disagree with key-value normalization. Queries end up being horrible; performance even worse. One 'simple' way to avoid the immediate problem (limitation of number of columns) is to 'vertically partition' the data. Have, say, 5 tables with 400 ...


3

I think some of it goes back to requirements. How much of a name are you storing? Do you ever want to keep the "names" separate (like first, middle, last)? Do you want to handle multiple middle names? I'd say 50 characters should be fairly sufficient for each name if you wished to separate them out. (That will be way more than enough for most names, but ...


3

Things to try: Adding an index on (user_id, date, score) Group by only on scores table and then join to users: SELECT s.total, u.name, u.gender, u.dob, u.country FROM users AS u JOIN ( SELECT user_id, SUM(score) AS total FROM scores WHERE date >= '2012-01-01' AND date < '2012-02-01' GROUP BY user_id HAVING SUM(score) >= 1000 ...


2

Table have 1,000,000 records but It look like Table doesn't have 800000+ records where `articles`.`hash` NOT IN ( '1z8y' ) But table have 2000+ records where `articles`.`hash` NOT IN ( '1z8y' ) LIMIT 800000,10 will return 10 records after 800000th records but it looks like you didn't have 800000 records which satisfy your where clause condition. You ...


2

In terms of operation SELECT id,name,description FROM tablename LIMIT 1000,25 SELECT id,name,description FROM tablename LIMIT 25 OFFSET 1000 there is absolutely no difference in the statements @siride's comment is exactly the point. LIMIT 1000,25 means LIMIT 25 OFFSET 1000 From the same Documentation LIMIT row_count is equivalent to LIMIT 0, ...


2

MyISAM or InnoDB? Your link does not say anything about SQL_CALC_FOUND_ROWS being slower. And I would expect it to be faster if for no other reason than having one fewer round trip to the server. The risk comes when you have thousands of rows. Then either method may be spending more time than you can afford. Come back when you encounter that; we can ...


1

Initially, I looked into database benchmarks for this (I'm interested in realistic benchmarks), but found that neither the HammerDB nor Sysbench tools appear to handle multiple databases (i.e. schemas). I couldn't find any Google link despite searching for many combinations of "MySQL benchmark multiple schemas/databases". With respect to multi-tenancy, ...


1

Condition 1 : You can define a composite primary key such as : CREATE customerItem ( customer_id INT, item_id INT, view_date DATETIME, PRIMARY KEY (customer_id, item_id) ) You can then use INSERT ... ON DUPLICATE KEY UPDATE such as : INSERT INTO customerItem (customer_id,item_id,view_date) VALUES (123,999,"2014-12-22") ON DUPLICATE KEY UPDATE ...


1

I think your approach is reasanoble. Because it would show online data (no need for background processing) and would only use resources from active transactions (customer not seeing anything will not need to have their data reprocessed). I would do it using triggers. You would not have to change your existing code and you could disable/enable this new ...


1

Both DBAs are right, in a way. There are no limits on the number of schemas, except those imposed by the filesystem, as in MySQL/MariaDB, a schema is basically a directory, producing absolutely no difference for both MyISAM and InnoDB if a table is part of one schema or another in terms of performance or features (you can join 2 tables in different schemas ...


1

If you want the longest substring that means there is no other which is longer. A NOT EXISTS predicate will give this. select <whatever> from <your table> as aa where <predicates> and not exists ( select 1 from <your table> as bb where <predicates> and len(bb.SomeColumn) > len(aa.SomeColumn) ); ...


1

I have the same issue. Mongo does not respect quota. I haven't found the answer, but I found SERVER-5136 . According to this, it is recorded issue but sadly resolution is still not planned.


1

Sounds like something for capped collections. Doesn't limit the database itself but does limit the collections? http://blog.mongodb.org/post/116405435/capped-collections


1

When sp_spaceused shows the database_size of about 25 GB in your first result set, that includes the data file/indexes, and the transaction log. Only the actual data and indexes count towards the SQL Server Express limits, so you have about 3.3 GB of data (as in your second result set) and the remaining 22 GB will be the transaction log. If you run ...


1

You can separate the problem into multiple steps. First lets create an example table: SQL Fiddle MySQL 5.5.32 Schema Setup: CREATE TABLE Visitors( id INT, -- other columns country CHAR(2) ); INSERT INTO Visitors(id,country)VALUES(1,'DE'); INSERT INTO Visitors(id,country)VALUES(2,'DE'); INSERT INTO Visitors(id,country)VALUES(3,'DE'); INSERT INTO ...


1

Unfortunately MySQL does not have the handy analytical functions that other SQL languages have. You can simulate them, tough, using some left outer join tricks: SELECT t1.payment_id ,t1.emp_id ,t1.cargeTime ,t1.payment FROM emps t1 LEFT OUTER JOIN emps t2 ON t1.emp_id = t2.emp_id AND ...


1

Here is a good script I shamelessly ripped from here: use [Insert DB Name] select a.FILEID, [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) , ...


1

While I wouldn't get out of control with what you permit for storage, using a VARCHAR or NVARCHAR type to store the strings makes it a much less critical question - since you're only going to use the storage that your data actually consumes, rather than the full length of your field, you can be a bit more generous here. Given the general pain of changing the ...


1

Try: SELECT * FROM yourtable ORDER BY chargeTime DESC LIMIT 3



Only top voted, non community-wiki answers of a minimum length are eligible