MYSQL :: Performance MySQL With ADO VB

Large Table Performance Problem With MYSQL Performance
I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results.

Environment
Emachine 6417 with amd3200+ 64 bit processor 3.3GB ram

mysql 4.1 with odbc 3.51 MYISAM
windows 2003 server std edition
date, account and invoice number are indexed
Database size 18 million rows

I am querying (selecting) columns of a date and an account

our tester program that opens a socket to the Mysql database and does a select for the above n times
each time the date and the account is randomized to minimize hits on records closeby.
This program will perform over 1000 queries per second.
At the end, the Mysql socket will be closed
Even when the socket is closed each time, I still get 400 queries per second.

When I enter a similar query manually a web interface, I get about 3-9 second response time. This program opens/closes a socket for each query when using EMS I get similar 9 second results.

Does anyone have any suggestions Also in production, this table will be accesse for both read and write will I have problems. My testing showed that Innodb is much slower.

MySQL Performance
i'm running a webserver with the following configuration:

P4 2,4 GHz | 1 GB Reg. ECC RAM PC266 | 2 * 18 GB SCSI RAID 0
Apache 1.3.28 (mod_ssl, mod_gzip) | PHP 4.3.2 | MySQL 4.0.12

This is the current server usage (200 parallel visitors):

9:57pm up 4 days, 1:45, 3 users, load average: 5.84, 5.48, 7.16
393 processes: 392 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: 14.2% user, 85.7% system, 0.0% nice, 0.0% idle
Mem: 1031008K av, 1017828K used, 13180K free, 0K shrd, 29856K buff
Swap: 1044216K av, 248428K used, 795788K free 630368K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
18508 mysql 15 0 326M 116M 14492 S 43.7 11.5 7:11 mysqld

It seems to me to be a too high load for this hardware configuration.
Sombody an idea?

MySQL 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.

MySQL Performance
Our database contains about 27,000 records only and it is very slow. Can someone please suggest any tips to increase the performance? We used InnoDB.

Mysql Performance
The case is, i need to create a website with about a quarter million records
database.

I'm not sure about the mysql+php performance.

The other option would be asp.net with sql server 2005.

Could anyone give me your opinion about using mysql+php in this case, is it
worth?

MySQL Performance
We ran MySQL (4.0.15-standard, 64-bit, SuSe 8.0) benchmarks
on 2 CPU and 4 CPU Opteron machines. Mostly we tested insertion
times with many simultaneous connections (hundreds). To our
surprise we did not find significant differences.Each of our servers (2 and 4 CPU machine) has one 1Gbit network
connection. I have feelings that the machines are the network
bounded, not the CPU bounded. Could it be true? How to verify it?

Mysql Performance
I cannot find MySQl performance info at linux "top" command. I already
tested on slow MySQL query that took more than 5 minutes (create
fulltext index), but during the 5 minutes, i cannot find any mysql
process at top command. Below is the system information:

Linux version: 2.6.9-1.667smp (Red Hat 3.4.2-6.fc3)
MySQL version: 4.1.11 (source installation)

MySQL Performance
I have a web application which uses MySQL and I wanted to check out the performance of the database.
I have been trying to use JMeter to test the performance of it but I was wondering if there is anything MySQL specific which can give me statistics on performance etc.
Just wondering what other devshedders do

Ajax Performance With Mysql?
I'm trying to tweak the performance of a php script, but I don't understand how mysql handles connections, so I'm not sure if I should use standard sql statments, prepared statements or stored procedures.

The php script is called by an ajax file, which will be executed very often. Right now each time the script is called I create a new connection (username, password, etc...) and at the end of the script I specifically close it (mysqli_close($con)).

Profile Performance On MySQL
I'm looking for recommendations on an app/function of mysql that will show a profile of the performance of my interaction with MySQL.

I'm a newb to php/MySQL, but not to programming/db. I'm about to launch a site and I'd like to look at a profile of the queries I'm running and if they are using indexes properly/doing full table scans, and other performance related items.

I've read up on the explain and benchmark functions, but they seem to require that the query is passed through them.

I'd prefer something more global like MS SQL's profiler that captures all of the actions, time expended and execution plan. Then I can look through it and see if any of the actions are taking longer than x time and then investigate further.

Does anything like this exist?

FYI, I'm on a shared hosted db server.

MYSQL Performance Issue
The following prg keeps cpu usage as 79% always .When i kill/stop the
prg,then cpu comes down.

main()
{
//code for mysql conn
while(1)
mysql_real_query (&mysqlcon, "SET CHARACTER SET 'utf8';", 25);
}

What might be the reason? Wil it be any config issues?

Mysql Query Performance
Basically, I need to exectute a query, when I execute the first query (with left join), it takes nearlly 4 mins 29.88 sec and the same query (with right join), it takes nearlly 0.22 sec.

I know I might be wrong somewhere....

MySQL Tables Performance
I have a table where misc data are stored. Right now this table has
about 30 columns, but for sure it will be more in the near future. So I
wonder how the big number (50-100) of table's columns affect for MySQL
DB performance. Maybe it's better to create more tables rather than more
table's columns ??

Optimizing MySQL For Performance
I am going to be running mySQL on a RAID5 array and am wondering if I
need to worry about splitting up the data and log files on to seperate
channels on the RAID array.

I have heard differing arguments. Some people say that you can install
SQL on the same logial drive as long as you are running RAID5 and not
worry about performance issues.

Others say that you need to split everything up even if you are
running RAID5.

Do I need to split it up in to 2 channels and 2 logical drives or is
one drive OK?

MySQL Performance Issue
Im running mysql4.0.13 on FrreBSD 5.1-release. The problem is MySQL is using 95% of the cpu leaving 0% idle and pushing the load avg up to > 1. This is the output of mysqladmin processlist..

$ mysqladmin -uroot -p processlist;
Enter password:
+--------+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------+----+---------+------+-------+------------------+
| 387980 | root | localhost | | Query | 0 | | show processlist |
+--------+------+-----------+----+---------+------+-------+------------------+
$ uptime
8:51PM up 82 days, 5:18, 2 users, load averages: 1.03, 1.07, 1.23

I normally would expect a slow query or a table that requires optimisation, but without any hints from the processlist, where can i start looking?

Problem With MYSQL Performance With ASP.NET
Large table performance problem with MYSQL performance with ASP.NET

I am doing some benchmarking of an application using MYSQL under
Windows and I confused about the results.

Environment
Emachine 6417 with amd3200+ 64 bit processor 3.3GB ram

mysql 4.1 with odbc 3.51 MYISAM
windows 2003 server std edition
date, account and invoice number are indexed
Database size 18 million rows

I am querying (selecting) columns of a date and an account

our tester program that opens a socket to the Mysql database and does a
select for the above n times
each time the date and the account is randomized to minimize hits on
records closeby.
This program will perform over 1000 queries per second.
At the end, the Mysql socket will be closed

When I enter a similar query manually a web interface, I get about 3
second response time.
This program opens/closes a socket for each query

Does anyone have any suggestions

Your assistance would be greatly appreciated

Also in production, this table will be accesse for both read and write
will I have problems. My testing showed that Innodb is much slower.

Analysis MySQL Performance
What is the best way to analayse the performance of an operational MySQL server?
Are there any good stats programs out there that show more than the variabels listed by MySQL (And phpMyAdmin)?
Are there any good tutorials about what figures to be alert for, and what should be ignored?In particular I'm facing a number of connections issue and need to isolate what is causing so many connections, and why so many stay in sleep mode.Database amostly use PEAR to connect to MySQL, are there any common pitfalls with PEAR and numerours connections?

MySQL Performance Monitor
Wanting to do some performance testing of MySQL on my Windows 2003 Server, and was wondering if there was anyway to setup some basic MySQL performance counters to monitor and log in perfmon? Things like queries per second, connections, traffic, etc? Doesn't have to be a permon counter, just something that can log usage values to a CSV at one second intervals

MySQL Performance Maintenance
can anyone please tell me how I keep the performance of the data within my MySQL database optimized? Ie, will the data over time become defragmented and therefore need to be optimized via (for example) an unload process, then a sort then a reload?
Most of my selects will need to order the data in alphabetical order (I am storing business names and addresses and returning the data in business name order) and I was wonderring if I needed to perform some process to reorder and reload the data periodically to maintain performance. I am expecting approximately 8000 rows of data.

MySQL Performance Question..
I'm running a server with a Pentium 133 w/32meg ram, 512 pipeline burst,
with a wd 512MB HD and I want to store George Bush in our MySQL database.
As far as table definitions are concerned, should I use a BLOB or should I
store him on disk and make a reference to the physical location in the MySQL
table instead?
Will I run into any performance/storage issues when querying this data?

Tips For Improving The Performance Of Mysql
I really hope I can get some general advice and suggestions about how I can improve the performance of our mysql server.

We have a dedicated webhost running a number of quite highly used websites. They all use mysql quite heavily at times. Recently we've had complete mysql gridlock and had to restart the server. What are the steps I can take to improve performance?

This is what i've done so far.

1. Optimise queries - i've been checking the queries we are running and trying to cache the results in a session rather than generating all the time. I've also installed something called mytop which is basically the same as the standard unix top command, except for mysql processlist and shows you the queries currently running and the time elapsed.

2. I have run explain on my queries and put in indexes all over the place to try and make lookups quicker.

Things i'm considering doing

1. Reducing the amount of data in the database and perhaps rotating the tables slightly, our biggest tables are approaching 2 million records and linking these together is i'm sure causing some of the problem, especially when we have to run a SUM query on them, even with good indexes.

2. Moving the database off the webserver and onto its own dedicated mysql server.

3. Load balancing the database and somehow clustering more than one database server, but this would cause serious headaches so not seriously considering unless it was thought the best solution.

4. Upgrading mysql... We are currently running version 4.0.27, should we upgrade to 5? Will this give us performance boost?

5. Re-compiling... After a google search I discovered that potentially we could recompile mysql with a better options set and perhaps improve performance.

6. Buying a book on mysql optimisation Any suggestions?

Mysql Too Many Connections Performance Issues
I have a website running on a linux/apache/mysql/php
server. I receive about 8,000-10,000 visitors a day with about 200,000
to 300,000 page views. The server is a RedHat Linux server running PHP
5.x, MySQL 5.x, Apache 2.x

We have been suffering from a number of performance issues. Our
hosting company has set our max connections to 100, and we are using
persistent connections in PHP. At times the mysqld process takes 100%
of the CPU. We have also been suffering from
mysql_pconnect(): Too many connections errors. What can I do to fix
these issues?

When I run a top on the server I see this ...
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
5567 mysql 16 0 169m 42m 4236 S 81.2 4.2 3078:09 mysqld
32539 apache 15 0 42676 25m 7708 S 1.0 2.5 0:03.40 httpd
32572 apache 15 0 42680 25m 7708 S 1.0 2.5 0:02.98 httpd
32608 apache 15 0 42680 25m 7704 S 1.0 2.5 0:00.72 httpd
32542 apache 15 0 42704 25m 7712 S 0.7 2.5 0:03.38 httpd
32561 apache 15 0 42732 25m 7712 S 0.7 2.5 0:02.61 httpd
32567 apache 15 0 42680 25m 7708 S 0.7 2.5 0:02.60 httpd
32591 apache ...................................................

MySQL General Query Log And Performance
I'm wondering how big impact on performance would have turning on the general query log. I read in manual it should be turned off in production environment to increase the speed but I didn't find any information about performance (how much cpu/disk usage it would take). Is it like 1% performance lost or is it bigger?

MySQL Performance - Option Setting
I set OPTION=3 in my connection string as recommended by MySQL
(http://dev.mysql.com/doc/refman/5.0...parameters.html)
for a VB application.

OPTION=3 is Don't Optimised Column Width (1) and Return Matching Rows (2).

I found this to be very slow on a database with just 27,000 records. It took
approx. 12 seconds to read 27,000 records.

When I change the OPTION to 8 (Allow Big Results), it took just 3 seconds to
read 27,000 records. BUT now my application cannot update records anymore
and getting random errors on transactions.

I try setting OPTION=11 (all 3 options added together) and the speed went
back to being slow.

Mysql Performance On Large Joins (its Bad)
Mysql is unable to perform joins with large tables ON VARCHAR.

these joins run fine when the queriers are performed on INT values. However int values are not practical in this instant because I am joining on product SKUs.

sample SQL
-----------------
SELECT a.sku AS this, b.sku AS that, b.price, b.SRP
FROM table1 a
INNER JOIN table2 b
ON a.sku LIKE b.sku

TABLE specs
-------------------------
table1 14000 records
table2 13000 records

Running Mysql 4.1, PHP 4.3.

Slow Performance After Falling Back To MySQL 3
Yesterday I have upgraded my linux box with a pair of Xeon 1G CPU and
reinstalled everything (it was a sinle Xeon 700). The default RH9
installation comes with MySQL 3.23.54. After the reinstallation I
found the machine was not as responsive as before. (It's not very
slow, but some lagging is noticed)

I was running Mysql-max 4.0.11 and the performance was ok, except the
slowness during the peak hours, which was regarded as normal. I would
like to know the differences between Mysql 3 and 4. Is the new version
making a significant jump in performance?

I also noticed the system eats up memory faster than before. With the
old setting the system never used up 60% of physical memory, even at
extremely high loading (30+). Now it easily used up 70% of memory.

The server is running Mysql only. It's equipped with 2 Xeon 1G and 2G
of memory. The kernel version is 2.4.20-8smp #1 SMP. Also attached the
my.cnf for a clearer picture: Code:

FreeBSD 5.4 And Poor Performance Using MySQL Administrator
It has been over 5 years since I have messed with FreeBSD and I have
never messed with MySQL until now so forgive me if my issue is trivial
and I thank everyone for the help.

The hospital I work for is finally getting off of Micrisift IIS to
server the Hospital website. i built a FreeBSD server with Apache
2.0.54 and PHP5 as well as MySQl 5.x.x. Running queries on the server
locally is fast as lighting as well as is creating db's and tables. How
ever if I use MySQL query or Administrator it takes what seems like
like about a minute or more to finally connect. I believe I have ruled
out all network issues as this server also sits in our corporate dmz by
attempting to connect directly to the server via cross over cable with
a laptop.

Question About MySQL Performance On 2 And 4 CPU Opteron Machines
We ran MySQL (4.0.15-standard, 64-bit, SuSe 8.0) benchmarks
on 2 CPU and 4 CPU Opteron machines. Mostly we tested insertion
times with many simultaneous connections (hundreds). To our
surprise we did not find significant differences.

Each of our servers (2 and 4 CPU machine) has one 1Gbit network
connection. I have feelings that the machines are the network
bounded, not the CPU bounded. Could it be true? How to verify it?

For Better Performance :: Should I Store Word Files In MySQL Or In Normal Folders ?
I am working on a web application and part of it is functionality

is that the user can upload a PDF / DOC file as part of his account info .

should i store that file in the MySQL DB or in a normal folder in the server ?

which is the best for performance ?

i read some text in some site that says:

MySQL Performance: Less Rows With Comma Delimited Lists Or More Columns?
I'm storing results from tests students have taken.

In the table where they'll be stored, I'm contemplating whether I should focus on keeping more columns and less rows, or if more rows would be better.

I'm on a shared server, so size is of concern to me here as well.

More rows would make it easier to reference the data as I'll be doing a lot of cross-referencing, but if I could choose between 1,000 rows for the data or 20,000 rows it seems that 20,000 would put me at a disadvantage for size and speed, even if there are only 3-4 SMALLINT columns per row.

Are rows more expensive to a server than columns? Going for less rows would mean moving from SMALLINT to VARCHAR fields (as they'd now be comma delimited lists possibly greater than 64 values [so no set]), but would drastically cut down the rowsize.

Performance Of LIKE
I'm weighing the performance of materialised path vs. nested set algorithms, for storing a tree structure. The tree will have a lot of searches, a fair amount of inserts, but probably fairly few move-operations. The decisive factor seems to be the speed of a LIKE-query. I know, that regular expressions perform pretty well, as long as they are anchored. From this, I deduct, that LIKE must be able to perform equally good, since a path is indeed anchored. The questions is, if a LIKE will always result in a full scan, or if it can utilise indices? If it doesn't allow use of indices, would it be worth it, to use an equality comparison, on a substring (Which is essentially, what I'll be querying for) or am I just entirely missing the point?

Performance
I'm developing a PHP script and using MySQL.

What is better, have one database with many tables por all accounts (example: account1_table1, account1_table2... account2_table1....) or one database for each account?

Is there many differences in performance?

Which Is Better In Performance
country

(id) country
(1) America
(2) Germany
(3) Canada
(4) China
(5) Egypt
(6) Russia
(7) Korea
(8) France
(9) Brazil

city

(id) city
(1) Berlin
(2) New York
(3) Cairo
(4) Paris
(5) Seoul
(6) Peking
(7) Chicago
(8) Moscow
(9) Denver
I have two tables like the above.

And I have one of these tables below.

message1

(id) (city) message
(1) (2) I love New York.
(2) (5) Kumkang mountains are beautiful.
(3) (4) I was born in France.
(4) (6) Great Wall is great.
(5) (7) Chicago is a big city.

message2

(id) (city) (country) message
(1) (2) (1) I love New York.
(2) (5) (7) Kumkang mountains are beautiful.
(3) (4) (8) I was born in France.
(4) (6) (4) Great Wall is great.
(5) (7) (1) Chicago is a big city.

code1

select city, country
from message1
left join city on city.id=message1.city
left join country on country.id=city.country

code2

select city, country
from message2
left join city on city.id=message2.city
left join ccountry on country.id=message2.country
I think The codes above have a same result.(sorry, I didn't test it.)

Performance
I have a "performance" related question.

I am developing an events calendar with php and mysql. In the calendar I want any day that has events to be a link. Therefore just before a day is added, I want to query the database to see if any events occur on that date.

Is it bad practice to query the database that much? In this case it'll be hit 30 or 31 times. also, what takes up less space in the db... the mysql date data type or the php timestamp?

Performance
I have 3 tables: books, images and books_xref_images (with the xref
table merely matching up bookIDs and imageIDs.

Assuming I only want information on one book (and it's image) would
it be quicker to do 3 LEFT JOINs in one query....

.... or 2 distinct queries, with the first retreving the book info and the
second getting the image info?

Performance
One of my customers runs MySQL. They encounter performance problems. It looks to be the storage, although they run on performant EMC storage (15000 rpm disks, RAID-1). I asked them how their MySQL is setup and they run MyISAM for nearly all tables, altough their application is typical OLTP. It is in my knowledge that you should use InnoDB for OLTP databases, or am I wrong.

My customer told me that they did tests with MyISAM vs InnoDB and he told me MyISAM was more performant. Is that possible ? I can imagine that they never will have enough server memory to keep all tables of their 50GB database in memory.

If there is not enough server memory to keep all tables in memory via MyISAM, what will happen ? Swapping, resulting in loads of extra I/O ?

Best Performance For WHERE LIKE AND ..
I'm doing heavy searches on a table with statements like

SELECT * FROM thetable WHERE col1 LIKE '%ab%' AND col1 LIKE '%cd%'

.. and so on. I need to search for keywords shorter than 4 characters, so I can't use fulltext MATCH-AGAINST. When searching using WHERE-LIKE -method above, should I use FULLTEXT or INDEX in the column in question?

Performance
i am having a problem at the moment with my pc. i keep getting performance issues when running mysql queries and when i go in to task manager
mysqld-nt.exe is always quoted as a very high number on cpu.

is there something i may have done incorrectly to cause this.

i have been using php/mysql for a good while now and have never came across this problem before?

it is the same regardless of which queries i am running (including those which always worked fine before).

Best For Performance
It's a web application, accessed via internet, with around 100.000 clients and
each client have around 10 users. This web app have a database of around
100 tables.

Ok, the question is: Is it better to have each client with it's own catalog on
the database (each catalog with around 100 tables) or is it better to use one
catalog for all the clients (the tables would have a lot more records this way,
but only one catalog)?

I'm considering performance.

Performance
I have problems with mysql-performance. When I check "Status", there is written: 23% of all questions are "change db". What does it mean? Select Task are only 13%. Could this be the reason of performance problems?

Performance - Select *
I'm trying to optimize an application, which is putting way too much load on the rdbms. Currently most queries select *, but I'm wondering how much it matters in terms of performance to specify the exact number of columns needed ? Obviously, I'd transfer less data from rdbms to application layer, but apart from that is there anything to archieve ?

Performance Troubles
I'm having some trouble with optimising my database queries. I've recently been making use of a feature that my host provides that lets me create reports on any queries that are taking more than 1 second to generate. Quite honestly I'm not sure if it could be the table structure, indexing issues or just the overall query that's horribly coded.

I've attached the query report from the server in a post attachment. As you can see I like using my inner joins, hehe.

Here are the table rows, in case you're wondering how big tables are relative to each other:

archive - 428,720 rows
blo - 3,741 rows
users - 3,945 rows

It might be useful information.

So yeah, basically what can I do, if anything, to get these queries to move along. Points would be awesome.
:-)

Performance Question
CREATE TABLE __testpages (
rowid int(11) NOT NULL auto_increment,
user varchar(255) NOT NULL default Ɔ',
pagebyurl varchar(255) default NULL,
timebegin int(10) NOT NULL default Ɔ',
pagenum int(5) NOT NULL default Ɔ',
lastpage tinyint(1) NOT NULL default Ɔ',
day char(2) default NULL,
month char(2) default NULL,
week char(2) default NULL,
quarter char(2) default NULL,
year varchar(4) default NULL,
PRIMARY KEY (rowid),
KEY user_2 (user,`year`,`month`,den),
KEY user (user)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

and I genarate 15,000,000 rows. Then I execute the query:
SELECT COUNT(*) FROM __testpages where user='xxx'

the user index cardinality is 11

this query returns between 17,000 and 11,000,000 rows depends on the 'xxx' and takes between 5 and 20sec on my mashine. Is it normal or it is too slow?
I am running the mysql server on dedicated server I don't know exactly its parameters

Low Performance With Linux SMP
I use
#mysql -V
mysql Ver 12.21 Distrib 4.0.15, for suse linux (i686)

with a SMP-kernel

When I used this version with a kernel not SMP mysql was very very fast but now
with the SMP-kernel the mysql is very very slow. I takes several minutes to make an INSERT on a table with near 50.000 Records

Is there any problem with mysql an SMP? is a problem of the version I have installed?

Performance Questions
My company make several flash-based games, and I use php to communicate
with
mysql to provide highscore-lists.

My problem is this:
When I save a player's score in the mysql-table, I want to find which
place
the player got with his score (today). To get this I have tried two
different solutions, which both works, but are very ineffective: (The
Time-field is a DateTime type, and I have Score and Time as Indexes.
The Score-field is a DECIMAL (20,2))

1) SELECT COUNT(*) FROM table WHERE Score>=$score AND Time>CURDATE().
- or -
2) SELECT Score FROM table WHERE Score>=$score AND Time>CURDATE().

.... $place = mysql_num_rows($result)

Both give the right result, but the problem is that when there are many

players playing at the same time, and the table consists of several
million
records, the query is just too heavy for the mysql-server, and it
breakes
down.

So my question is: Are there any better ways of getting a player's
place?

SET Datatype Performance
I have a â€œlittleâ€ performance problem using the SET datatype.

I have a database with over 800,000 rows which have 34 columns which are enum(â€˜0â€™,â€™1â€™) to describe various categories.

So when I search for a certain category or categories the search would be like:

Select count(id) from my_table where cat1=â€™1â€™ and cat10=â€™1â€™ and cat25=â€™1â€™;

If cat1 is indexed and cat10 is not.

Select count(id) from my_table where cat=â€™1â€™;

would return data in less than a sec while

select count(id) from my_table where cat10=â€™1â€™

might take over 5-6 secs to return the value.

Since I canâ€™t index all 34 category columns, due limitations of how many indexes I can create (there are other fields that required index besides the categories) I looked into how to do this more efficiently. I came up with the idea if using the set datatype. I created a test table

CREATE TABLE set_test(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
cats SET('cat1','cat2','cat3',â€¦â€¦'cat34')
);

I loaded it up with 800,000 values from my main table and searches like

Select count(id) from set_test where cats&1;

Would return the count in less than a second.

So far so good. So I added a new column to the main database with the â€˜catsâ€™ column, containing the same info I had in the set_test table. To my surprise:

Select count(id) from my_table where cats&1;

Would take over 6 secs to return the value.

Or course the table has 45 columns (including the old 34 category columns) instead of two, but does it really make that much of a difference? If so, what can I do to get a decent performance?