This is a follow up to this question. Here is my schema CREATE TABLE A( id serial NOT NULL, date timestamp without time zone, type text, sub_type text, filename text, filepath text, filesize integer, lock_status int ); In this database, a user can update the type,sub-type,filename,filepath,fil...
Assume I've a sql statement like the following with the variable @FOO set somewhere earlier in code: SELECT FIELDLIST FROM TABLE WHERE (FIELD = @FOO OR @FOO IS NULL) Is the query optimizer smart enough to do the second side of the OR first (@FOO IS NULL) because (another assumption) it is faster to do a null check than it is to d...
Hey all, I am just wondering if its possible to trace MySQL queries on my linux server as they happen? For example I'd love to set up some sort of listener, then request a web page and view all of the queries the engine executed, or just view all of the queries being run on a production server. Are there tools to do this? Thank you, ...
Is there a way to determine the size of the records returned by the database server? ...
Lets say I have this table: PetID Name Age Weight How can I in a Linq2SQL get the name, age and weight of each pet that is the heaviest in its age group? So if I have one pet of age 5 and four pets of age 2, I would like the Name, Age and Weight of the one that is age 5, and the one of the four with age 2 that is the heaveie...
Can you explain the concepts of, and relationship between, Covering Indexes and Covered Queries in Microsoft's SQL Server? ...
Let's say I have one table that has 1000 rows and the other table with the same structure/index but 10 million records. Will the performance of CRUD operations on the bigger table be slower than the smaller one? Thanks. ...
If I have a table with the hypothetical columns foo and bar. bar might have 50-60 distinct values in it. My goal here is to pick say, up to 5 rows for say 6 unique bars. So if the 6 unique bars that get selected out of the 50-60 each happen to has at least 5 rows of data, we'll have 30 rows in total. ...
I have a bunch of rows in a table with columns a, b, c. I'd like to be able to SELECT all rows where say a = 1, and reinsert them with a = 2. Essentially keeping all the rows where column a exist as is, and having a new batch of rows having as a = 2. What's the best query to establish such a multi-INSERT query? This is all happening in t...
I am using this SQL query to order a list of records by date in a php page. SELECT ARTICLE_NO, USERNAME, ACCESSSTARTS, ARTICLE_NAME FROM table WHERE upper(ARTICLE_NAME) LIKE % x % ORDER BY str_to_date(ACCESSSTARTS, '%d/%m/%Y %k:%i:%s'); This works fine. In a different php page, I want to be able to delete this record, and show the n...
When performing a query where the attributes selected make up the components of an index does that result in a faster query? I would imagine that the query planner/optimizer could see that the requested columns could be satisfied completely by the index scan. Trivial Example CREATE TABLE "liked" ( "id" BIGINT NOT NULL DEFAULT nextva...
I'm making a simple forum, and in the listing of the threads i want the classical "last post by (user) at (date)" to show. I'm using only one table to contain all the posts in the forum, where posts with the parentPost-field not equal to null are replies to a thread. These are the tables and necessary fields i need data from: forumPost...
I run an online magazine and would like a dead-simple way to track 3 metrics: How many comments are left each day. How many links my users submit. How many new members I get each day. This information is all my database, I'm just not sure how to get it out. I have a "comments" table with about 3500 comments. Each comment gets a row...
I have to create an SQL Server 2005 query which checks for the value of one attribute in the table and based on its value, select different sets of columns. How can I do that? for e.g. In table 'car', if the values of 'type' attribute are 1 and 2 when type = 1, i want to execute a select 'query1' with 3 columns. when type = 2, i wan...
I have an Event table that specifies a date range with start_date and end_date fields. I have another date range, specified in code, that defines the current week as 'week_start' and 'week_end'. I'd like to query all Events for the week. The cases seem to be: Event begins and ends within the week Event begins before the week, but en...
Hypothetical example: I have Cars and Owners. Each Car belongs to one (and only one) Owner at a given time, but ownership may be transferred. Owners may, at any time, own zero or more cars. What I want is to store the historical relationships in a MySQL database such that, given an arbitrary time, I can look up the current assignment...
I have the following two PL/SQL Oracle Queries that return a count: SELECT count(*) INTO counter_for_x FROM Table_Name WHERE Column_Name = 'X'; SELECT count(*) INTO counter_for_y FROM Table_Name WHERE Column_Name = 'Y'; Is it possible to write a single query that returns both the counts and populates the respective counter ...
I have a report that calculates multiple date differences (in business days, not DATEDIFF) for a variety of business reasons that are far too dull to get into. Basically the query (right now) looks something like SELECT -- some kind of information DATEDIFF(dd, DateOne, DateTwo) AS d1_d2_diff, DATEDIFF(dd, DateOne, DateThr...
Hello. I have what I'd thought would be a simple query, but it takes 'forever'. I'm not great with SQL optimizations, so I thought I could ask you guys. Here's the query, with EXPLAIN: EXPLAIN SELECT * FROM `firms_firmphonenumber` INNER JOIN `firms_location` ON ( `firms_firmphonenumber`.`location_id` = `firms_location`....
Is there a built-in function in PHP or MySQL that will provide the total number of MySQL queries used on a page? I've seen on a lot of sites (mainly forums) they have a message at the bottom saying something like "Page generated in 0.6 seconds with 20 queries". If there's nothing built in then I'll add something to my database class to ...