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

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.


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

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


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 ;


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

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


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

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

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

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


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


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

First Transaction ------------------------ LATEST DETECTED DEADLOCK ------------------------ 130320 15:53:37 *** (1) TRANSACTION: TRANSACTION 0 83395751, ACTIVE 35 sec, process no 26405, OS thread id 140507872417536 starting index read mysql tables in use 3, locked 3 LOCK WAIT 3 lock struct(s), heap size 1216, 2 row lock(s) MySQL thread id 1163191, query id ...


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


4

You need to keep the comparison column outside of your CASE statement. INNER JOIN Users u ON u.id = CASE WHEN c.sender = ? then c.reciever else c.sender END


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

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

There are a few options in this space. A favorite one for information like your buffer pool is too small is mysqltuner.pl. The developer did a nice trick where you can download it by running wget mysqltuner.pl. It outputs something similar to this >> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, ...


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



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