n stats available from the tables.
If you do not want to use hints it may be hard to make MySQL to use the plan you’re looking for - playing with indexing may help sometimes, running ANALYZE on tables or changing optimizer related MySQL settings. There is no general solution though.
You may report it as a bug to MySQL but Optimizer bugs usually are not fixed quickly because these are limitations rather than true bugs 
Hi Peter!
how it is possible for the optimizer to get the rows wrong in explain, there are over 4 million rows where avtal=‘Huvud’ but the explain shows 138 929??? This is the total number of rows in table VAL_FAKTA_VH divided by rows in VAL_DIM_AVTAL, is MySLQ a litttle to smart for itself? Is there a workaround for this problem that allways work?
mysql> SELECT avtal.avtal, count(),sum(utfallkronor) FROM VAL_FAKTA_VH V , VAL_DIM_AVTAL avtal where V.avtalid = avtal.avtalid group by avtal.avtal;
±----------±---------±------------------+
| avtal | count() | sum(utfallkronor) |
±----------±---------±------------------+
| Geriatrik | 28262 | 473027.543976605 |
| Huvud | 4427905 | 20396597.2103366 |
| Övrig | 267429 | 1495763.62524932 |
±----------±---------±------------------+
3 rows in set (24.25 sec)
mysql> explain SELECT avtal.avtal, count(*),sum(utfallkronor) FROM VAL_FAKTA_VH V , VAL_DIM_AVTAL avtal where V.avtalid = avtal.avtalid and avtal.avtal = ‘Huvud’ group by avtal.avtal;
±—±------------±------±-----±-----------------------+ ---------±--------±--------------------±-------±-------- ----+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-----±-----------------------+ ---------±--------±--------------------±-------±-------- ----+
| 1 | SIMPLE | avtal | ALL | PRIMARY | NULL | NULL | NULL | 34 | Using where |
| 1 | SIMPLE | V | ref | Index_2,Index__avtalid | Index_2 | 5 | carro.avtal.avtalid | 138929 | Using where |
±—±------------±------±-----±-----------------------+ ---------±--------±--------------------±-------±-------- ----+
2 rows in set (0.00 sec)
Best regards,
TedThe number of rows you see in explain in second table is number of rows which is estimated to be examined for each row in the first table.
This estimation is done using cardinality, so in average there are 130K matching records for each row in the first table. There are no stats to help MySQL to know distribution is skewed.
Workarounds are cases specific as I mentioned. One need to take a closer look at your schema/data and basically play with it trying various workarounds to find one.
For example covering indexes often able to help you to use the index you’re looking for. Hi Peter,
I am new to MySQL. I installed MySQL 5.0 and used the InnoDB engine. It currently have just one table with 39 columns, combination of varchar, datetime, double, and text data-types. At the DOS prompt, I ran this simple query (“select * from main where name like ‘%abc%’ or description like ‘%abc%’ order by cost;) with 20k rows, it ran pretty fast (2 seconds); but when the table grows to 800k rows, the query took over a minute. I tried the EXPLAIN method, but not sure what those values meant.
Another thing, while the SQL is executing, I monitored the Windows Task Manger and saw the mysqld-nt.exe went from 27K Memory Usage to 306K, and ran out of memory on 512MB RAM. I also added a primary key field and index it, but it doesn’t help. I read through your “Why MySQL could be slow with large tables?” blog, but still not sure what to do. There must be a way to execute the sql faster because the table will grows to 2M rows or more. Please help and let me know what I need to do.
Thanks,
BryanYou should not use LIKE search for large sizes. Use MySQL Full Text Search or some external search engine.
And buy some good MySQL book or read MySQL Manual Online.
You’re asking a question which is well documented. Hi,
Thanks for information. I read the Full-Text Search functions in MySQL 5.0 reference manual before, and I’m sure it said the Full-Text Search functions ONLY apply to MyISAM tables and not InnoDB tables. The table is currently in InnoDB engine. You also recommended to use external search engine, please provide a list of search engine so I can look for it.
I apologize if I asked the question to again, but after spent three days reading stuff through reference manual and web, and it still doesn’t work, it can be painful.
Thanks again,
BryanTry out sphinx
Or create “shadow” MyISAM table for searchHey,
I’m fairly new to MySQL’s Clustering capabilities, though I am familiar with non-clustered installs. I have been doing performance comparisons between a few database systems, and with MySQL, and also its clustered counterpart. I have found in my tests that when dealing with character indexed queries and millions of rows, clustered queries run orders of magnitude slower than single MyISAM servers with the same information.
Is there something I need to know about the configuration that would increase this performance?
TylerIt depends on what query you’re running.
In reality it is quite often the case as NDB needs to perform many lookups over network which are slow. For