Hello All,
This is my first time using this list, but hopefully I got the right one
for the question I need to ask :).
I have a table which has about 4 million records. When I do a search
(as I will explain below) it takes about 1.35 secs to get me back what I
am looking for. Since I am doing multiple types of these searches, the
total time goes in minutes, therefore, I am trying to see if I can get
any help in reducing this time from your suggestions.
There are 25 columns (of mixed data types) in this table, but my search
usually involves just the first 3 columns which are all integer values.
Let's say the first 3 columns are A,B and C respectively. My search
really needs to get all the As and look for the B's and some range of
Cs. The rows that have these values is what should be returned.
I have indexed A, so when I do a search with just using A (and not with
B and a C range), the search is done in 0.03 secs. If I include B
and/or C to that search, the search is done in 1.35 secs. I also tried
indexing B and then also C, but the search still took 1.35 secs.
The search is normally done as below (shown as an example):
select * from tab1 where A = 90 AND B = 37 AND C BETWEEN 10 AND 20;
As I said before, if the search only involved A, the search is done in
0.03 secs. And if B is also and indexed and the search is done using B
only, the search is done in about 0.05 secs. However, both A and B take
over 1 sec.
Is there anyway I can make this faster? Basically, I think, mysql
should first get all the As (since it seems that results faster) and
then look for B and then the C range. Should I change the indexing?
Should I change the select query? How?
Thanks for any help in advance,
Stan 1 3432
B wrote:
Is there anyway I can make this faster? Basically, I think, mysql
should first get all the As (since it seems that results faster) and
then look for B and then the C range. Should I change the indexing?
Should I change the select query? How?
Generally, MySQL can use only one index per table in a given query
(there is an improvement in MySQL 5.0 such that multiple indexes can be
merged, but only for certain types of conditions). So it's not
surprising that adding the indexes on B and C didn't improve the
performance, if the index on A was already being used.
You could try creating a compound index on all three columns: A, B, C.
Not a unique key or primary key, if that's not appropriate for the
table, but just an index. I'm not certain this will improve it for your
case, but it's possible.
Make sure your cache is large enough to hold the entire index, so the
query doesn't have to go to disk during the search. An index on 4
million integers is at least 16MB, which is probably larger than the
default cache sizes. Change the cache sizes by setting options in your
my.cnf (or my.ini) file.
If you use MyISAM tables, this is controlled with the key_buffer_size
server parameter. See http://dev.mysql.com/doc/refman/5.0/...variables.html
If you use InnoDB tables, this is controlled with the
innodb_buffer_p ool_size. See http://dev.mysql.com/doc/refman/5.0/...arameters.html
There might be a way to combine your multiple queries into fewer
queries, or even one query. But I don't know the specifics of your
table structure or the queries you're running, since you've given a
simplified example.
Regards,
Bill K. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Ken Fine |
last post by:
I'm looking to find or create an ASP script that will take a string, examine
it for a search term, and if it finds the search term in the string, return
the highlighted search term along with the words that surround it. In other
words, I want the search term highlighted and shown in an excerpt of the
context in which it appears.
Any suggestions or pointers? This behavior is most often seen as part of a
search engine. In my case, I want...
|
by: Alastair |
last post by:
Hello guys,
I've been building a search facility for an intranet site I'm part of
developing and we've been building a search engine using Index Server.
It mostly works, however there have been a few niggling problems and
fixing it seems to be a case of patching errors as we find them so I'm
thinking that it might be worth starting the logic from scratch and
rebuilding this again.
Basically we have a simple search, which is simply a...
|
by: pembed2003 |
last post by:
Hi all,
I asked this question in the C group but no one seems to be interested
in answering it. :-( Basically, I wrote a search and replace function
so I can do:
char source = "abcd?1234?x";
char search = '?';
char* replace = "***";
char* result = search_and_replace(source,search,replace);
|
by: Jason Heyes |
last post by:
I have read item 26 of "Exceptional C++" that explains a way of minimising
compile-time dependencies and, therefore, a way to improve compile speeds.
The procedure involves replacing #include directives with forward
declarations from within header files.
However, a potentially large number of source files may have previously
relied on the removed #include directives being present. Therefore it is
likely that a large number of source...
|
by: joshc |
last post by:
If I have an array of data that I know to be sorted in increasing
order, and the array is less than 50 elements, and I want to find the
first element greater than a certain value, is a simple linear search
the best here(for loop from beginning to end of array)? It seems like
some other search algorithm like binary or whatever would be of no
benefit on this data set.
| |
by: admyc |
last post by:
Hello
Is there a way to get a website whose index.html document is just the
document that sets-up the framesets and frames to contain info so that
google will reference it?
Any help much appreciated
AM
|
by: Russell |
last post by:
Hey,
ok i have numerous tables to search through for a 'site search'.
some of the searchble fields have html embeded within so after some quick
referencing, saw I can use the regExp function to strip out all the HTML
leaving only the raw text.
(done and works a treat)
My issue is:
|
by: zakhirn |
last post by:
Hello,
I have an extremely new user to XML and XSL, and I would like to know
how to search XML data via a input form in HTML, and have the results
displayed in HTML.
If anyone has code that can do that, could you please post the relevant
files. I have searched the internet for a while now, and have been
completely unsuccessful. I would like to do it using javascript in my
XSL file if possible, or using CGI script to search the XML...
|
by: Jeff |
last post by:
I have a db that has a couple of times closed Access completely when Saving
work.
So I usually compact and decompile and this seems to fix the problem. But
not his time. It has come back again.
But my query is this. How come I can keep reducing file size?
I decompiled, recompiled and compacted the db 3 times and each time the
file size reduced. First time from 13.5 MB to 7.5 MB. Second time down a few
|
by: Piero 'Giops' Giorgi |
last post by:
Hi!
I have the DB, now with about 100 Million rows (Going strong!!!)
One question... I'm using Thesaurus search (COOL!!!) for names,
(Rebecca, Reba, Becky, Becca....) to query for nicknames ans Synonyms,
but I don't seem to be able to update the XML file in real time.
The file is like this:
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |