Slow Max() Selection
I have a large table with some fields. Two of them are:
year char(1) && year's rightmost character
OrderNo integer
Both are keys (BTREE).
I need to select the last order in the year.
select max(OrderNo) from ORDENES where year='8'
While the year is growing the selection speed id decrement (12 seg).
View Complete Forum Thread with Replies
Related Forum Messages:
Top Ten SELECTION
I have a table with reviews, each record has a name in it, how do I count the names and select out the top ten? Here's my MSQL Details rev_id item_id title name email date review rating valid ip timestamp
View Replies !
Many-to-many Selection
I did a search, but they results were not helpful. This should be a very simple query, but I can't figure it out. I have a table like: A | B -----+----- 1 | 5 2 | 4 2 | 5 2 | 6 3 | 5 3 | 6 3 | 7 What I want to do is select unique ids from A such that for each item from A, B = 5 and B = 6 BUT NOT B = 4. So in the sample table above, the result would be: A 1: fails, since there's no B = 6 A 2: fails, although there is B 5 and 6, there is a B 4 A 3: passes Therefore, the selection query would return just 3 from column A. Does this make sense? What would the query to do what I want?
View Replies !
Selection
I want to SELECT only the next row of data that is after the current date. i.e. If I had a show coming up on the 15th of may, today being the 9th, how would I setup a select statement to select only the row that has the show listing for the 15th?
View Replies !
Database Selection
say I have a questionnaire where user has to answer a question from a selection list, e.g. "describe your personality" and the possible answers are relatively long. e.g. "I try to stay on top of things", "I sometimes fall behind", "I tend to procrastinate", etc. What is the most efficient way to store the user's answer if I need to display them later? (a) use abbreviations e.g. "staytop", "fallbehind", "procras" and then use another function to translate those values (b) use an ENUM list or (c) store the whole answer.
View Replies !
Line-selection
when doing a boolean match search on a text field, is there a posibility that mysql returns 2 lines where searched words are present? I want something like google: at google-search-result yyou always see 2 lines out of the document where the searchwords are in.
View Replies !
Selection From Two Tables
i have two tables say tab1 and tab2.in tab1 i have every record identified by an ID i.e tab1.1d the same id is entered in tab2 by the name of sid i.e tab2.sid for each record the data is same based on tab1.id=tab2.sid i am using the following query to select data from both table.i have confimed data is there in both tables with id=sid=39 but still i am geting EMPTY result.. i.e nothing is selected and neither i get any error
View Replies !
5 Greatest Of A Selection
if i have all score made by all player of categorie and i just want to keep all 5 best scorer of each team so imagine that i have for each player the info : player name, player team player score. so i want to find the 5 greatest of all team in only one request. because for one team at the time it is not hard. i work on this and now im there: SELECT `Team`,`Player`,`Score` FROM `Score` LEFT JOIN `Team` USING (PlayerID) ORDER BY `Team`,`Score` DESC And this is close from where i want to be, the only thing that is wrong is that i just want the 5 best scorer of EACH team
View Replies !
Version Selection
I am wanting to perform a fresh install of mysql on Fedora Core 6. I am assuming the Generic RPM package is best, but not sure whether to use the static or dynamic linked version.
View Replies !
Using ResultSet From One Selection In Another?
here's my dilemma: I have 2 databases. One has an ID that corresponds to a column in the second. currently, i do this: select ID from table where (some criteria) then, in the second table: select count(*) from table2 where ID2 = 'result from first'; is it possible to just pipe the results from the first criteria to the second so i can do this with one query instead of 2?
View Replies !
Hierarchical Selection
I have a problem that I'm hoping someone here will be able to solve for me. I have two tables in my DB- t_Items and t_Relations. t_Items is basically a list of items, identified by a unique ID. |ID | Name --------------- | 0 | item 0 --------------- | 1 | item 1 --------------- | 2 | item 2 --------------- | 3 | item 3 --------------- t_Relations defines relations between two items. | FirstID | SecondID ------------------------ | 0 | 1 ------------------------ | 1 | 3 ------------------------ | 3 | 5 ------------------------ Now, What I need to do is this: I need to write a select which finds the link between two items, so If I were to give that select the IDs: 0 And 5 It would return the items: 0, 1, 3 and 5 (with some kind of marker which signifies that they are one "tree"). If no Hierarchy exists it dosn't return any rows, and if more than one Hierarchy exists (there could be two or even more routes from one item to another) it returns all the items, with some kind of marker to distiguish the different "tree"s.
View Replies !
Selection Problem
I have a table as follow: Name Country Month Major Year Pete Canda Jan Sci 2004 Pete Canda Feb Sci 2004 John China Jan Art 2004 John China Jan Art 2005 Mary Italy Jan Art 2004 Lond Italy Feb Sci 2005 Lond Italy Mar Sci 2005 My objective is to extrct part of the table so that the output cannot have a record that only has different in Month. i.e. the result should be: Name Country Month Major Year Pete Canda Jan Sci 2004 John China Jan Art 2004 John China Jan Art 2005 Mary Italy Jan Art 2004 Lond Italy Feb Sci 2005 How can I input the SQL in order to achieve this target?
View Replies !
Invert Selection
I have two tables. They are 'contacts' and groups_to_contacts In one instance I need to select all contacts who are assigned to a group. This is easy enough. SELECT contacts. * FROM contacts, groups_to_contacts WHERE contacts.id = groups_to_contacts.contactsID; But in the other case, I need to select all contacts that are not assigned to a group. How do I invert the above query to return exactly the opposite results. If I write SELECT DISTINCT contacts. * FROM contacts, groups_to_contacts WHERE contacts.id != groups_to_contacts.contactsID; I get every record returned, because every contact.id does not equal at least one of the groups_to_contacts.contactsID.
View Replies !
Date Selection
This is my sql selection, it works good but when I do my query with a selection with two months it wont work. So when I select between June 1 2004 and June 30 2004 it works okay but when I select between June 30 2004 and July 16 2004 it wont select anything. Of course I understand why it is because the 1st datepart is bigger then the 2nd datepart so it cant select anything. But I dont know how to solve this. This is no MySQL but MSSQL, prolly that wont make a big difference in solving this. SELECT comment, name, datefield FROM TGESZ WHERE (DATEPART(DAY, datefield) >= 30) AND (DATEPART(MONTH, datefield) >= 6) AND (DATEPART(YEAR, datefield) >= 2004) AND (DATEPART(DAY, datefield) <= 16) AND (DATEPART(MONTH, datefield) <= 7) AND (DATEPART(YEAR, datefield) <= 2004) ORDER BY datefield DESC
View Replies !
Random Selection
Basically I have this: (category INT,type INT,item TEXT) I want to be able to select (say) 20 distinct random records with category=x and type=y. How can I do that ?
View Replies !
Conditional Selection
I have a table that contains some words and their translation, in various languages. I would like to select the translations for a particular language but use the English word in case their are no translations. I know how I could do it with PHP, but ideally I would like to do it in SQL only. I'm not sure it's clear at all, so here's an example: ID, translation, language 'BACKGROUND', 'Background', 'eng' 'INTRODUCTION', 'Introduction', 'eng' 'BACKGROUND', 'Contexte', 'fra' I would like to retrieve the French translations whenever they exist. In this case, only "BACKGROUND" has been translated, so I would need "INTRODUCTION" in English. The SELECT result should thus be: 'INTRODUCTION', 'Introduction', 'eng' 'BACKGROUND', 'Contexte', 'fra'
View Replies !
Selection Of Records In List
I was wondering how to query MySQL to display only a selection (1-20, 21-40) of a query in an HTML list. Can that be done in an SQL statement, or do you have to use PHP to dissect the returned array with for n elements to n+20 elements. How does Google achieve the returning of results in its search engine. I imagine that the same query may not necessarily be executed each page listing. I am wondering whether the results from the query would return an array of say 36000 elements.
View Replies !
Conditional Selection Clause
I'm trying to select all rows where if the field isChargable is Yes then the field charged must equal Yes to be selected. At the moment I'm trying: $query = 'SELECT * FROM calls WHERE status="closed" AND (CASE isChargable IS "Yes" THEN charged = "Yes" END) ORDER BY "opened" ASC'; But this doesn't work. If anyone could help me out with this I'd be very grateful, as I haven't managed to find anything relating to my problem online so far.
View Replies !
Query Distinct Selection
What I want 2 do is to select from 2 different columns that could have the same values e.g.My_Table { I_pk input1 input2 X_value } in the input1 field I have several names that could be or not in the input2 field so I need that my query shows every word that's in the input1 & the input2 but without repeating any of this words like a distinct but with the 2 fields
View Replies !
Help With Selection Of Datetime Fields
I'm having some trouble with certain SELECT statements that I need to execute. Basically I have a field of DATETIME format, and I need to be able to do things like: Select where <mydatetimefield> is between now and 15 minutes ago. Select where <mydatetimefield> is between now and 1 day ago. Select where <mydatetimefield> is between now and 1 hour ago. Can anyone point me in the general direction of some examples of this sort of thing?
View Replies !
Limit The Selection To 25 Rows
I am using MySQL 3.23. I would like to know how I can limit the selection to 25 rows based on the latest 25 tests and same batch. For example; select SN from tester where current_timestamp() > Date and UPS BC-06X1-H-SP-AUX1-C = Batch,? limit to 25 rows?
View Replies !
Re-sequencing A Column From A Selection
Here is the pseudo-code for what I want to accomplish: new_sequence = 10000 SELECT records WHERE list=(list I want to resequence) ORDER BY sequence_num For each record in SELECTion { UPDATE record SET sequence_num = new_sequence new_sequence = new_sequence + 10 } Now a little background: I am using MS Access 2003 as a front-end (linked table) to a MySQL v5 server. I have one table full of records from which many (400+) different lists are created. These lists have a unique, very specific order for the records (think Access Control Lists). I am using a column called sequence_num (an integer) to track the order. If an entry is inserted into a list, I have to give the new entry a sequence number between the two records where it is being inserted. Since I populated the database with all sequences incremented by 10, I just subtract five from the higer records sequence_num, then re-sequence the entire list using some VBA code to keep the increment between records at 10 for the next insert.
View Replies !
Selection From Multiple Tables
I've got three tables which all use a slightly different table structure but have a few similar columns.What I need to do is have a Query that looks through EACH of the three tables for rows that have a status of active and a spotlight of on.Then once I've got those rows,I need to order it via date_full. I've never had to do a large query involving multiple tables before but I had originally thought the below would work, but unfortunately it doesn't: SELECT * FROM community_events, knowledge_base, press_releases WHERE status = 'active' and spotlight = 'on' ORDER BY 'date_full' DESC I get the following error: "#1052 - Column: 'STATUS' in where clause is ambiguous". Obviously this is because the status (and spotlight) column is in each table. How do I get around this? I'd like someone to aid me in writing the query, but also some explanation of what the query does?
View Replies !
SQL Selection Based On Date
I'm using this function / string to pull data based on yesterday: $tomorrow = date('Y-m-d',strtotime('tomorrow')); How can I pull data similarily based on "last month"?
View Replies !
Advanced Date Selection
My database table holds 2 date fields containing the start of an event and the end of an event. Eg: Row 1 : event_start = 񟭇-05-31' | event_end = 񟭇-06-02' Row 2 : event_start = 񟭇-06-06' | event_end = 񟭇-06-07' Row 3 : event_start = 񟭇-06-12' | event_end = 񟭇-06-12' Row 4 : event_start = 񟭇-06-31' | event_end = 񟭇-07-02' ... Now i would like to run a query on this table to get me the days that have events planned within the month of a given date. If i would run this query correctly i would get the following results with the data above: 01 02 06 07 12 31 This is what i've got till now which only gives me the days an event start, i'm having trouble implementing the range in this query. Using mysql 4.1.20 SELECT DAY(event_start) FROM events WHERE event_start BETWEEN ADDDATE(LAST_DAY(SUBDATE(񟭇-06-27', INTERVAL 1 MONTH)),1) AND LAST_DAY(񟭇-06-27') OR event_end BETWEEN ADDDATE(LAST_DAY(SUBDATE(񟭇-06-27', INTERVAL 1 MONTH)),1) AND LAST_DAY(񟭇-06-27') GROUP BY DAY(event_start)
View Replies !
Engine Selection For Temp Tables
You can select which engine you're using when creating temp tables like this: CREATE TEMPORARY TABLE tmp ENGINE=Memory ... I would preferably like to use the Memory engine for all my temp tables since it gives me the best performance, however as the size of the database grows (and because of this the temp tables created will grow in size) I will run up against the size limit for the temp table. Is there a way to specify a "fallback", for example if I select the Memory engine but there isn't enough memory to have it gracefully fall back to MyISAM?
View Replies !
Conditional Selection Of Table Columns
I am searching for a mysql-command to make a selection on some table-columns only if a certain column exists otherwise the query should be on some alternative column. e.g. I'd like to make a selection 'SELECT columnA, columnX FROM table' only if the column 'columnA' exists, otherwise make a selection on 'columnB' instead of 'columnA' (lets suppose column 'columnB' exists). Is there a possibility to form such a conditional mysql-query. Please no replies suggesting a workaround with multiple queries.
View Replies !
Error - Problem In Product Selection:
I had just updated my website, however when I click on any subcategory on the Index.php page I get the following error message: Problem in Product Selection: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay. I have reviewed individual .php files and cannot find anything that could link me to this error message, I also looked through the MYSQL and could not find anything. I have been adding new products to the site but can see it except if it is though the main index page. I was told by my hosting company to do the following, I have minimal knowledge of the MYSQL. The Developer that worked on my site, did a crappy job and I am just fixing so far all his bad work. ***************************************************** Regarding the large MySQL query you are creating, please use an ON or USING clause to each table join so that you limit or decrease the amount of permutations the MySQL database needs to examine (thus decreasing the SELECT size). Please be advised that the code you are using should have this in front of the main query. mysql_query("set sql_big_selects=1");
View Replies !
Multiple Variable Selection In MySQL?
Im just trying to help my girlfriend get a query done. What she needs is pretty basic, select the total count of issues on table issues, the total count of projects in the projects table, divide them to get an average of issues per project, and then do a select that displays all projects and that project's issue count, compared to the average issue per project count. I just dont know the syntax to do it in sql, here's what i have. SELECT @NumberOfIssues:= COUNT(iss_id) FROM issue; SELECT @NumberOfProjects= COUNT(proj_id) FROM projects; SELECT @averageOfIssuesProj= @NumberOfIssues/ @NumberOfProjects; SELECT proj.projectName, count(issue_id), @averageOfIssuesProj - count(issue_id) from issue left join projects on projects.proj_id = issue.proj_id group by proj_id; How could I execute all that in MySQL at once? She cant get to execute everything at once, or how could she assign the variables and then call the select statements to that the variables are still in scope?
View Replies !
Multiple Many To Many Relationships Within A Heirarchical Selection Process
I have run into an interesting problem in having to develop a group of tables with an odd relationship set. I'll give you an idea of what it is: - I have 4 tables (3 contain classifications and the 4th contains the record's information) - the information for the end-user is taken from the 4th table based on selections made from the 3 tables - assume that each of the three tables contains a many-to-many relationship with the other two, i.e. a member of the third class can be a member of multiple second and first classes - the ideal selection process will go: select first(if second choices exist then select second, else display results from 4); select second(if third choices exist then select third, else display results from 4); select third(dislay results from 4). I am currently trying to implement this with 3 tables containing a class name and id then 2 linking tables between 1 and 2, and 2 and 3 referencing the class id's to denote the many to many relationships. What I'm not sure on is how many columns to use to categorize the records in the 4th table, or which to refrence them to, because they should be able to be accessed from any one of the three steps. If in fact the best idea is to have a foreign key in the 4th column for each of the other 3 tables then how would a many to many relationship actually be implemented? Also, it seems to me like there might need to be another linking table between 1 and 3 in case something from the third class resides under multiple first classes but only a single second class.
View Replies !
Product Price Selection Through Mysql Query
I have following data in table named "productprice" in mysql. id minqty maxqty price 1 10 20 10.00 $ 2 21 30 19.00 $ 3 31 40 28.00 $ My question is that what will be the query to find the price Case #1 : If quantity is greater than 30 [maxqty] then it should select record # 3. Case #2 : If quantity is less than 10 [minqty] then it should select record # 1 .
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 !
Slow
What generally would be the reason why all my db driven sites are running slowly or even hanging. I am on braodband speed but just changed hosts.
View Replies !
Update Too Slow
I need to update 25 * 5000 records, if I do one at the time it takes too long time, do any one have a good proposal ?
View Replies !
Very Slow Select
The line indicated below from my php script is very slow (about 10 seconds). I have this field indexed so I thought that it would be much faster. Could someone tell me what might be wrong? I'm also including the dump of the table definitions. This is a cd cataloging database. Right now the filenames table is empty and I'm trying to populate it, but at the rate it's going it would take days. I have about 700,000 records in the 'files' table, but none in the 'filenames' table yet. Code:
View Replies !
MySQLdb Slow
I have a Python program that parses a file and inserts records into a database with MySQLdb. I recently upgraded to MySQL 5.0.8, and now my parser runs *really* slow. Writing out to CSV files is fine, but when I try to insert the same records in a MySQL5 database, it slows to a crawl. Using MySQL 4.1 seems fine. The data seems to be inserted correctly, it's just really slow.
View Replies !
Slow Restore
Mysql 4.1.15 on Win2k. Using InnoDB. Using the mysql administrator gui to create a backup, everything goes fine, and restores quickly. Using the command line: mysqldump %dbname% --single-transaction > %dbname%.sql creates a file about 15% smaller than the gui produces, and is EXTREMELY slow to restore. I have tried adding locks, skip opt, everything. What does the gui use for a command to create this dump?
View Replies !
HEAP So Slow
I have a heap database, with 1.5-1.6 milion rows. on that is 2 columns... ID | Title title is indexed. When i run a query like this ------------------ SELECT index_data.* FROM index_data INNER JOIN `index` ON index_data.id=index.id AND index.title LIKE '%$query%' WHERE playtime > $dur... The execution time is about seconds... ------------------ Even a single like statement just on `index` (heap) takes 3-6 seconds. Here's the table stats... ---------------------------- Data 397,442 KB Index 24,639 KB Total 422,081 KB ---------------------------- Why??
View Replies !
Too Slow Queries
I am using version 5.0.4. I noticed that suddenly my queries were becoming too slow. I have data in three tables, with 40,000 rows, 50,000 rows and 70,000 rows respectively. I was able to run queries that joined the three tables together and get the results in less than 5 seconds. I tried updating the tables through a program that used a stored procedure to delete the tables but I had some other problems. Since the I restored the data in the tables from backup databases. Now I can't run any queries that join the three tables together, it waits for a long time and then times out. The data in the tables look good. Now I can't get the results from any of the other backup databases either. It look like the entire MySQL server has slowed down greatly. I tried rebooting the server to no avail.
View Replies !
Slow MySQL
mySQL has been running very slowly and I am getting errors. First I did 2 things I raised the ServerLimit number (apache) to allow for more connections, I also raised the max conncetions in my.cnf. I do not know if this took effect? That should have worked. But basically in phpmyadmin i get this error frequently. I am getting more traffic so I think it is that. MySQL said: Documentation #2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)
View Replies !
Slow Db Access
I have worked with a few mysql dbs on different servers but i have recently been asked to work with one on nicnames. It seems horribly slow. Working in phpMyAdmin (which i had to install myself) it takes ages when i want to do anything. View the table structure, view the data..etc. Any way i can test the speed so that i can compare it against another server i work with and proove there is a speed issue and take it to nicnames cus it is crazy and is going to affect the speed of the website!!
View Replies !
Slow Insertions
I have a MyISAM table with fixed width rows (ie no vars). I also have keys disabled. I am running a huge data load process. When it first started it was inserting about 38 records a second. Now, after about 200,000 records, it speed is about 22 recoerds per second. Whats going on here? and how can I improve it? I am loading about 15 million records, so the difference between 22 per sec and 38 per sec is SIGNIFICANT (ie 2 days).
View Replies !
Slow Performance
On my index.php page, I have a simple query that checks the session_id against a table where I store other session_id'. If it's not there, it records it (unique hit). If it's there, it doesn't record it (not a unique hit.) This usually goes off without a hitch, and every month or so I empty the table. Right now I only have about 2500 rows, and it's taking forever to load the page. Is there something possibly server related that could be causing this? My host charges an arm and a leg just to see if there's something wrong if I bring up an issue, so I'd like some insight as to whether there's a commonly known server-side issue that can bog down performance.
View Replies !
Slow Queries!
I have a website which has a users table in a mySQL database. This users table is large (It has about 25 columns - most varchar(100)) but only has about 10000 records. The records contain user information which is searched with a javascript form. My problem is that when I click to 'view all', it takes about 7 seconds to load. This seems a lot? Does 25 cloums seem sxcessive in a table? Can anyone point me to some good tutorials / docs on improving query performance? I have defined the colums as best as I can, but I am using SELECT * from table, would selecting individual columns make a big difference?
View Replies !
Slow Subquery
Can anyone tell me why the following query with sub-query takes forever to finish? (I've le it run for 20 minutes, and it still hasn't finished) select date from temps where date in (select distinct date from observations where camera like "a") The sub query returns 10 dates. The outer query is on a table that contains about 40,000 rows. What's the big deal here? All I'm trying to do is select rows from "temps" that match a small range of 10 dates. Is there another way to do this? Is a sub-query the wrong approach?
View Replies !
Slow Connection
I build an application and installed it on many machines. In every machine except two, the program works without problems. On this two, the connection with database is too slow. I saw the opened doors with 'netstat' and the computer opens about 5 or 6 ports (to the port 3306 of the mysql server) before sucessfuly connect with MySql Database and execute the sql. I don't know what could be happening. I realy need to fix this because the progrm is too slow with this error. Could anyone know what could be happening??
View Replies !
Slow Query Log
my slow log is catching a slow query, however the timestamp for the query is "0". I also placed a timestamp on the query to echo out to the results page, and it is about 4 thousands of a second. Why is it showing in the slow log?
View Replies !
Slow Query Using NOT IN
I am migrating a MSSQL server to MySQL. I know the following SQL is valid for both servers, but MSSQL finishes execution of the query almost instantly, and MySQL has been running the query for the past ten minutes and still is not finished. There is basically the same amount of data in each database. Does anyone know ....
View Replies !
Slow Subqueries
I know (by internet) that mySQL is very fast. Problem is that my subqueries that are very fast in Access or SQL Server but they are very slowly in mySQL - since I have to restart my computer because mySQL freeze all the processes. (the resources where used at maximum - 2 Gb RAM, 2,5 Ghz processor) This is the query : SELECT NPL, PP FROM P_A INNER JOIN ACTIONS ON P_A.NA =ACTIONS.ACT_N WHERE P_A.NA in (SELECT NA FROM P_A WHERE NP ='ABC') P_A has 5 columns and 12000 rows Actions has 5 columns and 770 rows Any suggestions ?
View Replies !
Slow Request
I want to run the following request: mysql> select count(*) from fingerprints where fingerprint in (select fingerprint from fingerprints where id_file=3263); where fingerprints is the following table: ....
View Replies !
|
TRACKER
