Hi ALl,
I have a speed problem with MySQL. I have a big table with over 2 million records in it. It has a non-unique index on one of the fields called City_Code, which has a type char(3) I need MySQL to quickly count the records with specific values and I found out a strange behaviour. There are for example over 500,000 records in the database with City_Code='LAX' and only about 12,000 with City_Code='LON' No if I try this query: SELECT COUNT(*) from Fares WHERE City_Code='LON' - it excutes in under 1 second However if I try the smae query but with LAX in the WHERE criteria: SELECT COUNT(*) from Fares WHERE City_Code='LAX' - it takes over 2 minutes to count the records!!!! Yes there are over half-a-million records there with City_Code='LAX', but I suppose the whole idea of a database index is to help anser such queries quickly... the weired thing is that is I try to do an "explain" query .i.e EXPLAIN SELECT COUNT(*) from Fares WHERE City_Code='LAX' - it gives me the number of rows that I need in 0.25 seconds, why it takes 2 minutes for that query to execute? Is it something specific to the table type? The table tabe was ISAM, I re-uilt it to be MyISAM and stil getting the same problem. How can I make my Count(*) queries to execute quickly regardless of number of rows matching the search criteria in large tables with millions of records?? MySQL version is: 3.23.37 Table type is MyISAM BTW: I am getting the same slow speed behaviour of MySQL if I am joining two tables on that field and appliing the same criteria to the join... Thanks in advance, Sam _________________________________________________________________ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
