Improve Sql Queries
i want to improve my sql queries, i am very poor in writing the queries so please tell me how to improve my writing of queries
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
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 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 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 !
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 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 !
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 !
Slow Queries When Using Views Compare To Direct Table Queries
I’ve having problems with my server load for a while now. I have two tables with different content, but I need to display them in the same results, so I created a view with a union all (named: top_news_videos). The problem that I’m seeing is that when running a select to the view it takes a lot longer (and in that way, more server intensive) than running the query directly to one of the tables. For example, I created a page where I run 7 queries similar to this one: MySQL Code: ...
View Replies !
Complex Queries Versus Multiple Simple Queries
I am constructing a database to contain information about stories posted on my site. Information included will be things like title, author(s), genre(s), story codes, synopsis, etc. I worked out that storing this information properly, so that it can all be searched on, could take as many as ten tables. My question is this: Is a single complex query really better (more efficient for the server) than multiple simple queries? In other words, I may need the information for as many as 25 or even 50 stories for a single page. Is it better to get all of the information out of a single, massive, complex query, or is it acceptable to get the information essentially one story at a time, which could mean 25 or 50 simple queries...?
View Replies !
Many Smaller Queries Vs. Large Comlicated Queries
I am wondering if any one can help me. I have a page that will run around 85 smaller queries but if i combine the queries it will go down by almost half. This page is a high traffic page and I don't a complicated query taking up mysql resources while it created a temp table and such. My question is this: Is it better for mysql to run a lot of smaller queries (ex: simple selects with zero or one join, group by) or one larger complicated query with everything combined. The thing i have to keep in mind is that the mysql selects are comming off the localhost that the web server is also running on so they share the same resources.
View Replies !
Long Queries VS Multiple Short Queries
I have a php script that requests a very long query from our mysql database. It has lots of joins and accesses at least 5-6 tables. My question is should i break it up into smaller separate queries or leave it as one long one, in regards to best practices?
View Replies !
How To Put These Two Queries Together?
Howto put these two queries together? sql = "SELECT DISTINCT medium_id, COUNT(tag) AS count FROM tags WHERE tag = 'test' GROUP BY medium_id ORDER BY count DESC" This query is working. Id like to try something like: sql = "SELECT * FROM media WHERE id IN (SELECT DISTINCT medium_id FROM tags WHERE tag = 'test')" But how can I order the resut by quantity (counts of tag)?
View Replies !
1 Or 2 Queries
I have an established schema of databases and tables. A requested feature came up, that would allow for a user to add an item to the database, and not have it display on the website. Would it be better for the site to run a query along the line of SELECT * FROM db.table WHERE item=item AND view='yes' or Get the data then run a query against a separate table to see if the part is in the not viewable database
View Replies !
2 Queries
it won't run both queries and unite them - it's ignoring 'where approved='yes')? : Code: $sql = "SELECT CONCAT(firstname , ' ' ,lastname) AS FullName, username, homepage FROM customer WHERE approved='yes' AND lastname LIKE 't%' OR username LIKE 't%' ORDER BY lastname ASC";
View Replies !
WHERE (xyz OR Abc) Queries?
I'm just wondering if it's possible to include some sort of "OR" type condition in MySQL queries? I'm assuming it can be done, but I can't seem to find anything, and of course typing MYSQL +"OR" into a search engine doesn't bring the most specific of answers.
View Replies !
2 SQL Queries
2 questions I'm hoping someone will be kind enough to help me with: Firstly, is it possible to do something like: SELECT * FROM (SELECT `table_name` FROM `table_list` WHERE `type` = 4 LIMIT 1); I'm trying to select the table name to use by querying another table. Secondly, I'm trying to use variables to tidy up another query: SELECT @r := TRIM(TRAILING '_' FROM `long_room`) AS 'Room' ... WHERE @r LIKE 'WL214'; Am I using variables in the right way? This doesn't return anything and using "'Room' LIKE 'WL214'" doesn't work. I know I can use the whole Trim statement in the Where clause, but I want a tidier way of doing it.
View Replies !
Max Queries
Using JDBC and a connection pool, MySQL 5.0 on Debian Sarge, does anyone have any prior knowledge or a suggestion - should I limit the number of queries per connection? If so - to what number? (I've already got a maximum time to live for a connection, so at any case I'm not going to reuse a connection forever). Is using and reusing a connection memory-consuming, tending to grow over time?
View Replies !
Best GUI For Queries
I currently use myqlcc instead of query browser, but have been looking for a replacement that might combine some of the best features of both (plus one still being updated). I use the prog for writing and running queries, not so much for administering the db. I don't hear a lot written about them, but these are what I consider to be the best features of mysqlcc, which I can't find replicated in other progs. Can anyone recommend a GUI that has at least some of them? --Table/field highlighting (recognizes real db data and highlights it when entered) --Autocomplete (shows me possible matches from partial entries with a keystroke) --Parentheses highlighting (lets me see what is in a given start/end parenthesis) --update/delete results messages (shows xx rows matched, yy rows affected) --Tabbed multi-threading (of course) --Ability to grab all/some results and copy to the clipboard for export to Excel (not all have this) Anytime I try to google favored applications, I come up with Navicat, which seems to have the budget to buy search results; doesn't make it a good program (does anyone like it?).
View Replies !
Queries Per Second: 135.73
According to Runtime Information: Queries per second: 135.73 Is this an acceptable value, or is this a sign that something is probably wrong? The site gets about 300k page views per day.
View Replies !
Two Queries At The Same Time?
I have 2 queries: The {..} are precalculated values. At the beginning no item can be matched by both where clauses. 1) update tabelle set L = L + {MOVE} set R = R + {MOVE} where L >= {LEFT} and R <= {RIGHT} 2) update tabelle set L = L - {DIFF} set R = R - {DIFF} where L >= {OTHERLEFT} and R <= {OTHERRIGHT} My problem: If I run update 1 first, the update 2 can match items from where clause 1 because the update changed the values from L and R so that they can be found at where clause 2. And if I run 2 first its the same in the other direction. So how can I do the 2 where clauses first, and when mysql knows my items I would like to update so no item gets updatet twice.
View Replies !
How To Log Specific Queries
I've got this DB. Every few minutes new data comes in and is inserted. Now and then for some reason duplicate data comes through - not a big problem - unique key. However I'd like to log the insert failures, due to dupes, but I don't want to log all of the good inserts also.
View Replies !
Join 2 Queries Into 1
I would like to know how to join 2 queries so that the results of these 2 queries show up in the same query: SELECT b.bios_serial_number FROM bios b: SELECT s.system_name FROM system s; Basically I want to create a report that includes both system name and serial number. I'm new to this and none of the JOIn documentation was clear to me.
View Replies !
IN Vs Individual Queries
If I needed to fetch 3 entries from a database, is there a significant performance difference between the following? SELECT * FROM JOBS WHERE myCol IN ('val1','val2',val3'); Versus SELECT * FROM JOBS WHERE myCol ='val1'; SELECT * FROM JOBS WHERE myCol ='val2'; SELECT * FROM JOBS WHERE myCol ='val3';
View Replies !
Sub Queries In Mysql?
Is there a way to do something like subqueries in one query? I have a customer table (Klant) with contacts (KlantKontakt) and a address (Adres) table. The address table keeps a record of each different address for a customer including old addresses (to maintain history). If I want to retrieve a list of customers with their newest address, I have to do a max() to retreive the last address. I can do this in PHP in a seperate query but I like to do it in one query on the mysql prompt. Code:
View Replies !
Multiple Queries W/ 4.0.x (C API)
When executing multiple queries, which return a result set, must mysql_free_result be called after retrieving each result set using mysql_store_result? My concern is that if the result was not freed after each retrieval, that perphaps it may impose a memory leak. Or, does MySql reuse previously allocated memory for subsequent result sets? My app performs several back-to-back select and select/insert queries. Just need to know how to handle this action.
View Replies !
!= Operator In Queries
I have a query with the following syntax to find students that haven't voted: SELECT * FROM Students WHERE Students.StudentID != Voted.StudentID The thing is, the results I'm getting are basically the same as if has just used 'SELECT * FROM Students' alone. The StudentID list of 'Voted' is shorter than that of 'Students', as some students haven't voted yet. Anyone know what I'm wording wrong here?
View Replies !
Combining Queries
How can I get this into one single query? I donot want to run one by one manually. Code: UPDATE TABLE1 SET title = '0' where title = ' ' UPDATE TABLE1 SET name = '0' where name = ' ' UPDATE TABLE1 SET book = '0' where book = ' ' UPDATE TABLE1 SET descr = '0' where descr = ' ' UPDATE TABLE1 SET code = '0' where code = ' '
View Replies !
Executing Queries
I am pretty new to MySql so please bear with me. I was wondering if it is possible to format your queries as a url which can then be run through a web browser. For example, lets say I have a string stored in a table is it possible to do something like this: http://myHost/myDatabase/Select myColum from myTable If that can be done will the result display on the screen?
View Replies !
Difference Between These 2 Queries
Code: select * from foo where (col1 or col2)='foo' and (col3 or col4)='blah' and select * from foo where (col1='foo' or col2='foo') and (col3='blah' or col4='blah') shouldnt they return the same set? or am i missing some logic here
View Replies !
Number Of Queries
My team is working on transfering old db data to mysql. Its been configured somehow where it caps at 1 type of query at a time. Anyway to increase this? At one point it did around 700 at one time, but some configuration messed this up, how can we incrase this?
View Replies !
Less Queries Or Less Data
is it better / faster to have rows that hold more data, (ie putting in a varchar username instead of an integer userID to save having to query another table to get the username) or is it better the other way around? (of course unless the username could be changing which in this application the username cannot be changed)
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 !
Some Date Queries
I need help writing a query that will pull results from the database that are dated this month.So I want to see all results for December this month and January next month ... and so on. The field name is 'date.
View Replies !
Queries Pr.page
I use php/mysql for website, I am on novice level, - meaning I do not write php classes and such. I keep it real simple, with a simple db, full of records. question is; how many queries is normally run on a complexed site? take DevShed and imagine it was run on mysql, how many queries aprox does it need to get loaded? I am currenlty doing about 10 different queries pr. page to bring out some user details and different records of the user using 3-4 different db-tables.. and I wonder if this is normal, or if it's extremly bad programming / db design..? it loads fast enough, but it just seems alot.
View Replies !
Quotes And Queries
I'm wondering about the proper formatting of queries with repect to quotes. For example: SELECT something WHERE something_else = 'this' AND 'that' or SELECT something WHERE something_else = this AND that
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 !
Executed Queries
I am doing database synchronization project . I wanna know, is there any file where the mysql stores the currently executed queries that is list of currently executed queries so that I could extract those queries from there and send them in mail for synchronization. [note: I am using linux platform] I added log-bin=[querylog_filename] in my.cnf and using mysqlbinlog command I was able to view the currently executed queries stored in [querylog_filename].000001 binary file but I was not permitted to access .
View Replies !
Rating Queries
say I query a mysql database with keywords from a user(search engine) that results in many records. Is there a way to rate the results by having the records that have the most matches to the users keywords, with the goal of displaying them in descending order(a search engine)??
View Replies !
Combine Two Queries Cant Seem To Get It!
This is the problem, below is the table I need to query I need to select the id with only the mac address and also get the data of the keyword role. something like this: SELECT id from table where mac address = (macaddress) and select data from table where keyword = role; +-----+-------------+---------------+-------+ | id | keyword | data | flags | +-----+-------------+---------------+-------+ | 263 | record_in | On-Demand | 0 | | 263 | role | default | 0 | | 263 | callerid | device <263> | 0 | | 263 | allow | | 0 | | 263 | qualify | no | 0 | | 263 | callgroup | | 0 | | 263 | pickupgroup | | 0 | | 263 | nat | yes | 0 | | 263 | mailbox | 263@default | 0 | | 263 | username | 263 | 0 | | 263 | mac | 00041324668B | 0 | | 263 | record_out | On-Demand | 0 | | 263 | context | from-internal | 0 | | 263 | dtmfmode | rfc2833 | 0 |
View Replies !
|
TRACKER
