Query Won't Use Index On "OR" Condition
I have a table full of teams:
Code:
team_id, name
And a table full of fixtures:
Code:
fixture_id, home_id, away_id, date
The fixtures table is now quite large and this week I added indexes on the "home_id" and "away_id" columns (in addition to the existing primary key on fixture_id).
I run a query which joins on the following:
Code:
JOIN fixtures ON (fixtures.home_id = teams.team_id OR fixtures.away_id = teams.team_id)
But it does not use my indexes for the join.... it therefore scans around 40,000 rows.
If I remove the "OR fixtures.away_id = teams.team_id" and just join the home ones, the number of rows scanned goes down to 11.
Is there a reason that the "OR" operator stops the index being used?
Do I need to re-write my sql so that it searches 11 rows twice (one home, one away) instead of doing the "OR" clause?
View Complete Forum Thread with Replies
Related Forum Messages:
Index With Condition
Will a table index run faster depending on the order of the columns in it? My table has 500k rows and 600 columns. The search and sort queries run on subgroups: order by ssn where ssn like %555% and group = 2; But these queries are _very slow. Do I create an index on group, ssn and lname, group? Or add more memory allocation to MYSQL?
View Replies !
Query Wont Work
PHP Code: if(mysql_query("UPDATE users SET referer_score=referer_score+1 WHERE userID='$frommysql_referer'")) { die(mysql_affected_rows()); im trying to figure out why refer_score isnt being iincremented as im expecting it to. i know that the value of $frommysql_referer is the right value that im expecting it to be. just it wont increment referer_score. and mysql_affected_rows() wont print anythnig to the screen
View Replies !
More Than 1 Condition In Same Query
I want to select the table and then order it by the start-date which is one of the column and check if the the start date is gone as in start-date > current_time(the time when the query is performed) and then report the relevant top 3 results!
View Replies !
Query Condition Didn't Get Logged
In slow query log query condition didn't logged. Only query with table name omitting condition get logged. What is the reason. How to log the entire query. The following query takes 2s to execute and is logged in slow query log. The original query is : Select count(*) from markingqc where persondate between '2007-07-09 00:00:00.000' and '2007-07-09 23:59:59.999' and PersonName='admin' Logged query is: SELECT * FROM markingqc; How to get the condition in the log?
View Replies !
Query Based On 'does Not Exist' Condition
Let's say I have a table called 'forumtopics' with a field representing the topic author's username. I then have another table called 'ignorelist' which has two fields; one is an account number and the other a reference to the aforementioned author. There can be many ignored authors for any one account. For any given account number, I'd like all the rows from the forumtopics table where there is no match for (account, author) held in the ignorelist table. Obviously it's easy to check for existence but can the opposite be done? I run MySQL 4.0.17.
View Replies !
Query To Ignore Results If Condition True
I am having trouble defining a query that ignores rows if a query if true IE. Column 1 = 1,2,3,4,5 Column 2 = a,b,a,a,c For example: I search for results from column 1 and if '3=a', ignore all 'a' rows thus returning the result b=>2, c=>5.
View Replies !
Index, Then Query On Only Part Of The Index
Lets say we have a fulltext index on... column 1, column 2, and column 3 If we do a select statement matching only on column 2 and column 3 will the index still be used effectively? Do we then need a second index only on column 2, and column 3? Would it be smart to simply create indexes on all of the following? column 1 column 2 column 3 column 1, 2, 3 column 2, 3 ?
View Replies !
Incorporate An Additional WHERE Condition (was "Help With My Query Please?")
I’m trying to incorporate an additional statement in the below query, which works: $sql = "SELECT b.bookID, b.roomID, b.arrive, b.depart, b.status, b.property_id FROM room_booking_two b WHERE ((b.arrive BETWEEN '$d1' AND '$d2') OR (b.depart BETWEEN '$d1' AND '$d2')) ORDER BY b.roomID, b.arrive"; Now I want to add the line: WHERE b.property_id=".$_GET['property_id']) So I thought it would work like this: $sql = "SELECT b.bookID, b.roomID, b.arrive, b.depart, b.status, b.property_id FROM room_booking_two b WHERE b.property_id=".$_GET['property_id']) AND ((b.arrive BETWEEN '$d1' AND '$d2') OR (b.depart BETWEEN '$d1' AND '$d2')) ORDER BY b.roomID, b.arrive"; But that does not seem to work. Can anyone please advise?
View Replies !
Win2003 Wont Allow
i have just downloaded the lastest mysql and the server wont allow the new version of mysql to install the service/ EDIT: i had an older version of mysql 4.0.3 or there abouts and uninstalled it so it was running nicely
View Replies !
Index This Query
Can someone shed some light on something I am not too familiar with... I have a table with 8 columns that I need to query as such SELECT col_1 FROM table1 WHERE col1=234 AND col2="sd" AND col3=1 how would I index this properly?
View Replies !
Query About INDEX
I am using a Servlet to access mysql db. I have a table called indexes(index_id,b_isbn,index_token). index_token contains token words which I will be searching. I created an INDEX(index_token, b_isbn) to speed up the search as there are thousands of entries in index_token. it all works fine, however I wish to speed up the search even more so I have created an algorithm that I will implement in the servlet. Im concerned that this might slow it down instead of speed it up.
View Replies !
MySQL Wont Start
I've just installed MySQL on my computer yesterday and it was running good at the time. Today I boot my computer and It won't load the MySQL service. I've added the MySQL path to the environment variables but that didn't solve the problem. I've attached the .err file below. My system is: PHP 5, MySQL 4.1 Apache 2 I'm conencting to my network with a wireless card if that has anything to do with it. Probaly not since its a local service?
View Replies !
MySQL Wont Start Up
Last night I was attempting to activate MySQL for one of my domains using the Ensim control panel. It gave me an error while activating, and shortly after i noticed all MySQL activity on my server was no longer working. Using ensim to activate the MySQL service I get the following error: Quote: Result : start for service mysql failed. Action start for MySQL failed with error. Failed to start service: mysql. The following error occured: Logged in using ssh i get similar messages. When I try to login to my MySQL powered blog system I get the following error: Quote: Got an error: Bad ObjectDriver config: Connection error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
View Replies !
Service Wont Start
I've JUST installed MySql onto my machine and I press the "Execute" button and it 1) prepares configuration, 2) writes the configuration file but then the whole thing flops and it can't start the service. it displays the following message: 'The service could not be started. Error: 0'
View Replies !
It Wont Let Me Reset My Password For Mac Osx
if mysql is not letting me reset my password what can i do?. I'm using Mac Osx and my password only works sometimes. I dont understand it. Actually it only worked once. I want to reset it but it wont let me kill the mysql. I was running mysql or attempting to try to start mysql 4.0 and i just downloaded the 4.3.4 or something of mysql and now its giving me this when i attempt the sudo /usr/local/mysql/bin/mysqladmin -u root -p shutdown: [Denise-Womacs-Computer:~] denisewo% sudo /usr/local/mysql/bin/mysqladmin -u root -p shutdown Enter password: /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!
View Replies !
Index Slows Down A Query?!
I have a simple Table in a MySQL-Database with the following structure: ID: INTEGER Name: VARCHAR(255) ID ist the Primary Index. It is a big Table with over 1,300,000 rows. So the following SQL-Query takes about 1,5 seconds: SELECT ID FROM Names WHERE (UCase(Name) LIKE "SOME NAME"); To speed things up I added the following Index for the field "Name": Index Kind: INDEX Index Type: DEFAULT Index Columns: Name But instead of performing faster MySQL now needs about 50 seconds! What is the reason for that? Has it something to do with the UCase-Function? Should I use different Index Kinds or Types?
View Replies !
Mysql Server Wont Work
i am having mysql install with Plesk 7.5 for windows and yasterday the mysql server wont work everytime i click to start it stop in same sec how can i solve the problem and did that will make m elost my db ?
View Replies !
Index Causing Much Slower Query
I am running a query on a 13 million row partitioned table and have found that removing an index from the table or removing the partitioning improves the performance dramatically. I'm confused why this is happening and would appreciate any help. My table is split into 12 partitions by 'intcol'. My query resembles the following: Code: SELECT * FROM table WHERE intcol=1 AND datetimecol IS NOT NULL; There are 15 rows in the table with intcol=1, 5 of which are not null. I have an index for intcol,datetimecol. I have confirmed, using EXPLAIN, that MySQL is using my intcol,datetimecol index to resolve the query. EXPLAIN also reports that the query only touches partition #1. Partition #1 is only about 85Mb with a 50Mb index. When I run the query on the table it takes some 6+ seconds to return the 5 rows. If I change the query to be simply "SELECT * FROM table WHERE intcol=1", the 15 result rows come immediately. If I then remove the intcol,datetimecol index and replace it with just an intcol index, the original query returns immediately. It appears that placing the datetimecol column in the index is causing my query to take much longer than it should. And even more confusing for me - if I rebuild the table without partitioning, the query is very efficient, returning immediately! I am clearly misunderstanding how partitioning is affecting how my index is used. I want the intcol,datetimecol index because I anticipate this table having large numbers of rows matching intcol in the future, and expect partition pruning and the index to narrow my results based on first intcol and then datetimecol. For this reason, it is not satisfactory to simply have an intcol index.
View Replies !
No Index Defined Error When Query
Will anyone tell me what's wrong with the following select statement: select * from email where (MdnName like '%".$lastName."%') This query does not return anything although there are records in the database that matches the criteria. I'm querying the contents from a php page with this code: $query = "select * from email where (MdnName like '%".$lastName."%')"; $result = mysql_query($query); $num_results = mysql_num_rows($result); When I tried runing the same select statement in PHPMyAdmin, I received this error: No Index Define!
View Replies !
Help With A Small Query Optimization Using An Index. THANKS!
I need this query optimized using indexes. So I was wondering how I could optimize it to work with an index for speed. The query is: Quote: select ided from products where dates <= 2006-11-11 or cost=0.00 or url not like 'http://%' or imgurl not like 'http%' I tried making an index on (dates,cost,url,imgurl) but it doesn't seem to use the index when I do the explain part. I find that even when I miss of the like parts of the query it still is not using the index. It seems to not use the index because of the "or" that is used in the query instead of the "and". Could this be the reason and how could I over come this so that it will use the index.
View Replies !
View That Wont Return Existing Rows
i have a problem performing simple query over view. To make it short, so anyone can recognize whether it is something im missing, this is the problem: * i have created few levels of views (some of them are based on actual tables and others are based on other created views or some mixed) * lets say the name of the view i have problems with is "aView" * when i perform "select * from aView" i get to see the view is correctly populated * when i add condition to the query above in some cases it returns ok results but in others it returns 0 rows (which is wrong) select * from aView where a=1, works fine select * from aView where a=1 or b=2, works fine select * from aVew where a=1 and b=3, returns 0 rows --- which is wrong(of course not by itself but in my case there are rows with ok values) same goes with using only the condition on b column a.) why wouldnt it fetch rows that are cleary in the view?? b.) is there any limitation on how high structure can we build using views
View Replies !
Query Performance Question (using Index Slows Down?)
I have the following query processed in my app and was wondering if there is any way to make it faster. SELECT * from city where last_sync > '1900-01-01 00:00:00' AND last_sync < CURRENT_TIMESTAMP limit [FILLED_IN_BY_APP],[FILLED_IN_BY_APP]; limit increments in steps of 500: limit 0, 500 limit 500,500 limit 1000,500 etc. explain returns: +----+-------------+-------+-------+---------------+------------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------------+---------+------+--------+-------------+ | 1 | SIMPLE | city | range | sync_dates | sync_dates | 8 | NULL | 138456 | Using where | +----+-------------+-------+-------+---------------+------------+---------+------+--------+-------------+ I've observed that the higher the limit goes (starting from limit 100000, 500) the querytime substantially increases. Note that my table has roughly 300K records. Because they have been inserted all at once, the result at the first time running this query will be everything (300k). However I've checked within my app how long it takes to execute the query and when -NOT- using an index the query times go to maximally 500ms while when I am using an index the max lies at 1 second. My questions: 1) Why is limit(0,500) (30ms) executed faster than "limit (200000, 500)" (600ms) 2) Why is not using an index actually faster than using an index in this case?
View Replies !
MySQL (PHPTriad Download) Window Wont Display.
Have downloaded the PHP Triad software from SourceForge.net as have had problems getting Apache working after installing from the Apache.org site. Apache works using this version but when selecting the MySQL - D from the start menu, the pop-up window doesn't display, but it flashes on then off. There is no uninstall option, and this isn't displayed in the windows remove programs setting either. How can I remove and reinstall, or get this working?
View Replies !
Mysql Installed And Running But Wont Start (XAMPP)
hey iv installed xampp as i want to begin learning php and need the php and mysql servers, it all seems to be running fine in the control panel of XAMPP it shows apache and mysql as running. The problem is when i go to start mysql to create an actual database,by going: C:xamppmysqlinmysql the DOS window pops up and disapears and the motherboard beeps. Is this not the correct way of starting mysql for editing? im using wondows xp serivce pack2 and XAMMP 1.5.5.
View Replies !
Enctype=multipart/form-data Wont Pass File Name
when i add enctype="multipart/form-data" to the form i cant get the file name into the databass... but if i leave that part out of the form then the files dont upload into my upload folder. so its either one or the other. is there a way to upload the file to my uploads folder and pass the data (filename) to the databass at the same time? . . . <form action="insert.php" method="post" enctype="multipart/form-data"> .....
View Replies !
"/" Signs Wont Show After INSERT
I am doing an insert of a file path example c:inetpubwwwroot est.gif but after the insert to the database it seems like mysql has deleted the so now it only looks like this c:inetpubwwwroottest.gif Why is it like this.. i have tested to change to varchar, text, mediumtext and tested the 4 and 5 versions om mysql server, i also have tested the 1.0.4 connector and 1.0.5 connector.
View Replies !
Where Condition
I want to query my database like so... $query = "SELECT * FROM wines WHERE restid =1 AND site_id_sask =1 ORDER BY name"; The only hitch is that in the restid field I would like to hold more than one entry. Meaning for some entries I would like to have the values 1, 2, 5, 9 for example. In another entry I may want to have 1, 5, 8. Can I search this the restid field?
View Replies !
Using IF Condition
I want to do a query that first checks whether 2 items are similar, and if they are, uses conditon A, and if it doesn't, uses condition B. For example: SELECT * FROM table IF(A = B, WHERE A > 10, WHERE B > 10) So if A = B, the query will be: SELECT * FROM table WHERE A > 10 and if A != B, the query will be: SELECT * FROM table WHERE B > 10
View Replies !
Like Condition
How would I select everything that starts with a and b from the name column in the persons table? I know how to get it for just people starting with a- SELECT * FROM persons WHERE name LIKE 'a%' But how do I do a and b?
View Replies !
If Condition Help
SET @RecordCount = 0, @PageCount = 0, @PageNumber = 0, @PageSize = 0; SET @PageSize = 10; IF(1 > 1.0) THEN SET @PageCount := (@RecordCount / @PageSize) + 1; END IF;
View Replies !
If...else Condition
I have a table - mytable and the columns in the table are as follows: CityName (varchar) updated_by (varchar) added_by (varchar) The first column is a city column, someone would add/update the city names and accordingly their name would be updated/added to the updated_by and added_by columns. Now, the condition of my query would be: - if the updated_by column is NOT empty and the added_by column is empty, then the value of the updated_by would show . - if the added_by column is NOT empty and the updated_by column is empty, then the value of the added_by column would show. - if both added_by and updated_by columns are NOT empty then the value of the updated_by would show.
View Replies !
More Than One Condition In A HAVING Clause
I have tried to run a query where the reuslts are restricted by a HAVING clause with 2 conditions, but the second condition seems to be ignored. for example SELECT userid, AVG(position) FROM `table1` WHERE (status='finished') GROUP BY userid HAVING (COUNT(*) >= 4 AND AVG(position) < 5.5) returns exactly the same as SELECT userid, AVG(position) FROM `table1` WHERE (status='finished') GROUP BY userid HAVING (COUNT(*) >= 4) even though there are values for AVG(position) returned that are > 6. Is it the case that I can't have more than one condition in a HAVING clause? have I got the syntax wrong? Is the behavior different in a later version? I am using "mysql Ver 11.15 Distrib 3.23.41, for redhat-linux-gnu (i386)"
View Replies !
Condition In CONCAT
i have three fields of name in database named as fnam,mname,lname. fname and lname is mandatory field and mname is optional. i want to make a search query on name Expand|Select|Wrap|Line Numbers
View Replies !
Insert If Condition
I am trying to optimise my application. One of the parts of it is inserting a value into a table if the number of rows in that table with the same `date` field as the row to be inserted is less than 24 So currently I do two statements, one to check the number of rows and another to insert. Is there any way to INSERT something if condition. I have looked as CASE but it does not seem to do what i want. So a mock up would be: Code: SELECT COUNT(*) FROM `table` WHERE `date`='2006-01-01' If the value is < 24 INSERT INTO `table` VALUES (...)
View Replies !
Join Condition
i have 2 tables users relations in the table relations i record relations.user_id and relations.connection those two fields refer both to the same id in the users table. Now to the problem: I want to do a query for one user, and find his friends, therefore i have to search both fields in the relations table, and then depending on where his id was found select the counterpart. (if his id was found in the relations.user_id i need the relations.connection and the other way round...) now i do not only want the id of the friend but i would like to pull his details from the users table.... to be honest i tried a few things but i need a hint whether this is possible or whether i need two queries... or maybe a subquery.
View Replies !
Condition Preferred
Imagine a table of company addresses. A single company can add many addresses and edit them. A company can mark one and only one particular address as Primary. I want to do a select query as follows: if the company has a marked Primary address then select it and no other address, else select all addresses (perhaps with some additional conditions) for that company The query would give just one result for some companies and a number of results for others.
View Replies !
|
TRACKER
