Tag Info

Hot answers tagged

9

The solution to your problem is a MySQL capability called "partitioning". The documentation is here. What partitioning does is store a single table in separate "partitions". These are defined by a particular expression, usually a column value or range. In your case, this would probably be based on endTime -- assuming that it is known when a record is ...


9

(Regarding PostgreSQL 9.3 and MySQL 5.6, written in 2014; if you're looking at other versions, this may be outdated): Lots more features. CHECK constraints True SERIALIZABLE isolation Arrays (including index support for arrays) Window functions (lead, lag, row_number, etc) Common table expressions (WITH queries) including recursive CTEs and writeable CTEs ...


9

That is called a column alias. An alias is used to give columns a new name for the result set. This can be used to name a column based on an expressions (because otherwise the expression wouldn't "have" a name) or to rename columns, e.g. because the select list contains columns from different tables with the same name: select t1.foo as t1_foo, ...


8

If you want to use reserved words as table or column names, you have 2 options: use brackets (the SQL-Server's way): SELECT [Kill] or double-quotes (the ANSI/ISO standard): SELECT "Kill" Your whole statement would become: SELECT [serial], [Kill] FROM tbl_pvporderview WHERE [Kill] > (?) ORDER BY [Kill] DESC ;


8

Wrap the column name in square brackets: $sql = "SELECT [Kill] FROM tbl_pvporderview";


7

First you need to do is run this query: SELECT user,host FROM mysql.user WHERE super_priv='Y' AND CONCAT(user,'@',host) <> 'root@localhost'; This will list all users that have SUPER privilege. Most users that do application-related DB processing do not require this privilege. According to the MySQL Documentation, those with SUPER privilege can do ...


7

This is a good "there's more than one way to do it" question. Start by thinking about your data usage: Think about which fields are going to be read and written more often. For example, first name and last name are probably written once and are rarely changed, but depending on your app, they could be read very often. Last login date could be written ...


7

I have always implemented a Soft Delete by including two additional columns in my tables, one for status and one for delete date. My table structure would be similar to the following: create table myTable ( id int, name varchar(50), IsDeleted bit, DeletedDate datetime ); The IsDeleted column has a default value of no and the DeletedDate is not ...


7

Use an array to represent the series of values: pg_prepare($con, "prep", "select * from test where tid=ANY($1::int[])"); $strpar = "{3,4,6,8,10}"; pg_execute($con, "prep", array($strpars)); The cast to int[] in the query might even be superfluous if the planner is able to infer the type by itself.


6

The question asked by yes123 is very relevant in the sense that we all want to know what we can do to improve MySQL's performance without touching the application that's consuming that data. What is that perfect configuration that will just make everything better? Although no one program can just spit out that perfect configuration, there are ways to ...


6

This looks like you might have hit a bug logged against Percona Server 5.5: Concurrent duplicate inserts can violate a unique key constraint in InnoDB tables. There is no fix and no reproducible test case for this bug yet. It has only been observed in a production environment. The pattern described is: INSERT a value into a column with a unique ...


5

From your description, you'll need the following tables ("..." means you might need additional fields in that entity): User (UserID, EmailAddress, Password ...) Session (SessionID, CreatedDate, UserID, ExpiryDate, PurchaseCompleteInd ...) Product (ProductID, Name, UnitPrice ...) SessionProductLink (ProductID, SessionID, Quantity ...) The ...


5

You must bind the elements of an IN list, otherwise Oracle will consider these separate statements. Any differences in the text of the SQL will cause a new statement to be parsed (excepting some cases when cursor_sharing = force). Therefore your examples with :a, :b and :a, :b, :c will be considered different SQL statements. Each element must be bound to a ...


5

Under Option 1, you are guaranteed a single row (if you remember to insert the one row firsthand). If you want to introduce a new setting, you would have to do ALTER TABLE to add the needed column. If you wanted to remove an old setting, you would have to do ALTER TABLE to remove the unneeded column. You would have to assume to the presence of the column ...


5

Personally, I tend to use the 2nd and subsequent executions as my benchmark when developing hence this answer... Arguably the real speed is with caching of data and execution plan. If it's run often enough to matter, then it will be cached in real life. You are measuring performance the 2nd time. For the first run, you're measuring load/compile times. On ...


5

If you are doing a range of consecutive dates, there is no need to use the IN operator. If you use this to compute midnight seven days ago DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND here is what you get back: mysql> SELECT DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND; +--------------------------------------------------+ | DATE(NOW()) - ...


4

KEY-VALUE NO!!! A table for phone numbers -- sure. It would have userid, phone_num, and (if you like) a phone type, such as ENUM('fax', 'home', ...). Then JOIN to the main table. To keep unlimited, unsearchable data, have a column with a bunch of key-value stuff. I like to do it in JSON, then compress it (in the app), and store it into a BLOB or ...


4

There have been a few great discussions of this at Stack Overflow. This one covers methods and processes, but don't try to create a catch-all function as a replacement for understanding your data flows. For your particular problem, it looks like HTML Purifier might be a good fit. That way, you could whitelist very specific HTML functionality (e.g., ...


4

You are looking for pt-archiver, part of the Percona Toolkit. The tool can purge rows from a table; write rows from a table to file; purge rows from a table while copying them to another table (possibly another server)... It does so in chunks, so like 1,000 rows at a time, so that there is no high load on your server.


4

I can understand you saying "stable". There is no clean mechanism in MySQL to implicitly govern the order in which MySQL can delete rows using LIMIT. It is actually not transaction-safe for MySQL Replication. The only way to guarantee an orderly deletion of rows is to delete by a PRIMARY KEY or UNIQUE KEY that does not rely on an auto_increment column. ...


4

In a relational database, there is no such thing as a "natural order". If you care about the order of your results, you have one and only one option-- specify an ORDER BY clause. Without looking at the query plan, I would guess that your gender table is the driving table of a nested loop join which happens to return all the male rows first. It would be ...


4

I don't use phpPgAmin. To change an existing column definition to use the CYCLE attribute you need to understand that this is an attribute of the sequence not the "column". A serial or bigserial is only a shorthand notation to assign a default value to a column which is take from a sequence. When you define a column as serial Postgres automatically ...


4

You should re-evaluate your database design. A relational database is not designed to store arrays in columns. Actually, elements in arrays are "columns" itself. So, what you're trying to do is store multiple columns in a single column... I'd recommend two tables: Persons (PersonId, Name,...) Friends (FriendsId, PersonId, FriendId, Accepted), where both, ...


3

Locate the namespace inside which the citext type resides: select nspname from pg_type t join pg_namespace n on n.oid=t.typnamespace where typname='citext'; Prepend that namespace (normally, 'public', but it might be different in your case and it might explain the problem) to the cast to citext: $sql = "SELECT column1 from schema1.column1 where column1 ...


3

After some thought I figured out that the slave can't tell the difference between a silent (idle) master from a crashed one unless an error is given, so the TCP connection will stay online until the slaves default TCP time out is reached. On my boxes, this was the default [root@db1 ~]# sysctl -e net.ipv4.tcp_keepalive_time net.ipv4.tcp_keepalive_time = ...


3

Replication and binary logging happen independently from innodb, which can unfortunately cause problems. Check out: http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_sync_binlog From what you describe I suspect that sync_binlog is set to 0 for your servers. Leaving it at 0 means mysql will rely upon the filesystem to ...


3

You can: increase memory_limit increase post_max_size increase max_execution_time must restart Apache after doing all this. or use Big Dump


3

Here is one thing I quickly see is missing : Each [mysqld] needs to have its own datadir. No two mysqld groups can share the same datadir. You need to allocate different datadirs in different folders and make sure you run "chown -R mysql:mysql (datadir folder)" on all datadirs. Your /etc/my.cnf should look something like this: [mysqld_multi] mysqld = ...


3

Here is your original query SELECT `school`.`ARCO_name`, `student-`.`ClassSize_7`, `student-`.`ClassSize_8`, `degree_o`.`degree_code`, `accredit`.`full_faculty_3`, `accredit`.`total_faculty_3`, `accredit`.`pc_terminal`, `accredit`.`stud_fac_ratio` FROM school INNER JOIN `student-` ON `school`.`scid` = `student-`.`scid` ...



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