Fields In The SELECT Affecting Execution Plan
Is it possible that fields in the part of the SELECT affecting performance?
e.g:
select * from a where employee>10 (run very fast)
select id from a where employee>10 (run very slowly).
View Complete Forum Thread with Replies
Related Forum Messages:
Update Affecting Only First Found Row
I'd like to write a query along the lines of UPDATE table SET field=value WHERE status=AVAILABLE Now...let's pretend that there are 4 rows that have status available...how can I just have it affect one row only?
View Replies !
MySQL Backup Plan
We are trying to use MySQL database to replace MS Access DB. Normally we daily backup the applications and DBs from web server into the tape. Is there any suggestion for us to prepare MySQL DB backup plan? As we know, mysqlhotcopy runs only on the same server. I am not sure mysqldump?
View Replies !
Advice On Plan Of Attack
OK, I have a large web based questionaire form I am constructing using PHP/MySql. There are around 50 questions with each question having anywhere between 4 and 30 possible answers, depending on the question, that the user can select using checkboxes, meaning they can have multiple checkbox answers per question. My question is what would be the best method to store the checkbox data in MySql, keeping in mind there is also is going to be a search routine that will need to pull the data back out efficiently? Should I | delimit each checkbox answer in a mysql field for each question so there are 50 fields with pipe delimited responses, or do I make a field in the table for each possible answer (around 750 fields)? Or is their a better method??? With so many checkbox answers, what is the best/ most efficient way to store their responses knowing the data will need to be searchable? I can get through the programming side of it, although I am not an expert by any stretch of the imagination. Just looking for advice on how to set it up before I get started. Don't want to do it one way then discover later that the search is too slow because I didn't lay it out right first
View Replies !
Select All Fields
I have two tables, "literature" and "publishers". The "literature" table has a field for "publisher_name", the "publishers" table has a field for "name". Now, I want to produce an output of all "publisher_name" fields in the "literature" table that don't have a matching entry in the table "publishers". In other words, to give our editors info of missing entries. Is there a "clean" way to accomplish this? That is, anything else except producing a humungously long "OR OR OR OR OR OR OR" query? That wouldn't be pretty at all...
View Replies !
Select WHERE Between Two Fields
I am trying to see if something like this is possible... I want to find all orders that have not been paid for. SELECT SUM(payments.paid) AS paid, orders.amount AS ordered FROM orders AS orders LEFT JOIN payments AS payments ON orders.ID=payments.orderID WHERE ordered>paid.
View Replies !
Select Distinct From 2 Fields
if i do a select distinct field1 from table1 ill get the distinct field1 but how do i get distinct field1 values and distinct field2 values. if i do a select distinct field1,field2 from table1 the result will contain similar values too...
View Replies !
Select Distinct From Several Fields
I'm using the following query to select distinct years from my 'datein' column, this is then used to populate a drop-down selection (using php). select distinct date_format(datein, '%Y') as year from f_flockrecord What I'd like to do is to also select distinct years from the column 'dateout' (in the same table) and have these appear in the drop-down too.
View Replies !
Using CASE To Select Different Fields
I have a table with 5 fields: a b c d e Based on the value in A, I want a SELECT statement to return 2 fields If a = 0, return b, c If a = 1, return c, d If a = 2, return d, e It works with a UNION... SELECT a, b, c FROM abcde WHERE a=0 UNION SELECT a, c, d FROM abcde WHERE a=1 UNION SELECT a, d, e FROM abcde WHERE a=2 ; I thought of using a CASE statement, but I can't figure out how to make it work. This case statement works... select a, case a when 0 then b when 1 then c when 2 then d end from abcde; This one doesn't... select a, case a when 0 then b, c when 1 then c, d when 2 then d, e end from abcde; Any ideas what I should be doing? Here's what I used to create the table - CREATE TABLE abcde (a int, b char(2), c char(2), d char(2), e char(2)); INSERT INTO abcde (a, b, c, d, e) VALUES (0,'b1','c1','d1','e1'), (0,'b2','c2','d2','e2'), (0,'b3','c3','d3','e3'), (1,'b4','c4','d4','e4'), (1,'b5','c5','d5','e5'), (1,'b6','c6','d6','e6'), (2,'b7','c7','d7','e7'), (2,'b8','c8','d8','e8'), (2,'b9','c9','d9','e9');
View Replies !
Select Distinct Along With Other Fields
I have a query where I am searching through a DB multiple times and don't want duplicates. For example I might search through the DB which has 15000 records for all the records that have a 12 in the number field. The catch is that there are multiple numbers in the field so the same company could come up 4 or 5 times but ultimately it will all bring up the same data for the 4 or 5 companies. When I use the distinct method it will only return the name not any of the other fields that I need for the query I am processing. Is there a way to remove the duplicates out of the search and still be able to access the other fields? So I can get a distinct name and the corresponding ID?
View Replies !
Select Fields Between 2 Dates
I am trying to develop a booking system in PHP with MySQL but I am having trouble writing some code that checks to see if a new booking clashes with another. e.g. in the database I have a booking that runs from 2008-03-10 to 2008-03-20. When I add a booking I want to check to see if a booking already exists between these dates. Is this possible? I am not an expert with MySQL but the closest I got is to carry out 2 query's: SELECT propertyid, arrival_date, departure_date FROM bookings WHERE arrival_date BETWEEN '$arrival_date' AND '$departure_date' SELECT propertyid, arrival_date, departure_date FROM bookings WHERE departure_date BETWEEN '$arrival_date' AND '$departure_date' but this obviously will not check if a booking is made in between these dates, e.g. if I try and add a booking from 2008-03-13 to 2008-03-18 it will not think that there is a collision of booking.
View Replies !
SELECT Fields Order
the order of the fields in a SELECT statement effects efficiency of the statement. If the table structure is field1, field2, field3 is SELECT field1, field2, field3 FROM TABLE; better than SELECT field3, field1, field2 FROM TABLE; or is there no difference?
View Replies !
SELECT Two COUNT Fields...
trying to select two count fields, but it's not working. there's no error in the query, but it seems not understand that I want it to count a userid from one table and then from another. here's the sql. mysql_query("SELECT COUNT(acomments.user), COUNT(attractions.userid) FROM users JOIN acomments ON users.id = acomments.user JOIN attractions ON users.id = attractions.userid WHERE users.id = '$u'")
View Replies !
Difference In SELECT WHERE IN With Datetime Fields
Anyone can explain the following? Table definition: CREATE TABLE testdate ( SalesDate DATETIME, SalesTotal DOUBLE, PRIMARY KEY (SalesDate) ) If I use the following SQL statement I get results: SELECT * FROM testdate WHERE SalesDate = '2003-08-27' OR SalesDate='2003-07-27'; But I get an empty set when I use this: SELECT * FROM testdate WHERE SalesDate IN ('2003-08-27', '2003-07-27'); This happens on both 4.0.15 and 3.23.57. But the above statements work the same under Microsoft SQL Server. Why is MySQL interpreting WHERE IN and WHERE = OR = differently?
View Replies !
Select Only Specific Data In The Fields How?
I have a table called cclp_players now that table has 3 fields for instance and has data see below, ------------------------------- | player_id |team_id|division_id | ------------------------------- | 001 | hp | golem | | 002 | ibm | cestino | | 003 | sun | mac os | | 004 | del | bim os | | 005 | acer | ms os | Now i want to SELECT the table either by team_id or by division_id. And let say i wanted to SELECT team_id by sun. What is the right expression or combination of SELECT command for this?
View Replies !
SELECT Common Text Among Fields
let's say i have this numbers as strings: 573445 573447 57341 573411 What query should i execute in order to get the leftmost common string in all those rows? the result should be 5734.
View Replies !
How To Write A SELECT/COUNT + Other Fields
I have a table like so: NAME | AGE Joey | 21 Mary | 18 Lass | 43 Moch | 33 Joey | 23 Mary | 25 Mary | 65 Lass | 90 I what to write a single query that retrieves the total number of times a single name is found in the the table (Joey appears two times, Mary appears 3 times, and Lass appears two times) AND also retrieves each record. SELECT COUNT(name) as name_total, name, age FROM persons_table WHERE 1 RESULT: row#: name_total, name, age: row1: 2, Joey, 21 row2: 3, Mary, 18 row1: 2, Lass, 43 row1: 1, Moch, 33 row1: 2, Joey, 23 row1: 3, Mary, 25 row1: 3, Mary, 65 row1: 2, Lass, 90
View Replies !
Select Only Rows Of 2 Fields That Does Not Repeat
I would like select only rows where registerNo AND entryid that does not repeat. Example, from the table only one row of 'registerNo=1 and entryid=sqbiiphiu1' and the last 5 rows will be selected. I'm using php to build this. i'm not sure if distinct can help. this is wat i thought of $query = "SELECT DISTINCT * FROM fn_vote WHERE registerNo = '".$registerNo."' AND entryid='".$entryid."'";
View Replies !
SELECT DISTINCT Then Additional Fields
I am trying to do the equivalent of : Code: SELECT DISTINCT email, * FROM student_bookings I know this will never work but think it explains best what I am looking for - I want to select additional fields from each DISTINCT email. I know I could use a subquery but are there any other ways of doing this? (unfortunatly I can't use subqueries as we don't have the correct version of mySQL for that ).
View Replies !
SELECT DISTINCT With Multiple Fields
I'm kind of stumped on a query I'm trying to build. basically I have a forum table like this: Post_ID | Thread_ID | Post_EntryDate | etc..... 90 | 22 | .... 89 | 21 | .... ... ... ... 85 | 1 | .... 84 | 1 | .... 83 | 10 |.... and so on. I would like to get the last 20 threads with most recent posts filtering for Thread_ID duplicates of course. so far I have this: SQLtemp = "SELECT DISTINCT Thread_ID FROM Posts ORDER BY Post_ID DESC LIMIT 0,20" this succesfully returs a list of unique Thread_IDs of the last 20 threads with most recent posts. However, I need to get the Post_IDs and other fields but when I throw Post_ID in the SELECT statement all I get is duplicate values for Thread_ID
View Replies !
Add Prefixes To Fields On Select With Join
My solution to this problem was to add two more queries. I used show columns on both tables. Then I've used a server side language (PHP in this case) to loop through the results and added the prefix to each result. The result of this process was this query select `fieldname` as `prefixfieldname` .... from `table`.... Now since this process is not very effective
View Replies !
How To Select Fields That Have Reserverd Names In MySQL
I have a stored procedure that does this select: SELECT Blob, BytesCount, FileName FROM ProblemReportAttachments WHERE AttachmentID = a_AttachmentID; But the column called 'Blob' causes a MySQL syntax error because Blob is a reserved word in the MySQL syntax (for the BLOB datatype). How to get around this? Please, no replies saying to rename the database table column to something else since this is not an option.
View Replies !
Select All Fields Of Specific Row In Multirow Result
I'm making a query that returns multiple rows, each with multiple fields. I need to be able to select individual rows (including all of their fields) without using a while loop. so, my query: $fullquery = "SELECT claimid, title, claim, dateSubmitted, totalVotes FROM claims ORDER BY overallrating DESC LIMIT 6"; $result = mysql_query($fullquery, $dbc); I need to select row 1, use its data in a specific way, then select row 2, etc. I know how to do this in a while loop, and how to remove particular fields, but not how to access all fields for just 1 row.
View Replies !
Select Fields On Selection Box(html) By Database Values
I have a form that I have put together to update, and add new fields. I have no problem with connecting to the database, updating fields, and adding new fields. My form autofills with current database information if the user wants to update certain fields. What is the easiest way to make these fields selected, if the database has the entries already? There’s about 30 fields, so I would like to do it dynamically. The fields in the database(in one single variable) are comma delimited... "value1,value2,value3"
View Replies !
Query To Select All Fields Using AUTO_INCREMENT, And Then Delete All Data With A Value Of 0?
Got a database with lots of tables using AUTO_INCREMENT quite a bit. Around a week ago a restore was done on this database from a backup made with compatibility mode turned on which removed the AUTO_INCREMENT value from the SQL. Problem I have now is that for all data being submitted to tables with fields using the AUTO_INCREMENT function are being submitted with a value of 0. I have now fixed my database structure so all the fields that need AUTO_INCREMENT are set to use it, but now I have the problem that most of these fields have data with a value of 0 which is invalid. What I need help with is to write a query that finds all the fields in all tables in the database using AUTO_INCREMENT, and then delete any values of 0. Does anyone know how I can do this, and if so could you show me?
View Replies !
Ending Of Execution
is it important to free resources and end connection after every code execution... i found a few codes examples and they don't usually end with DIE or EXIT... so i'm curious if thats a security risk?
View Replies !
Execution Time
After we execute a statement in mysql, it reports the query execution time in the format: n rows in set (t sec). We may do something like this to get the execution time: $time_start = getmicrotime(); mysql_query( "" ); $time_end = getmicrotime(); $time = $time_end - $time_start; But it includes extra CPU time to run two getmicrotime() functions and one subtraction. Is there any way to obtain the execution time directly from mysql without running extra functions or calculations?
View Replies !
Log Execution Queries
I am writing a program in python, and I use mySql v1.2.12, I wanna know If mysql logs any command such as wxecution commands(like INSERT, UPDATE, DELETE), if yes how can i access them? thanks all
View Replies !
Asynchronous Execution
I have the following situation php file 1: an SQL writes data to the database php file 2: an SQL reads data from the database Some users report errors that MIGHT be due to the SQL in file 1 not being executed, before they click on a link to open file 2. So they get out of date information. I want to know if this is technically possible, whether this might indeed be the problem, that if a database is very busy, that SQL statements are queued and data retrieval may read obsolete out of date information, because the data storage SQL has not been executed yet. And if this is the problem, I would like to wait in file 1 until the data has actually been written.
View Replies !
Mysql WHERE Condition Execution
I have 2 columns and I search rows with two conditions. SELECT * FROM table WHERE A = '$a' AND B = '$b' How can I make mysql search columns with A condition first and after that within these condition B. I would like to know, how mysql search works. How can I tell mysql how to search?
View Replies !
How To Get Time Execution Of A Query?
Do you know how to get the time that a query executes? Is there a formula on how to get it? Example: Note: I have 5000 data in the table cars $result = mysql_query("Select * from cars"); Question: How to get time of executing the query above?
View Replies !
Improve The Execution Time
i am new to db2 want to ask questions about the performance of my sql commands for a view based another 3 views the sql commnads are as following: create view b_central_subgroup as select communicator as central_member, project_id as project_id from b_normalized_communicator intersect select initiator as central_member, project_id as project_id from b_normalized_initiator intersect select monospeaker as central_member, project_id as project_id from b_normalized_monospeaker it takes 4.4 seconds to execute this sql. it likes that the time is the summ of the other three views. Can the execution time be reduced through some other methods? If it is possible , then how ?
View Replies !
How To Get Query Execution Time
How to get query execution time in "mysqlquery.log" files I am using: Linux 7.3 mysql 4.1 How to display the query execution time for each and every query in log files. i have configured "slow-log-query" and "mysqlquery.log" in "my.cnf" is pasted below. log = /var/lib/mysql/mysqlquery.log log-slow-queries = /var/lib/mysql/slowquery.log long-query-time = 0 slow-launch-time = 1 then i can get a output in "mysqlquery.log" like, 070830 9:24:29 2 Connect root@localhost on 2 Init DB jbdatabase 2 Query select count(*) from vacancy1_table 2 Query select category,listcategory from category_t able order by category 2 Quit but i need to display the "timetaken of each end every executed query" along with the query in the above logfile like, E.g:select category,listcategory from category_table order by category(0.03 secs).
View Replies !
Query Execution Too Slow!
I have migrated from MySQL 4.1.22 to MySQL 5.0.45. Many of the queries that were working on my old server is working too slow in my new server(MySQL version 5.0.45). I am not aware why is this happening. Actually there is no error message or query failing. The query is executing, but the time taking to execute the query is too long. This problem is not occurring for all queirs, but only for some complex queires, that is queries with Left Join or Right Join, Group By etc. I have also checked the MySQL details through SSH(that is by mysql> SHOW VARIABLES;) Now one thing I found is there is lot of difference in key_buffer_size, table_cache, join_buffer_size, read_buffer_size, read_rnd_buffer_size , table_cache etc between my new server MySQL and old server MySQL. Will this be the reason for query execution taking too much time. Can any one guide me why this is happening. The striking thing is that these quires was working very much fine and faster in my old server.
View Replies !
Measure Execution Time
I would like to measure the execution time of my queries in order to evalutate the performance in/decrease for different changes. Even if I use 'sql_no_cache' the query executes MUCH faster the second time I run it, and is therefore not representative of the true performance. How to I avoid this problem? I'm using MySQL 4.1 on a windows server 2000. I be glad to get an explanation to why the query executes faster the 2nd time.
View Replies !
Last Query Execution Time
Is there a way to retreive the elapsed time for the prevously executed query? Alternatively, Is there a way to query the current time in fractions of a second? I am attempting to use a stored procedure to execute and track the time it takes to run some queries and other commands. The logic of the stored proc would go something like this.....
View Replies !
Mysql Execution Sequence
I need to retrieve the last value from a column in a mysql table, perform some actions on it, then add 1 to the original value and store a new entry. I am worried that if multiple users access the database at the same time I will end up with duplicated 'new values'. Does mysql complete execution of a script before it allows access to another user or do I need to 'lock' the table in some way while the script executes?
View Replies !
Query Log With Execution Time?
I have developed a big property portal web site and have probably written about 1000 different SQL queries in the process (OK, maybe only 500 or so) but a lot. The site is now gaining popularity and although it is on a dedicated server I want to make sure that everything is running smoothly. I have optimised several tables with indexes etc. and have sped up certain tasks, but would like to know if there are any other queries that take too long and should be optimised. Is there a way to log all queries together with the time it took to execute the query? Is this already logged and if so - where is it?
View Replies !
Slow Query Execution
Right now I am fazing one issue which is getting hell out of me. I am explaining the issues step by step. 1.I have one site running from last 3 years with large database and there is one main table which has maximum load. 2.Now, I have redesigned the site with lots of changes within this table too. 3.The problem is that the server is same, queries are same but output in the new database is taking 50 times more execution time. 4.I also just copy paste the table, and fire the same query... Strange even that is not working... Is it something where newly added table or database is having issues in the server? I have tried all, compared the structures of the data and table but still the query result is too slow on live testing sever. While same query in main site of same server is running perfectly.
View Replies !
|
TRACKER
