Slow Join On Otherwise Fast Views?
select * from vw_fielddata
takes 16ms to return just four rows, why does
select
*
from
vw_fielddata firstname
join
vw_fielddata secondname
on
secondname.AccountID = firstname.AccountID
take over an hour? The views themselves contain joins on tables and further views, and although AccountID isn't a primary key from the tables it's gathered from it is a foreign key in them (I assume this makes it indexed?).
View Complete Forum Thread with Replies
Related Forum Messages:
How Do I Rewrite A Slow Subquery Into A Fast Join? (Prolly Real Easy For A Non-noob To Answer)
I used subquerys because they made more sense to me, until the table got "a lot" of data in it (not really... just 1000 entries) - then all querys including subquerys slowed down to 4-5 secs EACH!! :) This is insanely slow. What I am doing here below is looking into what messages a user has already read in the subquery, so that none of the ones he already has read will EVER again show up for him. So, how do I rewrite this subquery: NOT IN (SELECT reffen FROM $readt WHERE sender = $nr) From this entire SELECT: SELECT halfref, brokensms, DATE_FORMAT(arrived, '%y'), DATE_FORMAT(arrived, '%m'), DATE_FORMAT(arrived, '%d'), DATE_FORMAT(arrived, '%H'), DATE_FORMAT(arrived, '%i'), priv FROM $halft WHERE sender = $nr and halfref NOT IN (SELECT reffen FROM $readt WHERE sender = $nr) order by id desc limit 1 Into a faster join of some sort? I'd aprichiate if you told me what each part of the rewrite actually does, because I been reading about joins for a day now and still don't get them at all!
View Replies !
Innodb Fast, Then Very Slow
I have a massive table that I converted to innodb. It made a huge speed difference in my site because tables locks basically stopped happening. However, after about 10 minutes mysql slows to a crawl. All queries seem to take forever to run, like 20-30 seconds each. If I reset mysql its all fast again for another 10-15 mins. This only happens when I have that one table set to innodb. When everything is MyISAM the database speeds are consistent, I just get a lot of table locks (which I'm trying to reduce).
View Replies !
Slow Insert On My PC But Fast On My Laptop?
I'm just wondering what can be the cause of slow inserts on my PC but super fast on my Laptop? I'm using MySql x64 on both PC. Operating System should be also identical as I mirrored it between these two PCs but PC would have more programs running.. Settings on my MySql should be identical unless there's more than just Server Instance Config Wizard. My PC is definately faster in terms of CPU and Hard drive compared to my Laptop. But insertion on laptop was like 10x faster.
View Replies !
Fast In INTERNET, Slow In Wifi
- Internet connected to mysql: all ok and fast - Wifi or lan connected to mysql: all fast, except UPDATE, INSERT, DELETE, that works in about 5-10 seconds. How is possible that UPDATE, INSERT or DELETE registres is slow in network connection?
View Replies !
Fast SELECTs Versus Slow INSERTs With Indexes
I run a MySQL database using phpMyAdmin interfaced with an application written in C++, and most of my nine database tables have 500,000 - 2,000,000 records in them. These tables also have about five-to-six indexes each. This makes working with the C++ application extremely fast, which is nice. Any SELECT statements can be run in less than a second, and I'm very happy. The problem comes when I have to insert new records into the tables. Every morning, I am received a pipe-separated .txt file of the previous day's new records (about 500 - 1,000) that I have to insert into the tables using my C++ application. Well, this takes an excruciatingly long time (almost an hour, actually) and I need to find some way to make it run faster. I am thinking of several options here:
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 !
Join Too Slow
I'm creating a query that use Join clause. I tested it in MySQL 4.0.24 and with MS-ACCESS. . . . in MySQL is slow!!! any suggestion ?
View Replies !
Slow Join On Large Tables
I have two tables: D (500,000 recs), and DL (2,500,000 recs) D has a PK and an index on HLQ. DL has a PK and an index on ID. The following SQL: SELECT HLQ as "HLQ", count(*) FROM D, DL WHERE D.DLID=DL.ID GROUP BY HLQ produces the following explain: tabletypepossible_keyskeykey_lenrefrowsExtra DALL500000Using where; Using temporary; Using filesort DLeq_refIDID4D.DLID1Using index The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer settings. Adding the following buffer settings only slightly decrerased the time (~3:00). key_buffer=512M table_cache=256 sort_buffer=16M read_buffer_size=16M It appeasrs that the 'Using filesort' on table D is due to the Group By clause and is the problem. I have an index on HLQ. Is there any way to get MySQL to use it?
View Replies !
Slow Query W/ Join & Ordering
I am trying to figure out why I have a hugely slow query (~2 seconds in my testing environment). Details are below: It involves two tables, products and vendors. Products is a huge table, so I will only include the (ostensibly!) relevant fields in its description: CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `vendor_id` smallint(6) NOT NULL default Ɔ', `product_code` varchar(255) NOT NULL default '', `internal_name` varchar(255) NOT NULL default '', `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `product_code` (`product_code`), KEY `vendor_id` (`vendor_id`) ) ENGINE=MyISAM; Vendors are much more straightforward: CREATE TABLE `vendors` ( `id` smallint(6) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM; The following query executes in no MORE than 0.01 seconds: SELECT DISTINCT p.id , p.product_code , unix_timestamp(p.lastmodified) as lastmodified , p.internal_name FROM products as p ORDER BY p.product_code ASC LIMIT 0, 30; And has the following attributes: +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ | 1 | SIMPLE | p | index | NULL | product_code | 257 | NULL | 25124 | Using temporary | +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ When I join with the vendors table, so that I can fetch the vendor's name for each product, I use the following query, which takes about 1.88 seconds: SELECT DISTINCT p.id , p.product_code , unix_timestamp(p.lastmodified) as lastmodified , p.internal_name , v.name as vendor_name FROM products as p LEFT JOIN vendors as v ON v.id=p.vendor_id ORDER BY p.product_code ASC LIMIT 0, 30; It has the following characteristics: +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary; Using filesort | | 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ Note the addition of the filesort. I'm unhappy enough about the temporary, which I don't really understand, but the filesort is, I'm fairly sure, killing me. Closer investigation (or maybe just common sense if you aren't a MySQL newbie like me) shows that the ORDER BY clause is responsible, for when I join without the ORDER BY, my query time goes back down to 0.01 seconds or so: mysql> explain SELECT DISTINCT p.id -> , p.product_code -> , unix_timestamp(p.lastmodified) as lastmodified -> , p.internal_name -> , v.name as vendor_name -> FROM products as p -> LEFT JOIN vendors as v ON v.id=p.vendor_id -> LIMIT 0,30; +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary | | 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ Any clues on how I can get the execution time to go down when I am sorting? I'm also curious why MySQL is using a temporary table,
View Replies !
Slow Execution For A Left Outer Join Query
Whats likely to be the cause of slow execution for a left outer join query? The original query joins three tables but even if I narrow it down to one it still takes a long time to execute. $query = "select distinct materials.* from materials"; $query .= " left outer join materials_products on materials.material_id = materials_products.material_id"; There's 914 rows in the materials table and 1348 row in the materials_products table Is it likely to take a long time for this amount of data or is there likely to be a problem in the table(s) set up or query?
View Replies !
How Fast Is A Lookup?
I have 100K records in a mysql db and wondered how long - approximately - it takes to look up a matching ip address. My code looks something like this: PHP $ipaddress=$_SERVER['REMOTE_ADDR'];$offer=$_GET['offer'];$affiliate=$_GET['affiliate'];$db1=mysql_connect("mysite.com",'user','pw');mysql_select_db("mydb",$db1);$q=mysql_query("SELECT * FROM `offers` WHERE `offer`='$offer' AND `affiliate`='$affiliate'");if (mysql_num_rows($q)==1) $r=mysql_fetch_assoc($q) AND mysql_query("UPDATE visitor_log SET `sale` = Ƈ', `oid` = '{$r['offer']}', `comm`='{$r['comm']}' WHERE ipaddress='$ipaddress'");mysql_close($db1); Does this sort of thing take under a second? Ten seconds? I've no idea.
View Replies !
What Is The Best Way To Get Fast Query?
I've uploaded big database with millions of rows (different places with geographical coordinates) to my server (MySQL). I wonder how can I get faster queries? I have cheap server. It's Celeron 2.8 with 1 Gb of RAM. For example, this query takes 10 seconds: Quote: SELECT * FROM table WHERE latitude > ཬ.832633619561896' AND latitude < ཬ.90083790234088' AND longitude > ƈ.1649932861328125' AND longitude < ƈ.5014495849609375' ORDER BY 'population' DESC LIMIT 0, 20 I need to get fast queries, like 0.1-0.5 seconds instead of 10 seconds. What is the best way to get fast queries: 1. Better, more expensive server. Will this solve the problem? Or it's not worth of the money? 2. Optimizing queries. Still I don't think this will give me 0.1 seconds query instead of 10 seconds. Am I wrong? 3. Splitting database to 500-5000 tables by coordinates (splitting map virtually to small quares). Is this a good idea?
View Replies !
Fast Keyword Search
I´m asked to "speed up" a keyword search based on MySQL. The material i´m given to work with is a quite large MySQL table with 1.5 mio rows, defined something like: CREATE TABLE datarecords ( id BIGINT(20) NOT NULL auto_increment, [3-4 other INT type columns], keywords TEXT NOT NULL, updated TIMESTAMP(14) NOT NULL, PRIMARY KEY (id)) TYPE=MyISAM; The search requires the id-fields of the datarecords table to be returned, based on keywords within the keywords-field, like so: SELECT id FROM datarecords WHERE ( (keywords LIKE '%someword%' AND keywords LIKE '%otherword%') AND (keywords NOT LIKE '%notthisword%' AND keywords NOT LIKE '%thisneither%') ) The table has grown unexpectedly large and will keep growing, so a different method for keyword searching appears to be required, since search-speed has decreased significantly (now at about 12 secs, depending an the number of searched keywords) Using FULLTEXT indexing has brought no performance increase... Question: Does anybody have any suggestion on how to tackle this type of problem, to get search-speed down to about 2-3 secs? One way i´m looking at this is to create a seperate keyword-table something like this: CREATE TABLE testkwdlist ( keyword varchar(32) NOT NULL default '', ids text NOT NULL, PRIMARY KEY (keyword) ) TYPE=MyISAM; and filling this table up with the keywords and listing the IDs in the ids-field, doing seperate queries for the positive and negative searches and compiling the actual resultset in code... Does this sound it has potential for a performance boost or is it competely insane?
View Replies !
Fast Data Retieval
I want your suggestion about how I can retrieve data very fast from a mysql table.The table size is 1.2 GB. Whenever I am trying to run a query which includes group by it is taking too much time.
View Replies !
Fast Mysql Search
Is there anything for making the search fast in mysql? like indexing in MS Sql Server. I want to make the search function fast.
View Replies !
Looking For Fast MySQL Components For Delphi 6/7
I'm on a trek looking for fast reliable (bug free) MySQL 4.1 components for Delphi 6/7 for accessing large MyISAM and Innodb tables. Can anyone make any recommendations? I've tried MySQLDAC from MicroOlap and unfortunately support is terrible. (No reply to any of my e-mails in the past 2 weeks). I'm also looking at Zeos (where are the support newsgroups?), SciBit, and CoreLab. Does anyone have any recommendations? Horror stories? Ones to avoid?
View Replies !
How To Make Database Access Fast
In my linux server (LOCAL), *accessing data through the PHP scripts from MySQL (3.23.41)tables, it is taking more time. * Earlier it was very fast. * Now a days some reports it is taking more than five minuits. Earlier it use to come with in a minuit. *What may the problem and what I have to do spead up the process.
View Replies !
Fast MySQL Backup With Rsync
wouldn't it be possible to do a fast backup of the MySQL data in this way: 1.) rsync the MySQL data (with running mysqld) 2.) Stop mysqld 3.) rsync the MySQL data again Please answer. I have found a similiar post in the year 2001, but no replies. I think, if this is possible, it would be very useful for a whole bunch of tasks. (For example changing hard drives in servers: You could rsync everything, including the mysql data. The only downtime would be from the point of shutting down all daemons to get a consistent snapshot to the point of rebooting the server.)
View Replies !
Fast Input Of Binary Data
We are considering MySQL as an alternative to the Sybase databases that we have now. One thing we need to be able to do is insert tens of millions of rows of data quickly. Each row may have 10 to 20 columns. The source of these data are binary data files. Is there a way to do this quickly? Currently we are using Sybase's bulk copy utility bcp. Is there something comparable in MySQL? We also need to be able to extract large amounts of data from the database and put them into binary data files. Again we are currently doing this with Sybase's bcp utility. Can we do this in MySQL?
View Replies !
Trying To Write A Two-part Fast Query
The situation is this: there are two tables, one called "objects" (fields: object_id[int] and object_name) and properties (fields: object_id[int] and property_name and score[int]). Objects have one or more properties. In short, what these 2 queries need to ask are "what objects could an unknown object be, given what properties I know?" and "what property should I measure next to most narrow down the possible objects?" The objective of the first query is to return an object who has all of the given properties scored above or below the given value. For example, I might look for the object_id of an object who has the property "green" scored above 3, the property "hot" scored below 2, and the property "bright" scored above 0. It might have other properties, too, but it must have these 3. And then, for the second part, I need a query that will select properties which are NOT in this set of given properties but are relevant to the returned set of objects. In this part, I'm not concerned with the property's score - just that it is relevant to the objects. In other words, this query might tell me that the property "hard" is relevant to 3/4 objects (even though one of them had a very low score for the hard property, another had a very high one, etc). Basically I want to know which new property, once I can determine the score of this new property, will help narrow down the object-set the most, so that I can collect data for the most relevant property to narrow down the object set.
View Replies !
Searching Rows Containig A Substring Fast
I am creating a server indexing files in my local area network, in order to provide a searching feature. So i want to make it possible to searchsuch rows where the 'name' (VARCHAR) column contains a substring submitted by user. an i use a full-text index to make it faster? from my first sight i noticed that this index can be used to search for words in a string. So the example below would not go: searching 'cool' in 'uncoolness' Am i right? Are there any other solutions? Maby some other databases provide this use case?
View Replies !
Simple Fast Way To Grab The Highest ID From A MySQL Table?
I have a MySQL table on my web server (external web host) that has an auto-increment integer as it's primary key field. Is there a quick PHP function that will get me the highest ID in the table, that is, the ID that belongs to the last record added. Note, I need it to work across and between connection session, not from within the same connection session.
View Replies !
Views In 5.01
We have just loaded 5.01 so that we can take advantage of views. One question I have before I start experimenting is this: Can you "view" across databases? And if the answer is yes, can you "view" across servers? Given two tables "company" and "people", each living in a different database (and potentially on different servers) can you give an example (the SQL) of how to link them in a view? assume the following: company ------- c_id c_name people ------ p_id p_name c_id
View Replies !
Views
I just wanted to post and let people know that I discovered views today and I love them! I have a really complicated query that has 6 tables joined to return only 11 columns. At present, with a small number of values to be displayed, the result was generated in 0.046 seconds. Using a view it took only 0.016 seconds. I didn't try it as a view for performance reasons I must admit, but rather because I wanted to try it out and the query I was writing was just over complicated to be using in PHP. The tables that are queried have over 250,000 records in total (a couple of 110,000 each and a few very small ones) so I thought that was a reasonable performance increase. I am curious however; as this is my first foray in to views, is there anything I should watch out for? Anything that may appear all shiny and great now but will bite me in the *** later on?
View Replies !
Delete Views
how to drop a view in Ms Access using ColdFusion.I don't think the problem is ColdFusion, i think the problem is the query but it might be anything.
View Replies !
Mysql Views
I have only ever developed database application using Mysql and so have limited experience of views and how they work. I'm sorry if this seems basic. Suppose I create a view from a table, say A and tranform it using a select query to create my view, B. If I want all the data from B, ie. SELECT * FROM B will the DBMS need to go back to the original data and execute the query that generated B and then return my results - or - does it store the data for view B in a table like format and then just quickly return that. Essentially is there a performance advantage when using a view where the transformation from table A to view B is complex.
View Replies !
Views And MyODBC
well i just installed MySQL "5.0.1-alpha-nt-max" to play around with the new Features on my local PC.reating and accessing Views seems to work, but as it seems MyODBC 3.51.09 cant handle Views. Are there any plans to support Views also via MyODBC ? If so, is that Feature currently in the Bitkeeper-Repository of "MySQL Connector/ODBC 3.52" ?
View Replies !
Views In MySQL
I want to migrate my existing SQL Server database to MySQL. It had many views in it. When i tried to make views in MySQL 5.0, it gives error. I was suggested by this forum that views can be created in MySQL version 5.0.1, but i couldn't download it from anywhere. From where can i get it? I tried to download from mysql.com file named "mysql-5.0.1-alpha-snapshot-win.zip". This downloaded file gives me 5.0.0-alpha-nt server and 5.0.1-alpha client. This version is also not allowing me to create a view.
View Replies !
As Views Really Supported
With MySQL 5 beta and InnoDB tables, are views really supported? I can see the syntax in the manual but I get syntax errors on even a trivial attempt to define a view as in create view foo as select * from project produces You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'view foo as select * from project' at line 1
View Replies !
Views, Procedures
I have version 4.0.12 of MySql installed. I can not create views and stored procedures. Is this normal or is there something wrong in the installation?
View Replies !
Performance Of Views
I have a large table in a database that takes a long time to do any queries on. All user queries on this table are done as part of a background process at the moment. The table holds transaction information from organisation and account id's; each organisation may have more than one account id. This table contains the raw transaction data, so there are many 1000's of records for each id. The table is keyed on customer number, which is different from organisation id and one customer number may have more than one organisation id. Code:
View Replies !
Views In MySQL 4.1
Is it possible to create views in MySQL 4.1 or am I just wasting my time trying to figure out the syntax? I've tried searching this site but can't find anything about it for 4.1.
View Replies !
Views In Phpmyadmin
I created a veiw in phpmyadmin How can I modify it? when i click on structure instead of seeing the view I see it as if it was a table with fields.
View Replies !
Views In MySql/PHP
This thread is answering a question from [url=http://www.codingforums.com/showthread.php?s=&threadid=12528[/url]this thread] -- I didn't want to distract from the original question. To recap:A view is a virtual table. It is a stored query, usually based on two or more tables. It can be used as a table by other queries (by default, all queries created in Access are, in fact, views). The main advantages are efficiency and security. You can merge complex tables that store complete information to produce more simple tables that contain live information without any data duplication. You can also give the view query different access, meaning that you can let people see only some columns of a table (eg a view restrict who can see creditcard numbers, without restricting who can see other customer details). They are not supported in mySql. Followed by: what is the alternative to 'view' in mySQL/PHP then ... The simple answer is there is no alternative. You can easily enough create a database layer that stores queries in php. But, if the user has access to run these queries on the underlying tables, then they also have access to bypass them. You can also, with a little effort, create a method for building queries from SQL (this is a fairly standard way of operating a large system). However, you do not get the dabase level security benefits that you get from using views. Nor do you get the performance benefits that you get from views (I don't know all the details, but a view does run faster than a regular sql query). You can use a degree of database level security -- by limiting access to tables using GRANT statements. With views, you can use database security to completely limit access to rows, columns, and calculated fields without any data duplication. Of course, it is possible to implement application level securty in PHP, but the dual (or more often treble) layer of security is quite useful when you're dealing with complex two and three tier systems.
View Replies !
Views Overhead
Not far long ago, I was posting a question about performance of database with a lot of tables. Now I have a good idea towards moving to a shared tables approach, so there will be only one set of tables for all users, but each of the users will have an own set of views like: create view username_posts as select id, title from posts where user_id = user_id It would make database more efficient, and would need only a small set of changes to the application. What do you think about this approach? Is there a huge overhead of using views like the one mentioned above, in comparison with adding the user_id = ... clause to the application? How well does MySQL (InnoDB) behave in case database has a lot of views?
View Replies !
Views In MySQL 5.x
Was wondering if anyone here has had any experience with views in mysql 5? I know what they are and how to use them, was just wondering if anyone here has before and if they feel it is better, in mysql, to just create the new table or use the view instead? Or perhaps a complex join?
View Replies !
Question About VIEWS
I have never used MySQL views before, however, in my current situation I will sooner or later need to use them. I have one table which consists of sent messages and another which consists of recieved messages. When someone sends a message to another person, the message will be stored in their own sent messages. However, when they recieve a reply from the end user, the message will appear as a conversation as apart of their own inbox. So basically, the query which requests the inbox records will fetch both the sent and recieved message records. So I need to use a view to fetch both messages, how do I do this? If you guys are still wondering what I am talking about, then check out FaceBook's private messaging system.
View Replies !
Views Tutorial?
This tutorial explains what VIEWS are and how are they created. My Question is that why do we need VIEWS for? Why cannot we use the normal SELECT command and fetch records directly from the main table instead of creating a VIEW first and then display records?
View Replies !
Views Support In 5.0.0-alpha
from 1.8.5.6 Views: "Views are currently being implemented, and will appear in the 5.0 or 5.1 version of MySQL Server." i see the stored functions/procedures being added to the 5.0.0-alpha release but i do not see any support for views still. are they going to appear later in 5.0 or we would have to wait till 5.1 ?
View Replies !
Performance Improvement With Views?
If we have an option of using view to static assignment of options which one should we go for? Like if we have a combo box which is gonna have lets say 5 options then should we assign these options statically or should we store them in a view and then retrieve them dynamically each time someone clicks on the drop box.
View Replies !
Keeping Number Of Views To Max. How?
I've got a "smallint(3)" to track the number of views of an article, it is updated width "SET views=(views+1)". What I'm just wondering is if when the article reaches 999 views, the next view will set it to 100(0) or will just remain with 999. I think it will go on counting so, how can I just leave it with 999 views?
View Replies !
Stored Procedure Vs Views
I am a little confused on the benefits of views when compared to stored procedures. When should you use a view and when should you use a stored procedure? Do either technology have a performance edge over the other?
View Replies !
|
TRACKER
