473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How to hint MySQL which indexes use?

EXPLAIN SELECT A_id, B_id FROM A, B WHERE B_id =
'\\\\path\subpath\morepath\001-0466.html' AND A_id = 4 AND B_code=A_code;

Things get complicated when more than one tables are involved.
Let's say I have indexes for A_id, A_code on table A, and an index for B_id
on table B. How can I hint MySQL that which indexes it should use? In the
example above, I expect one index on table A and one index on table B are
actually used. When I looked at it with the EXPLAIN command, I found out
that only the index for B_id is used.
Jul 20 '05 #1
5 7788
"jy2003" <jy****@sbcglobal.net> wrote in message news:zqxvc.78249
EXPLAIN SELECT A_id, B_id FROM A, B WHERE B_id =
'\\\\path\subpath\morepath\001-0466.html' AND A_id = 4 AND B_code=A_code;

Things get complicated when more than one tables are involved.
Let's say I have indexes for A_id, A_code on table A, and an index for B_id on table B. How can I hint MySQL that which indexes it should use? In the
example above, I expect one index on table A and one index on table B are
actually used. When I looked at it with the EXPLAIN command, I found out
that only the index for B_id is used.


Take a look at the concurrent thread "SQL index problem" where there are 2
solutions: using ORDER BY, and MySql use index extensions.
Jul 20 '05 #2
Take a look at the concurrent thread "SQL index problem" where there are 2
solutions: using ORDER BY, and MySql use index extensions.


How do you force indexes for more than one table? Let's say index_a for
table A, and index_b for table B. FORCE INDEX(index_a, index_b)?
Jul 20 '05 #3
"jy2003" <jy****@sbcglobal.net> wrote in message news:Fszvc.4308
How do you force indexes for more than one table? Let's say index_a for
table A, and index_b for table B. FORCE INDEX(index_a, index_b)?


In the FROM clause after each table put a force index to specify a list of
possible indexes for that table. So

FROM
A force index(indexes_of_A, ...)
inner join B on ... force index(...)
Jul 20 '05 #4
> In the FROM clause after each table put a force index to specify a list of
possible indexes for that table. So

FROM
A force index(indexes_of_A, ...)
inner join B on ... force index(...)


Can you force index for a DELETE or INSERT query?
For example, I have an index on (Path(255), Col_A, Col_B):
I tried the following, but it didnt' work:
DELETE FROM Document FROCE INDEX(Path(255), Col_A, Col_B) WHERE
Path='f:\\path\\subpath\\fiele-00157.htm' AND Col_A=1 AND Col_B=3;

Also, EXPLAIN only works for SELECT query, do we have anything similar for
other queries like INSERT/DELETE?
Jul 20 '05 #5
"jy2003" <jy****@sbcglobal.net> wrote in message
news:b3*******************@newssvr25.news.prodigy. com...
Can you force index for a DELETE or INSERT query?
For example, I have an index on (Path(255), Col_A, Col_B):
I tried the following, but it didnt' work:
DELETE FROM Document FROCE INDEX(Path(255), Col_A, Col_B) WHERE
Path='f:\\path\\subpath\\fiele-00157.htm' AND Col_A=1 AND Col_B=3;
In the FORCE INDEX you give the name of the index, not the column names.

I don't know if it works for delete statements, but I think it should.
Also, EXPLAIN only works for SELECT query, do we have anything similar for
other queries like INSERT/DELETE?


I don't know. When I use the graphical MySql Control Center, available on
mysql.com, the Explain tab is grayed out for delete statements. Seems like
it should work. After all, one can say delete from ... where exists (select
* from ...).
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: sandy | last post by:
Hi All, I am a newbie to MySQL and Python. At the first place, I would like to know what are the general performance issues (if any) of using MySQL with Python. By performance, I wanted to...
2
by: mos | last post by:
I want to put a MySQL 4.1 database on a Win2k laptop but the problem is it contains confidential client information. It has to be Window because applications accessing the database are written in...
6
by: David Brown | last post by:
With MySQL is it possible to have indexes or tables held in RAM? If so how do I ensure that updates are copied to disk? Regards, Dave.
4
by: jy2003 | last post by:
I have read a book, which suggests we should change OR to UNION for better performance, but currently I have too many OR clauses(I have a query with 100 ORs) and it does not sound good to have 100...
0
by: Phil Powell | last post by:
Retracing my problem leads me to believe I never successfully created fulltext indexes for MySQL 3.23.58 MyISAM tables. I went to the MySQL manual and was able - or so I thought - to create them,...
1
by: Gregory.Spencer | last post by:
Hi there, Using PHPMyAdmin and it is very usefully reporting problems with my MySQL DB. "PRIMARY and INDEX keys should not both be set for column `column_name`" and
110
by: alf | last post by:
Hi, is it possible that due to OS crash or mysql itself crash or some e.g. SCSI failure to lose all the data stored in the table (let's say million of 1KB rows). In other words what is the worst...
2
by: O.L. | last post by:
Hello, On my LAMP webserver, I noticed that sometimes the MySQL daemon uses 99.99% of the CPU, and does not answer SQL queries, while several minutes. Does someone know what could be the causes...
9
by: Derrick Shields | last post by:
I'm working with a database that has over 11 million rows. The .SQL drop file is about 2.5gigs. Doing a simple query: select * from people where last_name like '%smith%' A query like this can...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.