Improve SELECT Command
I perform a SELECT on my database, but it takes over a minute for every
run. I have to run it over 10000 times (with different values in the
WHERE), so it takes way too long. A was therefore wondering if I could
improve the query speed. Below you find the query. It is based on the
ratio between a pixel (pix) vs. the average of its neighbourhood (from
geo) in the same table (vgt) based on additional requirements
(mgba,mgsc,eco). Code:
View Complete Forum Thread with Replies
Related Forum Messages:
How To Improve On A Nested Select
I'm working on a simple data import tool, and I need to insert email addresses from table two into table one, if they don't already exist in table one. I figured this was a pretty easy nested select statement, but what I'm doing is getting my site taken off line for exceeding the CPU limit. Here's the SQL I'm using to get the new email addresses: SELECT distinct value, id FROM table_two WHERE name = 'email_address' AND value NOT IN (SELECT DISTINCT subscriber_email FROM table_one) There are about 4600 rows in table one, and 145,000 rows in table two. Does this seem like it would be a burdensome query? I'm not a SQL expert my any means, so is there a better way to go about this? It seemed like a simple one to me. Maybe my web host is just stingy with the CPU time.
View Replies !
Select Command
I tried something like this Code: SELECT * FROM db where Db = '%test%'; [code] that would show me list of data (rows) that have a column that match the 'test' wording... Like "subtest", "abctestdef" and "test" for example... But that doesn't seem to work, so what is the correct symbol instead of "%"?? I'm using MySQL 5.0.16... Edited: Never mind. Found it via google search instead of mysql search, it said I need to do this for it to work.. [code] select * from db where Db like '%test%';
View Replies !
Select Command Question!
I have a medium sized database and I ran this query "select * from employee, job_title G" so it displays all of my columns how I want but I cant see them all though. Its just like if you guys have ever done a "dir" command in ms-dos. But I need to know is there a way where i can stop it in the middle of the query just like the "dir /p" command does?
View Replies !
Select Command Question
I am trying to compare data between table1 (compete information) and table2 (partial information) and get those not existing information for table2. My case: Compare table1 & table2. Get those left behind information of table2 table1: full complete information Field: id, code, price, date table2: partial information Field: autonum, b_id, code, close, close_date SELECT a.id FROM table1 AS a, table2 AS b WHERE a.id <> b.b_id The result is the command catch all the information in table1 and table2. Can anyone tell me what's wrong and what should I do to get those lleft behind information in table2.
View Replies !
SELECT Command - JOIN?
I have two tables, 'devices' and 'lads', that look like this: devices mysql> select * from `devices` where `team` = 'both' or `team` = 'sml'; ....
View Replies !
Select From 2 Tables With Count Command?
i have 2 tables called items and orders. i want a sql query which will count all orders to each items then select those items with 15 orders and more. i have done this but always an error: Code: SELECT itemid, orderid, ordername, itemname, count(orderid) FROM item i, orders o WHERE o.itemid=i.itemid GROUP BY o.itemid HAVING COUNT(orders.orderid) > 15
View Replies !
Using Limit With Select Or Update Command
How come I can do: select field1 from database limit 10 *shows first 10 records like it should select field1 from database limit 1,5 *It returns first 5 rows correctlly update database set field1='first set" limit 10 *It correctlly updates the field for the first 10 records update database set field1="top 5" limit 1,5 *I get an error that says problem with limit at ^5 check command for correct syntax Is there different syntax for limit when using with a select statement Vs. an update command?
View Replies !
Endless Loop When Using Select Command
I recently installed mysql server 5.0.18 on a suse enterprise server 10 box. The install went fine, and I imported some old data from a .sql file. Whenever I do a select command, however, the results scroll slowly across the screen, and endlessly loop. I have to terminate the client.
View Replies !
List All Tables In Db Using SQL Select Command
I need to get a list of all tables in a MySQL database. (I'm running 4.0.1) atm. I need to get at the tablenames using a plain sql select statement. I can't use show table and i can't use api functions.It has to go through odbc and ado.net! Schema information ought to be available somewhere, right? (Of course I can't scan the directories for files either!) follow-up question: is there an ole_db driver for MySql?I could use its getSchemaTable function then.
View Replies !
Strange Select Command, Is That Normal?
I am using addslashes before submitting a string to mysql. Means, O'REILLY is getting submitted as O'REILLY. Now I was using a select command. PHP $lname = addslashes(htmlspecialchars($_POST['lname'])); "SELECT * FROM $mytable WHERE lastname = '$lname'" Problem is that, it's not working. So I made a function, and after much hit and try, this is what I found. PHP function kgbaddslashes($string) { $string = str_replace("'", "\'", $string); return $string; } and then PHP $lname = addslashes(htmlspecialchars($_POST['lname'])); $lname = kgbaddslashes($lname); "SELECT * FROM $mytable WHERE lastname = '$lname'" Now, this is working, but I am wondering where's the problem. I mean, why I had to add so many slashes to make it work?
View Replies !
Improve Speed
I need to improve the speed of my ff sql statement but I do not know HOW! I am using mysql in running this. If the records are below a thousand, I have no problem getting the info quickly. But I tried it on 700,000 records, the feedback took 22 secs.In this sql statement, I am currenly accessing the same table but I have to get the rows of certain conditions multiplied to different numbers. SELECT COUNT(IF(code='play',1,NULL))*5+count(IF(code like 'ok%',1,NULL))*2.50 + COUNT(IF(code='prev' OR code='prev_here',1,NULL))*15 + COUNT(IF(code='hello' OR code='hi',1,NULL))*15 + COUNT(IF(code='new',1,NULL))* 2.50 as sum from table;
View Replies !
Improve Query
I have this query that I use for stepping through records in a table, it selects for me the lowest, previous, next and highest record id refs for the table in question given the current record number. However it returns its query on two rows with repeated (and obsolete) data due to the case statement, I pick out the data I require using mysql_result command in PHP, but it would be much nicer if there was a way to only return a one row result with only the data I require. user_id is the autoinc field in table_t $id is the current record number being viewed PHP SELECTmin(user_id) AS First,max(user_id) AS Last, CASE when sign(user_id - '$id') > 0 then min(user_id) else max(user_id) end AS PrevNextFROM table_tWHERE user_id <> '$id'GROUP BY sign(user_id - '$id')ORDER BY sign(user_id - '$id') for completeness my PHP code for extracting the values i need is PHP $first = mysql_result($result,0,0);$prev = mysql_result($result,0,2);$next = mysql_result($result,1,2);$last = mysql_result($result,1,1);
View Replies !
Using >= In Select Statement From Windows Command Line?
I've learned from a post by Guelphdad that I can output the results of a SELECT query into a text file (keeping the column headings) using the following (on Windows XP using MySQL). echo select sum(sales) from sales where year(ship_date) = '2008' | mysql -uuser -ppassword mydatabase > outputfile.txt That works just fine. But I run into trouble with: echo select sum(sales) from sales where ship_date >= '2008-01-01' and ship_date <= '2008-05-31' | mysql -uuser -ppassword mydatabase > outputfile.txt An output file is created, but it is zero kB and does not contain any information (as one would expect from 0 kb). I assume that the problem is with my usage of > in ">=" (and possibly also the < of the "<="). I've googled for help escaping > on the Windows command line, but I haven't found anything to solve my problem. I've tried 1. echo select sum(sales) from sales where ship_date ">"= .... (One suggested method for escaping the ">" symbol is to put it in quotes.) 2. echo "select .... " | ... 3. echo ("select ..... " ) | ..... All three atmysqlts gave me SQL errors. 4. echo select sum(sales) from sales where "ship_date >= '2008-01-01'" .... doesn't give me a SQL error, but it gives me the 0 kB file. I know that escaping ">" is really a "Windows" issue, not a SQL issue, but here it concerns a specific use of Windows, namely to put the results of a MySQL query into a text file. So I hoped someone here had already figured out how to do so. I realize that I can avoid the issue with "where ship_date between start_date and end_date". But I am hoping that one doesn't have to give up on using ">=" altogether, as that response would suggest. So is there a way to safely use ">" and "<" in queries executed from the command line?
View Replies !
Can I Issue One Select Command And Get 3 Different COUNT Values?
Can I issue one select command and get 3 different COUNT values on different WHERE condition on the same variable? Example: Lets say I want to know the number of sign-ups for today, last 7 days and last 30 days. Can these 3 COUNT values be obtained from 1 SELECT statement? If yes, what would that statement look like? FYI, I tried this and it does not work: ....
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 !
Best Way To Improve Performance On Order By?
I hope someone can help me with the following problem. (Note: I will simplify my table structure to the essential) I have two tables, one containing objects and one containing the objects a user has, so basically I have * table_userObjects with PRIMARY KEY idUserObject INDEX ON idObject (not unique) * table_objects with PRIMARY KEY idObject INDEX ON name What I want to optimize is the query which gets the objects for one user and sorts them by name. For example: SELECT * FROM table_userObjects,table_objects WHERE table_userObjects.idObject = table_objects.idObject AND table_userObjects.idUser = 3 ORDER BY table_objects.name LIMIT x,30 The db has around 40000 different objects and the top users have 200000 different items. In this cases it takes around 6 seconds to run the query. If I leave out the orderby no sorting has to be done and the query runs fast as expected. Is there anyway to create an index on table_userObjects, based on the name of the objects from table_objects? Or some other way to speed up this query?
View Replies !
Indexes To Improve Performance
I am looking to add indexes to my database to try and improve performance. Now I heard someone say once that you should add an index to any column that you are planning to filter using in the where part of your statements. So my question is this, in below is an example i have a table that I use to store reports that are generated by the system (i haven't chosen this especially but it has the core element that i user everywhere else). These core elements and columns that i regally in a where clause have '*' next to them. As such if i was following the advice of were to add indexes, i would probably add one to each of these columns. Then there are the other three that have '+', which occasionally i conduct a '%%' (wildcard) search on to help me find a cretin report. given this, plus the index on the primary key almost every table would have an index on it. To me this seems a bit much. So how many is too many?
View Replies !
How Do You Improve The Order By In Queries?
I read somewhere about mysql having to scan the table twice or something with "order by something" this was on mysql.com (as far as I remember) it said something about having it only have to scan it once but it didn't explain how to do it I really want to know, because a 500k row table of mine with a few indexes that cut it down to about 90k per topic is lagging more than I want (okay, so 1.2 seconds average page load on a 8mb connection isn't too bad, but I want to fix this order by stuff and maybe make it 0.9 or 1.0 :P)
View Replies !
Index To Improve Queries With AVG()
Is there any way to create some sort of index that will improve the performance of queries which use the AVG() function. An EXPLAIN statement on the query indicates that no index is being used. There are indexes on the ratings table for ID and ratings but they don't seem to be getting used EXPLAIN SELECT u.username AS username, ur.users_ID AS user_ID, IFNULL((AVG(r.rating))*(COUNT(ur.ratings_ID)),0) AS overall_score FROM ratings AS r, user_ratings AS ur, users AS u WHERE r.ID=ur.ratings_ID AND ur.active='yes' AND u.ID=ur.users_ID AND u.active='yes' GROUP BY ur.users_ID ORDER BY overall_score DESC LIMIT 0,40 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE r ALL PRIMARY 5 Using temporary; Using filesort 1 SIMPLE ur ref rating,users_ID_2,active rating 4 v2.r.ID 239 Using where 1 SIMPLE u eq_ref PRIMARY,active PRIMARY 4 v2.ur.users_ID 1 Using where
View Replies !
How To Set An Index On Table To Improve The Performance
i've got a table with about 500 mio records. there are 3 col. decimal,decimal,int. my aim is to find data which fits best to the two decimal col. in which way should i set the index for the best performance? at the moment my query needs sth about 5min and it shouldn't need longer than 10 sec. is it possible to improve the performance only by using the correct index.
View Replies !
Limit Records To Improve Performance
I'm facing a performance issue, I'm using JDBC to read rows on a huge mySQL database 1.5 Million rows. The programme basically read rows by sample of 1000 rows. select * from <tbl_name> LIMIT i, i+1000 and print result in a text file. This takes 5 minutes for the first 500000 rows, 10 min for the following 500000 and 18 for the rest. Which seems to me very slow just for reading rows? I wonder 2 things: 1- is this normal for mySQL to take this time? 2- if not, is using LIMIT this way in the SQL would have an effect on mySQL performance? 2- how can I improve this performance. If you think of anything that can improve the performance on a select * from , please let me know.
View Replies !
ERROR 1142 (42000): SELECT Command Denied To User
I've created a dump file and am trying to load it. When doing so I receive the following: ERROR 1142 (42000) at line 22: DROP command denied to user 'sqladmin'@'localhost' for table 'XXX_XXX' I've tried doing the same with other databases and receive the same error. I've tried our other web servers and those database and receive the same error. Here's the really odd thing, I was able to load data with the exact same syntax, same databases, a week ago. Nothing that any of us are aware of has changed. Any thoughts as to how I can resolve this?
View Replies !
Adding Missing Rows In Table In 1 Select (+ Join) Command.
Hi all! Here's what I need to do : I have two tables : A B a b c d ---- ---- 1 z 1 k 2 x 5 l 3 c 6 j I need a SELECT with JOIN that would give me : A a b ---- 1 z 2 x 3 c 5 NULL 6 NULL so I need to add the missing rows from the A.a and B.c JOIN, how can I do that ? I can't use a Union 'cause I can't use MySQL version 4.
View Replies !
How To Improve The Speed Of Mysql Query Using Count(*)
I'm using this kind of queries in mysql in InnoDB engine Select count(*) from marking1 where persondate between '2007-04-23 00:00:00.000' and '2007-04-23 23:59:59.999' and PersonName='aaa' While executing these queries from front end VB, It takes above 5 secs with 50 thousand records. How can I improve speed for this kind of queries. Is there any alternation for this command.
View Replies !
Error 1142 - Mes. The Following Occured: SELECT Command Denied To User Xx@hostx.com For Table 'user
When trying to do anything to do with "User Administration" in MySQL Administrator 5.1 connecting to a remote MySQL database, I get the following message when I click on the "User Administration" icon/text in top left frame. Verbatim quote apart from my connection details which I am anonymising): A MySQL error was encountered. The message is: Could not fetch user names. The following occured: SELECT command denied to user xxx@hostxxx.xxx.xxx.com for table 'user' (1142) No I have looked through search and found the following: http://lists.mysql.com/mysql/134369 Unfortunately that tells me that it has happened to someone else but that's about it. What is one supposed to do with the fix? I am wondering if this is why I cannot create ordrop any tables with this username at the moment, even though it is the Admin level username....
View Replies !
Best Command
i would like to know the best command to do backuping of the tables in the databases....... i'm using MySQL 4.0 on a solaris 9 OS .....
View Replies !
++ Command In SQL
I am developing a website in MySQL and PHP at www.zakirium.com. The site is supposed be a listing of the best freeware on the web. I'd like to be able to keep track of how many times each piece of software is downloaded. Is there a type of ++ command that could be used with an UPDATE command in PHP that would increase the number in the database column by one, so every time there was a download, the number would increase by one.
View Replies !
Where Command
If I want to update multiple rows and set the same column in each to the same thing, how would I go about doing that? UPDATE gfx SET creator = 202 WHERE id='1,2,3,4,5' Obviously that doesnt work, can I make an array somehow? Or do I have to use a bunch of or s?
View Replies !
SQL Command
If in my data I have for title the values Marketing Manager, Manager, Database Manager and I use LIKE 'Manager' I would want to return all titles with the word Manager or Managers etc. My code only returns an exact match. Can this be easy to do? Code: SELECT * FROM jobtitles j WHERE `title` LIKE 'Marketing Manager';
View Replies !
Sum Command
This may be a silly question but my knowledge is very very limited. If I have 6 columns, say a,b,c,d,e,f and i have a entry where values were a=1 b=2 c=3 d=1 e=2 f=3 If I wanted a sum of cells a,b,c and output a new column called abc and then a sum of cell d,e,f and output a new column called d.e.f how do I go about it ? select sum('a,b,c') $abc sum('d,e'f) $def from detailsas said, knowledge is very limited,
View Replies !
Command To Use
Is there a command that I can use to select a row that contains a particular word in a series of words? For eg. how do I select the row that contains "I am Neo and I hate Smith" in a column if I just search with "Neo"?
View Replies !
Using Command --help
I wonder if ther is any way to get help while using mysql clinet etc. In Linux one can type man command or command --help. If I have forgotten the syntax, for example of grant or update command, how can I get help?
View Replies !
Sql-command
why doesn't work this sql-command? it gives me a syntax-error: SELECT *, IF((SELECT COUNT(*) FROM A, B WHERE A.pk = B.fk) > 0, 'true', 'false') FROM A; the inner sub-selection itself works fine: (SELECT COUNT(*) FROM A, B WHERE A.pk = B.fk; also this would work: SELECT *, IF(1 == 1, 'true', 'false') FROM A; I'm using 4.0.21 - might it be too old for this command? if yes, how could I get it otherwise?
View Replies !
Command
I moved my forum to another folder and now all the references in the database still point to the old folder. What command can I use to update all the URL's in the database to the new folder? EDIT: Nevermind, I figured it out.
View Replies !
SLEEP Command
Is there a 'sleep' command in mysql? I'm trying to take a backup of MySQL instance from a ksh script, and I want to lock all the tables before I take a back of all datafiles. So, once I issue the lock tables command and exit the session to take backup of the OS files, the session (and there by the lock) is lost. So, I'm trying to issue a lock command from one script and let it sleep till my backup is completed.
View Replies !
GROUP BY Command
I've got a table with following fields and data: int int double article,userid,price 1 1 10 1 2 15 1 1 20 2 1 10 2 2 20 3 2 100 3 1 200 What I want to know is, which user pays the highes price for every article. And this in one sql-command. The Result have to look like this: int int double article,userid,price 1 1 20 2 2 20 3 1 200 Any ideas?
View Replies !
Connect Command
I'm new to MySql and I'm having a problem figuring out how to configure my Connect command so I can test on my PC and upload scripts to my ISP without constantly changing the Connect command to reflect the applicable host. I use LocalHost on my machine and then change it to my ISP's IP on their network before uploading the scripts. I'm sure there's something I'm missing in the manual about setting up Grants.
View Replies !
Command Editing
I'm running mysql 4.1 on a Mandrake 10.1 machine. When I try to edit commands using the arrow keys, many keys don't work properly, most notably the delete key. That is, if I make a typo at eh beginning of a long command, I have to way to correct it. Entering the delete key just echos tilde characters in the console. The bash prompt itself recognizes all these keys correctly. This sort of command editing seem to work fine under Win2K at the command prompt.
View Replies !
Mysql BETWEEN Command
i just want to know on how can i query certain part of data using the date.what i mean is, i want to query for example the data from 2005-04-25 till 2005-04-30.i've try the sql code below,but it does'nt work.can anyone give me an example on how can i query using mysql??plz help.tq rsLetterDate.Open " select * from letter BETWEEN letter.letterdate = '2005-04-01' AND '2005-05-31'", "DSN=Data", adOpenStatic, adLockOptimistic
View Replies !
Command For Reversing
How would I go about undoing this operation? I 've been searching for the reverse of CONCAT but the MYSQL manual doesn't seem to show this. UPDATE products_to_warehouses SET warehouse_sku = concat('SYNC',warehouse_sku) WHERE warehouse_id = 3 I want to remove the SYNC that I have at the beginning of every record.
View Replies !
Mysqlimport Command
Has anyone tried to use "mysqlimport" to import a comma delimited text file with SQL commands in it? I have tried using the following:- mysqimport -f Table2 /usr/home/ariff66/table2.txt; but keep getting an error 1064 - error in the syntax. (-f is because a Table2 doesn't exist)
View Replies !
Using The Mysqldump Command
I've been doing some research into creating a backup script for my PHP website. Using the mysqldump command looks like the best way to go. I am unsure on one thing though - where does it save the file to? Like if I don't specify a path to the file, just something like 'dump.sql', then where will it save the file to? And how do I make it save to, for example, the root folder of a users website? My script may be on different websites so the path to the root folder of the website may change.
View Replies !
|
TRACKER
