Tag Info

Hot answers tagged

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, ...


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 ...


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

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

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

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

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 ...


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 ...


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

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` ...


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

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html The above link clearly explains that mysqldump too puts set foreign_key_checks = 0; in the dump file so that data can be restored without the foreign key constraint error. I have always used set foreign_key_checks = 0 for importing data into tables and then set it back to 1.


3

While not a Graph or RDBMS based solution, let me suggest a NoSQL database. IMO all of your criteria seem like they could be met with a Cassandra/Solr implementation. We use Cassandra at work for storing large amounts of data, and we serve it to various applications with a JBoss service layer. Cassandra integrates right in with the Apache Solr search ...


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 ...



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